如何在C＃中的SQL查询中传递动态表名 [英] How to pass dynamic table name in SQL query in C#

问题描述

我想动态填写下拉列表取决于函数调用c＃



我尝试过：

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

What I have tried:

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", "");
    }

推荐答案

你不能 - 表名必须在SQL编译时可用并且可以'这是一个变量。

有很多方法可以解决这个问题：您可以在SQL中构建一个NVARCHAR命令并使用EXEC来执行它，或者您可以连接字符串以生成您从C＃代码发送的SQL命令。



但......这是一个很大的但是......两者都很危险，必须非常谨慎。连接字符串是危险的 - 它会将您的代码暴露给SQL注入，因此您必须非常非常清楚 能够汇总到发送到SQL的字符串中如果你想保持你的数据库完好无损！如果您的用户可以提供任何文本，如果他们（例如）输入

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;--

然后将它连接到你的字符串或NVARCHAR值。



个人？我不会这样做：我会使用固定的字符串并从列表中选择适当的字符串，而不是以任何方式构建它们。

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.

做一点修改，因为

do little modification as

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

这篇关于如何在C＃中的SQL查询中传递动态表名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！