SQL - Parameterized

Question

I'm trying to use a SQL request. Therefore I'd like to use the interface IDbCommand

I've tried to add the requested value with parameters, but if I use my script, the query looks like

SELECT * FROM DB WHERE Data = @val

Then I get a SqlException.

Can someone help me?

// "Using": - To make sure that the connection get closed correctly!
using (IDbConnection con = new SqlConnection(connectionstring)) 
{ 
    try 
    {
        /*
         * Use this for Initialize that it is a SQL Server, 
         * IDbConnection is for every connection
         */

        IDbCommand sql = con.CreateCommand();

        var exprParam = sql.CreateParameter();
        sql.CommandText = comand;
        exprParam.Value = value;
        exprParam.ParameterName = "@val";
        sql.Parameters.Add(exprParam);

        con.Open(); 

        // Connect to DB. Timeout: 15 Sekunden
        IDataReader rdr = sql.ExecuteReader();  

        // Build DataReader
        while (rdr.Read())  
            // Read data from console
            numberOfRows = Convert.ToInt32(rdr[0]);
     }
     catch(Exception ex)
     {
     }
}

connection string look like `SELECT * FROM DB WHERE Data = "@val`??? you must be kidding ... :) — Sachin, Feb 04 '14 at 18:09
You need to understand the difference between a connection string and a query. — SLaks, Feb 04 '14 at 18:13
You could also save yourself a couple lines of code by using [`Parameters.AddWithValue`](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparametercollection.addwithvalue(v=vs.110).aspx): `sql.Parameters.AddWithValue("@val", value);` — valverij, Feb 04 '14 at 18:27
"SELECT * FROM DB WHERE Data = "@val" is only a symbol for my sql statemant. It's not nessecary for my question how the query exactly looks like. I know the difference between connection string and query :) — Prog1511, Feb 04 '14 at 19:16
ooh .. now i see my mistake "SELECT * FROM DB WHERE Data = "@val" is of course the query :) not the connectionsstring^^ — Prog1511, Feb 04 '14 at 19:19

Chandan Kumar · Answer 1 · 2014-02-05T18:16:20.277

1

Modify your code with this :

     try
     {
        string value = "text value from your control";
        int numberOfRows = 0;

        //For Sql Server Authentication
        string connectionString = 
                @"server=yourservername;InitialCatalog=yourdatabasename;
                                          User Id=sa;Password=yoursqlserverpassword";

         //For Windows Authentication
        string connectionString = 
                @"server=yourservername;InitialCatalog=yourdatabasename;
                                         Integrated Security=SSPI";

        using(SqlConnection cn = new SqlConnection(connectionString))
        {
            cn.Open();
            string query = "SELECT * FROM DB WHERE Data=@val";

            SqlCommand cmd = new SqlCommand(query, cn);
            cmd.Parameters.Add(new SqlParameter("@val", value));

            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                numberOfRows += Convert.ToInt32(dr[0].ToString());
            }
        }
     }
     catch(Exception ex)
     {
        //Handle your Error - or show it if required
     }

edited Feb 05 '14 at 18:16

answered Feb 04 '14 at 18:35

Chandan Kumar

4,570
4
42
62

Is it possible to use IDbCommand instead of SqlCommand? I'd like to work with the Interface. Because if i may change the database behind i don't want to change much in my source code. – Prog1511 Feb 04 '14 at 19:13
1

If you change your db, you'll have to change SqlConnection (your code uses one!), SQLCommand, SQLParameter and SQLDataReader. Using IDBCommand makes no practical difference. – simon at rcl Feb 04 '14 at 19:18
@kumarch1 - SqlCommand inherits from Component and SqlDataReader implements IDisposeable, so you'd benefit from additional using blocks here. In addition, you should only catch base Exception in very rare circumstances, and arguably never if you're just going to eat it: http://stackoverflow.com/questions/114658/catching-base-exception-class-in-net – Dave R. Feb 04 '14 at 22:33
But my customer would like to do it. Is it possible to do this with the interface only? – Prog1511 Feb 05 '14 at 11:57

score 0 · Accepted Answer · answered Feb 05 '14 at 12:52

The .NET Framework's data access interfaces are not designed for you to use directly. Rather they are to promote consistency when building new data providers (examples of data providers are System.Data.SqlClient, System.Data.OleDb and so on). This means that writing code to access, for example, an Oracle database is similar to code for working with SQL Server data. The amount of code you'd need to change when switching data providers is already minimal because of these interfaces, but it can never be 100% generic when using providers directly.

If your client is keen on having the flexibility of changing the data access strategy in the future, it would be worth you doing some research on splitting your application into different layers, specifically separating the data access logic from your client code which uses that data. You may also want to consider using an ORM such as Entity Framework.

To answer your more pressing question regarding your SQL query, the following code should get you started:

using (var con = new SqlConnection(connectionstring))
{
    con.Open();

    using (var cmd = new SqlCommand("<YOUR QUERY TEXT>", con))
    {
        cmd.Parameters.AddWithValue("@val", yourValue);

        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                ...
            }
        }
    }
}

@Prog1511 - You're welcome. I'm glad you got it working :) To answer your question about interfaces, you're right - you don't call them directly. You work with code which implements the interface instead. Interfaces act like contracts which implementers follow. There's a decent book chapter about interfaces here: http://msdn.microsoft.com/en-us/library/orm-9780596521066-01-13.aspx — Dave R., Feb 05 '14 at 21:54

SQL - Parameterized

2 Answers2