数据库连接示例

Code
 public void CreateDataSource(DatasourceProperties dataSource, string policyInfo)
        {
            try
            {
                using (SqlConnection c = new SqlConnection(conn))
                {
                    if (c.State != ConnectionState.Open)
                    {
                        c.Open();
                    }
                    using (SqlTransaction tran = c.BeginTransaction())
                    {
                        try
                        {
                            int id = SqlHelper.ExecuteNonQueryTypedParamsWithReturnValue(tran, "CreateDatasource",
                                dataSource.Server,
                                dataSource.ConnectionType.ToString(),
                                dataSource.AuthorizationMode.ToString(),
                                dataSource.Catalog,
                                dataSource.Name,
                                dataSource.Description,
                                dataSource.User,
                                DataProtector.EncryptString(dataSource.Password),
                                dataSource.TimeOut,
                                dataSource.DataSourceInfo,
                                dataSource.GetPropertiesString(),
                                HttpContext.Current.User.Identity.Name
                                );
                            switch (id)
                            {
                                case -1:
                                    throw new Exception(SR.Exception_UserNotExist);
                                case -2:
                                    throw new Exception(SR.Exception_DatasourceAlreadyExist);
                                case -3:
                                    throw new Exception(SR.Exception_CreateDatasourceError);
                                default:
                                    break;
                            }

                            if (policyInfo.Length > 0)
                                SqlHelper.ExecuteNonQueryTypedParamsWithReturnValue(tran, "SetPolicy", id, policyInfo.Replace("-1", id.ToString()));
                            tran.Commit();
                        }
                        catch (Exception error)
                        {
                            tran.Rollback();
                            throw new Exception(error.Message);
                        }
                    }
                }
            }
            catch (Exception err)
            {
                throw err;
            }
        }

 

    public class PersistenceService
    {
        #region Singleton
        private static readonly PersistenceService _instance = new PersistenceService();
        public static PersistenceService Instance
        {
            get { return _instance; }
        }
        #endregion

        private ConnectionStringSettings _connectionStringSettings = null;
        private SqlConnection _connection = null;

        private PersistenceService()
        {
            this._connectionStringSettings = ConfigurationManager.ConnectionStrings["PersistenceConnectionString"];

            if (null == this._connectionStringSettings)
            {
                this._connectionStringSettings = WebConfigurationManager.ConnectionStrings["PersistenceConnectionString"];
            }

            if (null == this._connectionStringSettings)
            {
                throw new SystemException("Can not locate the application configuration file.");
            }

            if (this.Connection == null)
            {
                throw new SystemException("Can not create database connection.");
            }
        }

        public SqlConnection Connection
        {
            get
            {
                if (this._connection == null)
                {
                    try
                    {
                        this._connection = new SqlConnection(this._connectionStringSettings.ConnectionString);
                    }
                    catch
                    {
                        this._connection = null;
                    }
                }
                return this._connection;
            }
        }
        private OleDbConnection _oleDbConn;
        private string _oleDbConnStr;
        public string OleDbConnStr
        {
            get { return _oleDbConnStr; }
            set { _oleDbConnStr = value; }
        }
        public OleDbConnection OleDbConn
        {
            get
            {
                if (this._oleDbConn == null)
                {
                    try
                    {
                        this._oleDbConn = new OleDbConnection(this._oleDbConnStr);
                    }
                    catch
                    {
                        this._oleDbConn = null;
                    }
                }
                return this._oleDbConn;
            }
        }

        public void OpenOleDbConnection()
        {
            if (this.OleDbConn == null)
            {
                throw new SystemException("不能创建数据库连接.");
            }
            CloseOleDbConnection();
            try
            {
                this.OleDbConn.Open();
            }
            catch (Exception ex)
            {
                this._oleDbConn = null;
                throw new Exception(ex.Message);
            }
        }

        public void CloseOleDbConnection()
        {
            if (this.OleDbConn != null)
            {
                if (this.OleDbConn.State != ConnectionState.Closed)
                {
                    this.OleDbConn.Close();
                }
            }
        }
        public void OpenConnection()
        {
            if (this.Connection == null)
            {
                throw new SystemException("不能创建数据库连接.");
            }

            CloseConnection();

            try
            {
                this.Connection.Open();
            }
            catch (Exception ex)
            {
                this._connection = null;
                throw new SystemException(ex.Message);
            }
        }

        public void CloseConnection()
        {
            if (this.Connection != null)
            {
                if (this.Connection.State != ConnectionState.Closed)
                {
                    this.Connection.Close();
                }
            }
        }
        public bool ExecuteOleDbSql(string sqlQuery)
        {
            using (OleDbCommand command = this.OleDbConn.CreateCommand())
            {
                OpenOleDbConnection();
                OleDbTransaction tran = this.OleDbConn.BeginTransaction();
                command.Connection = this.OleDbConn;
                command.CommandType = CommandType.Text;
                command.CommandText = sqlQuery;
                command.Transaction = tran;
                try
                {
                    command.ExecuteNonQuery();
                    tran.Commit();
                    return true;
                }
                catch (Exception e)
                {
                    try
                    {
                        tran.Rollback();
                    }
                    catch (Exception ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    throw new Exception(e.Message);
                }
                finally
                {
                    this.CloseOleDbConnection();
                }
            }

        }
        public bool ExecuteSql(string sqlQuery)
        {
            using (SqlCommand command = this.Connection.CreateCommand())
            {
                OpenConnection();

                SqlTransaction tran = this.Connection.BeginTransaction();

                command.Connection = this.Connection;
                command.CommandType = CommandType.Text;
                command.CommandText = sqlQuery;
                command.Transaction = tran;

                try
                {
                    command.ExecuteNonQuery();
                    tran.Commit();
                    return true;
                }
                catch (Exception e)
                {
                    try
                    {
                        tran.Rollback();
                    }
                    catch (Exception ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    throw new Exception(e.Message);
                }
                finally
                {
                    this.CloseConnection();
                }
            }
        }
        public DataTable ExecuteOleDbQuery(string query)
        {
            DataTable dtItem = null;
            using (OleDbCommand command = OleDbConn.CreateCommand())
            {
                command.Connection = this.OleDbConn;
                command.CommandType = CommandType.Text;
                command.CommandText = query;

                OleDbDataAdapter adapter = new OleDbDataAdapter();
                adapter.SelectCommand = command;
                dtItem = new DataTable();
                try
                {
                    this.OpenOleDbConnection();
                    adapter.Fill(dtItem);
                }
                catch (Exception ex)
                {
                    dtItem = null;
                    throw new Exception(ex.Message);
                }
                finally
                {
                    this.CloseOleDbConnection();
                }
            }
            return dtItem;
        }
        public DataTable ExecuteQuery(string query)
        {
           
            DataTable dtItem = null;
            using (SqlCommand command = this.Connection.CreateCommand())
            {
                command.Connection = this.Connection;
                command.CommandType = CommandType.Text;
                command.CommandText = query;

                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = command;

                dtItem = new DataTable();

                try
                {
                    this.OpenConnection();

                    adapter.Fill(dtItem);
                }
                catch(Exception ex)
                {
                    dtItem = null;
                    throw new Exception(ex.Message);
                }
                finally
                {
                    this.CloseConnection();
                }
            }
            return dtItem;
        }
    }

 

 

转载于:https://www.cnblogs.com/xl696128/archive/2009/03/19/1416810.html