CODEWITHSUNDEEP
c#asp.net.netado.net
Bruce
Bruce

Reputation: 7

Connect to the Database with DbConnection, SqlConnection, SqlCommand, DbCommand, etc

I've looked into set up connection with database by using ado.net. I've been confused by DbConnection, SqlConnection, SqlCommand, DbCommand. Can someone tell me the difference?

I googled these terms, it seems like these DB prefix are the base class for these Sql prefix.

So when should we should these Sql prefix (SqlConnection) and when should we use DB prefix (DbConnection)?

Moreover, if I put the connection string in the App.config or Web.config, do I still need to use this kind of format for open and close the connection?

string connString = "Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;";

using (SqlConnection conn = new SqlConnection(connString))
{
}

Because after I've tried add the connection string in the App.config, it seems like I don't need to open and close connection anymore.

In addition, when to use SqlParameter? Can anyone give me an example?

Is that for passing the parameter from the stored procedure from database?

Is that more convenient to use the DataTable, DataSet or reader to pass the value?

Upvotes: 0

Views: 7372

Answers (2)

MooseMaster
MooseMaster

Reputation: 25

To elaborate on Marc's answer regarding parameters. You always want to use parameters so that whatever you pass in is always treated as a value. One benefit is that you won't have characters like a single quote causing your query to fail. An example of which I have in the code below. Another reason is to prevent SQL injection attacks. Wikipedia has some good examples https://en.wikipedia.org/wiki/SQL_injection.

    private void SingleQuoteExample()
    {
        string customerName = "John'Smith";

        SqlWithParameter(customerName); //This works

        SqlWithoutParameter(customerName); //This doesn't because we end up with
        //SELECT * FROM CUSTOMERS WHERE CUSTOMERNAME = 'JOHN'SMITH'
        //Which from SQLs point of view has an extra single quote
    }
    private void SqlWithParameter(string customerName)
    {
        string constr = "DataSource......";
        using (SqlConnection con = new SqlConnection(constr))
        {
            con.Open();
            string queryStr = "SELECT * FROM CUSTOMERS WHERE CUSTOMERNAME = @CUSTOMERNAME";
            using (SqlCommand cmd = new SqlCommand(queryStr, con))
            {
                cmd.Parameters.AddWithValue("@CUSTOMERNAME", customerName);
                //read stuff
            }
        }
    }

    private void SqlWithoutParameter(string customerName)
    {
        string constr = "DataSource......";
        using (SqlConnection con = new SqlConnection(constr))
        {
            con.Open();
            string queryStr = "SELECT * FROM CUSTOMERS WHERE CUSTOMERNAME = '" + customerName + "'";
            using (SqlCommand cmd = new SqlCommand(queryStr, con))
            {
                //read stuff
            }
        }
    }

Upvotes: 1

Marc Gravell
Marc Gravell

Reputation: 1063338

DbConnection describes a common API for talking to multiple databases; SqlConnection is a specific API for talking to SQL Server, that is a DbConnection. So you'd use SqlConnection if you know you're talking to SQL Server, and DbConnection if you want to talk about connections in the abstract "any server" sense. SqlConnection will give you more provider-specific APIs than DbConnection.

For parameters: basically, whenever you're passing input of any kind to any command. So: a lot. You may find tools like Dapper or EF easier.

For DataTable/DataSet: these should not be your default things to use any more. They still work, but usually any other approach is preferable.

Upvotes: 2

Related Questions