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
– Sachin, Commented Feb 4, 2014 at 18:09
You need to understand the difference between a connection string and a query. — SLaks
– SLaks, Commented Feb 4, 2014 at 18:13
You could also save yourself a couple lines of code by using Parameters.AddWithValue: sql.Parameters.AddWithValue("@val", value); — valverij
– valverij, Commented Feb 4, 2014 at 18:27

Chandan Kumar · Accepted Answer · 2014-02-05 18:16:20Z

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 5, 2014 at 18:16

answered Feb 4, 2014 at 18:35

Chandan Kumar

4,6228 gold badges45 silver badges63 bronze badges

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

4 Comments

Prog1511 Over a year ago

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.

simon at rcl Over a year ago

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.

Dave R. Over a year ago

@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: stackoverflow.com/questions/114658/…

Prog1511 Over a year ago

But my customer would like to do it. Is it possible to do this with the interface only?

Dave R. · Accepted Answer · 2014-02-05 12:52:52Z

0

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())
            {
                ...
            }
        }
    }
}

answered Feb 5, 2014 at 12:52

Dave R.

7,3033 gold badges32 silver badges53 bronze badges

1 Comment

Dave R. Over a year ago

@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: msdn.microsoft.com/en-us/library/orm-9780596521066-01-13.aspx

Collectives™ on Stack Overflow

SQL - Parameterized

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related