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

Question

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?

DbConnection is a base class for any other database connection. You have to explicitly open the connection. The using statement just ensures that resources are disposed of. — William Xifaras
– William Xifaras, Commented Jan 8, 2020 at 15:17

Marc Gravell · Accepted Answer · 2020-01-08 15:36:57Z

2

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.

answered Jan 8, 2020 at 15:36

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Bruce Over a year ago

For DataTable/DataSet. what are those preferable approaches?

Marc Gravell Over a year ago

@Bruce most of the emphasis these days is on regular POCOs - whether those are hand written, or generated for you by an ORM tool - and tools that do all the binding automatically; at the simple end, things like Dapper - at the complex (but powerful) end, things like EF, but with a whole range in between. Overview of Dapper (which is what we use here on Stack Overflow): github.com/StackExchange/Dapper

Bruce Over a year ago

but in my situation, I was trying to export the output as xml from the stored procedure from sql server. I was using the DataTable/DataSet to read the output. Would dapper or EF also works?

Marc Gravell Over a year ago

@bruce yes, simply

MooseMaster · Accepted Answer · 2020-01-08 17:22:04Z

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
            }
        }
    }

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related