- 浏览: 44563 次
- 性别:
- 来自: 荆门
最新评论
-
sptgreen:
神马都没有??
测试flickr -
sptgreen:
看了上面的代码,查了下关于IDataParameter[] 资 ...
使用DbHelperSQL调用存储过程的方法
我们现在写一个SQL SERVER的数据库简单的操作类。
包括事务,存储过程调用。
类文件如下:
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace SpLib.db
{
public class DBLib
{
// private SqlConnection con = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["connstr"]);
private SqlConnection con = new SqlConnection("Server=SUNLEI;DataBase=SUNLEI;UID=sa;PWD=123456");
//全局事务
private SqlTransaction Tx = null;
public DBLib()
{
}
//手动开始事务
public void BeginTransaction()
{
con.Open();
Tx = con.BeginTransaction();
}
//手动提交事务
public void CommitTransaction()
{
Tx.Commit();
con.Close();
Tx = null;
}
//手动回滚事务
public void RollbackTransaction()
{
Tx.Rollback();
con.Close();
Tx = null;
}
//给存储过程的参数赋值
public SqlCommand SetParams(SqlTypeBean[] bean, String ProcedureName)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = ProcedureName;
String para = "";
for (int i = 0; i < bean.Length; i++)
{
para = para + bean[i].GetName() + "=" + bean[i].GetValueString();
SqlParameter param = null;
if (object.Equals(bean[i].GetClumType(), SqlDbType.VarChar))
{
param = new SqlParameter(bean[i].GetName(), SqlDbType.VarChar, bean[i].GetClumLength());
if (bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = bean[i].GetValueString();
cmd.Parameters.Add(param);
continue;
}
else if (object.Equals(bean[i].GetClumType(), SqlDbType.Int))
{
param = new SqlParameter(bean[i].GetName(), SqlDbType.Int, bean[i].GetClumLength());
if (bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = bean[i].GetValueLong();
cmd.Parameters.Add(param);
continue;
}
else if (object.Equals(bean[i].GetClumType(), SqlDbType.DateTime))
{
param = new SqlParameter(bean[i].GetName(), SqlDbType.DateTime, bean[i].GetClumLength());
if (bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = DateTime.Parse(bean[i].GetValueString());
cmd.Parameters.Add(param);
continue;
}
else if (object.Equals(bean[i].GetClumType(), SqlDbType.Char))
{
param = new SqlParameter(bean[i].GetName(), SqlDbType.Char, bean[i].GetClumLength());
if (bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = char.Parse(bean[i].GetValueString());
cmd.Parameters.Add(param);
continue;
}
else if (object.Equals(bean[i].GetClumType(), SqlDbType.Bit))
{
param = new SqlParameter(bean[i].GetName(), SqlDbType.Bit, bean[i].GetClumLength());
if (bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = bean[i].GetValueString();
cmd.Parameters.Add(param);
continue;
}
}
SpLib.busi.Log.WriteLog("执行存储过程为:" + ProcedureName + "\n参数为:"+para);
cmd.Connection.Open();
return cmd;
}
//给绑定变量的参数赋值
public SqlCommand SetParamsSql(SqlTypeBean[] Bean, String Sql)
{
SqlCommand cmd = new SqlCommand(Sql);
cmd.Connection = con;
String para = "";
for (int i = 0; i < Bean.Length; i++)
{
para = para + Bean[i].GetName() + "=" + Bean[i].GetValueString();
SqlParameter param = null;
if (object.Equals(Bean[i].GetClumType(), SqlDbType.VarChar))
{
param = new SqlParameter(Bean[i].GetName(), SqlDbType.VarChar, Bean[i].GetClumLength());
if (Bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = Bean[i].GetValueString();
cmd.Parameters.Add(param);
continue;
}
else if (object.Equals(Bean[i].GetClumType(), SqlDbType.Int))
{
param = new SqlParameter(Bean[i].GetName(), SqlDbType.Int, Bean[i].GetClumLength());
if (Bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = Bean[i].GetValueLong();
cmd.Parameters.Add(param);
continue;
}
else if (object.Equals(Bean[i].GetClumType(), SqlDbType.DateTime))
{
param = new SqlParameter(Bean[i].GetName(), SqlDbType.DateTime, Bean[i].GetClumLength());
if (Bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = DateTime.Parse(Bean[i].GetValueString());
cmd.Parameters.Add(param);
continue;
}
else if (object.Equals(Bean[i].GetClumType(), SqlDbType.Char))
{
param = new SqlParameter(Bean[i].GetName(), SqlDbType.Char, Bean[i].GetClumLength());
if (Bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = char.Parse(Bean[i].GetValueString());
cmd.Parameters.Add(param);
continue;
}
else if (object.Equals(Bean[i].GetClumType(), SqlDbType.Bit))
{
param = new SqlParameter(Bean[i].GetName(), SqlDbType.Bit, Bean[i].GetClumLength());
if (Bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = Bean[i].GetValueString();
cmd.Parameters.Add(param);
continue;
}
}
SpLib.busi.Log.WriteLog("执行存储过程为:" + Sql + "\n参数为:" + para);
cmd.Connection.Open();
return cmd;
}
//给存储过程的参数赋值,这方法需要在事务中使用
public SqlCommand SetParamsTransactionProce(SqlTypeBean[] bean, String ProcedureName)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.Transaction = Tx;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = ProcedureName;
String para = "";
for (int i = 0; i < bean.Length; i++)
{
para = para + bean[i].GetName() + "=" + bean[i].GetValueString();
SqlParameter param = null;
if (object.Equals(bean[i].GetClumType(), SqlDbType.VarChar))
{
param = new SqlParameter(bean[i].GetName(), SqlDbType.VarChar, bean[i].GetClumLength());
if (bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = bean[i].GetValueString();
cmd.Parameters.Add(param);
continue;
}
else if (object.Equals(bean[i].GetClumType(), SqlDbType.Int))
{
param = new SqlParameter(bean[i].GetName(), SqlDbType.Int, bean[i].GetClumLength());
if (bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = bean[i].GetValueLong();
cmd.Parameters.Add(param);
continue;
}
else if (object.Equals(bean[i].GetClumType(), SqlDbType.DateTime))
{
param = new SqlParameter(bean[i].GetName(), SqlDbType.DateTime, bean[i].GetClumLength());
if (bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = DateTime.Parse(bean[i].GetValueString());
cmd.Parameters.Add(param);
continue;
}
else if (object.Equals(bean[i].GetClumType(), SqlDbType.Char))
{
param = new SqlParameter(bean[i].GetName(), SqlDbType.Char, bean[i].GetClumLength());
if (bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = char.Parse(bean[i].GetValueString());
cmd.Parameters.Add(param);
continue;
}
else if (object.Equals(bean[i].GetClumType(), SqlDbType.Bit))
{
param = new SqlParameter(bean[i].GetName(), SqlDbType.Bit, bean[i].GetClumLength());
if (bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = bean[i].GetValueString();
cmd.Parameters.Add(param);
continue;
}
}
SpLib.busi.Log.WriteLog("执行存储过程为:" + ProcedureName + "\n参数为:" + para);
return cmd;
}
//给绑定变量赋值,此方法需要事务控制
public SqlCommand SetParamsTransactionSql(SqlTypeBean[] Bean, String Sql)
{
SqlCommand cmd = new SqlCommand(Sql);
cmd.Connection = con;
cmd.Transaction = Tx;
String para = "";
for (int i = 0; i < Bean.Length; i++)
{
para = para + Bean[i].GetName() + "=" + Bean[i].GetValueString();
SqlParameter param = null;
if (object.Equals(Bean[i].GetClumType(), SqlDbType.VarChar))
{
param = new SqlParameter(Bean[i].GetName(), SqlDbType.VarChar, Bean[i].GetClumLength());
if (Bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = Bean[i].GetValueString();
cmd.Parameters.Add(param);
continue;
}
else if (object.Equals(Bean[i].GetClumType(), SqlDbType.Int))
{
param = new SqlParameter(Bean[i].GetName(), SqlDbType.Int, Bean[i].GetClumLength());
if (Bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = Bean[i].GetValueLong();
cmd.Parameters.Add(param);
continue;
}
else if (object.Equals(Bean[i].GetClumType(), SqlDbType.DateTime))
{
param = new SqlParameter(Bean[i].GetName(), SqlDbType.DateTime, Bean[i].GetClumLength());
if (Bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = DateTime.Parse(Bean[i].GetValueString());
cmd.Parameters.Add(param);
continue;
}
else if (object.Equals(Bean[i].GetClumType(), SqlDbType.Char))
{
param = new SqlParameter(Bean[i].GetName(), SqlDbType.Char, Bean[i].GetClumLength());
if (Bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = char.Parse(Bean[i].GetValueString());
cmd.Parameters.Add(param);
continue;
}
else if (object.Equals(Bean[i].GetClumType(), SqlDbType.Bit))
{
param = new SqlParameter(Bean[i].GetName(), SqlDbType.Bit, Bean[i].GetClumLength());
if (Bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = Bean[i].GetValueString();
cmd.Parameters.Add(param);
continue;
}
}
SpLib.busi.Log.WriteLog("执行存储过程为:" + Sql + "\n参数为:" + para);
return cmd;
}
//执行reader
public SqlDataReader GetReader(String Sql, SqlTypeBean[] Bean)
{
SqlCommand command = SetParamsSql(Bean, Sql);
return command.ExecuteReader();
}
//执行reader,需要在事务中使用。
public SqlDataReader GetTransactionReader(String Sql, SqlTypeBean[] Bean)
{
SqlCommand command = SetParamsTransactionSql(Bean, Sql);
return command.ExecuteReader();
}
//执行普通的sql
public bool ExecSql(String Sql, SqlTypeBean[] Bean)
{
SqlCommand command = SetParamsSql(Bean, Sql);
command.ExecuteNonQuery();
command.Connection.Close();
return true;
}
//执行事务控制的sql
public bool ExecTransactionSql(String Sql, SqlTypeBean[] Bean)
{
SqlCommand command = SetParamsTransactionSql(Bean, Sql);
command.ExecuteNonQuery();
return true;
}
//取得记录的第一行第一列的值
public String GetTransactionOneString(String Sql, SqlTypeBean[] Bean)
{
SqlCommand command = SetParamsTransactionSql(Bean, Sql);
String result = command.ExecuteScalar().ToString();
return result;
}
//取得记录的第一行第一列的值
public String GetOneString(String Sql, SqlTypeBean[] Bean)
{
SqlCommand command = SetParamsSql(Bean, Sql);
String result = command.ExecuteScalar().ToString();
command.Connection.Close();
command.Dispose();
return result;
}
//取得DataSet
public DataSet GetDataSet(String Sql, SqlTypeBean[] Bean)
{
SqlCommand command = SetParamsSql(Bean, Sql);
command.Connection.Open();
SqlDataAdapter SqlAdapter = new SqlDataAdapter(command);
DataSet ds = new DataSet();
SqlAdapter.Fill(ds, "");
return ds;
}
}
}
上面这个类包括常用的操作数据库的方法,下面我们来写几个test用例
public class UnitTest1
{
public UnitTest1()
{
//
// TODO: Add constructor logic here
//
}
[TestMethod]
//这里演示怎么调用事务,用绑定变量。
public void TestMethod1()
{
DBLib lib = new DBLib();
try
{
lib.BeginTransaction();//要使用事务,这里必须要先BeginTransaction(),下面执行的方法都要调用带Transaction的方法。
String sql = "INSERT INTO testtable VALUES(@tid,@text)";
SqlTypeBean[] bean = new SqlTypeBean[2];
bean[0] = new SqlTypeBean(false, "@tid", "18", 4, SqlDbType.Int);
bean[1] = new SqlTypeBean(false, "@text", "good", 50, SqlDbType.VarChar);
lib.ExecTransactionSql(sql, bean);
System.Console.WriteLine("ok!");
sql = "INSERT INTO testtable VALUES(@tid,@text)";
bean[0] = new SqlTypeBean(false, "@tid", "17", 4, SqlDbType.Int);
bean[1] = new SqlTypeBean(false, "@text", "good", 50, SqlDbType.VarChar);
lib.ExecTransactionSql(sql, bean);
System.Console.WriteLine("ok!");
lib.CommitTransaction();
}
catch (Exception e)
{
lib.RollbackTransaction();
throw e;
}
}
[TestMethod]
//这里普通的调用
public void TestMethod2()
{
DBLib lib = new DBLib();
String sql = "INSERT INTO testtable VALUES(@tid,@text)";
SqlTypeBean[] bean = new SqlTypeBean[2];
bean[0] = new SqlTypeBean(false, "@tid", "7", 4, SqlDbType.Int);
bean[1] = new SqlTypeBean(false, "@text", "good", 50, SqlDbType.VarChar);
lib.ExecSql(sql, bean);
}
[TestMethod]
//这里是带返回值的存储
public void TestMethod3()
{
DBLib lib = new DBLib();
SqlTypeBean[] bean = new SqlTypeBean[2];
bean[0] = new SqlTypeBean(true, "@COUNT", "7", 4, SqlDbType.Int);
bean[1] = new SqlTypeBean(false, "@TEXT", "good", 50, SqlDbType.VarChar);
SqlCommand cmd = lib.SetParams(bean,"GETCOUNT");
cmd.ExecuteNonQuery();
String result = cmd.Parameters["@COUNT"].Value.ToString();
String result1 = cmd.Parameters["@COUNT"].Value.ToString();
cmd.Connection.Close();
}
}
存储存储过程变量的bean
using System;
using System.Collections.Generic;
using System.Text;
namespace SpLib.db
{
//本类用于存放变量类型
public class SqlTypeBean
{
//这里设定变量是输入变量还是输出变量。默认是输入变量
private bool IsOutPut = false;
//这里存放字段变量的名称
private String Name;
//这里存放字段变量的值
private String Value;
//这里存放字段的长度
private int ClumLength;
//这里存放字段的类型
private object ClumType;
public SqlTypeBean(bool IsOutPut, String Name, String Value, int ClumLength, object ClumType)
{
this.IsOutPut = IsOutPut;
this.Name = Name;
this.Value = Value;
this.ClumLength = ClumLength;
this.ClumType = ClumType;
}
public SqlTypeBean( String Name, String Value, int ClumLength, object ClumType)
{
this.IsOutPut = false;
this.Name = Name;
this.Value = Value;
this.ClumLength = ClumLength;
this.ClumType = ClumType;
}
public bool GetIsOutPut()
{
return IsOutPut;
}
public String GetName()
{
return Name;
}
public object GetClumType()
{
return ClumType;
}
public String GetValueString()
{
return Value;
}
public long GetValueLong()
{
return long.Parse(Value);
}
public bool GetValueBool()
{
return bool.Parse(Value);
}
public int GetClumLength()
{
return ClumLength;
}
}
}
原创文章,转载请标明出处http://blog.csdn.net/keyboardsun
作者 keyboardsun
包括事务,存储过程调用。
类文件如下:
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace SpLib.db
{
public class DBLib
{
// private SqlConnection con = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["connstr"]);
private SqlConnection con = new SqlConnection("Server=SUNLEI;DataBase=SUNLEI;UID=sa;PWD=123456");
//全局事务
private SqlTransaction Tx = null;
public DBLib()
{
}
//手动开始事务
public void BeginTransaction()
{
con.Open();
Tx = con.BeginTransaction();
}
//手动提交事务
public void CommitTransaction()
{
Tx.Commit();
con.Close();
Tx = null;
}
//手动回滚事务
public void RollbackTransaction()
{
Tx.Rollback();
con.Close();
Tx = null;
}
//给存储过程的参数赋值
public SqlCommand SetParams(SqlTypeBean[] bean, String ProcedureName)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = ProcedureName;
String para = "";
for (int i = 0; i < bean.Length; i++)
{
para = para + bean[i].GetName() + "=" + bean[i].GetValueString();
SqlParameter param = null;
if (object.Equals(bean[i].GetClumType(), SqlDbType.VarChar))
{
param = new SqlParameter(bean[i].GetName(), SqlDbType.VarChar, bean[i].GetClumLength());
if (bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = bean[i].GetValueString();
cmd.Parameters.Add(param);
continue;
}
else if (object.Equals(bean[i].GetClumType(), SqlDbType.Int))
{
param = new SqlParameter(bean[i].GetName(), SqlDbType.Int, bean[i].GetClumLength());
if (bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = bean[i].GetValueLong();
cmd.Parameters.Add(param);
continue;
}
else if (object.Equals(bean[i].GetClumType(), SqlDbType.DateTime))
{
param = new SqlParameter(bean[i].GetName(), SqlDbType.DateTime, bean[i].GetClumLength());
if (bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = DateTime.Parse(bean[i].GetValueString());
cmd.Parameters.Add(param);
continue;
}
else if (object.Equals(bean[i].GetClumType(), SqlDbType.Char))
{
param = new SqlParameter(bean[i].GetName(), SqlDbType.Char, bean[i].GetClumLength());
if (bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = char.Parse(bean[i].GetValueString());
cmd.Parameters.Add(param);
continue;
}
else if (object.Equals(bean[i].GetClumType(), SqlDbType.Bit))
{
param = new SqlParameter(bean[i].GetName(), SqlDbType.Bit, bean[i].GetClumLength());
if (bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = bean[i].GetValueString();
cmd.Parameters.Add(param);
continue;
}
}
SpLib.busi.Log.WriteLog("执行存储过程为:" + ProcedureName + "\n参数为:"+para);
cmd.Connection.Open();
return cmd;
}
//给绑定变量的参数赋值
public SqlCommand SetParamsSql(SqlTypeBean[] Bean, String Sql)
{
SqlCommand cmd = new SqlCommand(Sql);
cmd.Connection = con;
String para = "";
for (int i = 0; i < Bean.Length; i++)
{
para = para + Bean[i].GetName() + "=" + Bean[i].GetValueString();
SqlParameter param = null;
if (object.Equals(Bean[i].GetClumType(), SqlDbType.VarChar))
{
param = new SqlParameter(Bean[i].GetName(), SqlDbType.VarChar, Bean[i].GetClumLength());
if (Bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = Bean[i].GetValueString();
cmd.Parameters.Add(param);
continue;
}
else if (object.Equals(Bean[i].GetClumType(), SqlDbType.Int))
{
param = new SqlParameter(Bean[i].GetName(), SqlDbType.Int, Bean[i].GetClumLength());
if (Bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = Bean[i].GetValueLong();
cmd.Parameters.Add(param);
continue;
}
else if (object.Equals(Bean[i].GetClumType(), SqlDbType.DateTime))
{
param = new SqlParameter(Bean[i].GetName(), SqlDbType.DateTime, Bean[i].GetClumLength());
if (Bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = DateTime.Parse(Bean[i].GetValueString());
cmd.Parameters.Add(param);
continue;
}
else if (object.Equals(Bean[i].GetClumType(), SqlDbType.Char))
{
param = new SqlParameter(Bean[i].GetName(), SqlDbType.Char, Bean[i].GetClumLength());
if (Bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = char.Parse(Bean[i].GetValueString());
cmd.Parameters.Add(param);
continue;
}
else if (object.Equals(Bean[i].GetClumType(), SqlDbType.Bit))
{
param = new SqlParameter(Bean[i].GetName(), SqlDbType.Bit, Bean[i].GetClumLength());
if (Bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = Bean[i].GetValueString();
cmd.Parameters.Add(param);
continue;
}
}
SpLib.busi.Log.WriteLog("执行存储过程为:" + Sql + "\n参数为:" + para);
cmd.Connection.Open();
return cmd;
}
//给存储过程的参数赋值,这方法需要在事务中使用
public SqlCommand SetParamsTransactionProce(SqlTypeBean[] bean, String ProcedureName)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.Transaction = Tx;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = ProcedureName;
String para = "";
for (int i = 0; i < bean.Length; i++)
{
para = para + bean[i].GetName() + "=" + bean[i].GetValueString();
SqlParameter param = null;
if (object.Equals(bean[i].GetClumType(), SqlDbType.VarChar))
{
param = new SqlParameter(bean[i].GetName(), SqlDbType.VarChar, bean[i].GetClumLength());
if (bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = bean[i].GetValueString();
cmd.Parameters.Add(param);
continue;
}
else if (object.Equals(bean[i].GetClumType(), SqlDbType.Int))
{
param = new SqlParameter(bean[i].GetName(), SqlDbType.Int, bean[i].GetClumLength());
if (bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = bean[i].GetValueLong();
cmd.Parameters.Add(param);
continue;
}
else if (object.Equals(bean[i].GetClumType(), SqlDbType.DateTime))
{
param = new SqlParameter(bean[i].GetName(), SqlDbType.DateTime, bean[i].GetClumLength());
if (bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = DateTime.Parse(bean[i].GetValueString());
cmd.Parameters.Add(param);
continue;
}
else if (object.Equals(bean[i].GetClumType(), SqlDbType.Char))
{
param = new SqlParameter(bean[i].GetName(), SqlDbType.Char, bean[i].GetClumLength());
if (bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = char.Parse(bean[i].GetValueString());
cmd.Parameters.Add(param);
continue;
}
else if (object.Equals(bean[i].GetClumType(), SqlDbType.Bit))
{
param = new SqlParameter(bean[i].GetName(), SqlDbType.Bit, bean[i].GetClumLength());
if (bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = bean[i].GetValueString();
cmd.Parameters.Add(param);
continue;
}
}
SpLib.busi.Log.WriteLog("执行存储过程为:" + ProcedureName + "\n参数为:" + para);
return cmd;
}
//给绑定变量赋值,此方法需要事务控制
public SqlCommand SetParamsTransactionSql(SqlTypeBean[] Bean, String Sql)
{
SqlCommand cmd = new SqlCommand(Sql);
cmd.Connection = con;
cmd.Transaction = Tx;
String para = "";
for (int i = 0; i < Bean.Length; i++)
{
para = para + Bean[i].GetName() + "=" + Bean[i].GetValueString();
SqlParameter param = null;
if (object.Equals(Bean[i].GetClumType(), SqlDbType.VarChar))
{
param = new SqlParameter(Bean[i].GetName(), SqlDbType.VarChar, Bean[i].GetClumLength());
if (Bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = Bean[i].GetValueString();
cmd.Parameters.Add(param);
continue;
}
else if (object.Equals(Bean[i].GetClumType(), SqlDbType.Int))
{
param = new SqlParameter(Bean[i].GetName(), SqlDbType.Int, Bean[i].GetClumLength());
if (Bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = Bean[i].GetValueLong();
cmd.Parameters.Add(param);
continue;
}
else if (object.Equals(Bean[i].GetClumType(), SqlDbType.DateTime))
{
param = new SqlParameter(Bean[i].GetName(), SqlDbType.DateTime, Bean[i].GetClumLength());
if (Bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = DateTime.Parse(Bean[i].GetValueString());
cmd.Parameters.Add(param);
continue;
}
else if (object.Equals(Bean[i].GetClumType(), SqlDbType.Char))
{
param = new SqlParameter(Bean[i].GetName(), SqlDbType.Char, Bean[i].GetClumLength());
if (Bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = char.Parse(Bean[i].GetValueString());
cmd.Parameters.Add(param);
continue;
}
else if (object.Equals(Bean[i].GetClumType(), SqlDbType.Bit))
{
param = new SqlParameter(Bean[i].GetName(), SqlDbType.Bit, Bean[i].GetClumLength());
if (Bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = Bean[i].GetValueString();
cmd.Parameters.Add(param);
continue;
}
}
SpLib.busi.Log.WriteLog("执行存储过程为:" + Sql + "\n参数为:" + para);
return cmd;
}
//执行reader
public SqlDataReader GetReader(String Sql, SqlTypeBean[] Bean)
{
SqlCommand command = SetParamsSql(Bean, Sql);
return command.ExecuteReader();
}
//执行reader,需要在事务中使用。
public SqlDataReader GetTransactionReader(String Sql, SqlTypeBean[] Bean)
{
SqlCommand command = SetParamsTransactionSql(Bean, Sql);
return command.ExecuteReader();
}
//执行普通的sql
public bool ExecSql(String Sql, SqlTypeBean[] Bean)
{
SqlCommand command = SetParamsSql(Bean, Sql);
command.ExecuteNonQuery();
command.Connection.Close();
return true;
}
//执行事务控制的sql
public bool ExecTransactionSql(String Sql, SqlTypeBean[] Bean)
{
SqlCommand command = SetParamsTransactionSql(Bean, Sql);
command.ExecuteNonQuery();
return true;
}
//取得记录的第一行第一列的值
public String GetTransactionOneString(String Sql, SqlTypeBean[] Bean)
{
SqlCommand command = SetParamsTransactionSql(Bean, Sql);
String result = command.ExecuteScalar().ToString();
return result;
}
//取得记录的第一行第一列的值
public String GetOneString(String Sql, SqlTypeBean[] Bean)
{
SqlCommand command = SetParamsSql(Bean, Sql);
String result = command.ExecuteScalar().ToString();
command.Connection.Close();
command.Dispose();
return result;
}
//取得DataSet
public DataSet GetDataSet(String Sql, SqlTypeBean[] Bean)
{
SqlCommand command = SetParamsSql(Bean, Sql);
command.Connection.Open();
SqlDataAdapter SqlAdapter = new SqlDataAdapter(command);
DataSet ds = new DataSet();
SqlAdapter.Fill(ds, "");
return ds;
}
}
}
上面这个类包括常用的操作数据库的方法,下面我们来写几个test用例
public class UnitTest1
{
public UnitTest1()
{
//
// TODO: Add constructor logic here
//
}
[TestMethod]
//这里演示怎么调用事务,用绑定变量。
public void TestMethod1()
{
DBLib lib = new DBLib();
try
{
lib.BeginTransaction();//要使用事务,这里必须要先BeginTransaction(),下面执行的方法都要调用带Transaction的方法。
String sql = "INSERT INTO testtable VALUES(@tid,@text)";
SqlTypeBean[] bean = new SqlTypeBean[2];
bean[0] = new SqlTypeBean(false, "@tid", "18", 4, SqlDbType.Int);
bean[1] = new SqlTypeBean(false, "@text", "good", 50, SqlDbType.VarChar);
lib.ExecTransactionSql(sql, bean);
System.Console.WriteLine("ok!");
sql = "INSERT INTO testtable VALUES(@tid,@text)";
bean[0] = new SqlTypeBean(false, "@tid", "17", 4, SqlDbType.Int);
bean[1] = new SqlTypeBean(false, "@text", "good", 50, SqlDbType.VarChar);
lib.ExecTransactionSql(sql, bean);
System.Console.WriteLine("ok!");
lib.CommitTransaction();
}
catch (Exception e)
{
lib.RollbackTransaction();
throw e;
}
}
[TestMethod]
//这里普通的调用
public void TestMethod2()
{
DBLib lib = new DBLib();
String sql = "INSERT INTO testtable VALUES(@tid,@text)";
SqlTypeBean[] bean = new SqlTypeBean[2];
bean[0] = new SqlTypeBean(false, "@tid", "7", 4, SqlDbType.Int);
bean[1] = new SqlTypeBean(false, "@text", "good", 50, SqlDbType.VarChar);
lib.ExecSql(sql, bean);
}
[TestMethod]
//这里是带返回值的存储
public void TestMethod3()
{
DBLib lib = new DBLib();
SqlTypeBean[] bean = new SqlTypeBean[2];
bean[0] = new SqlTypeBean(true, "@COUNT", "7", 4, SqlDbType.Int);
bean[1] = new SqlTypeBean(false, "@TEXT", "good", 50, SqlDbType.VarChar);
SqlCommand cmd = lib.SetParams(bean,"GETCOUNT");
cmd.ExecuteNonQuery();
String result = cmd.Parameters["@COUNT"].Value.ToString();
String result1 = cmd.Parameters["@COUNT"].Value.ToString();
cmd.Connection.Close();
}
}
存储存储过程变量的bean
using System;
using System.Collections.Generic;
using System.Text;
namespace SpLib.db
{
//本类用于存放变量类型
public class SqlTypeBean
{
//这里设定变量是输入变量还是输出变量。默认是输入变量
private bool IsOutPut = false;
//这里存放字段变量的名称
private String Name;
//这里存放字段变量的值
private String Value;
//这里存放字段的长度
private int ClumLength;
//这里存放字段的类型
private object ClumType;
public SqlTypeBean(bool IsOutPut, String Name, String Value, int ClumLength, object ClumType)
{
this.IsOutPut = IsOutPut;
this.Name = Name;
this.Value = Value;
this.ClumLength = ClumLength;
this.ClumType = ClumType;
}
public SqlTypeBean( String Name, String Value, int ClumLength, object ClumType)
{
this.IsOutPut = false;
this.Name = Name;
this.Value = Value;
this.ClumLength = ClumLength;
this.ClumType = ClumType;
}
public bool GetIsOutPut()
{
return IsOutPut;
}
public String GetName()
{
return Name;
}
public object GetClumType()
{
return ClumType;
}
public String GetValueString()
{
return Value;
}
public long GetValueLong()
{
return long.Parse(Value);
}
public bool GetValueBool()
{
return bool.Parse(Value);
}
public int GetClumLength()
{
return ClumLength;
}
}
}
原创文章,转载请标明出处http://blog.csdn.net/keyboardsun
作者 keyboardsun
发表评论
-
关于asp.net用户验证文摘
2010-07-05 15:55 963在ASP.NET中如何用C#.NET实现基于表单的验证 htt ... -
关于Firefox浏览器插件Web Developer
2010-03-28 15:47 1114Web黑客工具箱:Web Developer的应用 摘要:本文 ... -
.net 事务处理的实现方法
2009-06-10 10:28 2943事务类型: 1,手动事务:用开始和结束事务的显式指令( ... -
从SQL Server中导入/导出 Excel 的基本方法
2009-04-03 09:39 828从SQL Server中导入/导出 Excel 的基本方法 h ... -
使用DbHelperSQL调用存储过程的方法
2009-03-19 16:06 4876下面代码是个调用存储过程的例子,对于学习怎么使用DbHelpe ... -
asp.net中调用带输出参数的存储过程的两种方法
2009-03-18 17:11 23721. 存储过程 create ... -
c#调用存储过程两种方法
2009-03-18 17:09 5056摘要 存储过程的调用在 ... -
存储过程从入门到熟练(c#篇)
2009-03-13 11:05 847①为什么要使用存储过程? 因为它比SQL语句执行快. ②存储 ... -
C#中 取时间的年月日时分秒
2009-03-10 16:51 37811:时间格式转换 System.DateTime curre ... -
学习Asp.Net的打印技术
2009-02-26 17:14 781关于ASP.NET页面打印技术的总结 经典ASP.NET打印技 ... -
ASP.NET验证控件祥解
2009-02-12 10:22 659ASP.NET是微软推出的下一代WEB开发工具,其强大的功能立 ...
相关推荐
数据库操作类: 支持 SQLServer mysql sqlite Sybase Oracle等DB 数据库操作类 包括执行SQL或者存储过程,返回DataSet、DataTable等功能 完全支持存储过程和参数调用 javascriptClassLibrary 包括一些JavaScript 类...
便捷的操作sqlserver数据库的数据库操作类,可以实现存储过程及语句的灵活调用。
调用存储过程通用类DBHelper 加密解密 SQL常用DBHelper Asp.net存储过程无限分类 TreeView无限分类 无限分类MVC 荧光棒效果 获取控件上全选 回车转换成Tab DataGrid超级连接列 ...
调用存储过程通用类DBHelper 加密解密 SQL常用DBHelper Asp.net存储过程无限分类 TreeView无限分类 无限分类MVC 荧光棒效果获取控件上全选 回车转换成Tab DataGrid超级连接列 DataGrid行随鼠标...
1、自动生成windows数据库查询页面,数据库详细信息页面,甚至调用例子都为您写好,只要简单复制即可。 2、自动生成SQL SERVER 脚本语句,包括分页查询,选择查询,修改更新查询,插入查询,删除查询。 3、自动生成...
在ASP.NET中使用WINDOWS验证方式连接SQL SERVER数据库 改进ADO.Net数据库访问方式 ASP.NET 2.0 绑定高级技巧 简单实用的DataSet更新数据库的类+总结 [ADO.NET]由数据库触发器引发的问题 为ASP.NET封装的SQL数据库...
9.6 ASP.NET数据库操作实例 9.6.1 制作用户界面(UI) 9.6.2 使用GridView显示、删除、修改数据 9.6.3 使用DataList显示数据 9.6.4 DataList分页实现 9.6.5 使用SQLHelper操作数据库 9.7 小结 第10章 访问其他数据源...
9.6 ASP.NET数据库操作实例 9.6.1 制作用户界面(UI) 9.6.2 使用GridView显示、删除、修改数据 9.6.3 使用DataList显示数据 9.6.4 DataList分页实现 9.6.5 使用SQLHelper操作数据库 9.7 小结 第10章 访问其他数据源...
摘要:Data Access Application Block 是一个 .NET 组件,包含优化的数据访问代码,可以帮助用户调用存储过程以及向 SQL Server 数据库发出 SQL 文本命令。它返回 SqlDataReader、DataSet 和 XmlReader 对象。您可以...
第 9 章 ASP.NET 操作数据库 9.1 使用ADO.NET 操作数据库 9.1.1 使用ExecuteReader()操作数据库 9.1.2 使用ExecuteNonQuery()操作数据库 9.1.3 使用ExecuteScalar()操作数据库 9.1.4 使用ExecuteXmlReader()操作...
9.6 ASP.NET数据库操作实例 9.6.1 制作用户界面(UI) 9.6.2 使用GridView显示、删除、修改数据 9.6.3 使用DataList显示数据 9.6.4 DataList分页实现 9.6.5 使用SQLHelper操作数据库 9.7 小结 第10章 访问其他数据源...
asp.net中如何用c#实现弹出式的交互对话窗口.txt cookie登陆判断.txt CSharp基本书写规范.doc CustomValidator可以编程控制.txt html encode.txt html页面中数据绑定.txt html语法大全.txt listbox相互传值.txt MD5....
9.6 ASP.NET数据库操作实例 9.6.1 制作用户界面(UI) 9.6.2 使用GridView显示、删除、修改数据 9.6.3 使用DataList显示数据 9.6.4 DataList分页实现 9.6.5 使用SQLHelper操作数据库 9.7 小结 第10章 访问其他数据源...
先附加DB_51aspx文件夹里面数据库到你的mssql 2000当中,用VS2005打开,修改web.config中的数据库配置:<add key="ConnString" value="Server=.;DataBase=FCLhomepage;uid=sa;password=sa;"/> 这里改你的数据库...
ASP.NET 4高级程序设计(第4版)》【原版书为:Pro ASP.NET 4 in C# 2010】是ASP.NET领域的鸿篇巨制,全面讲解了ASP.NET4的各种特性及其背后的工作原理,并给出了许多针对如何构建复杂、可扩展的网站从实践中得出的...
C#调用存储过程.txt CheckBox控件.txt datagrid排序_选择_分页.txt DataSet对象.txt DotNET WinForm FAQ 16个.txt excel打印.txt EXCEL导出.txt EXCEL中合并单元格.txt mail.txt NET在RichTextBox控件加入...
在ASP.NET中,可以创建ASP.NET网站和ASP.NET应用程序,但是ASP.NET网站和ASP.NET应用程序开发过程和编译过程是有区别的。ASP.NET应用程序主要有以下特点: q 可以将ASP.NET应用程序拆分成多个项目以方便开发,管理和...
2.ASP.NET.2.0.高级编程(第4版) [1/7] 原书名: Professional ASP.NET 2.0 原出版社: Wrox 作者:(美)Bill Evjen, Scott Hanselman, Farhan Muhammad [同作者作品] [作译者介绍] 译者: 李敏波[同译者作品] ...