/// <summary>
/// Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified SqlConnection
/// using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <res>
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// int result = ExecuteNonQuery(conn, "PublishOrders", 24, 36);
/// </res>
/// <param name="connection">a valid SqlConnection</param>
/// <param name="spName">the name of the stored procedure</param>
/// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(SqlConnection connection, string spName, params object[] parameterValues)
{
//if we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName); //assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues); //call the overload that takes an array of SqlParameters
return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters);
}
//otherwise we can just call the SP without params
else
{
return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName);
}
}//微软的sqlhelper中什么都有
/// Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified SqlConnection
/// using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <res>
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// int result = ExecuteNonQuery(conn, "PublishOrders", 24, 36);
/// </res>
/// <param name="connection">a valid SqlConnection</param>
/// <param name="spName">the name of the stored procedure</param>
/// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(SqlConnection connection, string spName, params object[] parameterValues)
{
//if we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName); //assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues); //call the overload that takes an array of SqlParameters
return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters);
}
//otherwise we can just call the SP without params
else
{
return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName);
}
}//微软的sqlhelper中什么都有
using System.Data;
using System.Data.SqlClient;namespace z
{
/// <summary>
/// Class1 的摘要说明。
/// </summary>
public class SProcedure
{
String cnstr = "Data Source=nio;Integrated Security=SSPI;Initial Catalog=ZYZQ;";
private SqlCommand cm=new SqlCommand(); //建立Command对象
public SqlCommand getCommand //返回Command对象
{
get {return cm;}
} public SProcedure() //构造函数
{
cm.Connection=new SqlConnection(cnstr);
} //---------------------------------------------------------
//添加参数
//---------------------------------------------------------
//清除参数
public void ClearParameter()
{cm.Parameters.Clear();}
//全部的参数
public void addNewParameter(string ParameterName,string ParameterValue,SqlDbType sqlType,int size,string Direction)
{
cm.Parameters.Add(ParameterName,sqlType,size); //添加存储过程的参数
cm.Parameters[ParameterName].Value=ParameterValue; //赋值
cm.Parameters[ParameterName].Direction=getDirection(Direction);//设置方向
} //方向为输入的
public void addNewParameter(string ParameterName,string ParameterValue,SqlDbType sqlType,int size)
{
cm.Parameters.Add(ParameterName,sqlType,size); //添加存储过程的参数
cm.Parameters[ParameterName].Value=ParameterValue; //赋值
//cm.Parameters[ParameterName].Direction=getDirection(Direction);//设置方向
}
//添加int型的参数
public void addNewParameter(string ParameterName,int ParameterValue)
{
cm.Parameters.Add(ParameterName,SqlDbType.Int,4); //添加存储过程的参数
cm.Parameters[ParameterName].Value=ParameterValue; //赋值
//设置方向取默认值——输入
} //添加nvarChar型的参数
public void addNewParameter(string ParameterName,string ParameterValue,int size)
{
cm.Parameters.Add(ParameterName,SqlDbType.NVarChar,size); //添加存储过程的参数
cm.Parameters[ParameterName].Value=ParameterValue; //赋值
//设置方向取默认值——输入的
}
//添加bit型的参数
public void addNewParameter(string ParameterName,bool ParameterValue)
{
cm.Parameters.Add(ParameterName,SqlDbType.Bit); //添加存储过程的参数
cm.Parameters[ParameterName].Value=ParameterValue; //赋值
//设置方向取默认值——输入的
}
//按序号返回参数值,一般在执行完存储过程后使用
public string getParameter(int ParameterIndex)
{
return cm.Parameters[ParameterIndex].Value.ToString();
} //按名称返回参数值
public string getParameter(string ParameterName)
{
return cm.Parameters[ParameterName].Value.ToString();
}
//-------------------------------------------------------------
// 运行、返回记录集
//-------------------------------------------------------------
//运行存储过程返回DataSet
public DataSet runSPDataSet(string StoredProcedureName)
{
//cm.Connection=new SqlConnection(cnstr);
cm.CommandText=StoredProcedureName;
cm.CommandType=CommandType.StoredProcedure;
try
{
SqlDataAdapter da=new SqlDataAdapter(cm);
DataSet DS=new DataSet();
da.Fill(DS);
return DS;
}
catch(Exception ex)
{
throw ex;
}
finally
{
cm.Connection.Close();
}
} //运行存储过程返回第一条记录的数组
public Object[] runSPItems(string StoredProcedureName)
{
Object[] strValue=new Object[1];
//cm.Connection=new SqlConnection(cnstr);
cm.CommandText=StoredProcedureName;
cm.CommandType=CommandType.StoredProcedure;
try
{
cm.Connection.Open();
SqlDataReader r = cm.ExecuteReader(CommandBehavior.CloseConnection);
if (r.Read())
{
strValue=new Object[r.FieldCount];
r.GetValues(strValue);
}
else
{
strValue[0]="null";
}r.Close();
}
catch(Exception ex)
{
throw ex;
}
finally
{
cm.Connection.Close();
}
return strValue;
} //加入存储过程需要的参数,返回command
public SqlCommand getSPCommand(string StoredProcedureName)
{
//cm.Connection=new SqlConnection(cnstr);
cm.CommandText=StoredProcedureName;
cm.CommandType=CommandType.StoredProcedure;
return cm;
} //运行存储过程 不返回记录集,用于添加记录
public string runSP(string ParameterName)
{
//cm.Connection=new SqlConnection(cnstr);
cm.CommandType=CommandType.StoredProcedure;
cm.CommandText=ParameterName;
try
{
cm.Connection.Open();
cm.ExecuteNonQuery();
cm.Connection.Close();
return "true";
}
catch(Exception ex)
{
throw ex;
}
finally
{
cm.Connection.Close();
} }
//运行sql语句 返回DataSet
public DataSet runSqlDataSet(string SQL)
{
//cm.Connection=new SqlConnection(cnstr);
cm.CommandText=SQL;
cm.CommandType=CommandType.Text;
SqlDataAdapter da=new SqlDataAdapter(cm);
DataSet DS=new DataSet();
da.Fill(DS);
return DS;
}
//运行sql语句 不返回记录集
public string runSql(string SQL)
{
//cm.Connection=new SqlConnection(cnstr);
cm.CommandText=SQL;
cm.CommandType=CommandType.Text;
try
{
cm.Connection.Open();
cm.ExecuteNonQuery();
cm.Connection.Close();
return "true";
}
catch(Exception ex)
{
throw ex;
}
}
public Object[] runSQLItems(string SQL)
{
Object[] strValue=new Object[1];
//cm.Connection=new SqlConnection(cnstr);
cm.CommandText=SQL;
cm.CommandType=CommandType.Text;
try
{
cm.Connection.Open();
SqlDataReader r = cm.ExecuteReader(CommandBehavior.CloseConnection);
if (r.Read() )
{
strValue=new Object[r.FieldCount];
r.GetValues(strValue);
}
else
{
strValue[0]="null";
}
r.Close();
}
catch(Exception ex)
{
throw ex;
}
finally
{
cm.Connection.Close();
}
return strValue;
} //运行SQl语句返回第一条记录的第一列的值
public string runSQLgetID(string SQL)
{
cm.CommandText=SQL;
cm.CommandType=CommandType.Text;
string str="";
try
{
cm.Connection.Open();
SqlDataReader r = cm.ExecuteReader(CommandBehavior.CloseConnection);
if (r.Read() )
{
str= r[0].ToString();
}
else
{
str= "null";
}r.Close();
}
catch(Exception ex)
{
throw ex;
}
finally
{
cm.Connection.Close();
}
return str;
} //兼容以前的程序,用于后台不常用的程序
public string insertDataStr(string TableName , string[] ziduan , string[] msg )
{
//添加数据 返回新添加的ID
int i;
string Tables;
string msgs;
Tables = " (";
for( i = 0 ;i< ziduan.Length - 1;i++)
Tables = Tables + ziduan[i] + ",";
Tables = Tables + ziduan[i] + ") ";
msgs = " values ('"; for( i = 0;i<ziduan.Length - 1;i++)
msgs = msgs + msg[i] + "','";
msgs = msgs + msg[i] + "') ";
string insertStr = "insert into " + TableName + " " + Tables + msgs;
insertStr += " select scope_identity() as a1 ";
string re=runSQLgetID(insertStr);
return re;
}
public string insertDataStr(string TableName , string[] msg )
{
//添加数据,按标的字段顺序添加
int i;
//string Tables;
string msgs;
msgs = " values ('";
for( i = 0;i< msg.Length - 1;i++)
msgs = msgs + msg[i] + "','";
msgs = msgs + msg[i] + "') "; string insertStr = "insert into " + TableName + " " + msgs;
insertStr += " select scope_identity() as a1 ";
string re=runSQLgetID(insertStr);
return re;
} public bool updateData( string TableName ,string[] ziduan ,string[] msg ,string tiaojian)
{
//修改数据,传入表名,字段名,值,条件
//TableName:表名;ziduan()字段名的数组;msg字段对应内容的数组;Lens:要修改的字段的数量
//修改字段
int i;
string msgs; msgs = " set ";
for (i = 0 ;i< ziduan.Length - 1;i++)
msgs += ziduan[i] + "=" + "'" + msg[i] + "', ";
msgs += ziduan[i] + "=" + "'" + msg[i] + "'"; string insertStr = "update " + TableName + " " + msgs + " where " + tiaojian;
runSql(insertStr);
return true;
} public void updateOrder(string tableName , string columnName ,string columnValue , string orderName , string orderValue)
{
//修改表的排序
//tablename:要修改的表名;ids分类规则;fieldValue:用于排序的字段名;abc:序号
// select top 1 id from title1 where abc= 1 and userid= ''
string strOrder="select top 1 " + orderName + " from " + tableName + " where " + orderName + "=" + orderValue + " and " + columnName + "='" + columnValue + "'";
if (runSQLgetID(strOrder) !="null")
{
// update title1 set abc = abc+1 where abc> 2-1 and userid= ''
runSql("update " + tableName + " set " + orderName + " = " + orderName + " + 1 where " + orderName + ">=" + orderValue + " and " + columnName + "='" + columnValue + "'");
}
} public int findLastOrderID(string tableName ,string columnName , string columnValue , string orderName)
{
//查找表的最大序号
//tablename:要查找的表名;ids分类规则;fieldValue:用于排序的字段名。
// select top 1 id from title1 where userid= ''
string str ;
int re;
string strOrderID="select top 1 " + orderName + " from " + tableName + " where " + columnName + "='" + columnValue + "' order by " + orderName + " desc";
str=this.runSQLgetID(strOrderID);
if (str=="null")
re = 1;
else
re = Int32.Parse(str) + 1;
return re;
}
//=============================================================== //删除数据
public bool runSqlDelete(string strTableName ,string strSearchCondition )
{
// 删除记录——物理删除不能恢复
//strTableName 表名
//strSearchCondition 查询条件 where 后面的语句
string str;
str = "delete " + strTableName + " where " + strSearchCondition;
runSql(str);
return true;
}
//删除数据
public bool runSqlDelete( string strTableName , string strSearchCondition , string selectListName , string state )
{ //标志记录——一般用于修改记录的状态,比如是否已经删除
//strTableName 表名
//state 要修改的值
//selectListName 要修改的字段的名称
//strSearchCondition 查询条件 where 后面的语句 string str;
str = "update " + strTableName + " set " + selectListName + "='" + state + "' where " + strSearchCondition;
runSql(str);
return true;
}
//---------------------------------------------------------------------------
private ParameterDirection getDirection(string str)
{
switch(str.ToLower())
{
case "1" : //input
return ParameterDirection.Input;
// break;
case "2" : //InputOutput
return ParameterDirection.InputOutput ;
// break;
case "3" : //Output
return ParameterDirection.Output ;
// break;
case "4" : //ReturnValue
return ParameterDirection.ReturnValue ;
// break;
}
return ParameterDirection.Input;
}
}
}使用方法:
存储过程部分:
调用z.SProcedure add = new z.SProcedure();
DataSet ds = new DataSet();
ds = add.runSqlDataSet("sp"); //sp存储过程名DataGrid1.DataSource = ds.Tables[0].DefaultView;
DataGrid1.DataBind();//执行有参数的存储过程,把返回的数据集放到DataSet里 ,sp 是存储过程的名称add.ClearParameter(); //清除参数
add.addNewParameter("@a1",1); //添加bit型的参数
add.addNewParameter("@a2","aaa",100); //添加nvarchar型的参数
add.addNewParameter("@a3",100); //添加int型的参数
add.addNewParameter("@a4","bbb",SqlDbType.Text ,16); //添加其他数据类型的参数
//以上的参数都是输入型的 (input)
add.addNewParameter("@a4","ccc",SqlDbType.Text ,16,"1"); //需要设置参数的方向的情况
// 最后一个参数含义的说明——1:input;2:InputOutput;3:output;4:ReturnValue
DataSet ds=add.runSqlDataSet("sp");还有没列出,自己看、、