c# - SQL 不运行字符串命令
问题描述
我想编写一个可选的 SQL 命令,其中不采用任何为空的参数。例如,如果M_SYSCODE
为 -1,则选择查询不会根据 过滤数据M_SYSCODE
。
这是我的代码:
int m_syscode = 1;//-1 for false
string m_code = null;//null for false
string m_name = null;
string m_shortname = null;
string parentcode = null;
int m_abstract = -1;
string category = null;
int is_active = -1;
string sql = "SELECT * FROM PRODUCT WHERE ";
int length = sql.Length;
string andCondition = "AND ";
bool flag = false;
SqlCommand command = new SqlCommand(sql, connection);
if (m_syscode != -1)
{
sql += $"M_SYSCODE={m_syscode} ";
command.Parameters.Add("@m_syscode", System.Data.SqlDbType.Int, 4).Value = m_syscode;
flag = true;
}
if(m_code != null)
{
sql = flag ? sql+=andCondition : sql;
sql += $"M_CODE={m_syscode} ";
command.Parameters.Add("@m_code", System.Data.SqlDbType.VarChar, 15).Value = m_code;
flag = true;
}
if (m_name != null)
{
sql = flag ? sql += andCondition : sql;
sql += $"M_NAME={m_name} ";
command.Parameters.Add("@m_name", System.Data.SqlDbType.VarChar, 25).Value = m_name;
flag = true;
}
if (m_shortname != null)
{
sql = flag ? sql += andCondition : sql;
sql += $"M_SHORTNAME={m_shortname} ";
command.Parameters.Add("@m_shortname", System.Data.SqlDbType.VarChar, 10).Value = m_shortname;
flag = true;
}
if (parentcode != null)
{
sql = flag ? sql += andCondition : sql;
sql += $"M_PARENTCODE={m_syscode} ";
command.Parameters.Add("@parentcode", System.Data.SqlDbType.VarChar, 15).Value = parentcode;
flag = true;
}
if (m_abstract != -1)
{
sql = flag ? sql += andCondition : sql;
sql += $"M_ABSTRACTCODE={m_abstract} ";
command.Parameters.Add("@m_abstract", System.Data.SqlDbType.Bit, 1).Value = m_abstract;
flag = true;
}
if (category != null)
{
sql = flag ? sql += andCondition : sql;
sql += $"M_CATEGORY={category} ";
command.Parameters.Add("@category", System.Data.SqlDbType.VarChar, 12).Value = category;
flag = true;
}
if (is_active != -1)
{
sql = flag ? sql += andCondition : sql;
sql += $"IS_ACTIVE={is_active} ";
command.Parameters.Add("@is_active", System.Data.SqlDbType.Bit, 1).Value = is_active;
flag = true;
}
sql += ";";
//return sql;
string statement = sql.Length == length ? null : sql;
//string sql = $"SELECT * FROM PRODUCT WHERE M_SYSCODE={m_syscode} ";
//SqlCommand command = new SqlCommand(sql, connection);
//command.Parameters.Add("@m_syscode", System.Data.SqlDbType.Int, 4).Value = m_syscode;
if (statement == null)
return null;
SqlDataReader result = command.ExecuteReader();
if (result.Read())
{
return result[1].ToString();
}
result.Close();
return null; // return M_CODE
我打印了输出并且查询是正确的,但是当我执行它时,我得到一个错误:
System.Data.SqlClient.SqlException (0x80131904):“WHERE”附近的语法不正确。
我该如何解决?
这也是我的代码创建的正确查询:
SELECT *
FROM PRODUCT
WHERE M_SYSCODE = 1 ;
解决方案
我必须在我的 sql 字符串的最后一次更改之后添加 SqlCommand 命令。我无法在每次调用中更改参数。
解决方案在这里:
connection.Open();
//string sql = "INSERT INTO PRODUCT " +
// "(M_SYSCODE, M_CODE, M_NAME, M_SHORTNAME, M_PARENTCODE, M_ABSTRACT, M_CATEGORY, IS_ACTIVE) " +
// "VALUES("+M_SYSCODE.ToString()+
// ","+M_CODE+","+M_NAME+ "," + M_SHORTNAME + "," + M_PARENTCODE + "," + M_ABSTRACT.ToString() + ","
// + M_CATEGORY + "," + IS_ACTIVE.ToString()+ ")";
int m_syscode = 1;//-1 for false
string m_code = null;//null for false
string m_name = null;
string m_shortname = null;
string parentcode = null;
int m_abstract = -1;
string category = null;
int is_active = -1;
string sql = "SELECT * FROM PRODUCT WHERE ";
int length = sql.Length;
string andCondition = "AND ";
bool flag = false;
if (m_syscode != -1)
{
sql += $"M_SYSCODE=@m_syscode ";
//command.Parameters.Add("@m_syscode", System.Data.SqlDbType.Int, 4).Value = m_syscode;
flag = true;
}
if(m_code != null)
{
sql = flag ? sql+=andCondition : sql;
sql += $"M_CODE=@m_code ";
//command.Parameters.Add("@m_code", System.Data.SqlDbType.VarChar, 15).Value = m_code;
flag = true;
}
if (m_name != null)
{
sql = flag ? sql += andCondition : sql;
sql += $"M_NAME=@m_name ";
//command.Parameters.Add("@m_name", System.Data.SqlDbType.VarChar, 25).Value = m_name;
flag = true;
}
if (m_shortname != null)
{
sql = flag ? sql += andCondition : sql;
sql += $"M_SHORTNAME=@m_shortname ";
//command.Parameters.Add("@m_shortname", System.Data.SqlDbType.VarChar, 10).Value = m_shortname;
flag = true;
}
if (parentcode != null)
{
sql = flag ? sql += andCondition : sql;
sql += $"M_PARENTCODE=@parentcode ";
//command.Parameters.Add("@parentcode", System.Data.SqlDbType.VarChar, 15).Value = parentcode;
flag = true;
}
if (m_abstract != -1)
{
sql = flag ? sql += andCondition : sql;
sql += $"M_ABSTRACTCODE=@m_abstract ";
//command.Parameters.Add("@m_abstract", System.Data.SqlDbType.Bit, 1).Value = m_abstract;
flag = true;
}
if (category != null)
{
sql = flag ? sql += andCondition : sql;
sql += $"M_CATEGORY=@category ";
//command.Parameters.Add("@category", System.Data.SqlDbType.VarChar, 12).Value = category;
flag = true;
}
if (is_active != -1)
{
sql = flag ? sql += andCondition : sql;
sql += $"IS_ACTIVE=@is_active ";
//command.Parameters.Add("@is_active", System.Data.SqlDbType.Bit, 1).Value = is_active;
flag = true;
}
sql=sql.Substring(0, sql.Length - 1);
sql += ";";
//return sql;
string statement = sql.Length == length ? null : sql;
SqlCommand command = new SqlCommand(sql, connection);
if (m_syscode != -1)
{
command.Parameters.Add("@m_syscode", System.Data.SqlDbType.Int, 4).Value = m_syscode;
}
if (m_code != null)
{
command.Parameters.Add("@m_code", System.Data.SqlDbType.VarChar, 15).Value = m_code;
}
if (m_name != null)
{
command.Parameters.Add("@m_name", System.Data.SqlDbType.VarChar, 25).Value = m_name;
}
if (m_shortname != null)
{
command.Parameters.Add("@m_shortname", System.Data.SqlDbType.VarChar, 10).Value = m_shortname;
}
if (parentcode != null)
{
command.Parameters.Add("@parentcode", System.Data.SqlDbType.VarChar, 15).Value = parentcode;
}
if (m_abstract != -1)
{
command.Parameters.Add("@m_abstract", System.Data.SqlDbType.Bit, 1).Value = m_abstract;
}
if (category != null)
{
command.Parameters.Add("@category", System.Data.SqlDbType.VarChar, 12).Value = category;
}
if (is_active != -1)
{
command.Parameters.Add("@is_active", System.Data.SqlDbType.Bit, 1).Value = is_active;
}
//string sql = $"SELECT * FROM PRODUCT WHERE M_SYSCODE={m_syscode} ";
//SqlCommand command = new SqlCommand(sql, connection);
//command.Parameters.Add("@m_syscode", System.Data.SqlDbType.Int, 4).Value = m_syscode;
if (statement == null) return null;
SqlDataReader result = command.ExecuteReader();
if (result.Read()) { return result[1].ToString(); }
result.Close();
return null; // return M_CODE
推荐阅读
- android - (java.net.ProtocolException) 无法在视频视图中流式传输视频。(使用 FFmpeg 修剪的视频
- python-3.x - 'MissingRequiredArgument' 对象没有属性 'send' - discord.py 重写
- java - 有没有其他方法可以在 Android 中查询/加载本地图像?
- sql-server - 根据两个不同表中的条件删除两个表中的行
- java - 通过 Play 商店在 Android 应用程序中唯一标识用户?
- android - PDF.js不打开pdf文件android
- java - 无法打印二叉树节点值
- xamarin.ios - 我该如何调试:Xamarin SpringBoard(UIKitCore) 错误?
- c - 错误:核心转储在获取 C 中的字符串时
- python - 用替换字符串列表中的下一项替换匹配项