How do I connect to a SQL database from C#?

Question

I am trying to write a local program management and install system for my home network, and I think I've got the technologies nailed down:

• C#/.NET/WPF for the client
• Lua for installation scripting support (through LuaInterface)
• SQL Server Express for maintaining a database of programs

However I'm unsure what specifically I'll use to connect C# to the database. Is there something built into the .NET framework for this? Bonus points if you have a suggestion on what I should use for interacting with said database.

Answer 1

To connect to SQL Server Express you need nothing but System.Data, which is a standard .NET assembly. Just use SqlXXX classes and you'll be done.

However, writing mundane ADO.NET code is very boring, so it's very common to use an ORM or less heavy-weight result-set mapper such as BLToolkit.

And finally, consider using SQL Server CE. This is a fully ACID-compliant single-file embedded database engine which supports pretty much any feature you can expect form an SQL RDBMS.

Answer 2

Currently the easiest way to connect to your database and perform queries in C# is LinqToSQL. It will save you a lot of headache as compared to using "old-school" ADO connections.

Answer 3

SqlConnection

object is made for this.

Eg:

SqlConnection conn = new SqlConnection(
    "Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI"); 

or

SqlConnection conn = new SqlConnection(
"Data Source=DatabaseServer; Initial Catalog=Northwind; User ID=YourUserID; Password=YourPassword");

conn.Open(); // opens the database connection

Edit:

After doing all your stuff you have to close the connection by

conn.Close();

Data Source: Identifies the server. Could be local machine, machine domain name, or IP Address.

Initial Catalog: Database name.

Integrated Security: Set to SSPI to make connection with user's Windows login

User ID: Name of user configured in SQL Server.

Password: Password matching SQL Server User ID.

Answer 4

Sure of course, you can just use the classes in System.Data.SqlClient, though most people will use an ORM. I use LLBLGen Pro.

Answer 5

I wish this will help just try these..

@CLASS

using System.Data;
using System.Data.SqlClient;

namespace WindowsFormsApplication2
{
class clsDB
{
    public SqlDataAdapter mDataAdapter = new SqlDataAdapter();
    public DataSet mDataSet = new DataSet();
    public SqlConnection mConn;

    public clsDB()
    {
        mConn = new SqlConnection("Data Source=(the data source);Initial Catalog=sample;User ID=(the id);Password=(the password)");
    }



    public void SQLDB(string strSQL)
    {
        try
        {
            mDataAdapter = new SqlDataAdapter(new SqlCommand(strSQL, mConn));
            mDataSet = new DataSet();
            mDataAdapter.Fill(mDataSet);

        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            mConn.Close();
        }

    }

    public void ClearRes()
    {
        mDataAdapter.Dispose();
        mDataAdapter = null;
        mDataSet.Dispose();
        if (mConn.State != ConnectionState.Closed)
        {
            mConn.Close();

        }

    }

}
}

@LOGIN

public partial class Login : Form
{
    clsDB x = new clsDB();

    public Login()
    {
        InitializeComponent();
    }

    private void btnSubmit_Click(object sender, EventArgs e)
    {
            x.SQLDB("select * from tbl_accounts where u_username ='" + txtUser.Text + "' and u_password ='" + txtPass.Text + "'");
            if (x.mDataSet.Tables[0].Rows.Count > 0)
            {
                Main a = new Main();
                this.Hide();
                a.Show();
            }
            else
            {
                MessageBox.Show("wrong username or password");
            }
    }

@MAIN ACCESS

namespace WindowsFormsApplication2
{
public partial class Main : Form
{
    clsDB x = new clsDB();

    public Main()
    {
        InitializeComponent();
    }

    private void btnAdd_Click(object sender, EventArgs e)
    {
        x.SQLDB("insert into tbl_info (u_lastname, u_firstname, u_middlename) values ('" + atxtLN.Text + "','" + atxtFN.Text + "','" + atxtFN.Text + "')");
        fillgrid();
    }

    private void Main_Load(object sender, EventArgs e)
    {
        x.SQLDB(" select * from tbl_info ");
        dgv1.DataSource = x.mDataSet.Tables[0];
        fillgrid();
    }
    void fillgrid()
    {
        x.SQLDB("select * from tbl_info");
        dgv1.DataSource = null;
        dgv1.DataSource = x.mDataSet.Tables[0];
    }
    void search()
    {
        x.SQLDB("SELECT * from tbl_info where u_id  like '" + etxtID.Text + "%' order by u_id");
        if (x.mDataSet.Tables[0].Rows.Count > 0)
        {
            x.mDataAdapter.Fill(x.mDataSet, "tbl_info");
            dgv1.DataSource = x.mDataSet.Tables["tbl_info"].DefaultView;

            etxtLN.Text = dgv1.Rows[dgv1.CurrentRow.Index].Cells["u_lastname"].Value.ToString();
            etxtFN.Text = dgv1.Rows[dgv1.CurrentRow.Index].Cells["u_firstname"].Value.ToString();
            etxtMN.Text = dgv1.Rows[dgv1.CurrentRow.Index].Cells["u_middlename"].Value.ToString();
        }
        else if (etxtID.Text == "Type User ID to Edit")
        {
            etxtLN.Text = "";
            etxtFN.Text = "";
            etxtMN.Text = "";
        }
        else
        {
            etxtLN.Text = "";
            etxtFN.Text = "";
            etxtMN.Text = "";
        }
    }
    private void etxtID_TextChanged(object sender, EventArgs e)
    {

    }

    private void etxtID_Enter(object sender, EventArgs e)
    {
        etxtID.Text = "";
        etxtID.ForeColor = Color.Black;
    }

    private void etxtID_Leave(object sender, EventArgs e)
    {
        if (etxtID.Text == "")
        {
            etxtID.ForeColor = Color.Gray;
            etxtID.Text = "Type User ID to Edit";

            x.SQLDB(" select * from tbl_info ");
            dgv1.DataSource = x.mDataSet.Tables[0];
            fillgrid();
        }
    }

    private void etxtID_KeyUp(object sender, KeyEventArgs e)
    {
        search();
    }

    private void btnUpdate_Click(object sender, EventArgs e)
    {
        x.SQLDB("UPDATE tbl_info set u_lastname ='" + etxtLN.Text + "', u_firstname ='" + etxtFN.Text + "', u_middlename ='" + etxtMN.Text + "' where u_id =" + etxtID.Text);
        MessageBox.Show("Operation Successful!");
        fillgrid();
    }

    private void btnDelete_Click(object sender, EventArgs e)
    {
        x.SQLDB("delete from tbl_info where u_id =" + dtxtID.Text + "");
        MessageBox.Show("Operation Successful!");
        fillgrid();
    }
}
}

Answer 6

You can use ADO.Net and System.Data.SqlClient namespace for the same. I will advise you to go with Entities framework (ORM). Please find below links for Entity Framework walk through

http://thedatafarm.com/LearnEntityFramework/tutorials/creating-an-ado-net-entity-framework-entity-data-model/

http://thedatafarm.com/LearnEntityFramework/tutorials/use-an-entity-framework-entity-as-a-winforms-data-source/