这个问题简单吗?。。。。我不会头大了 在一个类中 定义一个方法,执行带参数的存储过程。然后在后面调用这个方法。看起来挺简单的:我想要一个完整的例子:能运行的。请贴出您的代码!!!谢谢。。 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 /// <summary> /// 增加一条数据 /// </summary> public long Add(admin.Model.topic model) { return dal.Add(model); }/// <summary> /// 增加一条数据 /// </summary> public long Add(admin0318.Model.gbtopic model) { //model.gbtopic_id=GetMaxId(); int rowsAffected; SqlParameter[] parameters = { new SqlParameter("@gbtopic_id", SqlDbType.BigInt,8), new SqlParameter("@gbtopic_dcuser_name", SqlDbType.VarChar,20), new SqlParameter("@gbtopic_mingchen", SqlDbType.VarChar,40), new SqlParameter("@gbtopic_title", SqlDbType.VarChar,250), new SqlParameter("@gbtopic_text", SqlDbType.Text), new SqlParameter("@gbtopic_num", SqlDbType.Int,4) }; parameters[0].Value = model.gbtopic_id; parameters[1].Value = model.gbtopic_dcuser_name; parameters[2].Value = model.gbtopic_mingchen; parameters[3].Value = model.gbtopic_title; parameters[4].Value = model.gbtopic_text; parameters[5].Value = model.gbtopic_num; DbHelperSQL.RunProcedure("UP_gbtopic_ADD",parameters,out rowsAffected); return model.gbtopic_id; }//UP_gbtopic_ADD 为存储过程名 http://topic.csdn.net/t/20060625/22/4842437.html public abstract class SqlHelper { //Database connection strings public static readonly string ConnectionStringLocalTransaction = ConfigurationManager.ConnectionStrings["SQLConnString1"].ConnectionString; public static readonly string ConnectionStringInventoryDistributedTransaction = ConfigurationManager.ConnectionStrings["SQLConnString2"].ConnectionString; public static readonly string ConnectionStringOrderDistributedTransaction = ConfigurationManager.ConnectionStrings["SQLConnString3"].ConnectionString; public static readonly string ConnectionStringProfile = ConfigurationManager.ConnectionStrings["SQLProfileConnString"].ConnectionString; // Hashtable to store cached parameters private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable()); public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); using (SqlConnection conn = new SqlConnection(connectionString)) { PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } } public static int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } 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; } public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); SqlConnection conn = new SqlConnection(connectionString); // we use a try/catch here because if the method throws an exception we want to // close the connection throw code, because no datareader will exist, hence the // commandBehaviour.CloseConnection will not work try { PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); return rdr; } catch { conn.Close(); throw; } } public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); using (SqlConnection connection = new SqlConnection(connectionString)) { PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return val; } } public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return val; } public static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters) { parmCache[cacheKey] = commandParameters; } public static SqlParameter[] GetCachedParameters(string cacheKey) { SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey]; if (cachedParms == null) return null; SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length]; for (int i = 0, j = cachedParms.Length; i < j; i++) clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone(); return clonedParms; } private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms) { if (conn.State != ConnectionState.Open) conn.Open(); cmd.Connection = conn; cmd.CommandText = cmdText; if (trans != null) cmd.Transaction = trans; cmd.CommandType = cmdType; if (cmdParms != null) { foreach (SqlParameter parm in cmdParms) cmd.Parameters.Add(parm); } } } using (SqlConnection connection = new SqlConnection( connectionString)) { SqlCommand command = new SqlCommand( "sp_rename", connection);command.CommandType = CommandType.StoredProcedure;command.Parameters.Add("@objname",SqlDbType.NVarChar).Value = "oldName"command.Parameters.Add("@NewName",SqlDbType.NVarChar).Value = "newName" connection.Open();command.ExecuteNonQuery();}这是执行系统的修改名字的存储过程的。自己看看吧。 public class A //类名为A{ public int b(int a,int c)//方法名为b { return (a+c); }}A n=new A();txtBox1.Text=n.b(1,1).ToString();//调用方法 一: 执行不带返回参数(Input)的存储过程 1: 首先在数据库写个存储过程, 如创建个 addUser存储过程。 Create Proc addUser @ID int, @Name varchar(20), @Sex varchar(20) As Insert Into Users Values( @ID, @Name,@Sex ) 2:创建SqlCommand对象,并初始SqlCommand对象 如: SqlCommand cmd = new SqlCommand( ); cmd.CommandText = "addUser"; // 制定调用哪个存储过程 cmd.CommandType = CommandType.StoredProcedure; // 制定Sql命令类型是存储过程, 默认的为Sql语句。 cmd.Connection = con; // 设定连接 3:向SqlCommand对象添加存储过程参数 SqlParameter param = new SqlParameter( ); // 定义一个参数对象 param.ParameterName = "@ID"; // 存储过程参数名称 param.Value = txtID.Text.Trim(); // 该参数的值 cmd.Parameters.Add( param ); // SqlCommand对象添加该参数对象 param = new SqlParameter( "@Name", txtName.Text.Trim() ); // 简写方式 cmd.Parameters.Add( param ); 4:SqlCommand对象调用执行Sql的函数。 如: cmd.ExecuteNonQuery(); 二:执行带返回参数(Output)的存储过程 1: 首先在数据库写个存储过程, 如创建个 queryUser存储过程。 alter Proc queryUser @ID int, @Suc varchar(10) output As select @Suc = 'false' if exists( Select * From users where u_id = @ID ) select @Suc = 'success' 2:创建SqlCommand对象,并初始SqlCommand对象 如: SqlCommand cmd = new SqlCommand( ); cmd.CommandText = "queryUser"; // 制定调用哪个存储过程 cmd.CommandType = CommandType.StoredProcedure; // 制定Sql命令类型是存储过程, 默认的为Sql语句。 cmd.Connection = con; // 设定连接 3:向SqlCommand对象添加存储过程参数 SqlParameter param1 = new SqlParameter( "@ID", txtID.Text ); // 添加输入参数 cmd.Parameters.Add( param1 ); SqlParameter param2 = new SqlParameter(); // 添加输出参数 param2.ParameterName = "@Suc"; // 名称 param2.SqlDbType = SqlDbType.VarChar; // 输出参数的Sql类型 param2.Size = 10; // 输出参数的Sql类型大小 param2.Direction = ParameterDirection.Output; // 指定该参数对象为输出参数类型 cmd.Parameters.Add( param2 ); 4:SqlCommand对象调用执行Sql的函数。 如: cmd.ExecuteNonQuery(); MessageBox.Show( param2.Value.ToString() ); // 输出输出参数的值输入参数的存储过程的示例: try { SqlCommand cmd = new SqlCommand(); cmd.Connection = con; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "addUser"; SqlParameter param = new SqlParameter( ); param.ParameterName = "@ID"; param.Value = txtID.Text.Trim(); cmd.Parameters.Add( param ); param = new SqlParameter( "@Name", txtName.Text.Trim() ); cmd.Parameters.Add( param ); param = new SqlParameter(); param.ParameterName = "@Sex"; param.Value = txtSex.Text.Trim(); cmd.Parameters.Add( param ); //da.InsertCommand = cmd; if ( cmd.ExecuteNonQuery() == 1 ) { MessageBox.Show( "添加成功" ); } else { MessageBox.Show("失败"); } } catch( SqlException ex ) { MessageBox.Show( ex.Message ); } 输出参数的存储过程的示例: try { SqlCommand cmd = new SqlCommand( ); cmd.CommandText = "queryUser"; cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = con; SqlParameter param1 = new SqlParameter( "@ID", txtID.Text ); cmd.Parameters.Add( param1 ); SqlParameter param2 = new SqlParameter(); param2.ParameterName = "@Suc"; param2.SqlDbType = SqlDbType.VarChar; param2.Size = 10; param2.Direction = ParameterDirection.Output; cmd.Parameters.Add( param2 ); cmd.ExecuteNonQuery(); MessageBox.Show( param1.Value.ToString() ); MessageBox.Show( param2.Value.ToString() ); } catch( SqlException ex ) { MessageBox.Show( ex.Message ); } 不用SqlHelper.cs 的人常常问类似的问题。 3楼是就是SqlHelper.cs 请楼主学会用,用会了这就会发现这个帖子的 60 分散得很不值得,呵呵。 .net程序第一次运行总是很慢?探讨下如何提高速度! tree问题 一段代码看不懂 触发怎么解决 关于dataGridView的问题大家来看看吧! 20分求解:listview控件中的问题 关于Access的select语句,请问 背单词软件怎么用语音实习,我用的是C#语言开发的! radiobutton控件问题!! 有没有和VB那样的API Viwer? 高分求CheckedListBox内部拖放项的源码 System.Windows.Forms.SendKeys.Send("A");报错
/// 增加一条数据
/// </summary>
public long Add(admin.Model.topic model)
{
return dal.Add(model);
}
/// <summary>
/// 增加一条数据
/// </summary>
public long Add(admin0318.Model.gbtopic model)
{
//model.gbtopic_id=GetMaxId();
int rowsAffected;
SqlParameter[] parameters = {
new SqlParameter("@gbtopic_id", SqlDbType.BigInt,8),
new SqlParameter("@gbtopic_dcuser_name", SqlDbType.VarChar,20),
new SqlParameter("@gbtopic_mingchen", SqlDbType.VarChar,40),
new SqlParameter("@gbtopic_title", SqlDbType.VarChar,250),
new SqlParameter("@gbtopic_text", SqlDbType.Text),
new SqlParameter("@gbtopic_num", SqlDbType.Int,4)
};
parameters[0].Value = model.gbtopic_id;
parameters[1].Value = model.gbtopic_dcuser_name;
parameters[2].Value = model.gbtopic_mingchen;
parameters[3].Value = model.gbtopic_title;
parameters[4].Value = model.gbtopic_text;
parameters[5].Value = model.gbtopic_num;
DbHelperSQL.RunProcedure("UP_gbtopic_ADD",parameters,out rowsAffected);
return model.gbtopic_id;
}//UP_gbtopic_ADD 为存储过程名
public abstract class SqlHelper { //Database connection strings
public static readonly string ConnectionStringLocalTransaction = ConfigurationManager.ConnectionStrings["SQLConnString1"].ConnectionString;
public static readonly string ConnectionStringInventoryDistributedTransaction = ConfigurationManager.ConnectionStrings["SQLConnString2"].ConnectionString;
public static readonly string ConnectionStringOrderDistributedTransaction = ConfigurationManager.ConnectionStrings["SQLConnString3"].ConnectionString;
public static readonly string ConnectionStringProfile = ConfigurationManager.ConnectionStrings["SQLProfileConnString"].ConnectionString;
// Hashtable to store cached parameters
private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); using (SqlConnection conn = new SqlConnection(connectionString)) {
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
}
public static int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
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;
}
public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) {
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connectionString); // we use a try/catch here because if the method throws an exception we want to
// close the connection throw code, because no datareader will exist, hence the
// commandBehaviour.CloseConnection will not work
try {
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;
}
catch {
conn.Close();
throw;
}
}
public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) {
SqlCommand cmd = new SqlCommand(); using (SqlConnection connection = new SqlConnection(connectionString)) {
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
}
public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
public static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters) {
parmCache[cacheKey] = commandParameters;
}
public static SqlParameter[] GetCachedParameters(string cacheKey) {
SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey]; if (cachedParms == null)
return null; SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length]; for (int i = 0, j = cachedParms.Length; i < j; i++)
clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone(); return clonedParms;
}
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms) { if (conn.State != ConnectionState.Open)
conn.Open(); cmd.Connection = conn;
cmd.CommandText = cmdText; if (trans != null)
cmd.Transaction = trans; cmd.CommandType = cmdType; if (cmdParms != null) {
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}
}
connectionString))
{
SqlCommand command = new SqlCommand(
"sp_rename", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@objname",SqlDbType.NVarChar).Value = "oldName"
command.Parameters.Add("@NewName",SqlDbType.NVarChar).Value = "newName"
connection.Open();
command.ExecuteNonQuery();
}
这是执行系统的修改名字的存储过程的。自己看看吧。
{
public int b(int a,int c)//方法名为b
{
return (a+c);
}
}A n=new A();
txtBox1.Text=n.b(1,1).ToString();//调用方法
1: 首先在数据库写个存储过程, 如创建个 addUser存储过程。
Create Proc addUser
@ID int,
@Name varchar(20),
@Sex varchar(20)
As
Insert Into Users Values( @ID, @Name,@Sex )
2:创建SqlCommand对象,并初始SqlCommand对象 如:
SqlCommand cmd = new SqlCommand( );
cmd.CommandText = "addUser"; // 制定调用哪个存储过程
cmd.CommandType = CommandType.StoredProcedure; // 制定Sql命令类型是存储过程, 默认的为Sql语句。
cmd.Connection = con; // 设定连接 3:向SqlCommand对象添加存储过程参数
SqlParameter param = new SqlParameter( ); // 定义一个参数对象
param.ParameterName = "@ID"; // 存储过程参数名称
param.Value = txtID.Text.Trim(); // 该参数的值
cmd.Parameters.Add( param ); // SqlCommand对象添加该参数对象 param = new SqlParameter( "@Name", txtName.Text.Trim() ); // 简写方式
cmd.Parameters.Add( param ); 4:SqlCommand对象调用执行Sql的函数。 如:
cmd.ExecuteNonQuery();
二:执行带返回参数(Output)的存储过程
1: 首先在数据库写个存储过程, 如创建个 queryUser存储过程。
alter Proc queryUser
@ID int,
@Suc varchar(10) output
As
select @Suc = 'false'
if exists( Select * From users where u_id = @ID )
select @Suc = 'success' 2:创建SqlCommand对象,并初始SqlCommand对象 如:
SqlCommand cmd = new SqlCommand( );
cmd.CommandText = "queryUser"; // 制定调用哪个存储过程
cmd.CommandType = CommandType.StoredProcedure; // 制定Sql命令类型是存储过程, 默认的为Sql语句。
cmd.Connection = con; // 设定连接 3:向SqlCommand对象添加存储过程参数
SqlParameter param1 = new SqlParameter( "@ID", txtID.Text ); // 添加输入参数
cmd.Parameters.Add( param1 ); SqlParameter param2 = new SqlParameter(); // 添加输出参数
param2.ParameterName = "@Suc"; // 名称
param2.SqlDbType = SqlDbType.VarChar; // 输出参数的Sql类型
param2.Size = 10; // 输出参数的Sql类型大小
param2.Direction = ParameterDirection.Output; // 指定该参数对象为输出参数类型
cmd.Parameters.Add( param2 ); 4:SqlCommand对象调用执行Sql的函数。 如:
cmd.ExecuteNonQuery();
MessageBox.Show( param2.Value.ToString() ); // 输出输出参数的值输入参数的存储过程的示例:
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "addUser";
SqlParameter param = new SqlParameter( );
param.ParameterName = "@ID";
param.Value = txtID.Text.Trim();
cmd.Parameters.Add( param ); param = new SqlParameter( "@Name", txtName.Text.Trim() );
cmd.Parameters.Add( param ); param = new SqlParameter();
param.ParameterName = "@Sex";
param.Value = txtSex.Text.Trim();
cmd.Parameters.Add( param ); //da.InsertCommand = cmd;
if ( cmd.ExecuteNonQuery() == 1 )
{
MessageBox.Show( "添加成功" );
}
else
{
MessageBox.Show("失败");
}
}
catch( SqlException ex )
{
MessageBox.Show( ex.Message );
} 输出参数的存储过程的示例:
try
{
SqlCommand cmd = new SqlCommand( );
cmd.CommandText = "queryUser";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
SqlParameter param1 = new SqlParameter( "@ID", txtID.Text );
cmd.Parameters.Add( param1 ); SqlParameter param2 = new SqlParameter();
param2.ParameterName = "@Suc";
param2.SqlDbType = SqlDbType.VarChar;
param2.Size = 10;
param2.Direction = ParameterDirection.Output;
cmd.Parameters.Add( param2 );
cmd.ExecuteNonQuery(); MessageBox.Show( param1.Value.ToString() );
MessageBox.Show( param2.Value.ToString() ); }
catch( SqlException ex )
{
MessageBox.Show( ex.Message );
}
不用SqlHelper.cs 的人常常问类似的问题。