//---------------------------------------------------
//日期： 2002.1.10
//作者： raxzhang
//版權(quán)： raxzhang
//環(huán)境： Microsoft Visual Studio.Net 7.0
//語言： Visual C#
//類型： 類文件，編譯后為.dll文件
//描述： 這是作為對(duì)數(shù)據(jù)操作的最常用的屬性和方法類。
// 是一個(gè)基礎(chǔ)類。可以被繼承和擴(kuò)展。
//注意： 使用這個(gè)類的條件是-1.dbo用戶。2.存儲(chǔ)過程的
// 參數(shù)名與表的字段名相差一個(gè)@
//---------------------------------------------------
using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient; 

namespace zyq.DBMapping
{
/// <summary>
/// 對(duì)SQL server進(jìn)行操作
/// </summary>
public class DataAccessSQL
{
#region class variables
private String connectionString;
private int _tablecount=-1;
private int _stroeprocedurecount=-1;
private SqlConnection conn=null;
#endregion
#region propertiesof class
/// <summary>
/// 屬性:數(shù)據(jù)庫存儲(chǔ)過程的個(gè)數(shù)(stat>0)
/// </summary>
public int StroeProcedureCount
{
get
{
if (this._stroeprocedurecount !=-1)
{
return this._stroeprocedurecount;
}
else
{
return this.GetStroeProcedures().Count;
}
}
}
/// <summary>
/// 屬性:數(shù)據(jù)庫用戶表的個(gè)數(shù)
/// </summary>
public int TablesCount
{
get
{
if(this._tablecount !=-1)
{
return this._tablecolscount;
}
else
{
return this.GetTables().Count;
}
 }

}
#endregion
#region structure of class
/// <summary>
/// 構(gòu)造函數(shù)
/// </summary>
/// <param name="ConnectionString">數(shù)據(jù)庫連接字符串，string</param>
public DataAccessSQL(string ConnectionString)
{
this.connectionString=ConnectionString;
this.conn =new SqlConnection(this.connectionString);
}
#endregion
#region Methods of class
/// <summary>
/// 獲得數(shù)據(jù)庫的所有表對(duì)象
/// </summary>
/// <returns>System.Data.SqlClient.SqlDataReader</returns>
public Hashtable GetTables()
{
try
{
Hashtable sptable=new Hashtable();
//驗(yàn)證連接
if(conn!=null && conn.State!=ConnectionState.Open)
{
conn.Open();
}
else
{
conn= new SqlConnection(this.connectionString);
conn.Open();
}
string Query = " select name, Id from sysobjectswhere (type='U') and (name <> 'dtproperties') order by name ";
//獲得指定數(shù)據(jù)庫中的所有用戶表的名稱和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>
/// 獲得數(shù)據(jù)庫的存儲(chǔ)過程的名稱及ID列表
/// </summary>
/// <returns>HasTable</returns>
public Hashtable GetStroeProcedures()
{
try
{
//驗(yàn)證連接
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) ";
//獲得指定數(shù)據(jù)庫中的所有用戶存儲(chǔ)過程的名稱和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>
///獲得數(shù)據(jù)庫的指定表的列對(duì)象定義
/// </summary>
/// <param name="spname">表名稱</param>
/// <returns>DataSet</returns>
public DataSet getTableColumns(string spname)
{

try
{
Int32 spid=-1; //指定表的ID號(hào)初始
//驗(yàn)證連接
if(conn!=null && conn.State!=ConnectionState.Closed)
{
conn.Open();
}
else
{
conn= new SqlConnection(this.connectionString);
conn.Open();
}
//獲取指定表名的ID號(hào)
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();
//驗(yàn)證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>
/// 獲得數(shù)據(jù)庫的指定表的列對(duì)象定義的列數(shù)組
/// </summary>
/// <param name="spid">表名稱</param>
/// <returns>DataSet</returns>
public DataSet getTableColumns(Int32 spid)
{
try
{
DataSet myDataSet=new DataSet();
//驗(yàn)證連接
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對(duì)象建立存儲(chǔ)過程的參數(shù)數(shù)組
/// </summary>
/// <remarks >參數(shù)只付值ParameterName,SqlDbType,IsNullable,Direction</remarks>
/// <param name="sqlcommand">SqlCommand</param>
/// <returns>SqlCommand</returns>
public SqlCommand getStroeProcedureParamsByName(SqlCommand sqlcommand)//, string spname)
{
try
{
Int32 spid=-1; //初始化存儲(chǔ)過程的ID
//驗(yàn)證連接
if(conn!=null && conn.State!=ConnectionState.Open)
{
conn.Open();
}
else
{
conn= new SqlConnection(this.connectionString);
conn.Open();
}
//獲取存儲(chǔ)過程的名稱
string spname=sqlcommand.CommandText;
//獲取存儲(chǔ)過程的ID號(hào)
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);
}
//驗(yàn)證ID號(hào)
if(spid==0 ||spid==-1)
throw new Exception ("StroedProcedure is not existed!");
//創(chuàng)建參數(shù)數(shù)組
return getStroeProcedureParamsByID( sqlcommand ,spid);
}
catch(SqlException se)
{
throw(se);
}
finally
{
if(conn.State ==ConnectionState.Open)
{
conn.Close();
}
}
}
/// <summary>
///為傳入SqlCommand對(duì)象建立存儲(chǔ)過程的參數(shù)數(shù)組
/// </summary>
/// <remarks >參數(shù)只付值ParameterName,SqlDbType,IsNullable,Direction</remarks>
/// <param name="sqlcommand">SqlCommand</param>
/// <param name="spid">存儲(chǔ)過程ID</param>
/// <returns>SqlCommand</returns>
public SqlCommand getStroeProcedureParamsByID(SqlCommand sqlcommand, Int32 spid)
{
try
{
//獲取存儲(chǔ)過程相關(guān)表的isnullable定義
Hashtable dependtble=this.SPDependonTable(spid);
DataSet myDataSet=new DataSet();
//驗(yàn)證連接
if(conn!=null && conn.State!=ConnectionState.Open)
{
conn.Open();
}
else
{
conn= new SqlConnection(this.connectionString);
conn.Open();
}
//獲取指定存儲(chǔ)過程的參數(shù)內(nèi)容
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++)
{
//參數(shù)名稱
string pname=myDataSet.Tables["dbo.syscolumns"].Rows[i]["name"].ToString();//.ToString();
//參數(shù)的SqlDBType類型定義
SqlDbType ptp=this.getSqlDbType(myDataSet.Tables["dbo.systypes"].Rows[i]["Type"].ToString());
//參數(shù)的DBType類型定義
//DbType dtp=this.getDbType(myDataSet.Tables["dbo.systypes"].Rows[i]["Type"].ToString());
//參數(shù)的長度定義
int flength=Convert.ToInt32(myDataSet.Tables["dbo.syscolumns"].Rows[i]["length"]);
//創(chuàng)建一個(gè)參數(shù)
sqlcommand.Parameters.Add(pname,ptp,flength);
//定義參數(shù)可否為空值，由相關(guān)表的isnullable定義得到
sqlcommand.Parameters[pname].IsNullable =(Boolean)dependtble[pname];
//sqlcommand.Parameters[pname].DbType =dtp;
//定義參數(shù)的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();
}

}
}