sqldbbaseconnection.cs

高校 学籍管理、成绩、收费、网上教学

//************************************************************
//项目:学生学籍管理系统　　　　　　　　　　 
//版权:Copyright(c) 2005,tzz  　　　　　　　　　　　　　
//模块名:公共类
//说明:用于连接Sql数据库的类
//版本：1.0
//修改历史：
//2006-6-6 by gawain
//初始版本	
//************************************************************

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Diagnostics;
using System.Reflection;
using System.Runtime.InteropServices;

namespace QSEDUNET.COMMAND
{
    /// <summary>
    /// 基础连类
    /// </summary>
    public class SqlDBBaseConnection
    {
        //private string dlConnectionString;
        public SqlConnection conn;
        public SqlTransaction objTranse;
        public SqlCommand command;

        public SqlConnection SingleSqlConnection
        {
            get {
                if (conn == null)
                {
                    conn = new SqlConnection(DLConnctionString);
                    conn.Open();
                }
                return conn;
            }
        }

        public SqlDBBaseConnection()
        {
            conn = null;
            objTranse = null;
        }

        public SqlDBBaseConnection(SqlConnection conn)
        {
            this.conn = conn;
            objTranse = null;
        }

        public SqlDBBaseConnection(SqlConnection conn, SqlTransaction objTranse)
        {
            //if (conn != null && objTranse !=null)
            //{
                this.conn = conn;
                this.objTranse = objTranse;
            //}
           
        }

        /// <summary>
        /// 定义连接字符串
        /// </summary>
        public static string DLConnctionString
        {
            get
            {

               // return dlConnectionString = ConfigurationManager.AppSettings["Deaiedu.Server.ConnectionStrings.Sql"];
                return "Data Source=zghy;Initial Catalog=QSEDU;User ID=sa;pwd=1221";
            }
        }

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        public SqlConnection Connection()
        {
            try
            {
                if (conn == null)
                {
                    conn = SingleSqlConnection;
                    //conn.Open();
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
            return conn;
        }

        /// <summary>
        /// 开始事务
        /// </summary>
        public void BeginTranse()
        {
            objTranse = conn.BeginTransaction();
        }

        /// <summary>
        /// 提交事务
        /// </summary>
        public void CommitTranse()
        {
            if (objTranse != null)
            {
                if (objTranse.Connection != null)
                {
                    objTranse.Commit();
                }
            }
        }

        /// <summary>
        /// Rollback事务
        /// </summary>
        public void RollbackTranse()
        {
            if (objTranse != null)
            {
                if (objTranse.Connection != null)
                {
                    objTranse.Rollback();
                }
            }
        }

        /// <summary>
        /// 释放数据连接
        /// </summary>
        public void CloseConnection()
        {
            try
            {
                if (conn != null)
                {

                    conn.Close();
                    conn.Dispose();
                    conn = null;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 设置存储过程参数
        /// </summary>
        /// <param name="stroedName">存储过程名字</param>
        /// <param name="parameters">存储过程参数数组</param>
        public void StroedProdedure(string stroedName, SqlParameter[] parameters)
        {
            command = new SqlCommand(stroedName,conn,objTranse);
            command.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }
            command.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4,
                ParameterDirection.ReturnValue, false, 4, 5, string.Empty, DataRowVersion.Default, 9));
            //command.Connection.Open();
        }

        /// <summary>
        /// 执行存储过程,返回标识
        /// </summary>
        /// <returns></returns>
        public int Run()
        {
            if (command == null)
                throw new ObjectDisposedException(GetType().FullName);
            command.ExecuteNonQuery();
            return (int)command.Parameters["ReturnValue"].Value;
        }

        public bool Run(string panduan)
        {
            if (command == null)
                throw new ObjectDisposedException(GetType().FullName);
            command.ExecuteNonQuery();
            return true;
        }

        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        public int RunSQL(string strSQL)
        {
            SqlCommand cmd = new SqlCommand(strSQL,conn,objTranse);
            return cmd.ExecuteNonQuery();
        }

        /// <summary>
        /// 执行存储过程,并填充数据
        /// </summary>
        /// <param name="objDataTable"></param>
        /// <returns></returns>
        public void Run(DataSet objDataSet, string sqlValue, string tableName)
        {
            //			if( command == null )
            //				throw new ObjectDisposedException( GetType().FullName );

            SqlDataAdapter objDataAdapter = new SqlDataAdapter(sqlValue, Connection());
            objDataAdapter.Fill(objDataSet, tableName);

        }
        public DataSet RunDS(string sql,string tableName)
        {
            DataSet ds = new DataSet();
            SqlDataAdapter objDataAdapter = new SqlDataAdapter(sql,conn);
            objDataAdapter.Fill(ds, tableName);
            return ds;
        }

        public DataSet Run(string sqlValue, string TableName)
        {
            DataSet objdataset = new DataSet();
            command = new SqlCommand(sqlValue,this.conn,this.objTranse);
            SqlDataAdapter objData= new SqlDataAdapter();
            objData.SelectCommand = command;
            objData.Fill(objdataset, TableName);
            return objdataset;
        }

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="sqlValue">SQL命令</param>
        /// <param name="tableName">表名</param>
        /// <returns>执行SQL命令后返回的行数，返回-1表示SQL命令为空</returns>
        public int run(string sqlValue, string tableName)
        {
            if (sqlValue == "" || sqlValue == null)
            {
                //返回-1，表示SQL命令为空
                return -1;
            }
            command = new SqlCommand(sqlValue, this.conn, this.objTranse);
            int rows = command.ExecuteNonQuery();
            
            return rows;
        }

        /// <summary>
        /// 释放command对象
        /// </summary>