DbConnection.cs
using
System;
using
System.Data.SqlClient;
![None.gif](/Images/OutliningIndicators/None.gif)
namespace
DbControl
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 数据库链接
/// </summary>
public class DbConnection
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
//SQL数据库链接字符串
private string _strSqlConnection = System.Configuration.ConfigurationSettings.AppSettings[ "SqlConnectionString" ];
//XML文件链接字符串
private string _strXmlConnection = string.Empty;
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
public string ConnectionString
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
get
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return _strSqlConnection;
}
set
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this._strSqlConnection = System.Configuration.ConfigurationSettings.AppSettings[ value ];
}
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
public SqlConnection SqlConnectionString
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
get
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return new SqlConnection( ConnectionString );
}
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
public string XmlConnectionString
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
get
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return this._strXmlConnection;
}
set
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this._strXmlConnection = value;
}
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
public DbConnection()
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
//
// TODO: 在此处添加构造函数逻辑
//
}
}
}
AppControl.cs
using
System;
using
System.Data;
using
System.Data.SqlClient;
![None.gif](/Images/OutliningIndicators/None.gif)
namespace
DbControl
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// AppControl 的摘要说明。
/// </summary>
public class SqlControl : DbConnection
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
private SqlConnection _Conn;
private SqlCommand _Cmd;
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
public SqlControl()
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
//
// TODO: 在此处添加构造函数逻辑
//
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 打开数据库链接
/// </summary>
private void Open()
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this._Conn = this.SqlConnectionString;
try
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
_Conn.Open();
}
catch ( System.Exception ex )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw ex;
}
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 关闭数据库链接
/// </summary>
private void Close()
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
try
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this._Conn.Close();
}
catch( System.Exception ex )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw ex;
}
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 运行SQL,返回DataTable数据
/// </summary>
/// <param name="query">SQL语句</param>
/// <returns>返回DataTable</returns>
public DataTable RunSqlToDataTable( string query )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this.Open();
DataTable objDataTable = new DataTable();
SqlDataAdapter objAdapter = new SqlDataAdapter( query , _Conn );
try
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
objAdapter.Fill( objDataTable );
}
catch( System.Exception ex )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw ex;
}
finally
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this.Close();
}
return objDataTable;
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 执行SQL语句,返回单个值
/// </summary>
/// <param name="query">标准T-SQL语句</param>
/// <returns>返回string</returns>
public string RunSqlToResult( string query )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this.Open();
this._Cmd = new SqlCommand( query,this._Conn );
System.Text.StringBuilder strResult = new System.Text.StringBuilder();
try
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
strResult.Append( this._Cmd.ExecuteScalar() );
}
catch( System.Exception ex )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw ex;
}
finally
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this.Close();
}
return strResult.ToString();
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 运行单个SQL的相关操作
/// </summary>
/// <param name="query">SQL语句</param>
/// <returns>返回AppControl</returns>
public SqlControl ExecuteNonQuery( string query )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this.Open();
_Cmd = new SqlCommand( query , _Conn );
try
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
_Cmd.ExecuteNonQuery();
}
catch( System.Exception ex )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw ex;
}
finally
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this.Close();
}
return this;
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 按事务执行SQL语句数组
/// </summary>
/// <param name="query">标准T-SQL语句数组</param>
/// <returns>返回AppControl</returns>
public SqlControl ExecuteNonQuery( string[] query )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this.Open();
this._Cmd = new SqlCommand();
SqlTransaction objTran = this._Conn.BeginTransaction();
this._Cmd.Connection = this._Conn;
this._Cmd.Transaction = objTran;
try
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
for ( int i=0 ; i<query.Length ; i++ )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this._Cmd.CommandText = query[i];
this._Cmd.ExecuteNonQuery();
}
objTran.Commit();
}
catch( System.Exception ex )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
objTran.Rollback();
throw ex;
}
finally
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this.Close();
}
return this;
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 执行SQL语句,返回SqlDataReader.
/// </summary>
/// <param name="query">标准SQL语句</param>
/// <returns>返回DataReader</returns>
public SqlDataReader RunSqlToDataReader( string query )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this.Open();
this._Cmd = new SqlCommand( query , this._Conn );
SqlDataReader objDataReader;
try
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
objDataReader = this._Cmd.ExecuteReader();
}
catch( System.Exception ex )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw ex;
}
finally
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this.Close();
}
return objDataReader;
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 运行存储过程(有参数,无输出)
/// </summary>
/// <param name="procedureName">存储过程名</param>
/// <param name="parameters">存储过程参数数组</param>
/// <returns>返回AppControl</returns>
public SqlControl RunProcedure( string procedureName,SqlParameter[] parameters )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this.Open();
this._Cmd = this.MakeProcedure( procedureName,parameters );
try
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this._Cmd.ExecuteNonQuery();
}
catch( System.Exception ex )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw ex;
}
finally
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this.Close();
}
return this;
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 运行存储过程(无参数,无输出)
/// </summary>
/// <param name="procedureName">存储过程名</param>
/// <returns>返回AppControl</returns>
public SqlControl RunProcedure( string procedureName )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this.Open();
this._Cmd = this.MakeProcedure( procedureName,null );
try
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this._Cmd.ExecuteNonQuery();
}
catch( System.Exception ex )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw ex;
}
finally
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this.Close();
}
return this;
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 运行存储过程(无参数,有输出)
/// </summary>
/// <param name="procedureName">存储过程名</param>
/// <param name="parametersOutput">存储过程输出参数数组</param>
/// <returns>返回AppControl</returns>
public SqlControl RunProcedure( string procedureName,ref SqlParameter[] parametersOutput )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this.Open();
this._Cmd = this.MakeProcedure( procedureName,parametersOutput );
try
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this._Cmd.ExecuteNonQuery();
}
catch( System.Exception ex )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw ex;
}
finally
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this.Close();
}
return this;
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 运行存储过程(有参数,无输出)
/// </summary>
/// <param name="procedureName">存储过程名</param>
/// <param name="parameters">存储过程参数数组</param>
/// <returns>返回DataTable</returns>
public DataTable RunProcedureToDataTable( string procedureName,SqlParameter[] parameters )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this.Open();
this._Cmd = this.MakeProcedure( procedureName,parameters );
DataTable objDataTable = new DataTable();
try
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this._Cmd.ExecuteNonQuery();
SqlDataAdapter objAdapter = new SqlDataAdapter( this._Cmd );
objAdapter.Fill( objDataTable );
}
catch( System.Exception ex )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw ex;
}
finally
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this.Close();
}
return objDataTable;
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 运行存储过程(有参数,有输出)
/// </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 )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this.Open();
this._Cmd = this.MakeProcedure( procedureName,parametersInput );
if ( parametersOutput != null )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
foreach( SqlParameter parameter in parametersOutput )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this._Cmd.Parameters.Add( parameter );
}
}
DataTable objDataTable = new DataTable();
try
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this._Cmd.ExecuteNonQuery();
SqlDataAdapter objAdapter = new SqlDataAdapter( this._Cmd );
objAdapter.Fill( objDataTable );
}
catch( System.Exception ex )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw ex;
}
finally
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this.Close();
}
return objDataTable;
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 运行存储过程(无参数)
/// </summary>
/// <param name="procedureName">存储过程名</param>
/// <returns>返回DataTable</returns>
public DataTable RunProcedureToDataTable( string procedureName )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this.Open();
this._Cmd = this.MakeProcedure( procedureName,null );
DataTable objDataTable = new DataTable();
try
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this._Cmd.ExecuteNonQuery();
SqlDataAdapter objAdapter = new SqlDataAdapter( this._Cmd );
objAdapter.Fill( objDataTable );
}
catch( System.Exception ex )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw ex;
}
finally
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this.Close();
}
return objDataTable;
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 建立存储过程
/// </summary>
/// <param name="procedureName">存储过程名</param>
/// <param name="parameters">存储过程参数数组</param>
/// <returns>返回AppControl</returns>
private SqlCommand MakeProcedure( string procedureName,SqlParameter[] parameters )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
SqlCommand objCmd = new SqlCommand( procedureName,this._Conn );
objCmd.CommandType = CommandType.StoredProcedure;
if ( parameters != null )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
foreach( SqlParameter sqlParamet in parameters )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
objCmd.Parameters.Add( sqlParamet );
}
}
return objCmd;
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 多表查询
/// </summary>
/// <param name="query">标准SQL语句集</param>
/// <returns>返回DataSet</returns>
public DataSet RunSqlToDataSet( string[] query )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this.Open();
DataSet objDataSet = new DataSet();
SqlDataAdapter objAdapter = new SqlDataAdapter();
this._Cmd = new SqlCommand();
this._Cmd.Connection = this._Conn;
objAdapter.SelectCommand = this._Cmd;
try
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
for ( int i=0 ; i<query.Length ; i++ )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
objAdapter.SelectCommand.CommandText = query[i];
objAdapter.Fill( objDataSet.Tables.Add() );
}
}
catch( System.Exception ex )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw ex;
}
finally
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this.Close();
}
return objDataSet;
}
}
}
转载于:https://www.cnblogs.com/HD/archive/2005/03/30/127905.html