C# SqlParameter 里面使用 LIKE % 模糊查询的时候 怎么写 都查询不出结果，但是把sql语句放到查询工具里查询，就可以出结果。
 
原写法：
 
 
 
 
后来在网上查了下资料，改为下面的写法，就可以出结果了，可能是比较特殊的了，记录下！
 
正确写法：
 
 
 
 
 
 
 
 
---------------------------------------------------------------------------------------------------------------------------------
 
 
 
.NET C# JAVA 基础交流群 1095936339 欢迎入群 交流
 
 
 
 

第一种：传统Sql的Where IN（）值拼接
 
 
 
不推荐，有sql注入风险
 
 
 //传统Sql的Where IN（）值拼装
 string str = "1,2,3"; 
 StringBuilder sql = new StringBuilder();
 sql.Append("select * from user where userId in(");
 sql.Append(str);
 sql.Append(")");
 string sqlStr = sql.ToString();
 Console.WriteLine(sqlStr);
 
结果
 
select * from user where userId in(1,2,3)
 
 
第二种：Sql的Where IN（）参数化传值
 
 
 
可以有效防止sql注入
 
 
公用变量
 
string strArray = "1,2,3";
string sql = "select * from user where userId in";
 
sqlParameters有值传递
 
DbParameter[] sqlParameters1 = {
                new SqlParameter("@UserID1", SqlDbType.Int) { Value = 1},
                new SqlParameter("@UserID2", SqlDbType.Int) { Value = 2},
                new SqlParameter("@UserID3", SqlDbType.Int) { Value = 3},
                new SqlParameter("@UserID4", SqlDbType.Int) { Value = 4}
                }; 
string sqlWhereInValue1 = "";
DbParameter[] dbParameters1 = WhereInFactory(sqlParameters1, strArray, out sqlWhereInValue1);
string sql1 = sql + sqlWhereInValue1;
 
结果
 
select * from user where userId in(@sqlWhereInValue0,@sqlWhereInValue1,@sqlWhereInValue2)
 
 
 
 
 
sqlParameters为空传递
 
 string sqlWhereInValue2 = "";
 DbParameter[] sqlParameters2 = { };
 DbParameter[] dbParameters2 = WhereInFactory(sqlParameters2, strArray, out sqlWhereInValue2);
 string sql2 = sql + sqlWhereInValue2;
 
结果
 
select * from user where userId in(@sqlWhereInValue0,@sqlWhereInValue1,@sqlWhereInValue2)
 
 
 
 
 
Sql的Where IN（）的拼接工厂 
 
        /// <summary>
        ///  Sql的Where IN（）的拼接工厂
        /// </summary>
        /// <param name="parameter">parameter数组</param>
        /// <param name="IdArray">ID数组 如：1,2,3</param>
        /// <param name="sqlWhereInValue">接收生成的sql字符串变量</param>
        private static DbParameter[] WhereInFactory(DbParameter[] parameter, string IdArray, out string sqlWhereInValue)
        {

            string[] strArray = IdArray.Split(',');
            int p_length = parameter.Count();
            int s_length = strArray.Count();
            int length = p_length + s_length;
            DbParameter[] dbParameters = new DbParameter[length];
            StringBuilder sqlWhereIn = new StringBuilder();

            int j = 0;
            for (int i = 0; i < length; i++)
            {

                if (i < p_length)
                {
                    dbParameters[i] = parameter[i];
                }
                else
                {
                    string dot = i + 1 != length ? ",":"";
                    sqlWhereIn.Append("@sqlWhereInValue" + j+ dot);
                    dbParameters[i] = new SqlParameter("@sqlWhereInValue" + j, Convert.ToInt32(strArray[j]));
                    j++;
                }
            }
            sqlWhereInValue = $"({sqlWhereIn.ToString()})";
            return dbParameters;
        }
 

完整代码
 
 using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace test1
{
    /// <summary>
    /// Sql的Where IN（）Test
    /// </summary>
    public class SqlWhereInTest
    {
        /// <summary>
        /// 执行
        /// </summary>
        public static void Exe()
        {
            //数组长度测试
            DbParameter[] sqlParameters6 = new DbParameter[6];
            DbParameter[] sqlParameters10 = new DbParameter[10];
            sqlParameters6 = sqlParameters10;

            {
                //传统Sql的Where IN（）值拼装
                string str = "1,2,3"; 
                StringBuilder sql = new StringBuilder();
                sql.Append("select * from user where userId in(");
                sql.Append(str);
                sql.Append(")");
                string sqlStr = sql.ToString();
                Console.WriteLine(sqlStr);
            }

            {
                //Sql的Where IN（）参数化值拼装 
                string strArray = "1,2,3";
                string sql = "select * from user where userId in";

                //sqlParameters有值传递
                DbParameter[] sqlParameters1 = {
                new SqlParameter("@UserID1", SqlDbType.Int) { Value = 1},
                new SqlParameter("@UserID2", SqlDbType.Int) { Value = 2},
                new SqlParameter("@UserID3", SqlDbType.Int) { Value = 3},
                new SqlParameter("@UserID4", SqlDbType.Int) { Value = 4}
                }; 
                string sqlWhereInValue1 = "";
                DbParameter[] dbParameters1 = WhereInFactory(sqlParameters1, strArray, out sqlWhereInValue1);
                string sql1 = sql + sqlWhereInValue1;

                //sqlParameters为空传递
                string sqlWhereInValue2 = "";
                DbParameter[] sqlParameters2 = { };
                DbParameter[] dbParameters2 = WhereInFactory(sqlParameters2, strArray, out sqlWhereInValue2);
                string sql2 = sql + sqlWhereInValue2;

                Console.WriteLine(strArray);
            }

          

        }

        /// <summary>
        ///  Sql的Where IN（）的拼接工厂
        /// </summary>
        /// <param name="parameter">parameter数组</param>
        /// <param name="IdArray">ID数组 如：1,2,3</param>
        /// <param name="sqlWhereInValue">接收生成的sql字符串变量</param>
        private static DbParameter[] WhereInFactory(DbParameter[] parameter, string IdArray, out string sqlWhereInValue)
        {

            string[] strArray = IdArray.Split(',');
            int p_length = parameter.Count();
            int s_length = strArray.Count();
            int length = p_length + s_length;
            DbParameter[] dbParameters = new DbParameter[length];
            StringBuilder sqlWhereIn = new StringBuilder();

            int j = 0;
            for (int i = 0; i < length; i++)
            {

                if (i < p_length)
                {
                    dbParameters[i] = parameter[i];
                }
                else
                {
                    string dot = i + 1 != length ? ",":"";
                    sqlWhereIn.Append("@sqlWhereInValue" + j+ dot);
                    dbParameters[i] = new SqlParameter("@sqlWhereInValue" + j, Convert.ToInt32(strArray[j]));
                    j++;
                }
            }
            sqlWhereInValue = $"({sqlWhereIn.ToString()})";
            return dbParameters;
        }
    }
}
 
 

 demo下载
 



 



 1.首先建立数据库连接：


 首先说明一下数据库连接字符串：


 string conString = @"Data Source=bds2715212581.my3w.com;Initial Catalog=bds271521258_db;Persist Security Info=True;User ID=bds271521258;Password=123456";
 



 



 下面是sql server2008 服务器启动界面：



 


 Data Source是服务器名称，即上图。


 


 User ID是数据库帐号（登录名），即上图，


 


 Password数据库帐号密码，即上图。


 
 

  Initial Catalog是数据库名称，打开数据库后，将会看到数据库名称，如下图：
  

 
 

  
 
 

 

  

 
蓝色箭头是创建数据库是默认的。其他的才是我们创建的，如上图红色箭头所指的名为“
 bds271521258_db”的数据库。
 

 
 

  所以我的数据库连接串 为：
  Initial Catalog=bds271521258_db。
 
 

  

 
 

  Persist Security Info属性的意思是表示是否保存安全信息，其实可以简单的理解为"ADO在数据库连接成功后是否保存密码信息"，详细说明请看http://blog.csdn.net/yzsind/article/details/1507717
  

 
 

 
 

  2建立数据库
 
 

  

 
 

 

  
  

 
 

  我工程里有两个数据库，你要建数据库的时候应该就有一个。不要被上图误导了。
 
 

  3建立连接
 
 

  代码如下：很简单如下：
 


 
class SQLServerConnectionTool
    {
        string conString = @"Data Source=bds2715212581.my3w.com;Initial Catalog=bds271521258_db;Persist Security Info=True;User ID=bds271521258;Password=123456";
        string cmd = "SELECT * FROM UserInfor";
        /// <summary> sql命令</summary>
        SqlCommand sqlCommand;
        /// <summary> sql查询语句</summary>
        SqlCommand sqlSelectCommand;
        /// <summary> 连接服务器</summary>
        SqlConnection sqlConnection;
        /// <summary> 建立数据库和dataGridView组建的桥梁-----》填充DataTable（表示数据库中一个库中的一个表）或者DataSet（表示数据库的一个库）类型</summary>
        SqlDataAdapter sqlDataAdapter;
        public SQLServerConnectionTool()
        {
            sqlConnection = new SqlConnection(conString);
            sqlCommand = new SqlCommand();
            sqlCommand.Connection = sqlConnection;
            sqlSelectCommand = new SqlCommand();
            sqlSelectCommand.CommandText = cmd;
            sqlSelectCommand.Connection = sqlConnection;

            sqlDataAdapter = new SqlDataAdapter();

        }
        /// <summary>
        /// 建立一个数据库连接
        /// </summary>
        public void serverConnetion()

        {
            try
            {
                sqlConnection.Open();
                //sqlConnection.OpenAsync(cancellationToken);
            }
            catch (InvalidOperationException e)
            {
                Debug.WriteLine(e.ToString());
            }
            catch (SqlException e)
            {
                Debug.WriteLine(e.Message);
            }

        }
    ｝


 4  建立连接后我们就可以发送sql指令了，下面是发送指令的方法。其中command就是sql语句，如“
 SELECT * FROM UserInfor”；


 

  

  

  

  

   

    

     

     

      

       
        /// <summary>
        /// 执行spl语句
        /// </summary>
        /// <param name="command"></param>
        public void SqlCommandExeCute(string command)
        {
            try
            {
                sqlCommand.CommandText = command;
                sqlCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Debug.WriteLine("语句执行错误" + e.ToString());
            }
            //catch
            //{
            //}
        }

       

       
到这里数据库建立与sql 命令就完成了。如果你不需要
       D
       ataGridView表格显示，到这里就完成了。
       

      
     
    
   
   

    5下面再说一下DataGridView表格组件和SqlDataAdapter类，DataTable类，和DataSet类。
    
最终实现表格与数据库的绑定：效果如下：
    代码封装如下：其中DataTable参数就是要与DataGridView绑定的数据
   
  
 


 
 #region sqlDataAdapter封装类查询
        string selecetCmd = "SELECT * FROM UserInfor";
        public DataTable DataTableSqlSelectExeCute(DataTable dataTable)
        {
            try
            {
                dataTable.Clear();
                sqlCommand.CommandText = selecetCmd;
                sqlDataAdapter.SelectCommand = sqlCommand;
                sqlDataAdapter.Fill(dataTable);
            }
            catch (Exception e)
            {
                MessageBox.Show("数据查询错误" + e.Message);
            }
            return dataTable;
        }
        string addCmd = "insert into UserInfor values({0},'{1}',{2},{3},'{4}')";
        public void DataTableSqlInsertExeCute(DataTable dataTable, int userId, string name, int sex, int age, string birthday)
        {
            try
            {
                sqlCommand.CommandText = string.Format(addCmd, userId, name, sex, age, birthday);
                sqlDataAdapter.InsertCommand = sqlCommand;
                dataTable.Rows.Add(userId, name, sex, age, birthday);
                sqlDataAdapter.Update(dataTable);
                dataTable.Clear();
                sqlDataAdapter.Fill(dataTable);
            }
            catch (Exception e)
            {
                MessageBox.Show("数据插入错误" + e.Message);
            }
        }
        string delete = "DELETE FROM UserInfor WHERE UserId = {0}";
        public DataTable DataTableSqlDeleteExeCute(DataTable dataTable, int row)
        {
            try
            {
                sqlCommand.CommandText = string.Format(delete, dataTable.Rows[row]["UserId"]);
                 
                sqlDataAdapter.DeleteCommand = sqlCommand;
                dataTable.Rows[row].Delete();
                sqlDataAdapter.Update(dataTable);
                dataTable.Clear();
                sqlDataAdapter.Fill(dataTable);
            }
            catch (Exception e)
            {
                MessageBox.Show("删除错误" + e.Message);
            }
            return dataTable;
        }
上面要注意SqlDataAdapter类的InsertCommand（必须初始化sql语句（如上面sqlDataAdapter.InsertCommand = sqlCommand;））、DeleteCommand（必须初始化sql语句（如上面sqlDataAdapter.DeleteCommand = sqlCommand;））。
 
当使用InsertCommand添加记录时，dataTable表也必须添加一条新记录（如上面的代码，dataTable.Rows.Add(userId, name, sex, age, birthday)；），保持一一对应关系。
 
当使用DeleteCommand删除记录时，dataTable表也必须删除相应的记录（如上面的代码， dataTable.Rows[row].Delete();），这个也要保持一致性。


 


所有代码：

 
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace SQLServer
{
    class SQLServerConnectionTool
    {
        string conString = @"Data Source=bds2715212581.my3w.com;Initial Catalog=bds271521258_db;Persist Security Info=True;User ID=bds271521258;Password=123456";
        string cmd = "SELECT * FROM UserInfor";
        /// <summary> sql命令</summary>
        SqlCommand sqlCommand;
        /// <summary> sql查询语句</summary>
        SqlCommand sqlSelectCommand;
        /// <summary> 连接服务器</summary>
        SqlConnection sqlConnection;
        /// <summary> 建立数据库和dataGridView组建的桥梁-----》填充DataTable（表示数据库中一个库中的一个表）或者DataSet（表示数据库的一个库）类型</summary>
        SqlDataAdapter sqlDataAdapter;
        public SQLServerConnectionTool()
        {
            sqlConnection = new SqlConnection(conString);
            sqlCommand = new SqlCommand();
            sqlCommand.Connection = sqlConnection;
            sqlSelectCommand = new SqlCommand();
            sqlSelectCommand.CommandText = cmd;
            sqlSelectCommand.Connection = sqlConnection;
            sqlDataAdapter = new SqlDataAdapter();
        }
        /// <summary>
        /// 建立一个数据库连接
        /// </summary>
        public void serverConnetion()
        {
            try
            {
                sqlConnection.Open();
                //sqlConnection.OpenAsync(cancellationToken);
            }
            catch (InvalidOperationException e)
            {
                Debug.WriteLine(e.ToString());
            }
            catch (SqlException e)
            {
                Debug.WriteLine(e.Message);
            }
        }
        /// <summary>
        /// 关闭连接，清理数据
        /// </summary>
        public void serverClose()
        {
            sqlCommand = null;
            sqlSelectCommand = null;
            sqlDataAdapter = null;
            try
            {
                if (sqlConnection != null)
                {
                    sqlConnection.Close();
                    sqlConnection = null;
                }
            }
            catch
            {
            }
 
        }
 
        #region SQL语句
        /// <summary>
        /// 执行spl语句
        /// </summary>
        /// <param name="command"></param>
        public void SqlCommandExeCute(string command)
        {
            try
            {
                sqlCommand.CommandText = command;
                sqlCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Debug.WriteLine("语句执行错误" + e.ToString());
            }
            //catch
            //{
            //}
        }
        #region sql包装 未使用
        public void SqlCommandInsertExeCute(string command)
        {
            SqlCommandExeCute(command);
        }
        public void SqlCommandDeleteExeCute(string command)
        {
            SqlCommandExeCute(command);
        }
        public void SqlCommandSelectExeCute(string command)
        {
            sqlCommand.CommandText = command;
            List<SqlParameter> list = new List<SqlParameter>();
            SqlDataReader sqlDataAdapter = sqlCommand.ExecuteReader();
            //添加参数
            sqlCommand.Parameters.AddRange(list.ToArray());
            while (sqlDataAdapter.Read())
            {
                Console.WriteLine("     {0}\t\t{1}", sqlDataAdapter["用户名"], sqlDataAdapter["性别"]);
            }
            sqlDataAdapter.Close();
        }
        #endregion
        #endregion
        #region sqlDataAdapter封装类查询
        string selecetCmd = "SELECT * FROM UserInfor";
        public DataTable DataTableSqlSelectExeCute(DataTable dataTable)
        {
            try
            {
                dataTable.Clear();
                sqlCommand.CommandText = selecetCmd;
                sqlDataAdapter.SelectCommand = sqlCommand;
                sqlDataAdapter.Fill(dataTable);
            }
            catch (Exception e)
            {
                MessageBox.Show("数据查询错误" + e.Message);
            }
            return dataTable;
        }
        string addCmd = "insert into UserInfor values({0},'{1}',{2},{3},'{4}')";
        public void DataTableSqlInsertExeCute(DataTable dataTable, int userId, string name, int sex, int age, string birthday)
        {
            try
            {
                sqlCommand.CommandText = string.Format(addCmd, userId, name, sex, age, birthday);
                sqlDataAdapter.InsertCommand = sqlCommand;
                dataTable.Rows.Add(userId, name, sex, age, birthday);
                sqlDataAdapter.Update(dataTable);
                dataTable.Clear();
                sqlDataAdapter.Fill(dataTable);
            }
            catch (Exception e)
            {
                MessageBox.Show("数据插入错误" + e.Message);
            }
        }
        string delete = "DELETE FROM UserInfor WHERE UserId = {0}";
        public DataTable DataTableSqlDeleteExeCute(DataTable dataTable, int row)
        {
            try
            {
                sqlCommand.CommandText = string.Format(delete, dataTable.Rows[row]["UserId"]);
                //sqlDataAdapter.SelectCommand = sqlSelectCommand;
                sqlDataAdapter.DeleteCommand = sqlCommand;
                dataTable.Rows[row].Delete();
                sqlDataAdapter.Update(dataTable);
                dataTable.Clear();
                sqlDataAdapter.Fill(dataTable);
            }
            catch (Exception e)
            {
                MessageBox.Show("删除错误" + e.Message);
            }
            return dataTable;
        }
        /// <summary>
        /// 更新函数未实现
        /// </summary>
        /// <param name="dataTable"></param>
        /// <returns></returns>
        public DataTable DataTableSqlUpdateExeCute(DataTable dataTable)
        {
            sqlCommand.CommandText = "";
            sqlDataAdapter.UpdateCommand = sqlSelectCommand;
            sqlDataAdapter.DeleteCommand = sqlCommand;
            sqlDataAdapter.Update(dataTable);
            sqlDataAdapter.Fill(dataTable);
            return dataTable;
        }
        #endregion
        ~SQLServerConnectionTool()
        {
            serverClose();
        }
 
    }
}

 

 
然后建立一个form窗体：
 


 


 
窗体代码如下：
 
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;


namespace SQLServer
{
    public partial class Form1 : Form
    {
        SQLServerConnectionTool sQLServerConnectionTool;
        public Form1()
        {
            InitializeComponent();
            sQLServerConnectionTool = new SQLServerConnectionTool();
            sQLServerConnectionTool.serverConnetion();


            dataTable = new DataTable();
            dataGridView1.DataSource = dataTable;
        }
        string cmd = "SELECT * FROM UserInfor";
        string addCmd = "insert into UserInfor values(5,'zcy',0,26,'')";
        string delete = "DELETE FROM UserInfor WHERE UserId = 3";


        DataTable dataTable;
        private void button1_Click(object sender, EventArgs e)
        {






            //sQLServerConnectionTool.SqlCommandExeCute(delete);
            //sQLServerConnectionTool.SqlCommandExeCute(addCmd);


            //dataGridView1.DataSource = sQLServerConnectionTool.DataTableSqlDeleteExeCute(delete);




              sQLServerConnectionTool.DataTableSqlSelectExeCute(dataTable);


            // sQLServerConnectionTool.SqlCommandSelectExeCute(cmd);
            //sQLServerConnectionTool.DataTableSqlInsertExeCute(addCmd);
            //dataGridView1.DataSource = sQLServerConnectionTool.DataTableSqlSelectExeCute(cmd);
            //dataGridView1.DataSource = sQLServerConnectionTool.DataTableSqlInsertExeCute(addCmd);
        }


        private void button2_Click(object sender, EventArgs e)
        {
            int row;
            if(int.TryParse(textBox1.Text, out row))


                sQLServerConnectionTool.DataTableSqlDeleteExeCute(dataTable,row);


        }


        private void button3_Click(object sender, EventArgs e)
        {
            if (dataTable.Columns.Count != 5)
            {
                MessageBox.Show("请先查询数据");
            }
            int userId;
            int sex;
            int age;


            if (int.TryParse(textBox2.Text, out userId) && int.TryParse(textBox4.Text, out sex) && int.TryParse(textBox5.Text, out age))
            {
                sQLServerConnectionTool.DataTableSqlInsertExeCute(dataTable, userId, textBox3.Text, sex, age, textBox6.Text);
            }
            else
            {
                MessageBox.Show("数据填写错误");
            }
        }


        private void button4_Click(object sender, EventArgs e)
        {
            sQLServerConnectionTool.SqlCommandExeCute(textBox7.Text);  //执行sql语句
            sQLServerConnectionTool.DataTableSqlSelectExeCute(dataTable);       //刷新界面
        }


        private void textBox2_KeyPress(object sender, KeyPressEventArgs e)
        {
 
            if (!char.IsNumber(e.KeyChar) && e.KeyChar != 8)
            {


                e.Handled = true;
                MessageBox.Show("请输入数字");
            }
            //else
            //{
            //    //
            //}
        }


        private void textBox4_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (!char.IsNumber(e.KeyChar) && e.KeyChar != 8)
            {


                e.Handled = true;
                MessageBox.Show("请输入数字");
            }
        }


        private void textBox5_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (!char.IsNumber(e.KeyChar) && e.KeyChar != 8)
            {


                e.Handled = true;
                MessageBox.Show("请输入数字");
            }
        }
    }
}
上面代码需要和你设计的窗体组件对应起来。嫌麻烦可以看我的Demo。
 

 

  demo下载