对数据库的几种基本操作

对数据库的几种基本操作

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Win32;
using System.Reflection;
using System.IO;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Runtime.InteropServices ;
using System.Text;
namespace IMS.Class
{
    /**//// <summary>
    /// LinkDatabase 的摘要说明。
    /// </summary>
    public class LinkDatabase
    {
        private string strSQL="";
        //private string connectionString = "Data Source=WYQ;Persist Security Info=False;Initial Catalog=InSave;Integrated Security=SSPI";
        private SqlConnection myConnection;
        
        private SqlCommandBuilder sqlCmdBld;
        private DataSet ds = new DataSet();
        private SqlDataAdapter da;

        public string DB_Conn="";
        public LinkDatabase()
        {
            // TODO: 在此处添加构造函数逻辑
            DB_Conn="Persist Security Info=False;Data Source=127.0.0.1;Initial Catalog=DelegateIMS;User ID=sa;Password=sa";//ConfigurationSettings.AppSettings["ConnStr"]; 
        //Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=DelegateIMS;Data Source=MICROSOF-9C75B7;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=ZHIYANG;Use Encryption for Data=False;Tag with column collation when possible=False
        } 
        public LinkDatabase(string Str) 
        { 
            try 
            { 
                this.DB_Conn = Str; 
            } 
            catch(Exception ex) 
            { 
                throw ex; 
            } 
        }
        public bool JudgeServer()
        {
            this.open();
            if(this.myConnection.State==ConnectionState.Open)
            {
                this.myConnection.Close();
                return true;
            }
            else
                return false;
        }
        public void open()
        {
            //调试时若无此句,将使得运行时提示:未将对象引用到实例
            this.myConnection=new SqlConnection(this.DB_Conn);
            if(this.myConnection.State==ConnectionState.Open)
            {
                
                return ;
            }
            else
                try
                {
                    this.myConnection.Open();
                }
                catch(System.Data.SqlClient.SqlException ex)
                {
                    throw new Exception(""+ex.Message +"");
                }
    
        }
        
        /**//// <summary>
        ///  根据输入的SQL语句检索数据库数据
        /// </summary>
        /// <param name="tempStrSQL">检索的sql语句</param>
        /// <param name="tempTableName">映射的表名</param>
        /// <returns></returns>
        public DataSet SelectDataBase(string tempStrSQL,string tempTableName)
        { 
            this.strSQL = tempStrSQL;
            this.myConnection = new SqlConnection(DB_Conn);
            this.da = new SqlDataAdapter(this.strSQL,this.myConnection);
            this.ds.Clear();
            try
            {
                this.da.Fill(ds,tempTableName);
            }
            catch(Exception e)
            { 
                throw new Exception(""+e.Message +"");
            }
            return ds;//返回填充了数据的DataSet，其中数据表以tempTableName给出的字符串命名
        }

        /**//// <summary>
        ///  数据库数据更新(传DataSet和DataTable的对象)
        /// </summary>
        /// <param name="changedDataSet">改变了的dataset</param>
        /// <param name="tableName">映射源表的表名</param>
        /// <returns>返回更新了的数据库表</returns>
        public DataSet UpdateDataBase(DataSet changedDataSet,string tableName,string str)
        {
            try
            {
                this.myConnection = new SqlConnection(DB_Conn);
                this.da = new SqlDataAdapter(str,this.myConnection);
                  this.da.SelectCommand=new SqlCommand(str,this.myConnection);
                this.sqlCmdBld = new SqlCommandBuilder(da);
                this.da.Update(changedDataSet,tableName);
                return changedDataSet;//返回更新了的数据库表
            }
            catch(Exception ex)
            {
               throw new Exception(""+ex.Message +"");
            }                         
        }
        /**//////////////////////////////////  直接操作数据库(未创建该类的实例时直接用)  /////////////////////////////////////////////////////

        /// <summary>
        ///  检索数据库数据(传字符串,直接操作数据库)
        /// </summary>
        /// <param name="tempStrSQL">检索的sql语句</param>
        /// <returns>查询的结果，存在于一个datatable中</returns>
        public DataTable SelectDataBase(string tempStrSQL)
        {
            this.myConnection = new SqlConnection(DB_Conn);
            DataSet tempDataSet = new DataSet();
            this.da = new SqlDataAdapter(tempStrSQL,this.myConnection);
            this.da.Fill(tempDataSet);
            return tempDataSet.Tables[0];
        }

        /**//// <summary>
        ///  数据库数据更新(传字符串，直接操作数据库)
        /// </summary>
        /// <param name="tempStrSQL">检索的sql语句</param>
        /// <returns>返回数据库中影响的行数</returns>
        public int UpdateDataBase(string tempStrSQL)
        {
            this.myConnection = new SqlConnection(DB_Conn);
            //使用Command之前一定要先打开连接,后关闭连接,而DataAdapter则会自动打开关闭连接
            myConnection.Open();
            SqlCommand tempSqlCommand = new SqlCommand(tempStrSQL,this.myConnection);
            int intNumber = tempSqlCommand.ExecuteNonQuery();//返回数据库中影响的行数
            return intNumber;
        }

        /**//// <summary>
        ///  关闭数据库
        /// </summary>
        public void CloseDataBase()
        {
            this.myConnection.Close();
            this.myConnection.Dispose();
            this.ds.Clear();
            this.ds.Dispose();
            GC.Collect(); 
        }
        /**//// 返回SQL语句执行结果的第一行第一列 
        /// </summary> 
        /// <returns>字符串</returns> 
        public string ReturnValue(string SQL) 
        { 
            this.myConnection = new SqlConnection(DB_Conn); 
            myConnection.Open(); 
            string result; 
            SqlDataReader Dr ; 
            try 
            { 
                SqlCommand Cmd= new SqlCommand(SQL,this.myConnection); 
                Dr = Cmd.ExecuteReader(); 
                if (Dr.Read()) 
                { 
                    result = Dr[0].ToString(); 
                    Dr.Close(); 
                } 
                else 
                { 
                    result = ""; 
                    Dr.Close(); 
                } 
            } 
            catch 
            { 
                throw new Exception(SQL); 
            } 
            Dispose(this.myConnection); 
            return result; 
        } 
        /**//// 运行存储过程,返回dataset. 
        /// </summary> 
        /// <param name="procName">存储过程名.</param> 
        /// <param name="prams">存储过程入参数组.</param> 
        /// <returns>dataset对象.</returns> 
        public DataSet RunProc(string procName,SqlParameter[] prams,DataSet Ds) 
        { 
            this.myConnection = new SqlConnection(DB_Conn); 
            myConnection.Open();
            SqlCommand Cmd = new SqlCommand(procName, this.myConnection); 
            Cmd.CommandType = CommandType.StoredProcedure; 
            if (prams != null) 
            { 
                foreach (SqlParameter parameter in prams) 
                { 
                    if(parameter != null) 
                    { 
                        Cmd.Parameters.Add(parameter); 
                    } 
                } 
            }  
            SqlDataAdapter Da = new SqlDataAdapter(Cmd); 
            try 
            { 
                Da.Fill(Ds); 
            } 
            catch(Exception Ex) 
            { 
                throw Ex; 
            } 
            return Ds; 
        } 
        /**//// 返回SQL语句第一列,第ColumnI列, 
        /// </summary> 
        /// <returns>字符串</returns> 
        public string ReturnValue(string SQL, int ColumnI) 
        { 
            this.myConnection = new SqlConnection(DB_Conn); 
            myConnection.Open();
            string result; 
            SqlDataReader Dr ; 
            try 
            { 
                SqlCommand Cmd= new SqlCommand(SQL,this.myConnection); 
                Dr = Cmd.ExecuteReader(); 
            } 
            catch 
            { 
                throw new Exception(SQL); 
            } 
            if (Dr.Read()) 
            { 
                result = Dr[ColumnI].ToString(); 
            } 
            else 
            { 
                result = ""; 
            } 
            Dr.Close(); 
            Dispose(this.myConnection); 
            return result; 
        } 
        public void Dispose(SqlConnection Conn) 
        { 
            if(Conn!=null) 
            { 
                Conn.Close(); 
                Conn.Dispose(); 
            } 
            GC.Collect(); 
        } 
    }
}