asp.net C#命名参数SqlParameter详解

DBHelper: 

执行查询 

有效的select语句 

返回SqlDataReader public static SqlDataReader ExecuteReader(string sql) { SqlConnection con = new SqlConnection(constring); con.Open(); SqlCommand cmd = new SqlCommand(sql, con); return cmd.ExecuteReader(CommandBehavior.CloseConnection); } 

执行查询带参数 

有效的select语句 

返回SqlDataReader public static SqlDataReader ExecuteReader(string sql,SqlParameter parameter) { SqlConnection con = new SqlConnection(constring); con.Open(); SqlCommand cmd = new SqlCommand(sql, con); cmd.Parameters.Add(parameter); return cmd.ExecuteReader(CommandBehavior.CloseConnection); } 

执行查询带参数数组 

有效的select语句 

 返回SqlDataReader public static SqlDataReader ExecuteReader(string sql, SqlParameter[] parameters) { SqlConnection con = new SqlConnection(constring); con.Open(); SqlCommand cmd = new SqlCommand(sql, con); 

AddRange添加数组 cmd.Parameters.AddRange(parameters); return cmd.ExecuteReader(CommandBehavior.CloseConnection); }

执行增删改 

影响的行数 

public static int ExecuteNonQuery(string sql)

 { using (SqlConnection con = new SqlConnection(constring)) 

{ con.Open(); SqlCommand cmd = new SqlCommand(sql, con); return cmd.ExecuteNonQuery(); } }

 public static int ExecuteNonQuery(string sql,SqlParameter[] parameters) 

{ using (SqlConnection con = new SqlConnection(constring)) 

{ con.Open(); SqlCommand cmd = new SqlCommand(sql, con); //foreach (SqlParameter item in parameters) 

{ 

 cmd.Parameters.Add(item); 

} 

cmd.Parameters.AddRange(parameters); return cmd.ExecuteNonQuery(); } } public static int ExecuteNonQuery(string sql, SqlParameter parameter) 

{ 

using (SqlConnection con = new SqlConnection(constring)) { con.Open(); SqlCommand cmd = new SqlCommand(sql, con); cmd.Parameters.Add(parameter); 

return cmd.ExecuteNonQuery(); } }

DAL:public static int Insert(company model) { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into company"); strSql.Append("(FullName,ShortName,Keywords,Description,Type,Property,Style,Capital,Size,Details,Province,City,Address,Postalcode,Tel,Fax,Mailbox,Url,Link,createtime,Poss,Linkman,Product,Userid)"); strSql.Append(" values ("); strSql.Append("@FullName,@ShortName,@Keywords,@Description,@Type,@Property,@Style,@Capital,@Size,@Details,@Province,@City,@Address,@Postalcode,@Tel,@Fax,@Mailbox,@Url,@Link,@Createtime,@Poss,@Linkman,@Product,@Userid)"); SqlParameter[] parameters = { new SqlParameter("@FullName",SqlDbType.VarChar), new SqlParameter("@ShortName",SqlDbType.VarChar), new SqlParameter("@Keywords",SqlDbType.VarChar), 。。。。。。。。。。。。。。。。。。 }; 

new SqlParameter( PARAM_PASSWORD, password== null ? (object)DBNull.Value : (object)password ), parameters[0].Value = model.FullName; parameters[1].Value = ""; 

model.ShortName; parameters[2].Value = "";

 model.Keywords; parameters[3].Value = model.Description; parameters[4].Value = model.Type; parameters[5].Value = model.Property; parameters[6].Value = model.Style; parameters[7].Value = model.Capital; parameters[8].Value = model.Size; //如果model.Details为空的话在执行的时候就会报“需要@Details参数，但未提供该参数”所以不能parameters[9].Value = model.Details;这样写 parameters[9].Value = model.Details == null ? (object)System.DBNull.Value : model.Details; //parameters[9].Value = model.Details; 。。。。。。。。。。。。。。。。。。。 return DBHelper.ExecuteNonQuery(strSql.ToString(), parameters); }public static List SelectTop5(string type) { //asp.NET SqlParameter关于Like的传参数无效问题问题在于Sql给参数自动添加了单引号。实际上在Sql，将like的代码解析成为了like '%'type'%' ",所以要写成下面的形式 string sql = "select top 5 * from company where poss='通过' and type like @type order by createtime desc"; string seach = "%"+type+"%"; SqlDataReader reader = DBHelper.ExecuteReader(sql, new SqlParameter("@type",ObjToStr(seach))); 。。。。。。。。。。。。。。。。。。。}public static int UpdateComInfo(company model) { string sql11 = "update company set FullName=@FullName,ShortName=@ShortName,Keywords=@Keywords,Description=@Description,[Type]=@Type,[Property]=@Property,Style=@Style,Capital=@Capital,[Size]=@Size,Details=@Details,Province=@Province,City=@City,Address=@Address,Postalcode=@Postalcode,Tel=@Tel,Fax=@Fax,Mailbox=@Mailbox,Url=@Url,Link=@Link,createtime=@Createtime,Poss=@Poss,Linkman=@Linkman,Product=@Product,Userid=@Userid where Id=@Id"; SqlParameter[] parameters = { new SqlParameter("@FullName",ObjToStr(model.FullName)), new SqlParameter("@ShortName",ObjToStr(model.ShortName)), new SqlParameter("@Keywords",ObjToStr(model.Keywords)), 。。。。。。。。。。。。。。。。。。。 }; return DBHelper.ExecuteNonQuery(sql11,parameters); }public static company SelectById(string id) { string sql = "select * from company where Id=@id"; SqlDataReader reader = DBHelper.ExecuteReader(sql, new SqlParameter("@id", ObjToStr(id))); 。。。。。。。。。。。。。。。。}