SqlParameter用法

在c#中执行sql语句时传递参数的小经验

 1、直接写入法：

     例如：

            int Id =1;

            string Name="lui";

            cmd.CommandText="insert into TUserLoginvalues("+Id+",'"+Name+"')";

       因为Id是数值，所以在传递的时候只需要在sql字符串中用"+Id+"即可实现，而  Name是字符串，所以在传递的时候还需要在"+Name+"两边各加一个单引号（'）来 实现;

 

 

2、给命令对象添加参数法：

    例如：

           int Id =1;

           string Name="lui";

           cmd.CommandText="insert into TUserLogin values(@Id,@Name)";

         //上条语句中直接在sql语句中写添加的参数名,不论参数类型都是如此.

          SqlParameter  para=newSqlParameter("@Id",SqlDbType.int,4);//生成一个名字为@Id的参数,必须以@开头表示是添加的参数，并设置其类型长度，类型长度与数据库中对应字段相同

          para.Value=Id;//给参数赋值

          cmd.Parameters.Add(para);//必须把参数变量添加到命令对象中去。

          //以下类似

          para=new SqlParameter("@Name",SqlDbType.VarChar,16);

          para.Value=Name;

          com.Parameters.Add(para);

       ...然后就可以执行数据库操作了。
-----------------------------------
3、

SqlParameter[] paraList = new SqlParameter[7];

paraList[0] = new SqlParameter( "@userName ", SqlDbType.VarChar,50);
paraList[0].Value = personModel.UserName;
paraList[1] = new SqlParameter( "@account ", SqlDbType.VarChar,100);
paraList[1].Value = personModel.Account;
paraList[2] = new SqlParameter( "@pwd ", SqlDbType.VarChar,100);
paraList[2].Value = personModel.Pwd;
paraList[3] = new SqlParameter( "@unitID ", SqlDbType.VarChar,20);
paraList[3].Value = personModel.UnitID;
paraList[4] = new SqlParameter( "@email ", SqlDbType.VarChar,100);
paraList[4].Value = personModel.Email;
paraList[5] = new SqlParameter( "@officeTel ", SqlDbType.VarChar,20);
paraList[5].Value = personModel.OfficeTel;
paraList[6] = new SqlParameter( "@mobile ", SqlDbType.VarChar,20);
paraList[6].Value = personModel.Mobile;

return sd.ExecuteNonQuery( "UP_Person_ADD ", paraList); ///        SqlParameter[] parms = new SqlParameter[] {
                   new SqlParameter(SQL_PARM_RECEIVE, SqlDbType.NVarChar,20),
                   new SqlParameter(SQL_PARM_SEND, SqlDbType.NVarChar,20),
                   new SqlParameter(SQL_PARM_TITLE, SqlDbType.NVarChar,20),
                   new SqlParameter(SQL_PARM_CONTENT, SqlDbType.NVarChar, 100)};
           SqlCommand cmd = new SqlCommand();           // 依次给参数赋值
           parms[0].Value = receive;
           parms[1].Value = send;
           parms[2].Value = title;
           parms[3].Value = content;           //将参数添加到SqlCommand命令中
           foreach (SqlParameter parm in parms)
               cmd.Parameters.Add(parm);           //获取数据库的连接字符串
     using (SqlConnection conn = newSqlConnection(SqlHelper.ConnectionStringLocalTransaction))
           {
               //打开数据库连接，执行命令
               conn.Open();
               //设置Sqlcommand命令的属性
               cmd.Connection = conn;
               cmd.CommandType = CommandType.Text;
               cmd.CommandText = SQL_INSERT_MESSAGE;
               //执行添加的SqlCommand命令
               int val = cmd.ExecuteNonQuery();
               //清空SqlCommand命令中的参数
               cmd.Parameters.Clear();
 

}

http://hi.baidu.com/aweibsb/item/e775342a64d231cddcf69ac7