自动填充SqlCommand.Parameters的类（1）
2018-10-16 21:11:34
供稿：网友
//---------------------------------------------------
//日期： 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();
}
}
}
