一份禮物： 自動填充SqlCommand.Parameters的類（1）
2024-07-21 02:22:32
供稿：網友
//---------------------------------------------------
//日期： 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[i]["name"].tostring();//.tostring();
//參數的sqldbtype類型定義
sqldbtype ptp=this.getsqldbtype(mydataset.tables["dbo.systypes"].rows[i]["type"].tostring());
//參數的dbtype類型定義
//dbtype dtp=this.getdbtype(mydataset.tables["dbo.systypes"].rows[i]["type"].tostring());
//參數的長度定義
int flength=convert.toint32(mydataset.tables["dbo.syscolumns"].rows[i]["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[i]["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();
}
}
}
