• 设为首页
  • 点击收藏
  • 手机版
    手机扫一扫访问
    迪恩网络手机版
  • 关注官方公众号
    微信扫一扫关注
    公众号

[ASP.net]数据访层

原作者: [db:作者] 来自: [db:来源] 收藏 邀请
DbConnection.cs
using System;
using System.Data.SqlClient;

namespace DbControl
{

    
/// 数据库链接
    
/// </summary>
    public class DbConnection
{
        
//SQL数据库链接字符串
        private string _strSqlConnection = System.Configuration.ConfigurationSettings.AppSettings[ "SqlConnectionString" ];
        
//XML文件链接字符串
        private string _strXmlConnection = string.Empty;

        
public string ConnectionString
{
            
get
{
                
return _strSqlConnection;
            }

            
set
{
                
this._strSqlConnection = System.Configuration.ConfigurationSettings.AppSettings[ value ];
            }

        }


        
public SqlConnection SqlConnectionString
{
            
get
{
                
return new SqlConnection( ConnectionString );
            }

        }


        
public string XmlConnectionString
{
            
get
{
                
return this._strXmlConnection;
            }

            
set
{
                
this._strXmlConnection = value;
            }

        }


        
public DbConnection()
{
            
//
            
// TODO: 在此处添加构造函数逻辑
            
//
        }
    }

}



AppControl.cs
using System;
using System.Data;
using System.Data.SqlClient;

namespace DbControl
{

    
/// AppControl 的摘要说明。
    
/// </summary>
    public class SqlControl : DbConnection
{
        
private SqlConnection _Conn;
        
private SqlCommand _Cmd;

        
public SqlControl()
{
            
//
            
// TODO: 在此处添加构造函数逻辑
            
//
        }


        
/// 打开数据库链接
        
/// </summary>
        private void Open()
{
            
this._Conn = this.SqlConnectionString;
            
try
{
                _Conn.Open();
            }

            
catch ( System.Exception ex )
{
                
throw ex;
            }

        }



        
/// 关闭数据库链接
        
/// </summary>
        private void Close()
{
            
try
{
                
this._Conn.Close();
            }

            
catch( System.Exception ex )
{
                
throw ex;
            }

        }



        
/// 运行SQL,返回DataTable数据
        
/// </summary>
        
/// <param name="query">SQL语句</param>
        
/// <returns>返回DataTable</returns>
        public DataTable RunSqlToDataTable( string query )
{
            
this.Open();
            DataTable objDataTable 
= new DataTable();
            SqlDataAdapter objAdapter 
= new SqlDataAdapter( query , _Conn );
            
try
{
                objAdapter.Fill( objDataTable );
            }

            
catch( System.Exception ex )
{
                
throw ex;
            }

            
finally
{
                
this.Close();
            }

            
return objDataTable;
        }



        
/// 执行SQL语句,返回单个值
        
/// </summary>
        
/// <param name="query">标准T-SQL语句</param>
        
/// <returns>返回string</returns>
        public string RunSqlToResult( string query )
{
            
this.Open();
            
this._Cmd = new SqlCommand( query,this._Conn );
            System.Text.StringBuilder strResult 
= new System.Text.StringBuilder();
            
try
{
                strResult.Append( 
this._Cmd.ExecuteScalar() );
            }

            
catch( System.Exception ex )
{
                
throw ex;
            }

            
finally
{
                
this.Close();
            }

            
return strResult.ToString();
        }



        
/// 运行单个SQL的相关操作
        
/// </summary>
        
/// <param name="query">SQL语句</param>
        
/// <returns>返回AppControl</returns>
        public SqlControl ExecuteNonQuery( string query )
{
            
this.Open();
            _Cmd 
= new SqlCommand( query , _Conn );
            
try
{
                _Cmd.ExecuteNonQuery();
            }

            
catch( System.Exception ex )
{
                
throw ex;
            }

            
finally
{
                
this.Close();
            }

            
return this;
        }



        
/// 按事务执行SQL语句数组
        
/// </summary>
        
/// <param name="query">标准T-SQL语句数组</param>
        
/// <returns>返回AppControl</returns>
        public SqlControl ExecuteNonQuery( string[] query )
{
            
this.Open();
            
this._Cmd = new SqlCommand();
            SqlTransaction objTran 
= this._Conn.BeginTransaction();
            
this._Cmd.Connection = this._Conn;
            
this._Cmd.Transaction = objTran;
            
try
{
                
for ( int i=0 ; i<query.Length ; i++ )
{
                    
this._Cmd.CommandText = query[i];
                    
this._Cmd.ExecuteNonQuery();
                }

                objTran.Commit();
            }

            
catch( System.Exception ex )
{
                objTran.Rollback();
                
throw ex;
            }

            
finally
{
                
this.Close();
            }

            
return this;
        }




        
/// 执行SQL语句,返回SqlDataReader.
        
/// </summary>
        
/// <param name="query">标准SQL语句</param>
        
/// <returns>返回DataReader</returns>
        public SqlDataReader RunSqlToDataReader( string query )
{
            
this.Open();
            
this._Cmd = new SqlCommand( query , this._Conn );
            SqlDataReader objDataReader;
            
try
{
                objDataReader 
= this._Cmd.ExecuteReader();
            }

            
catch( System.Exception ex )
{
                
throw ex;
            }

            
finally
{
                
this.Close();
            }

            
return objDataReader;
        }



        
/// 运行存储过程(有参数,无输出)
        
/// </summary>
        
/// <param name="procedureName">存储过程名</param>
        
/// <param name="parameters">存储过程参数数组</param>
        
/// <returns>返回AppControl</returns>
        public SqlControl RunProcedure( string procedureName,SqlParameter[] parameters )
{
            
this.Open();
            
this._Cmd = this.MakeProcedure( procedureName,parameters );
            
try
{
                
this._Cmd.ExecuteNonQuery();
            }

            
catch( System.Exception ex )
{
                
throw ex;
            }

            
finally
{
                
this.Close();
            }

            
return this;
        }



        
/// 运行存储过程(无参数,无输出)
        
/// </summary>
        
/// <param name="procedureName">存储过程名</param>
        
/// <returns>返回AppControl</returns>
        public SqlControl RunProcedure( string procedureName )
{
            
this.Open();
            
this._Cmd = this.MakeProcedure( procedureName,null );
            
try
{
                
this._Cmd.ExecuteNonQuery();
            }

            
catch( System.Exception ex )
{
                
throw ex;
            }

            
finally
{
                
this.Close();
            }

            
return this;
        }



        
/// 运行存储过程(无参数,有输出)
        
/// </summary>
        
/// <param name="procedureName">存储过程名</param>
        
/// <param name="parametersOutput">存储过程输出参数数组</param>
        
/// <returns>返回AppControl</returns>
        public SqlControl RunProcedure( string procedureName,ref SqlParameter[] parametersOutput )
{
            
this.Open();
            
this._Cmd = this.MakeProcedure( procedureName,parametersOutput );
            
try
{
                
this._Cmd.ExecuteNonQuery();
            }

            
catch( System.Exception ex )
{
                
throw ex;
            }

            
finally
{
                
this.Close();
            }

            
return this;
        }



        
/// 运行存储过程(有参数,无输出)
        
/// </summary>
        
/// <param name="procedureName">存储过程名</param>
        
/// <param name="parameters">存储过程参数数组</param>
        
/// <returns>返回DataTable</returns>
        public DataTable RunProcedureToDataTable( string procedureName,SqlParameter[] parameters )
{
            
this.Open();
            
this._Cmd = this.MakeProcedure( procedureName,parameters );
            DataTable objDataTable 
= new DataTable();
            
try
{
                
this._Cmd.ExecuteNonQuery();
                SqlDataAdapter objAdapter 
= new SqlDataAdapter( this._Cmd );
                objAdapter.Fill( objDataTable );
            }

            
catch( System.Exception ex )
{
                
throw ex;
            }

            
finally
{
                
this.Close();
            }

            
return objDataTable;
        }



        
/// 运行存储过程(有参数,有输出)
        
/// </summary>
        
/// <param name="procedureName">存储过程名</param>
        
/// <param name="parametersInput">存储过程输入参数数组</param>
        
/// <param name="parametersOutput">存储过程输出参数数组</param>
        
/// <returns>返回DataTable</returns>
        public DataTable RunProcedureToDataTable( string procedureName,SqlParameter[] parametersInput,ref SqlParameter[] parametersOutput )
{
            
this.Open();
            
this._Cmd = this.MakeProcedure( procedureName,parametersInput );
            
if ( parametersOutput != null )
{
                
foreach( SqlParameter parameter in parametersOutput )
{
                    
this._Cmd.Parameters.Add( parameter );
                }

            }

            DataTable objDataTable 
= new DataTable();
            
try
{
                
this._Cmd.ExecuteNonQuery();
                SqlDataAdapter objAdapter 
= new SqlDataAdapter( this._Cmd );
                objAdapter.Fill( objDataTable );
            }

            
catch( System.Exception ex )
{
                
throw ex;
            }

            
finally
{
                
this.Close();
            }

            
return objDataTable;
        }



        
/// 运行存储过程(无参数)
        
/// </summary>
        
/// <param name="procedureName">存储过程名</param>
        
/// <returns>返回DataTable</returns>
        public DataTable RunProcedureToDataTable( string procedureName )
{
            
this.Open();
            
this._Cmd = this.MakeProcedure( procedureName,null );
            DataTable objDataTable 
= new DataTable();
            
try
{
                
this._Cmd.ExecuteNonQuery();
                SqlDataAdapter objAdapter 
= new SqlDataAdapter( this._Cmd );
                objAdapter.Fill( objDataTable );
            }

            
catch( System.Exception ex )
{
                
throw ex;
            }

            
finally
{
                
this.Close();
            }

            
return objDataTable;
        }



        
/// 建立存储过程
        
/// </summary>
        
/// <param name="procedureName">存储过程名</param>
        
/// <param name="parameters">存储过程参数数组</param>
        
/// <returns>返回AppControl</returns>
        private SqlCommand MakeProcedure( string procedureName,SqlParameter[] parameters )
{
            SqlCommand objCmd 
= new SqlCommand( procedureName,this._Conn );
            objCmd.CommandType 
= CommandType.StoredProcedure;
            
if ( parameters != null )
{
                
foreach( SqlParameter sqlParamet in parameters )
{
                    objCmd.Parameters.Add( sqlParamet );
                }

            }

            
return objCmd;
        }



        
/// 多表查询
        
/// </summary>
        
/// <param name="query">标准SQL语句集</param>
        
/// <returns>返回DataSet</returns>
        public DataSet RunSqlToDataSet( string[] query )
{
            
this.Open();
            DataSet objDataSet 
= new DataSet();
            SqlDataAdapter objAdapter 
= new SqlDataAdapter();
            
this._Cmd = new SqlCommand();
            
this._Cmd.Connection = this._Conn;
            objAdapter.SelectCommand 
= this._Cmd;
            
try
{
                
for ( int i=0 ; i<query.Length ; i++ )
{
                    objAdapter.SelectCommand.CommandText 
= query[i];
                    objAdapter.Fill( objDataSet.Tables.Add() );
                }

            }

            
catch( System.Exception ex )
{
           

鲜花

握手

雷人

路过

鸡蛋
该文章已有0人参与评论

请发表评论

全部评论

专题导读
热门推荐
热门话题
阅读排行榜

扫描微信二维码

查看手机版网站

随时了解更新最新资讯

139-2527-9053

在线客服(服务时间 9:00~18:00)

在线QQ客服
地址:深圳市南山区西丽大学城创智工业园
电邮:jeky_zhao#qq.com
移动电话:139-2527-9053

Powered by 互联科技 X3.4© 2001-2213 极客世界.|Sitemap