How to pass column names as parameters in dynamic sql - C#, sql server compact

Question

0.00/5 (No votes)

See more:

Hi all. My first question after a long time. excuse me for such basic questions cause i am learning/refreshing it.
I have a winform app which has checkbox controls in it. The names of the checkboxes matches column names of a table. I can not normalize the tables cause of huge data involved, already received for the live project. so everything stays as it is. I get the selected checbox names as a csv col1,col2,col3 which later i concatenate it to sql string.(no SPs as its a sql compact 3.5 sdf dbase). In my GetData() method of the DataAccess class i form the sql string. But to avoid sql injections how can ensure that the column names passed are validated.
Could any help how following can be extended?

C#

public static DataTable GetDataPostsCars(string selectedMPs, DateTime fromDateTime, DateTime toDateTime)
{
   var table = new DataTable();
   #region debug block
   //string[] cols = selectedMPs.Split(','); //converts to array
   //object[] cols2 = cols;//gets as object array            
   //string sql = String.Format("SELECT {0} FROM GdRateFixedPosts", cols); // does not work like that?
   #endregion
   string sql = string.Format(
                "SELECT " + selectedMPs + " " +
                "FROM GdRateFixedPosts " +
                "WHERE MonitorDateTime BETWEEN '" + fromDateTime + "' AND '" + toDateTime + "'");

   if (FkDbConnection.conn != null && FkDbConnection.conn.State == ConnectionState.Open)
   {
      using (SqlCeCommand cmd = new SqlCeCommand(sql, FkDbConnection.conn))
      {
         cmd.CommandType = CommandType.Text;
         //cmd.Parameters.Add("@fromDateTime",DbType.DateTime);
         //cmd.Parameters.Add("@toDateTime",DbType.DateTime);
         table = FkDbConnection.ExecuteSelectCommand(cmd);
      }
   }
   return table;
 }

Posted 14-Dec-14 13:27pm

shantiom

Add a Solution

1 solution

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)

syed shanu · Answer 1 · 2014-12-14T14:13:00

Hi,
It will be good to create a Stored procedure and pass your Column and date condition to the store procedure.
here i have made a sample Store procedure for you.Hope this will help you.

SQL

-- exec USP_GdRateFixedPosts 'Item_Code,item_Name','2014-12-12 08:18:14.740','2014-12-12 08:18:14.740'
Create PROCEDURE [dbo].[USP_GdRateFixedPosts ]                                              
   (                                
          @MyColumns AS NVARCHAR(MAX)     = '' ,
          @fromDateTime    AS DateTime,
          @ToDateTime as DateTime
      )                                                        
AS                                                                
BEGIN                                                 
 DECLARE    @SQLquery  AS NVARCHAR(MAX)

set @SQLquery = N'SELECT ' + @MyColumns + N' FROM GdRateFixedPosts 
            where MonitorDateTime  BETWEEN ' + @fromDateTime + ' and  ' + @ToDateTime + ''
            
exec sp_executesql @SQLquery;
    END