sqlDbc.cs

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);}

   }
  }

 }
}