sqlDbc.cs

    技术2022-05-11  6

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

       }  }

     }} 


    最新回复(0)