数据库操作类

using System;

using System.IO ;

using System.Data;

using System.Data.OleDb ;

using System.Web;

using System.Web.UI;

 

namespace data.common

{

     ///<summary>

     /// DataFunc 的摘要说明。

     ///</summary>

     public class DataFunc

     {

         public string ConnectStr=""; //连接数据库字符串,

         public DataFunc()

         {

              System.Web.UI.Page newpage=new Page();

              ConnectStr=newpage.Session["Connection"].ToString();

         }

        

 

         ///<summary>

         /// 生成数据连接

         ///</summary>

         ///<returns>数据连接</returns>

          public System.Data.OleDb.OleDbConnection CreateConnection()

         {

              string connstr=ConnectStr;

              OleDbConnection conn = new OleDbConnection(connstr);

             return conn;        

         }

        

         ///<summary>

         /// 根据sql语句生成数据表

         ///</summary>

         ///<param name="sqlstr">sql语句</param>

         ///<returns>数据表，如果sql语句执行异常，返回null</returns>

          public System.Data.DataTable GetDtBySql(string sqlstr)

         {

              OleDbDataAdapter adapter=new OleDbDataAdapter(sqlstr,CreateConnection());

              DataTable dt=new DataTable();

              try

              {

                   adapter.Fill(dt);

                   return dt;

              }

              catch (Exception e)

              {

                   DBErrorLog("GetDtBySql",sqlstr,e.ToString());

                   return null;

              }    

             

         }

 

         ///<summary>

         /// 根据sql语句，指定其中若干条记录生成数据表

         ///</summary>

         ///<param name="sqlstr">sql语句</param>

         ///<param name="StartRecord">从其开始的从零开始的记录号</param>

         ///<param name="MaxRecord">要检索的最大记录数</param>

         ///<returns>数据表，如果sql语句执行异常，返回null</returns>

          public System.Data.DataTable GetDtBySql(string sqlstr,int StartRecord,int MaxRecord)

         {

              OleDbDataAdapter adapter=new OleDbDataAdapter(sqlstr,CreateConnection());

              DataSet ds=new DataSet();

              try

              {

                   adapter.Fill(ds,StartRecord,MaxRecord,"srcTable");

                   return ds.Tables["srcTable"];

              }

              catch (Exception e)

              {

                   DBErrorLog("GetDtBySql",sqlstr,e.ToString());

                   return null;

              }    

             

         }

 

         ///<summary>

         /// 执行SQL语句

         ///</summary>

         ///<param name="sqlstr">sql语句</param>

         ///<returns>是否执行成功</returns>

          public bool Execsql(string sqlstr)

         {

              System.Data.OleDb.OleDbConnection conn=CreateConnection();

              conn.Open();

              System.Data.OleDb.OleDbTransaction tr= conn.BeginTransaction();

 

              System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand(sqlstr);

              myCommand.Connection=conn;

              myCommand.Transaction=tr;

              try

              {

                   myCommand.ExecuteNonQuery();

                   tr.Commit();

                   return true;

              }

              catch (Exception e)

              {

                   DBErrorLog("Execsql",sqlstr,e.ToString());

                   tr.Rollback();

                   return false;

              }

              finally

              {

                   conn.Close();

              }

         }

         ///<summary>

         /// 通过sql语句获得字段值

         ///</summary>

         ///<param name="sqlstr">sql语句</param>

         ///<returns>返回sql语句查询结果的第一记录的第一个字段内容

         /// 如果sql语句执行异常或者没有记录，返回空字符串""

         ///</returns>

          public string GetFieldValue(string sqlstr)

         {

              string result="";

              object obj=null;

              OleDbCommand oleDbCommand= new OleDbCommand();

              try

              {

                   oleDbCommand.Connection=CreateConnection();

                   oleDbCommand.Connection.Open();

                   oleDbCommand.CommandText = sqlstr;

                   obj=oleDbCommand.ExecuteScalar();

                   if(obj!=null)

                        result=(string)obj.ToString();     

              }

              catch (Exception e)

              {

                   DBErrorLog("GetFieldValue",sqlstr,e.ToString());                 

              }

              finally

              {

                   oleDbCommand.Connection.Close();                 

              }

              return result;

         }

         ///<summary>

         /// 取系列号，根据不同的数据表，返回不同值，用于生成数据记录的ID字段内容

         ///</summary>

         ///<param name="TableName">数据表名</param>

         ///<returns>数据值</returns>

         public int GetSerialNo(string TableName,string schoolcode)

         {

              int MaxValue=1;

              DataTable dt =null;

 

              string tname=TableName.ToUpper();

              TrimChineseStr(ref tname, 60);

 

              string SqlStr ="select top 1 * from " +tname+" where schoolcode="+schoolcode;

              dt =GetDtBySql(SqlStr);

              if (dt!=null&&dt.Rows.Count >0)

              {

                   SqlStr ="select top 1 * from "+tname+" where schoolcode="+schoolcode+" order by "+dt.Columns[0].ColumnName +" desc";

                   dt =GetDtBySql(SqlStr);

                   MaxValue =Convert.ToInt32(dt.Rows[0][0]);

                   MaxValue=MaxValue+1;

              }

             

              return MaxValue;

         }

    

 

        

     }

}