using system;
using system.io;
using system.data;
using system.data.sqlclient;
using system.configuration;
using system.collections;
namespace sqlserverbase
{
/// <summary>
///内部类:存储过程的返回值记录类
/// </summary>
public class sqlresult
{
public bool succeed; //存储过程是否执行成功.
public hashtable outputvalues; // 存储过程output值,放在(hashtable)表outputvalues里.
public datatable datatable; //存储过程返回的结果集,放在(datatable)表datatable里.
public dataset dataset; //存储过程返回的结果集,放在dataset表中
public string errormessage; //访问数据库失败
public int inflecntnum;
public sqlresult()
{
succeed = false;
outputvalues = new hashtable();
datatable=new datatable();
dataset=new dataset();
errormessage = "";
}
}
/// <summary>
/// ====================***调用存储过程和sql的基类***============================
/// abstract:该类不能被实例化,只能通过派生子类来使用它
/// </summary>
public abstract class spsql_base : idisposable
{
public spsql_base() : this("","")
{
}
//重载
public spsql_base(string sp_name,string sql_name)
{
this.procedurename = sp_name;
this.sqlname = sql_name;
}
//私有成员变量
private string sp_name;
private string sql_name;
private sqlconnection myconnection;
private sqlcommand mycommand;
private sqlparameter myparameter;//存储过程参数
//公共属性
public string procedurename//获取和设置存储过程名
{
get
{
return this.sp_name;
}
set
{
this.sp_name = value;
}
}
//公共属性
public string sqlname//获取和设置存储过程名
{
get
{
return this.sql_name;
}
set
{
this.sql_name = value;
}
}
/// <summary>
/// 调用存储过程
/// </summary>
/// <param name="parameters">参数集合</param>
/// <returns></returns>
public sqlresult call_sp(params object[] parameters)
{
string strconn=configurationsettings.appsettings["connectionstring"];
//存储过程的返回值记录类
sqlresult result = new sqlresult();
myconnection = new sqlconnection(strconn);
mycommand = new sqlcommand(this.procedurename, myconnection);
mycommand.commandtype = commandtype.storedprocedure;
sqldataadapter myadapter = new sqldataadapter(mycommand);
myconnection.open();
//将参数添加到存储过程的参数集合
getprocedureparameter(result,parameters);
//开始事物
using(sqltransaction trans = myconnection.begintransaction())
{
try
{
if(trans!=null)
{
mycommand.transaction = trans;
}
//填充数据,将结果填充到sqlresult集中
myadapter.fill(result.dataset);
if(result.dataset.tables.count>0)
result.datatable=result.dataset.tables[0].copy();
//将输出参数的值添加到result的outputvalues
getoutputvalue(result);
//提交事物
trans.commit();
}
catch(exception e)
{
result.errormessage = e.message;
//事物回滚
trans.rollback();
}
//如果捕捉了异常，但仍会执行包括在 finally 块中的输出语句
finally
{
myadapter.dispose();
mycommand.dispose();
myconnection.close();
myconnection.dispose();
}
}
return result;
}
/// <summary>
/// 将参数添加到存储过程的参数集合
/// </summary>
/// <param name="parameters"></param>
private void getprocedureparameter(sqlresult result,params object[] parameters)
{
sqlcommand mycommand2 = new sqlcommand();
mycommand2.connection = this.myconnection;
mycommand2.commandtext = "select * from information_schema.parameters where specific_name='" +this.procedurename+ "' order by ordinal_position";
sqldatareader reader = null;
try
{
reader = mycommand2.executereader();
int i = 0;
while(reader.read())
{
myparameter = new sqlparameter();
myparameter.parametername = reader["parameter_name"].tostring();
myparameter.direction = reader["parameter_mode"].tostring()=="in"?parameterdirection.input:parameterdirection.output;
switch(reader["data_type"].tostring())
{
//bigint
case "bigint":
if(myparameter.direction == parameterdirection.input)
myparameter.value = convert.toint64(parameters[i]);
myparameter.sqldbtype = sqldbtype.bigint;
break;
//binary
//bit
case "bit" :
if(myparameter.direction == parameterdirection.input)
myparameter.value = convert.toboolean(parameters[i]);
myparameter.sqldbtype = sqldbtype.bit;
break;
//char
case "char" :
if(myparameter.direction == parameterdirection.input)
myparameter.value = (string)parameters[i];
myparameter.size = convert.toint32(reader["character_maximum_length"]);
myparameter.sqldbtype = sqldbtype.char;
break;
//datetime
case "datetime" :
if(myparameter.direction == parameterdirection.input)
myparameter.value = convert.todatetime(parameters[i]);
myparameter.sqldbtype = sqldbtype.datetime;
break;
//decimal
case "decimal" :
if(myparameter.direction == parameterdirection.input)
myparameter.value = (decimal)parameters[i];
myparameter.sqldbtype = sqldbtype.decimal;
myparameter.precision = (byte)reader["numeric_precision"];
myparameter.scale = byte.parse(reader["numeric_scale"].tostring());
break;
//float
case "float" :
if(myparameter.direction == parameterdirection.input)
myparameter.value = (float)parameters[i];
myparameter.sqldbtype = sqldbtype.float;
break;
//image
case "image" :
if(myparameter.direction == parameterdirection.input)
{
myparameter.value=(byte[])parameters[i];
}
myparameter.sqldbtype = sqldbtype.image;
break;
//int
case "int" :
if(myparameter.direction == parameterdirection.input)
myparameter.value = convert.toint32(parameters[i].tostring());
myparameter.sqldbtype = sqldbtype.int;
break;
//money
case "money":
if(myparameter.direction==parameterdirection.input)
myparameter.value=convert.todecimal(parameters[i]);
myparameter.sqldbtype=sqldbtype.money;
break;
//nchar
case "nchar" :
if(myparameter.direction == parameterdirection.input)
myparameter.value = (string)parameters[i];
myparameter.size = convert.toint32(reader["character_maximum_length"]);
myparameter.sqldbtype = sqldbtype.nchar;
break;
//ntext
case "ntext" :
if(myparameter.direction == parameterdirection.input)
myparameter.value = (string)parameters[i];
myparameter.sqldbtype = sqldbtype.ntext;
break;
//numeric
case "numeric" :
if(myparameter.direction == parameterdirection.input)
myparameter.value = (decimal)parameters[i];
myparameter.sqldbtype = sqldbtype.decimal;
myparameter.precision = (byte)reader["numeric_precision"];
myparameter.scale = byte.parse(reader["numeric_scale"].tostring());
break;
//nvarchar
case "nvarchar" :
if(myparameter.direction == parameterdirection.input)
myparameter.value = convert.tostring(parameters[i]);
myparameter.size = convert.toint32(reader["character_maximum_length"]);
myparameter.sqldbtype = sqldbtype.nvarchar;
break;
//real
case "real":
if(myparameter.direction==parameterdirection.input)
myparameter.value=convert.tosingle(parameters[i]);
myparameter.sqldbtype = sqldbtype.real;
break;
//smalldatetime
case "smalldatetime" :
if(myparameter.direction == parameterdirection.input)
myparameter.value = convert.todatetime(parameters[i]);
myparameter.sqldbtype = sqldbtype.datetime;
break;
//smallint
case "smallint" :
if(myparameter.direction == parameterdirection.input)
myparameter.value = convert.toint16(parameters[i].tostring());
myparameter.sqldbtype = sqldbtype.smallint;
break;
//smallmoney
case "smallmoney":
if(myparameter.direction==parameterdirection.input)
myparameter.value=convert.todecimal(parameters[i]);
myparameter.sqldbtype=sqldbtype.smallmoney;
break;
//sql_variant
//text
case "text" :
if(myparameter.direction == parameterdirection.input)
myparameter.value = (string)parameters[i];
myparameter.sqldbtype = sqldbtype.text;
break;
//timestamp
//tinyint
case "tinyint":
if(myparameter.direction == parameterdirection.input)
myparameter.value = convert.tobyte(parameters[i]);
myparameter.sqldbtype = sqldbtype.tinyint;
break;
//uniqueidentifier
//varbinary
case "varbinary":
if(myparameter.direction==parameterdirection.input)
myparameter.value=(byte[])parameters[i];
myparameter.sqldbtype = sqldbtype.varbinary;
break;
//varchar
case "varchar" :
if(myparameter.direction == parameterdirection.input)
myparameter.value = (string)parameters[i];
myparameter.size = convert.toint32(reader["character_maximum_length"]);
myparameter.sqldbtype = sqldbtype.varchar;
break;
default :
break;
}
i++;
mycommand.parameters.add(myparameter);
}
}
catch(exception e)
{
result.errormessage = e.message;
}
finally
{
if(reader!=null)
{
reader.close();
}
mycommand2.dispose();
}
}
/// <summary>
/// 将输出的值添加到result的outputvalues
/// </summary>
/// <param name="result"></param>
private void getoutputvalue(sqlresult result)
{
if(result.succeed==false)
{
result.succeed=true;
}
foreach(sqlparameter parameter in mycommand.parameters)
{
if(parameter.direction == parameterdirection.output)
{
//hashtab表是一个键值对
result.outputvalues.add(parameter.parametername, parameter.value);
}
}
}
public void dispose()
{
dispose(true);
gc.suppressfinalize(true);
}
protected virtual void dispose(bool disposing)
{
if (! disposing)
return;
if(myconnection != null)
{
myconnection.dispose();
}
}
//=======end======
//=======begin====
/// <summary>
/// 调用sql的基类
/// </summary>
/// <param name="parameters">参数集合</param>
/// <returns></returns>
public sqlresult call_sql()
{
string strconn=configurationsettings.appsettings["connectionstring"];
//存储过程的返回值记录类
sqlresult result = new sqlresult();
myconnection = new sqlconnection(strconn);
mycommand = new sqlcommand(this.sql_name, myconnection);
mycommand.commandtype = commandtype.text;
sqldataadapter myadapter = new sqldataadapter(mycommand);
myconnection.open();
using(sqltransaction trans = myconnection.begintransaction())
{
try
{
if(trans!=null)
{
mycommand.transaction = trans;
}
//填充数据,将结果填充到sqlresult集中
myadapter.fill(result.datatable);
result.succeed = true;
//提交事物
trans.commit();
}
catch(exception e)
{
result.succeed = false;
result.errormessage = e.message;
}
//如果捕捉了异常，但仍会执行包括在 finally 块中的输出语句
finally
{
myadapter.dispose();
mycommand.dispose();
myconnection.close();
myconnection.dispose();
}
}
return result;
}
//=======end=========
}
}
继承此类后直接调用，如下：db：northwind
public class datest : spsql_base
{
public datest()
{}
public sqlresult sqltest()
{
base.sqlname="select employeeid,lastname from dbo.employees";
return base.call_sql();
}
public sqlresult sptest()
{
base.procedurename="custorderhist";
return base.call_sp("alfki");
}
}配置文件
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appsettings>
<add key="connectionstring" value="server=bim-7c67612053c;database=northwind;uid=sa;pwd=;" />
</appsettings>
</configuration>显示查询结果：
private void datashow_load(object sender, system.eventargs e)
{
datest da=new datest();
if(da.sptest().succeed && da.sqltest().succeed)
{
this.datasp.datasource=da.sptest().datatable;
this.datasql.datasource=da.sqltest().datatable;
}
}
此类还有待完善，诸如存储过程参数为//binary、//sql_variant、//timestamp、//uniqueidentifier这些类型时还不能执行查询，其余bug请大家多多指正～～
感谢以前一起在二炮工作过的师兄们提供源代码，小弟只是做简单修改，谢谢各位师兄！
出处：shanvenleo blog
新闻热点
疑难解答
