.net教程：一份礼物： 自动填充sqlcommand.parameters的类（1）
//---------------------------------------------------
//日期： 2002.1.10
//作者： raxzhang
//版权： raxzhang
//环境： microsoft visual studio.net 7.0
//语言： visual c#
//类型： 类文件，编译后为.dll文件
//描述： 这是作为对数据操作的最常用的属性和方法类。
// 是一个基础类。可以被继承和扩展。
//注意： 使用这个类的条件是-1.dbo用户。2.存储过程的
// 参数名与表的字段名相差一个@
//---------------------------------------------------
using system;
using system.collections;
using system.data;
using system.data.sqlclient;
namespace zyq.dbmapping
{
/// <summary>
/// 对sql server进行操作
/// </summary>
public class dataaccesssql
{
#region class variables
private string connectionstring;
private int _tablecount=-1;
private int _stroeprocedurecount=-1;
private sqlconnection conn=null;
#endregion
#region properties of class
/// <summary>
/// 属性:数据库存储过程的个数(stat>0)
/// </summary>
public int stroeprocedurecount
{
get
{
if (this._stroeprocedurecount !=-1)
{
return this._stroeprocedurecount;
}
else
{
return this.getstroeprocedures().count;
}
}
}
/// <summary>
/// 属性:数据库用户表的个数
/// </summary>
public int tablescount
{
get
{
if(this._tablecount !=-1)
{
return this._tablecolscount;
}
else
{
return this.gettables().count;
}
}
}
#endregion
#region structure of class
/// <summary>
/// 构造函数
/// </summary>
/// <param name="connectionstring">数据库连接字符串，string</param>
public dataaccesssql(string connectionstring)
{
this.connectionstring=connectionstring;
this.conn =new sqlconnection(this.connectionstring);
}
#endregion
#region methods of class
/// <summary>
/// 获得数据库的所有表对象
/// </summary>
/// <returns>system.data.sqlclient.sqldatareader</returns>
public hashtable gettables()
{
try
{
hashtable sptable=new hashtable();
//验证连接
if(conn!=null && conn.state!=connectionstate.open)
{
conn.open();
}
else
{
conn= new sqlconnection(this.connectionstring);
conn.open();
}
string query = " select name, id from sysobjects where (type='u') and (name <> 'dtproperties') order by name ";
//获得指定数据库中的所有用户表的名称和id
sqlcommand comm= new sqlcommand(query,conn);
sqldatareader reader=comm.executereader(commandbehavior.closeconnection);
//录制hashtable
while(reader.read())
{
sptable.add(reader.getint32(1),reader.getstring(0));
}
this._tablecount =sptable.count;
return sptable;
}
catch(sqlexception se)
{
throw(se);
}
}
/// <summary>
/// 获得数据库的存储过程的名称及id列表
/// </summary>
/// <returns>hastable</returns>
public hashtable getstroeprocedures()
{
try
{
//验证连接
if(conn!=null && conn.state!=connectionstate.closed)
{
conn.open();
}
else
{
conn= new sqlconnection(this.connectionstring);
conn.open();
}
hashtable sptable=new hashtable();
string query = " select name, id from sysobjects where (type = 'p') and (status > 0) ";
//获得指定数据库中的所有用户存储过程的名称和id
sqlcommand comm= new sqlcommand(query,conn);
sqldatareader reader=comm.executereader(commandbehavior.closeconnection);
//录制hashtable
while(reader.read())
{
sptable.add(reader.getint32(1),reader.getstring(0));
}
this._stroeprocedurecount =sptable.count;
return sptable;
}
catch(sqlexception se)
{
throw(se);
}
catch(exception e)
{
throw(e);
}
finally
{
if(conn.state==connectionstate.open )
conn.close();
}
}
/// <summary>
///获得数据库的指定表的列对象定义
/// </summary>
/// <param name="spname">表名称</param>
/// <returns>dataset</returns>
public dataset gettablecolumns(string spname)
{
try
{
int32 spid=-1; //指定表的id号初始
//验证连接
if(conn!=null && conn.state!=connectionstate.closed)
{
conn.open();
}
else
{
conn= new sqlconnection(this.connectionstring);
conn.open();
}
//获取指定表名的id号
sqlcommand comm= new sqlcommand("select id from dbo.sysobjects where name = '"+spname,conn);
sqldatareader reader=comm.executereader(commandbehavior.closeconnection);
while(reader.read())
{
spid = reader.getint32(0);
}
reader.close();
//验证id
if(spid==0 ||spid==-1)
throw new exception ("stroedprocedure is not existed!");
//获得表的列定义
return gettablecolumns(spid);
}
catch(sqlexception se)
{
throw(se);
}
finally
{
if(conn.state ==connectionstate.open)
conn.close();
}
}
/// <summary>
/// 获得数据库的指定表的列对象定义的列数组
/// </summary>
/// <param name="spid">表名称</param>
/// <returns>dataset</returns>
public dataset gettablecolumns(int32 spid)
{
try
{
dataset mydataset=new dataset();
//验证连接
if(conn!=null && conn.state!=connectionstate.closed)
{
conn.open();
}
else
{
conn= new sqlconnection(this.connectionstring);
conn.open();
}
sqldataadapter comm= new sqldataadapter("select dbo.syscolumns.name, dbo.systypes.name as type, dbo.syscolumns.length,dbo.syscolumns.isoutparam, dbo.syscolumns.isnullable from dbo.syscolumns inner join dbo.systypes on dbo.syscolumns.xtype = dbo.systypes.xtype where dbo.syscolumns.id ='"+spid+"'",conn);
//获取指定表的列定义
comm.fill(mydataset,"dbo.systypes");
comm.fill(mydataset,"dbo.systypes");
/*this._tablecolscount=mydataset.tables[0].rows.count;
datacolumn[] dcols=new datacolumn[5];
dcols[0]=mydataset.tables["dbo.syscolumns"].columns["name"];
dcols[1]=mydataset.tables["dbo.systypes"].columns["type"];
dcols[2]=mydataset.tables["dbo.syscolumns"].columns["length"];
dcols[3]=mydataset.tables["dbo.syscolumns"].columns["isoutparam"];
dcols[3]=mydataset.tables["dbo.syscolumns"].columns["isnullable"];*/
return mydataset;
}
catch(sqlexception se)
{
throw(se);
}
finally
{
if(conn.state ==connectionstate.open)
{
conn.close();
}
}
}
/// <summary>
/// 为传入sqlcommand对象建立存储过程的参数数组
/// </summary>
/// <remarks >参数只付值parametername,sqldbtype,isnullable,direction</remarks>
/// <param name="sqlcommand">sqlcommand</param>
/// <returns>sqlcommand</returns>
public sqlcommand getstroeprocedureparamsbyname(sqlcommand sqlcommand)//, string spname)
{
try
{
int32 spid=-1; //初始化存储过程的id
//验证连接
if(conn!=null && conn.state!=connectionstate.open)
{
conn.open();
}
else
{
conn= new sqlconnection(this.connectionstring);
conn.open();
}
//获取存储过程的名称
string spname=sqlcommand.commandtext;
//获取存储过程的id号
sqlcommand comm= new sqlcommand("select id from dbo.sysobjects where name = '"+spname+"'",conn);
sqldatareader reader=comm.executereader(commandbehavior.closeconnection);
while(reader.read())
{
spid = reader.getint32(0);
}
//验证id号
if(spid==0 ||spid==-1)
throw new exception ("stroedprocedure is not existed!");
//创建参数数组
return getstroeprocedureparamsbyid( sqlcommand ,spid);
}
catch(sqlexception se)
{
throw(se);
}
finally
{
if(conn.state ==connectionstate.open)
{
conn.close();
}
}
}
/// <summary>
///为传入sqlcommand对象建立存储过程的参数数组
/// </summary>
/// <remarks >参数只付值parametername,sqldbtype,isnullable,direction</remarks>
/// <param name="sqlcommand">sqlcommand</param>
/// <param name="spid">存储过程id</param>
/// <returns>sqlcommand</returns>
public sqlcommand getstroeprocedureparamsbyid(sqlcommand sqlcommand, int32 spid)
{
try
{
//获取存储过程相关表的isnullable定义
hashtable dependtble=this.spdependontable(spid);
dataset mydataset=new dataset();
//验证连接
if(conn!=null && conn.state!=connectionstate.open)
{
conn.open();
}
else
{
conn= new sqlconnection(this.connectionstring);
conn.open();
}
//获取指定存储过程的参数内容
sqldataadapter comm= new sqldataadapter("select dbo.syscolumns.name, dbo.systypes.name as type, dbo.syscolumns.length,dbo.syscolumns.isoutparam from dbo.syscolumns inner join dbo.systypes on dbo.syscolumns.xtype = dbo.systypes.xtype where dbo.syscolumns.id ="+spid,conn);//, dbo.syscolumns.isnullable
comm.fill(mydataset,"dbo.syscolumns");
comm.fill(mydataset,"dbo.systypes");
int paramcount = mydataset.tables[0].rows.count;
for(int i=0;i<paramcount;i++)
{
//参数名称
string pname=mydataset.tables["dbo.syscolumns"].rows["name"].tostring();//.tostring();
//参数的sqldbtype类型定义
sqldbtype ptp=this.getsqldbtype(mydataset.tables["dbo.systypes"].rows["type"].tostring());
//参数的dbtype类型定义
//dbtype dtp=this.getdbtype(mydataset.tables["dbo.systypes"].rows["type"].tostring());
//参数的长度定义
int flength=convert.toint32(mydataset.tables["dbo.syscolumns"].rows["length"]);
//创建一个参数
sqlcommand.parameters.add(pname,ptp,flength);
//定义参数可否为空值，由相关表的isnullable定义得到
sqlcommand.parameters[pname].isnullable =(boolean)dependtble[pname];
//sqlcommand.parameters[pname].dbtype =dtp;
//定义参数的input和output
if((int)(mydataset.tables["dbo.syscolumns"].rows["isoutparam"])==1)
{
sqlcommand.parameters[pname].direction =parameterdirection.output;
}
else
{
sqlcommand.parameters[pname].direction =parameterdirection.input;
}
}
this._spparamcount=paramcount;
return sqlcommand;
}
catch(sqlexception se)
{
throw(se);
}
finally
{
if(conn.state ==connectionstate.open)
{
conn.close();
}
}
}
