using System;using Microsoft.Win32;using System.Data;using System.Data.SqlClient;using System.Collections;using System.Text;using System.Web.UI.HtmlControls;using System.Configuration;
namespace USTC{ /// <summary> /// vDB 的摘要说明。 /// class vDB is like a virtual Database, /// All operations on Database are done by this class. /// </summary> internal class ParameterList { private ArrayList al; #region SQL调用参数的准备 public ParameterList() { al = new ArrayList(); } #endregion //======================================= public ParameterList(int capacity) { al = new ArrayList(capacity); } //======================================= public void add(string paramName, SqlDbType type, int size, object paramValue) { SqlParameter odp = new SqlParameter(paramName, type); if((type!=SqlDbType.NText)&&(type!=SqlDbType.Text)) odp.Size=size; odp.Value = paramValue; odp.Direction = ParameterDirection.Input; al.Add(odp); } //======================================= public void add(string paramName, SqlDbType type, int size, object paramValue, ParameterDirection pd) { SqlParameter odp = new SqlParameter(paramName, type); if((type!=SqlDbType.NText)&&(type!=SqlDbType.Text)) odp.Size=size; odp.Value = paramValue; odp.Direction = pd; al.Add(odp); } //======================================= public SqlParameter getParameter(int index) { return (SqlParameter)al[index]; } public object getParamValue(int index) { return this.getParameter(index).Value; } public int Length { get { return al.Count; } } } public class SqlDbc { private string connectStringForReading =null;// "Provider=Sybase.ASESqlProvider;Server Name=syit7,5000;Catalog=hsinfo;User Id=sa;Password="; private string connectStringForWriting = null;//"Provider=Sybase.ASESqlProvider;Server Name=syit2,5000;Catalog=hsdsj;User Id=sa;Password="; ///for oracleconnecting//ORACLE="Provider=MSDAORA;uid=查寻分析器的登陆名;pwd=?;server=数据库服务名" ///OR "Data Source=数据库服务名;Integrated Security=yes"; //for sqlconnecting//sqlserver= "server=localhost;uid=查寻分析器的登陆名;pwd=?;database=数据库名"; //OR "server=localhost;Integrated Security=SSPI;database=pubs"; //Access ="Microsoft.Jet.Sql.4.0;Data Source=数据库路径"；//集成WINDOWS安全方式连接 //Visual Foxpro="Driver=Microsoft Visual FoxPro Driver;SourceType=DBC;SourceDB=数据库名"; // OR "Driver=Microsoft Visual FoxPro Driver;SourceType=DBC;SourceDB=该表所在路径名"; private SqlConnection conn; /** * 使用Oracle格式的DriverManager * @see LPWDatabaseOperation#getUseContextType */ private int useOracle = 0; /** * 使用Sybase格式的DriverManager * @see LPWDatabaseOperation#getUseContextType */ private int useSybase = 1; /** * 使用MySQL格式的DriverManager * @see LPWDatabaseOperation#getUseContextType */ private int useMysql = 2; /** * 使用SQLServer格式的DriverManager * @see LPWDatabaseOperation#getUseContextType */ private int useSqlserver = 3; /** * 使用DB2格式的DriverManager * @see LPWDatabaseOperation#getUseContextType */ private int useAccess = 4; /** * 使用Informix格式的DriverManager * @see LPWDatabaseOperation#getUseContextType */ private int useFoxpro = 5; /** * 使用PostgreSQL格式的DriverManager * @see LPWDatabaseOperation#getUseContextType */ private int usePostgresql = 6; /** * 使用Jdbc-Odbc-Bridge格式的DriverManager * @see LPWDatabaseOperation#getUseContextType */ private int useInterbase = 7; /** * 使用Tomcate格式的DataSource * @see LPWDatabaseOperation#getUseContextType */ private int useTomcateDataSource = 8; /** * 使用WebLogic格式的DataSource * @see LPWDatabaseOperation#getUseContextType */ private int useWeblogicDataSource = 9; /** * 使用WebSphere格式的DataSource * @see LPWDatabaseOperation#getUseContextType */ private int useWebsphereDataSource = 10; private int useContextType=1; private int pageSize=20,pageCount,absolutePage,recordCount; private ParameterList pl; private DataSet resultSet;
public SqlDbc() { this.connectStringForReading=ConfigurationSettings.AppSettings["connectionString"]; } public SqlDbc(int useContextType,string user,string passwd,string database,string server) { if(useContextType==this.useOracle) {this.connectStringForReading=(@"Provider=MSDAORA;uid="+user+";pwd="+passwd+";server="+server);} if(useContextType==this.useSybase) { this.connectStringForReading=(@"Provider=Sybase.ASESqlProvider;Server Name="+server+";Catalog="+database+";User Id="+user+";Password="+passwd); } if(useContextType==this.useSqlserver) {//="server=SYIT99;uid=sa;pwd=111111;database=jacnew" this.connectStringForReading=(@"server="+server+";uid="+user+";pwd="+passwd+";database="+database); } if(useContextType==this.useFoxpro) { this.connectStringForReading=("Driver=Microsoft Visual FoxPro Driver;SourceType=DBC;SourceDB="+database); } if(useContextType==this.useAccess) { this.connectStringForReading=(@"Provider=Microsoft.Jet.Sql.4.0;Data Source="+database); } // // TODO: 在此处添加构造函数逻辑 // } /// <summary> /// 从注册表读取数据。 /// 如果无法读取，就报错。 /// </summary> /// <returns></returns> private bool checkForConnectString() { if (this.connectStringForReading == null && connectStringForWriting == null) { RegistryKey rk = Registry.LocalMachine.OpenSubKey(@"Software/ustc", false); this.connectStringForWriting = (string)rk.GetValue("connectStringForWriting"); this.connectStringForReading = this.connectStringForWriting; }
if (this.connectStringForReading == null && this.connectStringForWriting == null) return false; else return true; } /// <summary> /// 获得对数据库的连接 /// </summary> /// <param name="isReadOnly">是否以只读方式连接数据库</param> /// <returns>数据库连接</returns> public void open() { //if(isReadOnly) this.conn=new SqlConnection(this.connectStringForReading); //else // this.conn= getSqlConnection(this.connectStringForWriting); this.conn.Open(); }
/// <summary> /// 设定SQL需要提供的参数个数 /// </summary> /// <param name="num">参数个数的值</param> public void addparameters(int num) { this.pl=new ParameterList(num); } /// <summary> /// 为SQL添加参数的值 /// </summary> /// <param name="param">参数在SQL中的名称</param> /// <param name="type">类型</param> /// <param name="size">尺寸</param> /// <param name="paramvalue">参数的实际值</param> public void add(string param, SqlDbType type,int size,string paramvalue) { this.pl.add(param,type,size,paramvalue); }
/// <summary> /// 获得对数据库的连接 /// </summary> /// <param name="isReadOnly">连接数据库字符串</param> /// <returns>数据库连接</returns> public void open(string connectString) { this.connectStringForReading=connectString; this.conn= new SqlConnection(connectString); this.conn.Open(); }
/// <summary> /// 关闭数据连接 /// </summary> public void close() { this.conn.Close(); } //for getting data set; private DataSet getDataSet(string searchString, bool isStoredProcedure) { if(!this.checkForConnectString()) return null;
DataSet ds = new DataSet(); SqlDataAdapter adapter = new SqlDataAdapter(searchString,this.conn); if (isStoredProcedure) adapter.SelectCommand.CommandType = CommandType.StoredProcedure; adapter.Fill(ds); adapter.Dispose();
return ds; }
//for getting scalar data private object getScalarData( string searchString, bool isStoredProcedure) { object returnData = new object();
if(!this.checkForConnectString()) return null;
SqlCommand command = new SqlCommand(searchString,this.conn);
if (isStoredProcedure) command.CommandType = CommandType.StoredProcedure;
command.ExecuteScalar(); return command.ExecuteScalar(); }
private DataSet getDataSet( string searchString, ParameterList parameters, bool isStoredProcedure) { if( !this.checkForConnectString()) return null;
DataSet ds = new DataSet(); SqlDataAdapter adapter = new SqlDataAdapter(searchString, this.conn); if (isStoredProcedure) adapter.SelectCommand.CommandType = CommandType.StoredProcedure; if(parameters!=null) for (int i = 0; i < parameters.Length; ++i) {
/*SqlParameter param = adapter.SelectCommand.Parameters.Add(parameters.getParamName(i), parameters.getParamType(i), parameters.getParamSize(i));
param.Direction = parameters.getParamDirection(i); param.Value = parameters.getParamValue(i);*/ adapter.SelectCommand.Parameters.Add(parameters.getParameter(i));
}
adapter.Fill(ds); adapter.Dispose();
return ds; } private object getScalarData( string searchString, ParameterList parameters, bool isStoredProcedure) {
if( !this.checkForConnectString()) return null;
SqlCommand command = new SqlCommand(searchString, this.conn); if(parameters!=null) for (int i = 0; i < parameters.Length; ++i) { command.Parameters.Add(parameters.getParameter(i)); }
if(isStoredProcedure) command.CommandType = CommandType.StoredProcedure;
return command.ExecuteScalar(); } //=============================================================== //专门为 storyproc而做。(不需要返回的) private SqlDataReader getDataSet( bool isStoredProcedure,string searchString, ParameterList parameters) { if( !this.checkForConnectString()) return null; SqlCommand command = new SqlCommand(searchString, this.conn); // SqlDataReader ds = new SqlDataReader(); if (isStoredProcedure) command.CommandType = CommandType.StoredProcedure; if(parameters!=null) for (int i = 0; i < parameters.Length; ++i) { command.Parameters.Add(parameters.getParameter(i)); } SqlDataReader ds=command.ExecuteReader(); return ds; } //========================================================================= //专门为 storyproc而做。(需要返回的) private SqlDataReader getDataSet( SqlCommand command,string searchString, ParameterList parameters) { if( !this.checkForConnectString()) return null; //SqlCommand command = new SqlCommand(searchString, conn); // SqlDataReader ds = new SqlDataReader(); command.CommandType = CommandType.StoredProcedure; for (int i = 0; i < parameters.Length; ++i) { command.Parameters.Add(parameters.getParameter(i)); } SqlDataReader ds=command.ExecuteReader(); return ds; } //========================================================================== //for insert delete update operations private int updateRows( string updateString, bool isStoredProcedure) { if( !this.checkForConnectString()) return -1;
SqlCommand command = new SqlCommand(updateString, this.conn); if (isStoredProcedure) command.CommandType = CommandType.StoredProcedure; return command.ExecuteNonQuery(); } //for insert delete update operations private int updateRows( string updateString, ParameterList parameters, bool isStoredProcedure) { if( !this.checkForConnectString()) return -1;
SqlCommand command = new SqlCommand(updateString, this.conn);
if (isStoredProcedure) command.CommandType = CommandType.StoredProcedure; if(parameters!=null) for (int i = 0; i < parameters.Length; ++i) { /*SqlParameter param = command.Parameters.Add(parameters.getParamName(i), parameters.getParamType(i), parameters.getParamSize(i)); param.Direction = parameters.getParamDirection(i); param.Value = parameters.getParamValue(i);*/ command.Parameters.Add(parameters.getParameter(i)); }
return command.ExecuteNonQuery();
} /// <summary> /// 执行SQL语句，并且返回数据。 /// </summary> /// <param name="sqlString">select,update或delete语句</param> /// <param name="parameters">参量</param> /// <param name="isStoredProcedure">是存储过程么？</param> /// <returns></returns> public object getData( string sqlString,bool isStoredProcedure) { string sql=sqlString.Trim(); string sub=sql.Substring(0,1).ToString().ToUpper(); if(sub=="S") { this.resultSet =(DataSet)getDataSet(sql,pl, isStoredProcedure); return this.resultSet; } else { return updateRows(sql,pl,isStoredProcedure);
} }
public int getUseContextType() { return useContextType; }
/** * 设置当前将要显示的页码，每页显示的数据行数由setPageSize方法的参数值决定， * 当输入的页码值小于1时，将显示第一页数据； * 当输入的页码值大于最后一页的页码值时，将显示最后一页。 * 需要注意的是，通过setAbsolutePage来对结果集进行定位，不会使查询结果集发生改变， * 而只是对结果集中的指针（cursor）进行了定位。 * 因此如果程序员想得到真正的分页效果，应参照如下程序实现相应的功能：<br> * <br> * private void Page_Load(object sender, System.EventArgs e) *{ * int STARTROW=1;//假设第0行写列名，所以填充从第1行开始。 * //dm是一个数据模块（dataModule） // Sqlc qq=new Sqlc(1,"sa","111111","ahtax2000","superstring");
* dm.db.addparameters(1); * dm.db.add("SPH",SqlType.Char,8,"11111111"); * dm.db.open(); * DataSet ds=(DataSet)dm.qq.getData("select * from DS_ZT where SPBSM=?",false); * dm.qq.close(); * dm.qq.setPageSize(6); * if(!IsPostBack) * { * dm.qq.FillPages(TABLE2,1,STARTROW,5); //从第一页开始 * * Session["pages"]=dm.qq.getAbsolutePage(); * } * *} * * private void Button2_Click(object sender, System.EventArgs e)//后滚 * { * int tmp=(int)Session["pages"]+1; * dm.qq.FillPages(TABLE2,tmp,STARTROW,5); * Session["pages"]=dm.qq.getAbsolutePage(); * }
* private void Button1_Click(object sender, System.EventArgs e)//前翻 * { int tmp=(int)Session["pages"]-1; * dm.qq.FillPages(TABLE2,tmpSTARTROW,,5); * Session["pages"]=dm.qq.getAbsolutePage(); * } * @see LPWDatabaseOperation#setPageSize * @param absolutePage int<br>欲显示的页码值 */ //------------------------------------------------------------------------------------------ /// <summary> /// 设置当前将要显示的页码，每页显示的数据行数由setPageSize方法的参数值决定， /// 当输入的页码值小于1时，将显示第一页数据； /// 当输入的页码值大于最后一页的页码值时，将显示最后一页。 /// 需要注意的是，通过setAbsolutePage来对结果集进行定位，不会使查询结果集发生改变， /// 而只是对结果集中的指针（cursor）进行了定位。 /// 因此如果程序员想得到真正的分页效果，应参照如下程序实现相应的功能 /// </summary> /// <param name="absolutePage">每页显示的记录条数</param> public void setAbsolutePage(int absolutePage) { this.getPageCount(); if(absolutePage<1) absolutePage = 1; if(absolutePage>this.pageCount) absolutePage = this.pageCount; this.absolutePage = absolutePage; }
/// <summary> /// 获得当前显示的页码值 /// </summary> /// <returns>int</returns> public int getAbsolutePage() { return absolutePage; }
/// <summary> /// 设置每页显示记录的条数。 /// 当输入的参数值小于1时，每页显示一条记录； ///当输入的参数值大于记录总数时，将显示所有数据； ///默认情况下每页将显示20条记录。 ///程序员可通过setAbsolutePage方法改变当前显示的页码。 /// </summary> /// <param name="pageSize">每页显示数据的行数</param> public void setPageSize(int pageSize) { if(pageSize<1) pageSize = 1; this.pageSize = pageSize; }
/// <summary> /// 获得每页显示记录的条数 /// </summary> /// <returns>每页显示记录的条数</returns> public int getPageSize() { return pageSize; }
/// <summary> /// 获得记录结果集显示的总的页数，其值为：总的记录数/每页显示的记录数[+1]。 /// </summary> /// <returns>记录结果集显示的总的页数</returns> public int getPageCount() { this.getRecordCout(); this.pageCount = this.recordCount/this.pageSize; if((this.recordCount/this.pageSize!=0)) this.pageCount++; return pageCount; }
/// <summary> /// 记录结果集总的记录数 /// </summary> /// <returns></returns> public int getRecordCout() { try { if(this.resultSet.Tables[0].Rows.Count!=0) { this.recordCount = this.resultSet.Tables[0].Rows.Count; } else { this.recordCount = 0; } } catch(Exception e) { }
return this.recordCount; }
public string GetResult(DataTable dt) { string sumstring; StringBuilder tmpstring=new StringBuilder(); DataRow dr; int k; int ROWCOUNT=dt.Rows.Count; int COLUMSCOUNT=dt.Columns.Count; tmpstring.Append(ROWCOUNT); tmpstring.Append("^"); tmpstring.Append(COLUMSCOUNT.ToString()); tmpstring.Append("^"); for(int i=0;i<ROWCOUNT;i++) if(ROWCOUNT>0) { dr=dt.Rows[i]; for(k=0;k<COLUMSCOUNT;k++) { tmpstring.Append(dr[k].ToString()); tmpstring.Append("^"); } }//end if //END FOR
sumstring=tmpstring.ToString(); return sumstring; } /// <summary> /// 往表格第i行，j列的单元插入值val，第一个有效单元为1行1列 /// </summary> /// <param name="TABLE1">html〈table〉元素设定为runnat后生成的ID</param> /// <param name="row"></param> /// <param name="col"></param> /// <param name="val"></param> public void SetCell(HtmlTable TABLE1,int row,int col,string val) { if ((val.Trim())== "") ((HtmlTableCell)(TABLE1.Rows[row].Cells[col])).InnerText="-"; else ((HtmlTableCell)(TABLE1.Rows[row].Cells[col])).InnerText = val; /*if ((col==1)||(col==2)||(col==5)) TABLE1.Rows[row].Cells[col].Align = "Left"; else TABLE1.Rows[row].Cells[col].Align = "Center"; */ } /// <summary> /// 填充指定页码的页面 /// </summary> /// <param name="TABLE1">html〈table〉元素设定为runnat后生成的ID</param> /// <param name="page">页数</param> /// // <param name="startrow">html表开始填充的行数，设第一行为0</param> /// <param name="HtmlCol">html表要填充的列数</param> public void FillPages(HtmlTable TABLE1,int page,int startrow, int HtmlCol) { string tmp=""; this.setAbsolutePage(page); int ps=this.getPageSize();//当前页面表的最大行数。 int ap=this.getAbsolutePage();//当前页面的绝对值。 int max=this.getRecordCout(); for(int i=(ap-1)*ps;i<(ap-1)*ps+ps;i++) { for(int j=0;j<HtmlCol;j++) { if(i<=max) tmp=this.resultSet.Tables[0].Rows[i][j].ToString(); else tmp="";//如果数据库到了头，用空行填充。 this.SetCell(TABLE1,i-(ap-1)*ps+startrow,j,tmp);}
} }
}}
