一份禮物： 自動填充SqlCommand.Parameters的類（2）
2024-07-21 02:22:31
供稿：網友
/// <summary>
/// 獲取存儲過程的相關表的列名和isnuable
/// </summary>
/// <param name="spid">存儲過程名稱</param>
/// <returns>hastable</returns>
public hashtable spdependontable(int32 spid)
{
try
{
//驗證連接
if(conn!=null && conn.state!=connectionstate.open)
{
conn.open();
}
else
{
conn= new sqlconnection(this.connectionstring);
conn.open();
}
string sqlstr="select name, isnullable as allownull from dbo.syscolumns where (id in (select distinct id from dbo.sysobjects where (id in (select distinct dbo.sysdepends.depid from dbo.sysdepends where (dbo.sysdepends.id ="+spid+")))))";
//獲取相關表的isnullable
sqldataadapter comm= new sqldataadapter(sqlstr,conn );
dataset depds=new dataset();
comm.fill(depds,"dbo.syscolumns");
hashtable dependtbl=new hashtable();
//建立hashtble
for(int n=0;n<depds.tables[0].rows.count;n++)
{
string keyname="@"+depds.tables["dbo.syscolumns"].rows[n]["name"];
boolean isnullabled=convert.toboolean( depds.tables["dbo.syscolumns"].rows[n]["allownull"]);
dependtbl.add(keyname,isnullabled);
}
return dependtbl;
}
catch(sqlexception se)
{
throw(se);
}
catch(exception e)
{
throw(e);
}
finally
{
if(conn.state ==connectionstate.open)
{
conn.close();
}
}
}
/// <summary>
/// 獲得指定表的所有字段對象
/// </summary>
/// <param name="tablename">表名</param>
/// <returns>system.data.datatable</returns>
public datatable getfields(string tablename)
{
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 * from "+tablename,conn);
comm.fillschema(mydataset, schematype.mapped,tablename);
return mydataset.tables[0];
}
catch(sqlexception se)
{
throw(se);
}
}
/// <summary>
///私有: 獲取數據類型(dbtype)
/// </summary>
/// <param name="typename">數據類型名稱</param>
/// <returns>dbtype</returns>
private dbtype getdbtype(string typename)
{
//dbtype t;
#region switch datatype
switch(typename)
{
//int64
case "bigint":
return dbtype.int64;
//break;
//int32
case "int":
return dbtype.int32;
//break;
//int16
case "smallint":
return dbtype.int16;
//break;
//byte
case "binary":
return dbtype.byte;
//break;
case "image":
return dbtype.byte;
//break;
case "varbinary":
return dbtype.byte;
//break;
case "tinyint":
return dbtype.byte;
//break;
//boolean
case "bit":
return dbtype.boolean;
//break;
//string
case "varchar":
return dbtype.string;
//break;
case "text":
return dbtype.string;
//break;
case "nvarchar":
return dbtype.string;
//break;
case "ntext":
return dbtype.string;
//break;
case "nchar":
return dbtype.string;
//break;
case "char":
return dbtype.string ;
//break;
//datetime
case "datetime":
return dbtype.datetime;
//break;
case "smalldatetime":
return dbtype.datetime;
//break;
case "timestamp":
return dbtype.datetime;
//break;
//double
case "float":
return dbtype.double;
//break;
//decimal
case "decimal":
return dbtype.decimal;
//break;
case "money":
return dbtype.decimal ;
//break;
case "smallmoney":
return dbtype.decimal;
//break;
default:
return dbtype.object;
//break;
}
#endregion
}
/// <summary>
/// 獲取數據類型(sqldbtype)
/// </summary>
/// <param name="typename">數據類型名稱</param>
/// <returns>sqldbtype</returns>
public sqldbtype getsqldbtype(string typename)
{
//sqldbtype t;
#region switch datatype
switch(typename)
{
//int64
case "bigint":
return sqldbtype.bigint;
//break;
//int32
case "int":
return sqldbtype.int;
//break;
//int16
case "smallint":
return sqldbtype.smallint;
//break;
//byte
case "binary":
return sqldbtype.binary;
//break;
case "image":
return sqldbtype.image;
//break;
case "varbinary":
return sqldbtype.varbinary;
//break;
case "tinyint":
return sqldbtype.tinyint;
//break;
//boolean
case "bit":
return sqldbtype.bit;
//break;
//string
case "varchar":
return sqldbtype.varchar;
//break;
case "text":
return sqldbtype.text;
//break;
case "nvarchar":
return sqldbtype.nvarchar;
//break;
case "ntext":
return sqldbtype.ntext ;
//break;
case "nchar":
return sqldbtype.nchar;
//break;
case "char":
return sqldbtype.char;
//break;
//datetime
case "datetime":
return sqldbtype.datetime;
//break;
case "smalldatetime":
return sqldbtype.smalldatetime;
//break;
case "timestamp":
return sqldbtype.timestamp;
//break;
//double
case "float":
return sqldbtype.float;
//break;
//decimal
case "decimal":
return sqldbtype.decimal;
//break;
case "money":
return sqldbtype.money;
//break;
case "smallmoney":
return sqldbtype.smallmoney;
//break;
default:
return sqldbtype.variant;
//break;
}
#endregion
}
#endregion
}
}
