How to pass dynamic table name in SQL query in C#

Question

1.00/5 (1 vote)

See more:

i want to dynamically fill the dropdown list depends on the function call in c#

What I have tried:

C#

public static string Bindyears(string type)
    {
        string s = type;
        string ConfigString = ConfigurationManager.ConnectionStrings["autobuyConnectionString"].ConnectionString;
        StringWriter builder = new StringWriter();
        String strQuery = "select distinct  car_year  , car_year as car_year1 from  s where car_year !='0' and car_year!=''    order by car_year desc ";
        
        DataSet ds = new DataSet();
        using (SqlConnection con = new SqlConnection(ConfigString))
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = strQuery;
                
                cmd.Connection = con;
                con.Open();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);
                con.Close();
            }
        }
        DataTable dt = ds.Tables[0];
        builder.WriteLine("[");
        if (dt.Rows.Count > 0)
        {
            // builder.WriteLine("{\"optionDisplay\":\"Select Make\",");
            // builder.WriteLine("\"optionValue\":\"0\"},");
            for (int i = 0; i <= dt.Rows.Count - 1; i++)
            {
                builder.WriteLine("{\"optionDisplay\":\"" + dt.Rows[i]["car_year"] + "\",");
                builder.WriteLine("\"optionValue\":\"" + dt.Rows[i]["car_year1"] + "\"},");
            }
        }
        else
        {
            builder.WriteLine("{\"optionDisplay\":\"Select Year\",");
            builder.WriteLine("\"optionValue\":\"0\"},");
        }
        string returnjson = builder.ToString().Substring(0, builder.ToString().Length - 3);
        returnjson = returnjson + "]";
        return returnjson.Replace("\r", "").Replace("\n", "");
    }
 public static string Bindyears(string type)
    {
        string s = type;
        string ConfigString = ConfigurationManager.ConnectionStrings["autobuyConnectionString"].ConnectionString;
        StringWriter builder = new StringWriter();
        String strQuery = "select distinct  car_year  , car_year as car_year1 from  where car_year !='0' and car_year!=''    order by car_year desc ";
        
        DataSet ds = new DataSet();
        using (SqlConnection con = new SqlConnection(ConfigString))
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = strQuery;
                
                cmd.Connection = con;
                con.Open();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);
                con.Close();
            }
        }
        DataTable dt = ds.Tables[0];
        builder.WriteLine("[");
        if (dt.Rows.Count > 0)
        {
            // builder.WriteLine("{\"optionDisplay\":\"Select Make\",");
            // builder.WriteLine("\"optionValue\":\"0\"},");
            for (int i = 0; i <= dt.Rows.Count - 1; i++)
            {
                builder.WriteLine("{\"optionDisplay\":\"" + dt.Rows[i]["car_year"] + "\",");
                builder.WriteLine("\"optionValue\":\"" + dt.Rows[i]["car_year1"] + "\"},");
            }
        }
        else
        {
            builder.WriteLine("{\"optionDisplay\":\"Select Year\",");
            builder.WriteLine("\"optionValue\":\"0\"},");
        }
        string returnjson = builder.ToString().Substring(0, builder.ToString().Length - 3);
        returnjson = returnjson + "]";
        return returnjson.Replace("\r", "").Replace("\n", "");
    }

Posted 26-Apr-16 0:05am

Nethaji chennai

Updated 26-Apr-16 0:38am

Andy Lanng

v2

Add a Solution

2 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)

Karthik_Mahalingam · Answer 1 · 2016-04-26T00:38:00

do little modification as

C#

public static string Bindyears(string type)
   {
       string s = type;
       string ConfigString = ConfigurationManager.ConnectionStrings["autobuyConnectionString"].ConnectionString;
       StringWriter builder = new StringWriter();
       String strQuery = "select distinct  car_year  , car_year as car_year1 from  @type where car_year !='0' and car_year!=''    order by car_year desc ";

       DataSet ds = new DataSet();
       using (SqlConnection con = new SqlConnection(ConfigString))
       {
           using (SqlCommand cmd = new SqlCommand())
           {
               cmd.CommandType = CommandType.Text;
               cmd.CommandText = strQuery;
               cmd.Parameters.Add("@type",type);

OriginalGriff · Answer 2 · 2016-04-26T00:25:00

You can't - the table name has to be available at SQL "compile time" and can't be a variable.
There are ways around this: you can build a NVARCHAR command in SQL and use EXEC to execute it, or you can concatenate strings to produce the SQL command you send from your C# code.

But...and it's a big "but"...both are dangerous and have to be done with extreme caution. Concatenating strings is dangerous - it exposes your code to SQL Injection, so you have to be very, very aware of exactly what can get assembled into the string that gets sent to SQL if you want to keep your database intact! If your users can provide the text at all, you are at a very real risk of damage or deletion if they (for example) enter

cars; DROP TABLE cars;--

and you concatenate that into your string or NVARCHAR value.

Personally? I wouldn't do it: I'd use fixed strings and select the appropriate one from a list instead of building them in any way.

How to pass dynamic table name in SQL query in C#

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0