c# tablename as sql PROCEDURE

Question

0.00/5 (No votes)

See more:

I have this code in button1 click event

C#

if (cn.State == ConnectionState.Closed) cn.Open();
            cm.Connection = cn;
            foreach (ListViewItem item in listView1.Items)
            {
                item.Selected = true;

            }
            ListViewItem lvi2 = listView1.SelectedItems[0];
            string tableName = lvi2.SubItems[5].Text;
            string sql = "UPDATE " + tableName + " SET [itmcount] = [itmcount] - @itmcount1 WHERE [itmcode] = @itmcode1 AND [itmcount] IS NOT NULL";
            cm.CommandText = sql;
            foreach (ListViewItem item in listView1.Items)
            {
                SqlParameter par_ItmCount = new SqlParameter("@itmcount1", SqlDbType.Int);
                par_ItmCount.Value = int.Parse(item.SubItems[3].Text);
                cm.Parameters.Add(par_ItmCount);

                SqlParameter par_ItmCode = new SqlParameter("@itmcode1", SqlDbType.NVarChar);
                par_ItmCode.Value = item.Text;
                cm.Parameters.Add(par_ItmCode);
                {
                    cm.ExecuteNonQuery();
                    
                }
                
            }

as u can see my tablename is not a Parameter so how to change this code to sql stored PROCEDURE to prevent sql injection ?

Posted 8-Nov-15 9:07am

Ahmed Zoeil

Add a Solution

Comments

PIEBALDconsult 8-Nov-15 17:14pm

I recommend redesigning your database to avoid having to do that.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Suvendu Shekhar Giri · Answer 1 · 2015-11-08T09:46:00

There are ways to do this but I don't understand why you need multiple tables with same structure.

You can achieve such requirement by using sp_executesql[^] Your stored procedure should look something like following-

Check these articles and try writing the stored proc yourself and if you face any problem, come back here and ask with what you have tried.

Using sp_executesql[^]
Sp_executesql - TSQL Tutorial[^]

Hope, it helps :)

**Debojyoti Saha** · Answer 2 · 2015-11-08T20:58:00

Solution 2

Send your ListView data as a Table parameter and update it on SQL
Please follow the link for how to create User Defined Table in SQL
http://www.c-sharpcorner.com/UploadFile/4d9083/insert-and-update-in-sql-using-user-defined-table-type-and-x/[^]

Posted 8-Nov-15 20:58pm

Debojyoti Saha

Richard Deeming · Answer 3 · 2015-11-10T08:09:00

This looks like a poor database design, but if you have to do it, you need to properly validate the table name, and use sp_executesql[^] to execute the query:

SQL

CREATE PROC dbo.usp_UpdateItemCount
(
    @TableName    sysname,
    @ItmCode1     nvarchar(50), -- TODO: Set the correct size here
    @ItmCount1    int
)
As
BEGIN
DECLARE @RealTableName sysname;
DECLARE @Statement nvarchar(max);
    
    SET NOCOUNT ON;
    
    SELECT
        @RealTableName = name
    FROM
        sys.tables
    WHERE
        name = @TableName
    ;
    
    If @@ROWCOUNT = 0 RAISERROR('Table "%s" was not found.', 16, 1, @TableName);
    
    SET @Statement = N'UPDATE ' + QUOTENAME(@RealTableName) 
    + N' SET [itmcount] = [itmcount] - @itmcount1'
    + N' WHERE [itmcode] = @itmcode1 AND [itmcount] IS NOT NULL';
    
    EXEC sp_executesql @Statement, 
        -- TODO: Set the correct size for this parameter as well
        N'@ItmCode1 nvarchar(50), @ItmCount1 int',
        @ItmCode1 = @ItmCode1,
        @ItmCount1 = @ItmCount1
    ;
END

c# tablename as sql PROCEDURE

3 solutions

Solution 1

Solution 2

Solution 4

Add your solution here

Preview 0