关闭

net数据库操作类(C#)——V2.0

标签: 数据库c#pathdataset存储asp.net
174人阅读 评论(0) 收藏 举报
分类:
ASP.NET中一般都是使用SQL Server作为后台数据库。一般的ASP.NET数据库操作示例程序都是使用单独的数据访问,就是说每个页面都写连接到数据库,存取数据,关闭数据库的代码。这种方式带来了一些弊端,一个就是如果你的数据库改变了,你必须一个页面一个页面的去更改数据库连接代码。第二个弊端就是代码冗余,很多代码都是重复的,不必要的。因此,我试图通过一种一致的数据库操作类来实现ASP.NET的数据访问,在我之前的文章.net数据库操作类(C#)  中已经简单总结了对数据库的操作,现在.net数据库操作类(C#)  的基础上进行了如下改进:
     1、添加了事务处理机制;
     2、添加了SQL参数化操作;
     3、添加了对存储过程的操作;
     4、添加了对SQL参数和存储过程的综合操作;
     现把.net数据库操作类代码公布出来,如有问题,欢迎大家指正!
using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.IO;
using System.Globalization;

namespace VoteInfo
{
    
public class DataAccess
    
{
        
#region 定义
        
protected SqlCommand Comm;
        
protected SqlDataAdapter Adap;
        
protected SqlConnection Conn;    //SQL连接        
        private string _connectString;    //连接串
        private string _commandString;    //SQL命令
        private Hashtable _dict, _result, _mapTable;
        
private DataSet _ds;    //返回结果数据集
        private DataRow _recordSet;        //纪录集
        private string _tableName;        //表名
        private int _recordCount;            //纪录集的行数
        private bool _eOF;                //结果集是否为空,是否已经到了结尾
        private string DB;
        
private string _deleteOP;
        
private string _path;
        
private StreamWriter SWCreate, SWApp;
        
private string _errorMessage;
        
private bool _writeLog;
        
#endregion


        
#region 属性集
        
/// <summary>
        
/// 出错信息
        
/// </summary>
        
/// 

        public string ErrorMessage
        
{
            
get return this._errorMessage; }
            
set this._errorMessage = value; }
        }


        
/**/
        
/// <summary>
        
/// 设置或者取得删除的操作者
        
/// </summary>

        public string DeleteOP
        
{
            
get return this._deleteOP; }
            
set this._deleteOP = value; }
        }


        
/**/
        
/// <summary>
        
/// 取得是否溢出
        
/// </summary>

        public bool EOF
        
{
            
get return this._eOF; }
            
set this._eOF = value; }
        }


        
/**/
        
/// <summary>
        
/// 取得执行语句后得出的纪录条数
        
/// </summary>

        public int RecordCount
        
{
            
get return this._recordCount; }
            
set this._recordCount = value; }
        }


        
/**/
        
/// <summary>
        
/// 数据库中的表名
        
/// </summary>

        public string TableName
        
{
            
get return this._tableName; }
            
set this._tableName = value; }
        }


        
/**/
        
/// <summary>
        
/// 返回的记录集
        
/// </summary>

        public DataRow RecordSet
        
{
            
get return this._recordSet; }
            
set this._recordSet = value; }
        }


        
/**/
        
/// <summary>
        
/// 返回的数据集
        
/// </summary>

        public DataSet DS
        
{
            
get return this._ds; }
            
set this._ds = value; }
        }


        
/**/
        
/// <summary>
        
/// 字段和控件的映射表
        
/// </summary>

        public Hashtable MapTable
        
{
            
get return this._mapTable; }
            
set this._mapTable = value; }
        }


        
/**/
        
/// <summary>
        
/// 修改数据时,作为修改结果
        
/// </summary>

        public Hashtable Result
        
{
            
get return this._result; }
            
set this._result = value; }
        }


        
/**/
        
/// <summary>
        
/// 保存数据用的字段和值对应的哈希表,修改数据时用作条件
        
/// </summary>

        public Hashtable Dict
        
{
            
get return this._dict; }
            
set this._dict = value; }
        }


        
/**/
        
/// <summary>
        
/// 查询语句
        
/// </summary>

        public string CommandString
        
{
            
get return this._commandString; }
            
set this._commandString = value; }
        }


        
/**/
        
/// <summary>
        
/// 连接串
        
/// </summary>

        public string ConnectString
        
{
            
get return this._connectString; }
            
set this._connectString = value; }
        }


        
#endregion
 
       
        
#region DataAccess的构造函数
        
/// <summary>
        
/// 空构造函数
        
/// </summary>

        public DataAccess()
        
{
            ConnectString 
= System.Configuration.ConfigurationSettings.AppSettings["DBConn"];
            Conn 
= new System.Data.SqlClient.SqlConnection(ConnectString);
            
if (System.Configuration.ConfigurationSettings.AppSettings["WriteLog"== "true")
            
{
                _writeLog 
= true;
            }

            
else
            
{
                _writeLog 
= false;
            }


        }


        
~DataAccess()
        
{
        }


        
/**/
        
/// <summary>
        
/// DataAccess的构造函数
        
/// </summary>
        
/// <param name="DB1">要访问的数据库名,Web.config里设置的连接字符串对应的key</param>
        
/// <param name="TableName1">要访问的数据表名</param>

        public DataAccess(string DB1, string TableName1)
        
{
            
this.ErrorMessage = "";
            DB 
= DB1;
            TableName 
= TableName1;
            
try
            
{
                ConnectString 
= System.Configuration.ConfigurationSettings.AppSettings[DB1];
                
if (System.Configuration.ConfigurationSettings.AppSettings["WriteLog"== "true")
                
{
                    _writeLog 
= true;
                }

                
else
                
{
                    _writeLog 
= false;
                }

                Conn 
= new System.Data.SqlClient.SqlConnection(ConnectString);
                Dict 
= new Hashtable();
                Result 
= new Hashtable();
                MapTable 
= new Hashtable();
                DS 
= new DataSet();

                
//            IS_Open = false;
                _path = "C:\\WebDebug.log";
                
if (_writeLog)
                
{
                    
if (!File.Exists(_path))
                    
{
                        
using (SWCreate = File.CreateText(_path))
                        
{
                            SWCreate.WriteLine(
" ");
                            SWCreate.Close();
                        }

                    }

                    
using (SWApp = File.AppendText(_path))
                    
{
                        SWApp.WriteLine(
" ");
                    }

                }

            }

            
catch (Exception e)
            
{
                
this.ErrorMessage = e.ToString();
            }

        }


        
/**/
        
/// <summary>
        
/// DataAccess的构造函数
        
/// </summary>
        
/// <param name="CST">数据库的连接字符串</param>
        
/// <param name="TableName1">要访问的数据表名</param>
        
/// <param name="flag">是否初始化</param>

        public DataAccess(string CST, string TableName1, bool flag)
        
{
            
if (flag == true)
            
{
                
this.ErrorMessage = "";
                TableName 
= TableName1;
                
try
                
{
                    
if (System.Configuration.ConfigurationSettings.AppSettings["WriteLog"== "true")
                    
{
                        _writeLog 
= true;
                    }

                    
else
                    
{
                        _writeLog 
= false;
                    }

                    ConnectString 
= CST;
                    Conn 
= new System.Data.SqlClient.SqlConnection(ConnectString);
                    Dict 
= new Hashtable();
                    Result 
= new Hashtable();
                    MapTable 
= new Hashtable();
                    DS 
= new DataSet();

                    _path 
= "C:\\WebDebug.log";
                    
if (_writeLog)
                    
{
                        
if (!File.Exists(_path))
                        
{
                            
using (SWCreate = File.CreateText(_path))
                            
{
                                SWCreate.WriteLine(
" ");
                                SWCreate.Close();
                            }

                        }

                        
using (SWApp = File.AppendText(_path))
                        
{
                            SWApp.WriteLine(
" ");
                        }

                    }

                }

                
catch (Exception e)
                
{
                    
this.ErrorMessage = e.ToString();
                }

            }

        }

        
#endregion


        
#region ExecuteNonQuery
        
/// <summary>
        
/// 执行无返回结果的SQL
        
/// </summary>
        
/// <param name="strSQL"></param>

        public void ExecuteNonQuery(string strSQL)
        
{                   
            Comm 
= new SqlCommand();
            SqlTransaction Trans;
            Conn.Open();
            Trans 
= Conn.BeginTransaction();
            Comm.CommandText 
= strSQL;
            Comm.Connection 
= Conn;
            Comm.Transaction 
= Trans;
            Comm.CommandTimeout 
= 60;

            
try
            
{
                
if (_writeLog)
                
{
                    
using (SWApp = File.AppendText(_path))
                    
{
                        SWApp.WriteLine(
"[" + DateTime.Now.ToString() + "]  CommandString = " + strSQL);
                        SWApp.Close();
                    }

                }


                Comm.ExecuteNonQuery();
                Trans.Commit();
                
this.Conn.Close();
            }

            
catch (Exception e)
            
{
                Trans.Rollback();
                
this.Conn.Close();
                
if (_writeLog)
                
{
                    
using (SWApp = File.AppendText(_path))
                    
{
                        SWApp.WriteLine(
"[" + DateTime.Now.ToString() + "]  Error Message : " + e.ToString());
                    }

                }

                
this.ErrorMessage = e.ToString();
                
throw new Exception(e.ToString());
            }
            
        }


        
/// <summary>
        
/// 执行无返回结果的SQL
        
/// </summary>
        
/// <param name="param">参数集合</param>
        
/// <param name="strSQL"></param>

        public void ExecuteNonQuery(string strSQL, ICollection param)
        
{           
            Comm 
= new SqlCommand();
            SqlTransaction Trans;
            Conn.Open();
            Trans 
= Conn.BeginTransaction();
            Comm.CommandText 
= strSQL;
            Comm.Connection 
= Conn;
            Comm.Transaction 
= Trans;
            Comm.CommandTimeout 
= 60;

            
try
            
{
                
if (_writeLog)
                
{
                    
using (SWApp = File.AppendText(_path))
                    
{
                        SWApp.WriteLine(
"[" + DateTime.Now.ToString() + "]  CommandString = " + strSQL);
                        SWApp.Close();
                    }

                }


                
if (param != null)
                
{
                    
foreach (ParamInfo p in param)
                    
{
                        IDbDataParameter pa 
= Comm.CreateParameter();
                        pa.ParameterName 
= p.Name;
                        pa.Value 
= p.Value;

                        
//处理大文本
                        if (pa is System.Data.SqlClient.SqlParameter && pa.Value != null && pa.Value.ToString().Length >= 4000)
                        
{
                            System.Data.SqlClient.SqlParameter p1 
= pa as System.Data.SqlClient.SqlParameter;
                            p1.SqlDbType 
= System.Data.SqlDbType.NText;
                            Comm.Parameters.Add(p1);
                        }

                        
else
                        
{
                            Comm.Parameters.Add(pa);
                        }

                    }

                }


                Comm.ExecuteNonQuery();
                FillParameterValue(Comm.Parameters, param);
                Trans.Commit();
                
this.Conn.Close();
            }

            
catch (Exception e)
            
{
                Trans.Rollback();
                
this.Conn.Close();
                
if (_writeLog)
                
{
                    
using (SWApp = File.AppendText(_path))
                    
{
                        SWApp.WriteLine(
"[" + DateTime.Now.ToString() + "]  Error Message : " + e.ToString());
                    }

                }

                
this.ErrorMessage = e.ToString();
                
throw new Exception(e.ToString());
            }
           
        }

        
#endregion


        
#region ExecuteScalar
        
/// <summary>
        
/// 返回查询结果的第一行第一列的值
        
/// </summary>
        
/// <param name="strSQL"></param>
        
/// <returns></returns>

        public object ExecuteScalar(string strSQL)
        
{
            SqlTransaction Trans;
            Comm 
= new SqlCommand();
            Conn.Open();
            Trans 
= Conn.BeginTransaction();
            Comm.CommandText 
= strSQL;
            Comm.Connection 
= Conn;
            Comm.Transaction 
=Trans ;             
            Comm.CommandTimeout 
= 60;
            
try
            
{
                
if (_writeLog)
                
{
                    
using (SWApp = File.AppendText(_path))
                    
{
                        SWApp.WriteLine(
"[" + DateTime.Now.ToString() + "]  CommandString = " + strSQL);
                        SWApp.Close();
                    }

                }


                
object objResutl = Comm.ExecuteScalar();
                Trans.Commit();
                
this.Conn.Close();
                
return objResutl;
            }

            
catch (Exception e)
            
{
                Trans.Rollback();
                
this.Conn.Close();
                
if (_writeLog)
                
{
                    
using (SWApp = File.AppendText(_path))
                    
{
                        SWApp.WriteLine(
"[" + DateTime.Now.ToString() + "]  Error Message : " + e.ToString());
                    }

                }

                
this.ErrorMessage = e.ToString();
                
throw new Exception(e.ToString());
            }

        }


        
/// <summary>
        
/// 返回查询结果的第一行第一列的值
        
/// </summary>
        
/// <param name="strSQL"></param>
        
/// <param name="param">参数集合</param>
        
/// <returns></returns>

        public object ExecuteScalar(string strSQL,ICollection param)
        
{
            SqlTransaction Trans;
            Comm 
= new SqlCommand();
            Conn.Open();
            Trans 
= Conn.BeginTransaction();
            Comm.CommandText 
= strSQL;
            Comm.Connection 
= Conn;
            Comm.Transaction 
=Trans ;             
            Comm.CommandTimeout 
= 60;
            
try
            
{
                
if (_writeLog)
                
{
                    
using (SWApp = File.AppendText(_path))
                    
{
                        SWApp.WriteLine(
"[" + DateTime.Now.ToString() + "]  CommandString = " + strSQL);
                        SWApp.Close();
                    }

                }

                
if ( param != null )
                
{
                    
foreach ( ParamInfo p in param )
                    
{
                        IDbDataParameter pa 
= Comm.CreateParameter();
                        pa.ParameterName 
= p.Name;
                        pa.Value 
= p.Value;
                        pa.Direction 
= p.Direction;

                        Comm.Parameters.Add(pa);
                    }

                }


                
object objResutl = Comm.ExecuteScalar();
                FillParameterValue(Comm.Parameters,param);
                Trans.Commit();
                
this.Conn.Close();
                
return objResutl;

            }

            
catch (Exception e)
            
{
                Trans.Rollback();
                
this.Conn.Close();
                
if (_writeLog)
                
{
                    
using (SWApp = File.AppendText(_path))
                    
{
                        SWApp.WriteLine(
"[" + DateTime.Now.ToString() + "]  Error Message : " + e.ToString());
                    }

                }

                
this.ErrorMessage = e.ToString();
                
throw new Exception(e.ToString());
            }

        }

        
#endregion


        
#region ExecuteDataSet
        
/// <summary>
        
/// 执行SQL语句并返回DataTable对象
        
/// </summary>

        public DataSet ExecuteDataSet(string strSQL)
        
{
            SqlTransaction Trans;
            Comm 
= new SqlCommand();
            Conn.Open();
            Trans 
= Conn.BeginTransaction();
            Comm.CommandText 
= strSQL;
            Comm.Connection 
= Conn;
            Comm.Transaction 
=Trans ;             
            Comm.CommandTimeout 
= 60;
            DataSet ds 
= new DataSet();
            
try
            
{                
                
if (_writeLog)
                
{
                    
using (SWApp = File.AppendText(_path))
                    
{
                        SWApp.WriteLine(
"[" + DateTime.Now.ToString() + "]==> Begin Get DataSet.");
                        SWApp.WriteLine(
"CommandString = " + strSQL);
                        SWApp.Close();
                    }

                }


                
try
                
{
                    IDataReader dr 
= Comm.ExecuteReader();        
                    
do
                    
{
                        DataTable dt 
= new DataTable();
                        dt.Locale 
= CultureInfo.CurrentCulture;

                        DataColumn col 
= null;
                        DataRowCollection rows 
= dr.GetSchemaTable().Rows;
                        
foreach(DataRow row in rows)
                        
{
                            col 
= new DataColumn();
                            col.ColumnName 
= row["ColumnName"== null ? null : row["ColumnName"].ToString();
                            col.Unique 
= Convert.ToBoolean(row["IsUnique"]);
                            col.AllowDBNull 
= Convert.ToBoolean(row["AllowDBNull"]);
                            col.ReadOnly 
= Convert.ToBoolean(row["IsReadOnly"]);
                            col.DataType 
= row["DataType"as Type;

                            dt.Columns.Add(col);
                        }


                        
while (dr.Read())
                        
{
                            DataRow row 
= dt.NewRow();
                        
                            
foreach(DataColumn c in dt.Columns)
                            
{
                                row[c] 
= dr[c.ColumnName];
                            }


                            dt.Rows.Add(row);
                        }


                        ds.Tables.Add(dt);
                    }

                    
while (dr.NextResult());
                    dr.Close();                    
                    Trans.Commit();
                }

                
catch (Exception e)
                
{
                    Trans.Rollback();                    
                    
this.ErrorMessage = e.ToString();
                    
if (_writeLog)
                    
{
                        
using (SWApp = File.AppendText(_path))
                        
{
                            SWApp.WriteLine(
"[" + DateTime.Now.ToString() + "]==> Error Message: " + e.ToString());
                        }

                    }

                    
throw new Exception(e.ToString());
                }


                
if (_writeLog)
                
{
                    
using (SWApp = File.AppendText(_path))
                    
{
                        SWApp.WriteLine(
"[" + DateTime.Now.ToString() + "]==>  End of Getting DataSet.");
                        SWApp.Close();
                    }

                }

            }

            
finally
            
{
                Conn.Close();
            }

            
return ds;
        }


        
/// <summary>
        
/// 执行SQL语句并返回DataTable对象
        
/// <param name="strSQL">SQL语句</param>
        
/// <param name="param">参数集合</param>
        
/// </summary>

        public DataSet ExecuteDataSet(string strSQL, ICollection param)
        
{
            SqlTransaction Trans;
            Comm 
= new SqlCommand();
            Conn.Open();
            Trans 
= Conn.BeginTransaction();
            Comm.CommandText 
= strSQL;
            Comm.Connection 
= Conn;
            Comm.Transaction 
=Trans ;             
            Comm.CommandTimeout 
= 60;
            DataSet ds 
= new DataSet();
            
try
            
{                
                
if (_writeLog)
                
{
                    
using (SWApp = File.AppendText(_path))
                    
{
                        SWApp.WriteLine(
"[" + DateTime.Now.ToString() + "]==> Begin Get DataSet.");
                        SWApp.WriteLine(
"CommandString = " + strSQL);
                        SWApp.Close();
                    }

                }


                
try
                
{
                    
if ( param != null )
                    
{
                        
foreach ( ParamInfo p in param )
                        
{
                            IDbDataParameter pa 
= Comm.CreateParameter();
                            pa.ParameterName 
= p.Name;
                            pa.Value 
= p.Value;
                            pa.Direction 
= p.Direction;

                            Comm.Parameters.Add(pa);
                        }

                    }

                    IDataReader dr 
= Comm.ExecuteReader(); 

                    
do
                    
{
                        DataTable dt 
= new DataTable();
                        dt.Locale 
= CultureInfo.CurrentCulture;

                        DataColumn col 
= null;
                        DataRowCollection rows 
= dr.GetSchemaTable().Rows;
                        
foreach(DataRow row in rows)
                        
{
                            col 
= new DataColumn();
                            col.ColumnName 
= row["ColumnName"== null ? null : row["ColumnName"].ToString();
                            col.Unique 
= Convert.ToBoolean(row["IsUnique"]);
                            col.AllowDBNull 
= Convert.ToBoolean(row["AllowDBNull"]);
                            col.ReadOnly 
= Convert.ToBoolean(row["IsReadOnly"]);
                            col.DataType 
= row["DataType"as Type;

                            dt.Columns.Add(col);
                        }


                        
while (dr.Read())
                        
{
                            DataRow row 
= dt.NewRow();
                        
                            
foreach(DataColumn c in dt.Columns)
                            
{
                                row[c] 
= dr[c.ColumnName];
                            }


                            dt.Rows.Add(row);
                        }


                        ds.Tables.Add(dt);
                    }

                    
while (dr.NextResult());
                    dr.Close();                    
                    Trans.Commit();
                }

                
catch (Exception e)
                
{
                    Trans.Rollback();                    
                    
this.ErrorMessage = e.ToString();
                    
if (_writeLog)
                    
{
                        
using (SWApp = File.AppendText(_path))
                        
{
                            SWApp.WriteLine(
"[" + DateTime.Now.ToString() + "]==> Error Message: " + e.ToString());
                        }

                    }

                    
throw new Exception(e.ToString());
                }


                
if (_writeLog)
                
{
                    
using (SWApp = File.AppendText(_path))
                    
{
                        SWApp.WriteLine(
"[" + DateTime.Now.ToString() + "]==>  End of Getting DataSet.");
                        SWApp.Close();
                    }

                }

            }

            
finally
            
{
                Conn.Close();
            }

            
return ds;
        }

        
#endregion


        
#region ExecuteDataTable
        
/// <summary>
        
/// 执行SQL语句并返回DataTable对象
        
/// </summary>

        public DataTable ExecuteDataTable(string strSQL)
        
{
            
return ExecuteDataSet(strSQL).Tables[0];
        }


        
/// <summary>
        
/// 执行SQL语句并返回DataTable对象
        
/// <param name="strSQL">SQL语句</param>
        
/// <param name="param">参数集合</param>
        
/// </summary>

        public DataTable ExecuteDataTable(string strSQL, ICollection param)
        
{
            
return ExecuteDataSet(strSQL,param).Tables[0];
        }

        
#endregion


        
#region ExecuteDataReader
        
/// <summary>
        
/// <param name="strSQL">SQL语句</param>
        
/// </summary>

        public IDataReader ExecuteDataReader(string strSQL)
        
{
            SqlTransaction Trans;
            Comm 
= new SqlCommand();
            Conn.Open();
            Trans 
= Conn.BeginTransaction();
            Comm.CommandText 
= strSQL;
            Comm.Connection 
= Conn;
            Comm.Transaction 
= Trans;
            Comm.CommandTimeout 
= 60;
            IDataReader dr ; 
            
try
            
{
                
if (_writeLog)
                
{
                    
using (SWApp = File.AppendText(_path))
                    
{
                        SWApp.WriteLine(
"[" + DateTime.Now.ToString() + "]==> Begin Get DataReader.");
                        SWApp.WriteLine(
"CommandString = " + strSQL);
                        SWApp.Close();
                    }

                }


                
try
                
{
                    dr
=Comm.ExecuteReader();
                    Trans.Commit();
                }

                
catch (Exception e)
                
{
                    Trans.Rollback();
                    
this.ErrorMessage = e.ToString();
                    
if (_writeLog)
                    
{
                        
using (SWApp = File.AppendText(_path))
                        
{
                            SWApp.WriteLine(
"[" + DateTime.Now.ToString() + "]==> Error Message: " + e.ToString());
                        }

                    }

                    
throw new Exception(e.ToString());
                }


                
if (_writeLog)
                
{
                    
using (SWApp = File.AppendText(_path))
                    
{
                        SWApp.WriteLine(
"[" + DateTime.Now.ToString() + "]==>  End of Getting DataReader.");
                        SWApp.Close();
                    }

                }

            }

            
finally
            
{
                Conn.Close();
            }

            
return dr;
        }


        
/// <summary>
        
///
        
/// <param name="strSQL">SQL语句</param>
        
/// <param name="param">参数集合</param>
        
/// </summary>

        public IDataReader ExecuteDataReader(string strSQL, ICollection param)
        
{
            SqlTransaction Trans;
            Comm 
= new SqlCommand();
            Conn.Open();
            Trans 
= Conn.BeginTransaction();
            Comm.CommandText 
= strSQL;
            Comm.Connection 
= Conn;
            Comm.Transaction 
= Trans;
            Comm.CommandTimeout 
= 60;
            IDataReader dr;            
            
try
            
{
                
if (_writeLog)
                
{
                    
using (SWApp = File.AppendText(_path))
                    
{
                        SWApp.WriteLine(
"[" + DateTime.Now.ToString() + "]==> Begin Get DataReader.");
                        SWApp.WriteLine(
"CommandString = " + strSQL);
                        SWApp.Close();
                    }

                }


                
try
                
{
                    
if (param != null)
                    
{
                        
foreach (ParamInfo p in param)
                        
{
                            IDbDataParameter pa 
= Comm.CreateParameter();
                            pa.ParameterName 
= p.Name;
                            pa.Value 
= p.Value;
                            pa.Direction 
= p.Direction;

                            Comm.Parameters.Add(pa);
                        }

                    }

                    dr 
= Comm.ExecuteReader();
                    Trans.Commit();                    
                }

                
catch (Exception e)
                
{
                    Trans.Rollback();
                    
this.ErrorMessage = e.ToString();
                    
if (_writeLog)
                    
{
                        
using (SWApp = File.AppendText(_path))
                        
{
                            SWApp.WriteLine(
"[" + DateTime.Now.ToString() + "]==> Error Message: " + e.ToString());
                        }

                    }

                    
throw new Exception(e.ToString());
                }


                
if (_writeLog)
                
{
                    
using (SWApp = File.AppendText(_path))
                    
{
                        SWApp.WriteLine(
"[" + DateTime.Now.ToString() + "]==>  End of Getting DataReader.");
                        SWApp.Close();
                    }

                }

            }

            
finally
            
{
                Conn.Close();
            }

            
return dr;
        }

        
#endregion


        
#region ExecuteProcNonQuery
        
/// <summary>
        
/// 执行无返回结果的存储过程
        
/// </summary>
        
/// <param name="procName">存储过程名</param>

        public void ExecuteProcNonQuery(string procName)
        
{            
            Comm 
= new SqlCommand();
            SqlTransaction Trans;
            Conn.Open();
            Trans 
= Conn.BeginTransaction();  
            Comm.CommandType 
= CommandType.StoredProcedure;
            Comm.CommandText 
= procName;                      
            Comm.Connection 
= Conn;
            Comm.Transaction 
= Trans;
            Comm.CommandTimeout 
= 60;

            
try
            
{
                
if (_writeLog)
                
{
                    
using (SWApp = File.AppendText(_path))
                    
{
                        SWApp.WriteLine(
"[" + DateTime.Now.ToString() + "]  StoredProcedureName = " + procName);
                        SWApp.Close();
                    }

                }

                Comm.ExecuteNonQuery();
                Trans.Commit();
                
this.Conn.Close();
            }

            
catch (Exception e)
            
{
                Trans.Rollback();
                
this.Conn.Close();
                
if (_writeLog)
                
{
                    
using (SWApp = File.AppendText(_path))
                    
{
                        SWApp.WriteLine(
"[" + DateTime.Now.ToString() + "]  Error Message : " + e.ToString());
                    }

                }

                
this.ErrorMessage = e.ToString();
                
throw new Exception(e.ToString());
            }
           
        }


        
/// <summary>
        
/// 执行无返回结果的存储过程
        
/// </summary>
        
/// <param name="procName">存储过程名</param>
        
/// <param name="param">参数集合</param>

        public void ExecuteProcNonQuery(string procName, ICollection param)
        
{            
            Comm 
= new SqlCommand();
            SqlTransaction Trans;
            Conn.Open();
            Trans 
= Conn.BeginTransaction();
            Comm.CommandType 
= CommandType.StoredProcedure;
            Comm.CommandText 
= procName;
            Comm.Connection 
= Conn;
            Comm.Transaction 
= Trans;
            Comm.CommandTimeout 
= 60;

            
try
            
{
                
if (_writeLog)
                
{
                    
using (SWApp = File.AppendText(_path))
                    
{
                        SWApp.WriteLine(
"[" + DateTime.Now.ToString() + "]  StoredProcedureName = " + procName);
                        SWApp.Close();
                    }

                }


                
if (param != null)
                
{
                    
foreach (ParamInfo p in param)
                    
{
                        IDbDataParameter pa 
= Comm.CreateParameter();
                        pa.ParameterName 
= p.Name;
                        pa.Value 
= p.Value;

                        
//处理大文本
                        if (pa is System.Data.SqlClient.SqlParameter && pa.Value != null && pa.Value.ToString().Length >= 4000)
                        
{
                            System.Data.SqlClient.SqlParameter p1 
= pa as System.Data.SqlClient.SqlParameter;
                            p1.SqlDbType 
= System.Data.SqlDbType.NText;
                            Comm.Parameters.Add(p1);
                        }

                        
else
                        
{
                            Comm.Parameters.Add(pa);
                        }

                    }

                }


                Comm.ExecuteNonQuery();
                FillParameterValue(Comm.Parameters, param);
                Trans.Commit();
                
this.Conn.Close();
            }

            
catch (Exception e)
            
{
                Trans.Rollback();
                
this.Conn.Close();
                
if (_writeLog)
                
{
                    
using (SWApp = File.AppendText(_path))
                    
{
                        SWApp.WriteLine(
"[" + DateTime.Now.ToString() + "]  Error Message : " + e.ToString());
                    }

                }

                
this.ErrorMessage = e.ToString();
                
throw new Exception(e.ToString());
            }
           
        }

        
#endregion


        
#region ExecuteProcScalar
        
/// <summary>
        
/// 执行存储过程并返回第一行第一列的值
        
/// </summary>
        
/// <param name="procName">存储过程名</param>
        
/// <returns></returns>

        public object ExecuteProcScalar(string procName)
        
{
            SqlTransaction Trans;
            Comm 
= new SqlCommand();
            Conn.Open();
            Trans 
= Conn.BeginTransaction();
            Comm.CommandType 
= CommandType.StoredProcedure;
            Comm.CommandText 
= procName;
            Comm.Connection 
= Conn;
            Comm.Transaction 
= Trans;
            Comm.CommandTimeout 
= 60;
            
try
            
{
                
if (_writeLog)
                
{
                    
using (SWApp = File.AppendText(_path))
                    
{
                        SWApp.WriteLine(
"[" + DateTime.Now.ToString() + "]  StoredProcedureName = " + procName);
                        SWApp.Close();
                    }

                }


                
object objResutl = Comm.ExecuteScalar();
                Trans.Commit();
                
this.Conn.Close();
                
return objResutl;
            }

            
catch (Exception e)
            
{
                Trans.Rollback();
                
this.Conn.Close();
                
if (_writeLog)
                
{
                    
using (SWApp = File.AppendText(_path))
                    
{
                        SWApp.WriteLine(
"[" + DateTime.Now.ToString() + "]  Error Message : " + e.ToString());
                    }

                }

                
this.ErrorMessage = e.ToString();
                
throw new Exception(e.ToString());
            }

        }


        
/// <summary>
        
/// 执行存储过程并返回第一行第一列的值
        
/// </summary>
        
/// <param name="procName">存储过程名</param>
        
/// <param name="param">参数集合</param>
        
/// <returns></returns>

        public object ExecuteProcScalar(string procName, ICollection param)
        
{
            SqlTransaction Trans;
            Comm 
= new SqlCommand();
            Conn.Open();
            Trans 
= Conn.BeginTransaction();
            Comm.CommandType 
= CommandType.StoredProcedure;
            Comm.CommandText 
= procName;
            Comm.Connection 
= Conn;
            Comm.Transaction 
= Trans;
            Comm.CommandTimeout 
= 60;
            
try
            
{
                
if (_writeLog)
                
{
                    
using (SWApp = File.AppendText(_path))
                    
{
                        SWApp.WriteLine(
"[" + DateTime.Now.ToString() + "]  StoredProcedureName = " + procName);
                        SWApp.Close();
                    }

                }

                
if (param != null)
                
{
                    
foreach (ParamInfo p in param)
                    
{
                        IDbDataParameter pa 
= Comm.CreateParameter();
                        pa.ParameterName 
= p.Name;
                        pa.Value 
= p.Value;
                        pa.Direction 
= p.Direction;

                        Comm.Parameters.Add(pa);
                    }

                }


                
object objResutl = Comm.ExecuteScalar();
                FillParameterValue(Comm.Parameters, param);
                Trans.Commit();
                
this.Conn.Close();
                
return objResutl;

            }

            
catch (Exception e)
            
{
                Trans.Rollback();
                
this.Conn.Close();
                
if (_writeLog)
                
{
                    
using (SWApp = File.AppendText(_path))
                    
{
                        SWApp.WriteLine(
"[" + DateTime.Now.ToString() + "]  Error Message : " + e.ToString());
                    }

                }

                
this.ErrorMessage = e.ToString();
                
throw new Exception(e.ToString());
            }

        }

        
#endregion


        
#region  ExecuteProcDataSet
        
/// <summary>
        
///执行存储过程并返回DataSet对象
        
/// </summary>
        
/// <param name="procName">存储过程名</param>
        
/// <returns>返回DataSet对象</returns>

        public DataSet ExecuteProcDataSet(string procName)
        
{
            SqlTransaction Trans;
            Comm 
= new SqlCommand();
            Conn.Open();
            Trans 
= Conn.BeginTransaction();
            Comm.CommandType 
= CommandType.StoredProcedure;
            Comm.CommandText 
= procName;
            Comm.Connection 
= Conn;
            Comm.Transaction 
= Trans;
            Comm.CommandTimeout 
= 60;
            DataSet ds 
= new DataSet();
            
try
            
{
                
if (_writeLog)
                
{
                    
using (SWApp = File.AppendText(_path))
                    
{
                        SWApp.WriteLine(
"[" + DateTime.Now.ToString() + "]==> Begin Get DataSet.");
                        SWApp.WriteLine(
"StoredProcedureName = " + procName);
                        SWApp.Close();
                    }

                }


                
try
                
{
                    IDataReader dr 
= Comm.ExecuteReader();
                    
do
                    
{
                        DataTable dt 
= new DataTable();
                        dt.Locale 
= CultureInfo.CurrentCulture;

                        DataColumn col 
= null;
                        DataRowCollection rows 
= dr.GetSchemaTable().Rows;
                        
foreach (DataRow row in rows)
                        
{
                            col 
= new DataColumn();
                            col.ColumnName 
= row["ColumnName"== null ? null : row["ColumnName"].ToString();
                            col.Unique 
= Convert.ToBoolean(row["IsUnique"]);
                            col.AllowDBNull 
= Convert.ToBoolean(row["AllowDBNull"]);
                            col.ReadOnly 
= Convert.ToBoolean(row["IsReadOnly"]);
                            col.DataType 
= row["DataType"as Type;

                            dt.Columns.Add(col);
                        }


                        
while (dr.Read())
                        
{
                            DataRow row 
= dt.NewRow();

                            
foreach (DataColumn c in dt.Columns)
                            
{
                                row[c] 
= dr[c.ColumnName];
                            }


                            dt.Rows.Add(row);
                        }


                        ds.Tables.Add(dt);
                    }

                    
while (dr.NextResult());
                    dr.Close();
                    Trans.Commit();
                }

                
catch (Exception e)
                
{
                    Trans.Rollback();
                    
this.ErrorMessage = e.ToString();
                    
if (_writeLog)
                    
{
                        
using (SWApp = File.AppendText(_path))
                        
{
                            SWApp.WriteLine(
"[" + DateTime.Now.ToString() + "]==> Error Message: " + e.ToString());
                        }

                    }

                    
throw new Exception(e.ToString());
                }


                
if (_writeLog)
                
{
                    
using (SWApp = File.AppendText(_path))
                    
{
                        SWApp.WriteLine(
"[" + DateTime.Now.ToString() + "]==>  End of Getting DataSet.");
                        SWApp.Close();
                    }

                }

            }

            
finally
            
{
                Conn.Close();
            }

            
return ds;
        }


        
/// <summary>
        
/// 执行存储过程并返回DataSet对象
        
/// </summary>
        
/// <param name="procName">存储过程名</param>
        
/// <param name="param">参数集合</param>
        
/// <returns>返回DataSet对象</returns>

        public DataSet ExecuteProcDataSet(string procName, ICollection param)
        
{
            SqlTransaction Trans;
            Comm 
= new SqlCommand();
            Conn.Open();
            Trans 
= Conn.BeginTransaction();
            Comm.CommandType 
= CommandType.StoredProcedure;
            Comm.CommandText 
= procName;
            Comm.Connection 
= Conn;
            Comm.Transaction 
= Trans;
            Comm.CommandTimeout 
= 60;
            DataSet ds 
= new DataSet();
            
try
            
{
                
if (_writeLog)
                
{
                    
using (SWApp = File.AppendText(_path))
                    
{
                        SWApp.WriteLine(
"[" + DateTime.Now.ToString() + "]==> Begin Get DataSet.");
                        SWApp.WriteLine(
"StoredProcedureName = " + procName);
                        SWApp.Close();
                    }

                }


                
try
                
{
                    
if (param != null)
                    
{
                        
foreach (ParamInfo p in param)
                        
{
                            IDbDataParameter pa 
= Comm.CreateParameter();
                            pa.ParameterName 
= p.Name;
                            pa.Value 
= p.Value;
                            pa.Direction 
= p.Direction;

                            Comm.Parameters.Add(pa);
                        }

                    }

                    IDataReader dr 
= Comm.ExecuteReader();

                    
do
                    
{
                        DataTable dt 
= new DataTable();
                        dt.Locale 
= CultureInfo.CurrentCulture;

                        DataColumn col 
= null;
                        DataRowCollection rows 
= dr.GetSchemaTable().Rows;
                        
foreach (DataRow row in rows)
                        
{
                            col 
= new DataColumn();
                            col.ColumnName 
= row["ColumnName"== null ? null : row["ColumnName"].ToString();
                            col.Unique 
= Convert.ToBoolean(row["IsUnique"]);
                            col.AllowDBNull 
= Convert.ToBoolean(row["AllowDBNull"]);
                            col.ReadOnly 
= Convert.ToBoolean(row["IsReadOnly"]);
                            col.DataType 
= row["DataType"as Type;

                            dt.Columns.Add(col);
                        }


                        
while (dr.Read())
                        
{
                            DataRow row 
= dt.NewRow();

                            
foreach (DataColumn c in dt.Columns)
                            
{
                                row[c] 
= dr[c.ColumnName];
                            }


                            dt.Rows.Add(row);
                        }


                        ds.Tables.Add(dt);
                    }

                    
while (dr.NextResult());
                    dr.Close();
                    Trans.Commit();
                }

                
catch (Exception e)
                
{
                    Trans.Rollback();
                    
this.ErrorMessage = e.ToString();
                    
if (_writeLog)
                    
{
                        
using (SWApp = File.AppendText(_path))
                        
{
                            SWApp.WriteLine(
"[" + DateTime.Now.ToString() + "]==> Error Message: " + e.ToString());
                        }

                    }

                    
throw new Exception(e.ToString());
                }


                
if (_writeLog)
                
{
                    
using (SWApp = File.AppendText(_path))
                    
{
                        SWApp.WriteLine(
"[" + DateTime.Now.ToString() + "]==>  End of Getting DataSet.");
                        SWApp.Close();
                    }

                }

            }

            
finally
            
{
                Conn.Close();
            }

            
return ds;
        }

        
#endregion


        
#region ExecuteProcDataTable
        
/// <summary>
        
/// 执行存储过程并返回DataTable对象
        
/// </summary>
        
/// <param name="procName">存储过程名</param>
        
/// <returns>返回DataTable对象</returns>

        public DataTable ExecuteProcDataTable(string procName)
        
{
            
return ExecuteProcDataSet(procName).Tables[0];
        }


        
/// <summary>
        
/// 执行存储过程并返回DataTable对象
        
/// </summary>
        
/// <param name="procName">存储过程名</param>
        
/// <param name="param">参数集合</param>
        
/// <returns>返回DataTable对象</returns>

        public DataTable ExecuteProcDataTable(string procName, ICollection param)
        
{
            
return ExecuteProcDataSet(procName, param).Tables[0];
        }

        
#endregion
        

        
#region FillParameterValue
        
/// <summary>
        
/// 填充输出型参数和返回值型参数
        
/// </summary>
        
/// <param name="OutPutParameters">SQL命令执行后的参数集合</param>
        
/// <param name="param">SQL命令执行前的参数集合</param>

        void FillParameterValue(System.Data.IDataParameterCollection OutPutParameters, ICollection param)
        
{
            
if (OutPutParameters == null || param == nullreturn;

            ArrayList procParam 
= new ArrayList();
            
foreach (IDbDataParameter sqlParameter in OutPutParameters) //记录经过存储过程运算后的参数值
            {
                
foreach (ParamInfo p in param)
                
{
                    
if (p.Name == sqlParameter.ParameterName)
                    
{
                        procParam.Add(
new ParamInfo(p.Name, sqlParameter.Value, p.Direction, p.Size));
                    }

                }

            }


            ArrayList procOutParam 
= param as ArrayList;
            procOutParam.Clear();
            
foreach (ParamInfo p in procParam) //填充参数值
            {
                procOutParam.Add(
new ParamInfo(p.Name, p.Value, p.Direction, p.Size));
            }

        }

        
#endregion

    }
        
}

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;

namespace VoteInfo
{
    
/// <summary>
    
/// SQL参数结构体
    
/// </summary>

    public struct ParamInfo
    
{
        
/// <summary>
        
/// 参数名称
        
/// </summary>

        public string Name;

        
/// <summary>
        
/// 值
        
/// </summary>

        public object Value;

        
/// <summary>
        
/// 参数长度
        
/// </summary>

        public int Size;

        
/// <summary>
        
/// 参数方向
        
/// </summary>

        public ParameterDirection Direction;

        
/// <summary>
        
/// 初始化参数对象
        
/// </summary>
        
/// <param name="name">参数名称</param>
        
/// <param name="val"></param>

        public ParamInfo(string name, object val)
        
{
            Name 
= name;
            Value 
= val;
            Direction 
= ParameterDirection.Input;
            Size 
= Value == null ? 50 : Value.ToString().Length;
        }


        
/// <summary>
        
/// 初始化参数对象
        
/// </summary>
        
/// <param name="name">参数名称</param>
        
/// <param name="val"></param>
        
/// <param name="direction"></param>

        public ParamInfo(string name, object val, ParameterDirection direction)
        
{
            Name 
= name;
            Value 
= val;
            Direction 
= direction;
            Size 
= Value == null ? 50 : Value.ToString().Length;
        }


        
public ParamInfo(string name, object val, ParameterDirection direction, int size)
        
{
            Name 
= name;
            Value 
= val;
            Direction 
= direction;
            Size 
= size;
        }

    }

}

0
0

猜你在找
【直播】机器学习&数据挖掘7周实训--韦玮
【套餐】系统集成项目管理工程师顺利通关--徐朋
【直播】3小时掌握Docker最佳实战-徐西宁
【套餐】机器学习系列套餐(算法+实战)--唐宇迪
【直播】计算机视觉原理及实战--屈教授
【套餐】微信订阅号+服务号Java版 v2.0--翟东平
【直播】机器学习之矩阵--黄博士
【套餐】微信订阅号+服务号Java版 v2.0--翟东平
【直播】机器学习之凸优化--马博士
【套餐】Javascript 设计模式实战--曾亮
查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:6368次
    • 积分:73
    • 等级:
    • 排名:千里之外
    • 原创:1篇
    • 转载:17篇
    • 译文:0篇
    • 评论:0条
    文章分类
    文章存档