SqlCommand 关于Parameters的问题

hackztx 2009-05-26 08:09:47 
static readonly SqlParameter[] sps; 

static Brand() 

{ 

sps = new SqlParameter[4]; 

sps[0] = new SqlParameter("@PKID", SqlDbType.TinyInt, 2); 

sps[0].Direction = ParameterDirection.Output; 

sps[1] = new SqlParameter("@Name", SqlDbType.VarChar, 50); 

sps[2] = new SqlParameter("@Picture", SqlDbType.VarChar, 120); 

sps[3] = new SqlParameter("@Url", SqlDbType.VarChar, 120); 

} 



private static SqlCommand SetCmd(string sProcedure,SqlParameter[]sps) 

{ 

SqlCommand cmd = new SqlCommand(sProcedure,new SqlConnection(sConnectionString)); 

cmd.CommandType = CommandType.StoredProcedure; 

if (sps != null) 

{ 

cmd.Parameters.AddRange(sps); 

} 

return cmd; 

} 



public static void Save(Camera.OL.Brand brand) 

{ 

sps[1].Value = brand.Name; 

sps[2].Value = brand.Picture; 

sps[3].Value = brand.Url; 

object[]obj; 

DBHelper.Execute("Camera_Brand_Save", sps, new string[] { "@PKID" }, out obj);//当第二遍执行的时候 System.ArgumentException: 另一个 SqlParameterCollection //中已包含 SqlParameter。 

brand.PKID = Convert.ToInt32(obj[0]); 

list.Add(brand); 

} 



public static void Execute(string sProcedure, SqlParameter[] sps, string[] sSource, out object[] obj) 

{ 

try 

{ 

SqlCommand cmd = SetCmd(sProcedure, sps); 

cmd.Connection.Open(); 

cmd.ExecuteNonQuery(); 

cmd.Connection.Close(); 

obj = new object[sSource.Length]; 

for (int i = 0; i < sSource.Length; i++) 

{ 

obj[i] = cmd.Parameters[i].Value; 

} 

} 

catch (Exception ex) 

{ 

throw ex; 

} 

}
...全文
180 6 打赏 收藏 转发到动态 举报
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
wangwenfengksyc 2009-05-31
  • 打赏
  • 举报
 回复
学习
阿非 2009-05-26
  • 打赏
  • 举报
 回复
[Quote=引用 2 楼 Sandy945 的回复:]
C# code
public static void Execute(string sProcedure, SqlParameter[] sps, string[] sSource, out object[] obj)
{
try
{
SqlCommand cmd = SetCmd(sProcedure, sps);
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
cmd.Connection.Close();
obj = new object[sSource.Length];
for (int i = 0; i < sSource.Length; i++)
{
obj[i] = cmd.Parameters[i].Value;
}
}
…
[/Quote]


public static void Execute(string sProcedure, SqlParameter[] sps, string[] sSource, out object[] obj) 

{ 

try 

{ 

SqlCommand cmd = SetCmd(sProcedure, sps); 

cmd.Connection.Open(); 

cmd.ExecuteNonQuery(); 

obj = new object[sSource.Length]; 

for (int i = 0; i < sSource.Length; i++) 

{ 

obj[i] = cmd.Parameters[i].Value; 

} 

cmd.Parameters.Clear();

cmd.Connection.Close(); 

} 

catch (Exception ex) 

{ 

throw ex; 

} 

}
阿非 2009-05-26
  • 打赏
  • 举报
 回复 


public static void Execute(string sProcedure, SqlParameter[] sps, string[] sSource, out object[] obj) 

{ 

try 

{ 

SqlCommand cmd = SetCmd(sProcedure, sps); 

cmd.Connection.Open(); 

cmd.ExecuteNonQuery(); 

cmd.Parameters.Clear();

cmd.Connection.Close(); 

obj = new object[sSource.Length]; 

for (int i = 0; i < sSource.Length; i++) 

{ 

obj[i] = cmd.Parameters[i].Value; 

} 

} 

catch (Exception ex) 

{ 

throw ex; 

} 

}

cmd.Parameters.Clear();
yanfei_519 2009-05-26
  • 打赏
  • 举报
 回复
路过，学习。。
薪水 2009-05-26
  • 打赏
  • 举报
 回复
up
SqlCommand_Parameters
SqlCommand_Parameters属性用法例子，亲自运行过，没有问题初学者参考
菜品管理数据库.doc
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SqlClient; using System.Data; namespace 网吧计费系统 { class SqlHelper { static string connStr = "server=.;database=netBarDb;uid=sa;pwd=123456"; //增删改 #region /// /// 执行增删改语句 /// /// insert/delelt/update语句 /// 参数的数组 /// public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters) { //创建连接对象 SqlConnection conn = new SqlConnection(connStr); conn.Open(); //创建命令对象 SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.AddRange(parameters); //返还受影响的行数 int row = cmd.ExecuteNonQuery(); conn.Close(); return row; } #endregion //单行单列查询 #region /// /// 单行单列查询 /// /// select语句 /// 参数数组 /// 第一列第一行的内容 public static object ExecuteScalar(string sql, params SqlParameter[] parameters) { ////创建连接对象 //SqlConnection conn = new SqlConnection(connStr); //conn.Open(); ////创建命令对象 //SqlCommand cmd = new SqlCommand(sql, conn); //cmd.Parameters.AddRange(parameters); //object obj = cmd.ExecuteScalar(); //conn.Close(); //return obj; using (SqlConnection conn = new SqlConnection(connStr)) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = sql; foreach (SqlParameter parameter in parameters) { cmd.Parameters.Add(parameter); } return cmd.ExecuteScalar(); } } } #endregion //多行多列查询 #region /// /// 多行多列连接查询 /// /// select语句 /// 参数数组 /// SqlDataReader读取器 public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] parameters) { SqlConnection conn = new SqlConnection(connStr); conn.Open(); //创建连接对象 SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.AddRange(parameters); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection
SqlHelper基类.rar
SqlHelper基类 /// /// 执行一条不返回结果的SqlCommand，通过一个已经存在的数据库事物处理 /// 使用参数数组提供参数 /// /// /// 使用示例： /// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); /// /// 一个存在的 sql 事物处理 /// SqlCommand命令类型 (存储过程， T-SQL语句， 等等。) /// 存储过程的名字或者 T-SQL 语句 /// 以数组形式提供SqlCommand命令中用到的参数列表 /// 返回一个数值表示此SqlCommand命令执行后影响的行数 public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; }
DBHelper方便连接数据库 DBHelper
using System; using System.Collections; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.SqlClient; using System.Configuration; namespace MyOfficeDAL { public static class DBHelper { private static SqlConnection connection; public static SqlConnection Connection { get { string connectionString = ConfigurationManager.ConnectionStrings["MyOfficeConnectionString"].ConnectionString; connection = new SqlConnection(connectionString); if (connection == null) { connection.Open(); } else if (connection.State == System.Data.ConnectionState.Closed) { connection.Open(); } else if (connection.State == System.Data.ConnectionState.Broken) { connection.Close(); connection.Open(); } return connection; } } public static int ExecuteCommand(string safeSql) { SqlCommand cmd = new SqlCommand(safeSql, Connection); int result = cmd.ExecuteNonQuery(); return result; } public static int ExecuteCommand(string sql, params SqlParameter[] values) { SqlCommand cmd = new SqlCommand(sql, Connection); cmd.Parameters.AddRange(values); return cmd.ExecuteNonQuery(); } public static string ReturnStringScalar(string safeSql) { SqlCommand cmd = new SqlCommand(safeSql, Connection); try { string result = cmd.ExecuteScalar().ToString(); return result; } catch (Exception ex) { return "0"; } connection.Close(); } public static int GetScalar(string safeSql) { SqlCommand cmd = new SqlCommand(safeSql, Connection); try { int result = Convert.ToInt32(cmd.ExecuteScalar()); return result; } catch (Exception e) { return 0; } connection.Close(); } public static int GetScalar(string sql, params SqlParameter[] values) { SqlCommand cmd = new SqlCommand(sql, Connection); cmd.Parameters.AddRange(values); int result = Convert.ToInt32(cmd.ExecuteScalar()); return result; connection.Close(); } public static SqlDataReader GetReader(string safeSql) { SqlCommand cmd = new SqlCommand(safeSql, Connection); SqlDataReader reader = cmd.ExecuteReader(); return reader; reader.Close(); reader.Dispose(); } public static SqlDataReader GetReader(string sql, params SqlParameter[] values) { SqlCommand cmd = new SqlCommand(sql, Connection); cmd.Parameters.AddRange(values); SqlDataReader reader = cmd.ExecuteReader(); return reader; reader.Close(); reader.Dispose(); } public static DataTable GetDataSet(string safeSql) { DataSet ds = new DataSet(); SqlCommand cmd = new SqlCommand(safeSql, Connection); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(ds); connection.Close(); connection.Dispose(); return ds.Tables[0]; } public static DataTable GetDataSet(string sql, params SqlParameter[] values) { DataSet ds = new DataSet(); SqlCommand cmd = new SqlCommand(sql, Connection); cmd.Parameters.AddRange(values); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(ds); connection.Close(); connection.Dispose(); return ds.Tables[0]; } } }
spl数据访问接口
对数据库进行增删改方法（使用事务） public void ExcuteNonQuery SqlTransaction trans CommandType ct string cmdTxt SqlParameter[] cmdParms { SqlCommand cmd new SqlCommand ; this Preparecommand cmd trans Connection trans ct cmdTxt cmdParms ; cmd ExecuteNonQuery ; cmd Parameters Clear ;"> 对数据库进行增删改方法（使用事务） public void ExcuteNonQuery SqlTransaction trans CommandType ct string cmdTxt SqlParameter[] cmdParms { SqlCommand cmd new SqlCommand ; this Preparecommand cmd trans Connection trans ct cmdTxt cmdPar [更多]
.NET Framework

17,742

社区成员

24,334

社区内容

发帖
与我相关
我的任务
社区描述
.NET技术 .NET Framework
社区管理员
  • .NET Framework社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章