I want to code an optional SQL command where any parameters that is null does not taken. For example if M_SYSCODE
is -1 the select query does not filter data according to M_SYSCODE
.
This is my code:
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
I printed the output and the query is correct, but when I execute it, I get an error:
System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near 'WHERE'.
How can I fix it?
Also here is a correct query that my code created:
SELECT *
FROM PRODUCT
WHERE M_SYSCODE = 1 ;
I have to add SqlCommand command after the last change in my sql string. I cannot change parameters in every call.
The solution is here:
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
User contributions licensed under CC BY-SA 3.0