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.

Patsy

Answer 1

Check out

• Introduction to ADO.NET Tutorial
• ADO.NET Tutorial Lesson 1
• An introduction to ADO.NET

I'm sure there's plenty more out there - just google for "ADO.NET" and "Tutorial" ......

UPDATE:

If you want to connect to your local SQL Server Express, and connect to the "Northwind" database, and read the top 5 customers from the "Customers" table, you'd have to do something like this:

string connectionString = "server=(local)\SQLExpress;database=Northwind;integrated Security=SSPI;";

using(SqlConnection _con = new SqlConnection(connectionString))
{
   string queryStatement = "SELECT TOP 5 * FROM dbo.Customers ORDER BY CustomerID";

   using(SqlCommand _cmd = new SqlCommand(queryStatement, _con))
   {
      DataTable customerTable = new DataTable("Top5Customers");

      SqlDataAdapter _dap = new SqlDataAdapter(_cmd);

      _con.Open();
      _dap.Fill(customerTable);
      _con.Close();

   }
}

Now you would have all 5 top customers from your Northwind database in the DataTable and you can inspect them, print them out, manipulate them - whatever you want to do.

That's ADO.NET in action!

As for the details of the connection string - what options you can use and what it should look like, check out the Connection Strings web site - it has tons of examples and explanations.

Marc

Quarantine

Um, this may sound like a dumb question but how doesw ADO.NET relate to my question? – Patsy

ADO.NET is the .NET subsystem to connect from C# or VB.NET to a database like SQL Server, SQL SErver Express, Oracle or whatever – Quarantine

Ok, how do i use LINQ to SQL or similar with this? – Patsy

LINQ-to-SQL builds on top of ADO.NET - you don't "waste" anything if you first learn the basics of ADO.NET, and once you understand that, move on to LINQ-to-SQL or Entity Framework. – Quarantine

Answer 2

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.

Bobbybobbye

+1 from a fellow "downvote with no comment" recipient. There is nothing incorrect or unhelpful in this answer. – Faux

[Disclaimer: i didn't downvote it] How do i use an Entity Framework or Linq to SQL with this? – Patsy

lol. A bit of a stretch but now someone should come in and downvote for just opening and closing the connection without actually doing something with it. The real reason offcourse should only be divulged after enough people wonder why oh why... – Borate

Answer 3

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.

Octavie

Answer 4

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.

Lenitalenitive

Answer 5

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/

Utoaztecan

That deals with using an object as a database, i've set up an instance of SQL server express for that. – Patsy

EF seems a bit overkill just for a very small beginner's sample...... unnecessarily complicates things, in my opinion. Learn the basics of bare bones ADO.NET first! – Quarantine

I agree with marc_s.its always good to learn basics first and I dont think it will take much time also ( for ADO.Net ) – Utoaztecan

Answer 6

I would recommend using Microsoft's Patterns & Practices Enterprise Library. You would specifically be using the The Data Access Application Block.

An excerpt from MSDN:

The Data Access Application Block provides the following benefits:

• It uses the functionality provided by ADO.NET 2.0 and with it, you can use ADO.NET functionality along with the application block's functionality.
• It reduces the need to write boilerplate code to perform standard tasks.
• It helps maintain consistent data access practices, both within an application and across the enterprise.
• It reduces difficulties in changing the database type.
• It relieves developers from learning different programming models for different types of databases.
• It reduces the amount of code that developers must write when they port applications to different types of databases.

I've used this method for years and it's been very successfull thus far. Good luck!

Ochoa

Answer 7

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();
    }
}
}

Gaullist

Answer 8

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

Mita

Answer 9

You can use https://github.com/MohamadParsa/AdoDbConnection.Net and use the project as a project reference in your solution and enjoy. Also, you can explore DBConnection.cs file and copy class or method in your project. but ... for make a connection or disconnect to express you can use:

SqlConnection con = new SqlConnection();
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
string _ErorrString = "";
string _InternalErorrString = "";
private void Connect()
{            
    cmd.Connection = con;
    da.SelectCommand = cmd;
    try
    {
        string cs = "";
        cs = @"Data source=.\SQLEXPRESS;Attachdbfilename=|DataDirectory|\" 
        + DataBbaseName + ".mdf;Integrated security=true;user Instance=true";
        con.ConnectionString = cs;
        con.Open();
    }
    catch (Exception ex)
    {
        _ErorrString += "Erorr NO. : 100" + ", connection error.";
        _InternalErorrString += ex.Message;
    }
}
private void Disconnect()
{
    con.Close();
}

and to execution command and get results:

public DataSet RunAndGet(string sql)
{
    //first, make a connection
    Connect();
    //to hold and return results
    DataSet dataSet = new DataSet();
    try
    {
        //set command
        cmd.CommandText = sql;
        //run and fill results into the dataset
        da.Fill(dataSet);
    }
    catch (Exception ex)
    {
        _ErorrString += "Erorr NO. : 101" + ", internal error.";
        _InternalErorrString += ex.Message;

    }
    //finally closes connection
    Disconnect();
    return dataSet;
}

Berglund

Answer 10

SqlConnection documentation from Microsoft was mentioned in another answer. What is missing is that connection.Close() does not need to be called if used in the following way (example is directly from the link above):

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

namespace SqlCommandCS
{
    class Program
    {
        static void Main()
        {
            string str = "Data Source=(local);Initial Catalog=Northwind;"
                + "Integrated Security=SSPI";
            string qs = "SELECT OrderID, CustomerID FROM dbo.Orders;";
            CreateCommand(qs, str);
        }
        private static void CreateCommand(string queryString,
            string connectionString)
        {
            using (SqlConnection connection = new SqlConnection(
                       connectionString))
            {
                SqlCommand command = new SqlCommand(queryString, connection);
                command.Connection.Open();
                command.ExecuteNonQuery();
            }
        }
    }
}

Taken directly from the link:

To ensure that connections are always closed, open the connection inside of a using block, as shown in the following code fragment. Doing so ensures that the connection is automatically closed when the code exits the block.

Ablation