开发者

How can i select from table where tablename is specified as SqlParameter?

I am trying to perform dynamic sql select where I am selecting from a table using a parameter.

SELECT null FROM @TableName 

However I am getting error must declare table variable @TableName. I suspect this is because I am selecting from a table using a variable. I have not needed to do this before.

List<SqlParameter> sqlParams = new List<SqlParameter>()
{ 
    new SqlParameter("TableName", "testtable"),
    new SqlParameter("FieldName", "testfield"),
    new SqlParameter("Find", "testfind"),
};
string sqlSelect = "SELECT null FROM @TableName 
                    WHERE @FieldName LIKE '%' + @Find + '%' ";

DataTable dtSelect = SqlHelper.ExecuteDataset(sqlConn, CommandType.Text, 
                        sqlSelect, 30, sqlParams.ToArray()).Tables[0]; 
//30 = timeout

How can I perform the above开发者_C百科 using dynamic sql? (no stored procedures please)


You cannot use parameters for things like table and column names. For those you could have a whitelist of possible values and then use string concatenation when building the SQL query.


You can't use parameters like that, so you have to build the query as a string. You could do that in SQL, but you can also just create the string in the C# code.

Make sure that the table name and field name are safe and trusted values, and doesn't come directly from an unsafe source like a web request.

string tableName = "testtable";
string fieldName = "testfield";

List<SqlParameter> sqlParams = new List<SqlParameter>() { 
  new SqlParameter("Find", "testfind"),
};
string sqlSelect =
  "SELECT null " +
  "FROM " + tableName + " " +
  "WHERE " + fieldName + " LIKE '%' + @Find + '%' ";


    private DataTable ExecuteDynamic(string TableName,string FieldName, string Find)
    {

    string sqlSelect = "SELECT * FROM " + TableName +  
                        " WHERE " + FieldName + " LIKE '%'" + Find + "'%' ";
    using (connection = new SqlConnection(Strcon))
        connection.Open();
    {
        using (cmd = new SqlCommand(sqlSelect, connection))
        {
            cmd.CommandType = CommandType.Text;
            cmd.CommandTimeout = 60;
            adpt = new SqlDataAdapter(cmd);
            dt = new DataTable();
            adpt.Fill(dt);
            return (dt);
        }
    }
}
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜