update sql statement with unknown name/amount of params

Question

I have a classic ASP site, that I am slowly upgrading. I would like to create a function to securely update a SQL database without specifying parameters manually. Something just a tad more dynamic.

(I do not want to use entity framework or Linq)

Here is the code so far:

string updateSql = "UPDATE sometable" + "SET test1= @testData1 " + "WHERE a = @aData1";
SqlCommand UpdateCmd = new SqlCommand(updateSql, conn);
UpdateCmd.Parameters.Add("@testData1 ", SqlDbType.NVarChar, 10, "testData1 ");
UpdateCmd.Parameters.Add("@aData1", SqlDbType.NVarChar, 20, "aData1");
UpdateCmd.Parameters["@testData1 "].Value = "21515";
UpdateCmd.Parameters["@aData1"].Value = "32t3t";
UpdateCmd.ExecuteNonQuery();

pseudo-code (what I would like to achieve)

Create an Ilist covering all variables {get; set:} [validate type/length here]

For every variable that contains a value (without validation issues) create sql update string.

Execute it.

Possible problem: The only problem I can foresee, is that the list may have 500 variables, but each SQL update may only have only 2 or 3 columns being updated. Is this not efficient?

What would you like to write? I mean you may add some pseudo-code to describe your optimal solution. — Adriano Repetti
– Adriano Repetti, Commented Mar 27, 2012 at 13:29
I don't understand what you're trying to do. You don't want to specify parameters manually? Then where are the values going to come from? Please post some pseudo-code of what you're looking for. — ean5533
– ean5533, Commented Mar 27, 2012 at 13:35
just posted some poseudo-code. Sorry for the lack of information. — tdjfdjdj
– tdjfdjdj, Commented Mar 27, 2012 at 13:37
Please don't prefix your titles with "c#" and such. That's what the tags are for. — John Saunders
– John Saunders, Commented Mar 27, 2012 at 16:02

Timmerz · Accepted Answer · 2012-03-27 13:56:34Z

you need to do something like this....needs more coding obviously....

 static void Main(string[] args)
    {
        var values = new Dictionary<string, object>( );

        values.Add( "name", "timmerz" );
        values.Add( "dob", DateTime.Now );
        values.Add( "sex", "m" );

        SqlUpdate( "sometable", values );
    }

    public static void SqlUpdate( string table, Dictionary<string,object> values, string where )
    {
        var equals      = new List<string>( );
        var parameters  = new List<SqlParameter>( );

        var i = 0;

        foreach( var item in values )
        {
            var pn = "@sp" + i.ToString( );

            equals.Add( string.Format( "{0}={1}", item.Key, pn ) );

            parameters.Add( new SqlParameter( pn, item.Value ) );

            i++;
        }

        string command = string.Format( "update {0} set {1} where {2}", table, string.Join( ", ", equals.ToArray( ) ), where );

        var sqlcommand = new SqlCommand(command);

        sqlcommand.Parameters.AddRange(parameters.ToArray( ) );

        sqlcommand.ExecuteNonQuery( );
    }

ean5533 · Accepted Answer · 2012-03-27 14:08:19Z

I'm not sure I fully understand what you're trying to do, but this might be close to what you're looking for. You can create an arbitrarily long list of parameters and respective values, then build the corresponding UPDATE dynamically from that list.

//set up SqlCommand
SqlCommand UpdateCmd = new SqlCommand();
UpdateCmd.Connection = conn;

//build your dictionary (probably happens elsewhere in your code)
Dictionary<string, object> parameters = new Dictionary<string, object>();
parameters.Add("col1", "col1 value");
parameters.Add("col2", 42);
parameters.Add("col3", DateTime.Now);

//build a command string and add parameter values to your SqlCommand
StringBuilder builder = new StringBuilder("UPDATE sometable SET ");
foreach(KeyValuePair<string, object> parameter in parameters) {
    builder.Append(parameter.Key).Append(" = @").Append(parameter.Key).Append(",");
    UpdateCmd.Parameters.AddWithValue("@" + parameter.Key, parameter.Value);
}
builder.Remove(builder.Length - 1,1);

//set the command text and execute the command
UpdateCmd.CommandText = builder.ToString();
UpdateCmd.ExecuteNonQuery();

Jive Boogie · Accepted Answer · 2012-03-27 18:09:44Z

If you are using SQL Server 2008 you have the option of passing in the parameters and their values as a table to a Stored Procedure. Inside the Stored Procedure you can join the table to be updated with the table passed in. That would probably be more efficient than creating hundreds of sep update statements. Here is a link that may help http://msdn.microsoft.com/en-us/library/bb675163.aspx And here is some sample code based on the code you posted in your question

First Create a table to play with and populate it with some data

CREATE TABLE [dbo].[sometable](
    [Test1] [nvarchar](100) NULL,
    [a] [nvarchar](100) NULL
) ON [PRIMARY]

Insert sometable Select 'rerere', '122342'
Insert sometable Select 'sfsfw', '343'
Insert sometable Select 'sfdrgss', '434545'
Insert sometable Select 'srgegrgeg', '3939932'

Then Create the Type in SQL Server

Create TYPE dbo.ParamsType AS TABLE
( Test1 nvarchar(100), a nvarchar(100) )

Then Create the Stored Procedure that accepts the type as a parameter

    CREATE PROCEDURE usp_UpdateSomeTable 
@Parameters dbo.ParamsType READONLY
AS
BEGIN
    SET NOCOUNT ON;
UPDATE sometable
    SET sometable.Test1 = p.Test1
    FROM sometable INNER JOIN @Parameters as p
    ON sometable.a = p.a;
END
GO

To test from SQL Server Management Studio you can run

Declare @t as ParamsType
Insert @t Select 'newValue1', '122342'
Insert @t Select 'morenew ', '343'
Insert @t Select 'again', '434545'
Insert @t Select 'OnceMore', '3939932'

exec usp_UpdateSomeTable @Parameters=@t

To Test from C# Try

        static void Main(string[] args)
        {
            System.Data.DataTable YourData = new DataTable("Parameters");
            DataColumn column;
            DataRow row;

            column = new DataColumn();
            column.DataType = System.Type.GetType("System.String");
            column.ColumnName = "Test1";
            YourData.Columns.Add(column);

            column = new DataColumn();
            column.DataType = System.Type.GetType("System.String");
            column.ColumnName = "a";

            YourData.Columns.Add(column);

            row = YourData.NewRow();
            row["Test1"] = "newValue1";
            row["a"] = "122342";
            YourData.Rows.Add(row);

            row = YourData.NewRow();
            row["Test1"] = "morenew";
            row["a"] = "343";
            YourData.Rows.Add(row);

            row = YourData.NewRow();
            row["Test1"] = "again";
            row["a"] = "434545";
            YourData.Rows.Add(row);

            SqlConnectionStringBuilder connString = new SqlConnectionStringBuilder();
            connString.DataSource = "127.0.0.1";
            connString.InitialCatalog = "SO";
            connString.IntegratedSecurity = true;

            using (SqlConnection conn = new SqlConnection())
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "usp_UpdateSomeTable";
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                SqlParameter p = cmd.Parameters.AddWithValue("@Parameters", YourData);
                p.SqlDbType = SqlDbType.Structured;
                p.TypeName = "dbo.ParamsType";

                cmd.Connection = conn;

                conn.ConnectionString = connString.ConnectionString;
                conn.Open();
                cmd.ExecuteNonQuery();
            }
        }

Collectives™ on Stack Overflow

update sql statement with unknown name/amount of params

3 Answers 3

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Linked

Related