首页 > 代码库 > ASP.NET经典的、封装好的ADO.NET类包

ASP.NET经典的、封装好的ADO.NET类包

using System;using System.Collections;using System.Collections.Specialized;using System.Runtime.Remoting.Messaging;using System.Data;using System.Data.SqlClient;using System.Configuration;namespace LTP.SQLServerDAL{ /// <summary> /// ADO.NET数据库操作基础类。 /// </summary> public abstract class DbManagerSQL {  //数据库连接字符串  protected static string connectionString = ConfigurationSettings.AppSettings["ConnectionString"];   public DbManagerSQL()  {   //   // TODO: 在此处添加构造函数逻辑   //  }  /// <summary>  /// 执行SQL语句,返回影响的记录数  /// </summary>  /// <param name="SQLString"></param>  /// <returns></returns>  public static int ExecuteSql(string SQLString)  {   using (SqlConnection connection = new SqlConnection(connectionString))   {        using (SqlCommand cmd = new SqlCommand(SQLString,connection))    {     try     {        connection.Open();      int rows=cmd.ExecuteNonQuery();      return rows;     }     catch(System.Data.SqlClient.SqlException E)     {          throw new Exception(E.Message);     }    }       }  }  /// <summary>  /// 执行两条SQL语句,实现数据库事务。  /// </summary>  /// <param name="SQLString1"></param>  /// <param name="SQLString2"></param>  public static void ExecuteSqlTran(string SQLString1,string SQLString2)  {   using (SqlConnection connection = new SqlConnection(connectionString))   {    connection.Open();    SqlCommand cmd = new SqlCommand();    cmd.Connection=connection;        SqlTransaction tx=connection.BeginTransaction();       cmd.Transaction=tx;        try    {          cmd.CommandText=SQLString1;     cmd.ExecuteNonQuery();     cmd.CommandText=SQLString2;     cmd.ExecuteNonQuery();          tx.Commit();         }    catch(System.Data.SqlClient.SqlException E)    {       tx.Rollback();     throw new Exception(E.Message);    }    finally    {     cmd.Dispose();     connection.Close();    }    }  }   /// <summary>  /// 执行多条SQL语句,实现数据库事务,每条语句以“;”分割。  /// </summary>  /// <param name="SQLStringList"></param>  public static void ExecuteSqlTran(string SQLStringList)  {   using (OdbcConnection conn = new OdbcConnection(connectionString))   {    conn.Open();    OdbcCommand cmd = new OdbcCommand();    cmd.Connection=conn;        OdbcTransaction tx=conn.BeginTransaction();       cmd.Transaction=tx;        try    {        string [] split= SQLStringList.Split(new Char [] { ‘;‘});     foreach (string strsql in split)      {      if (strsql.Trim()!="")      {       cmd.CommandText=strsql;       cmd.ExecuteNonQuery();      }     }            tx.Commit();         }    catch(System.Data.Odbc.OdbcException E)    {       tx.Rollback();     throw new Exception(E.Message);    }   }  }  /// <summary>  /// 执行带一个存储过程参数的的SQL语句。  /// </summary>  /// <param name="SQLString"></param>  /// <param name="content"></param>  /// <returns></returns>  public static int ExecuteSql(string SQLString,string content)  {       using (SqlConnection connection = new SqlConnection(connectionString))   {    SqlCommand cmd = new SqlCommand(SQLString,connection);      System.Data.SqlClient.SqlParameter  myParameter = new System.Data.SqlClient.SqlParameter ( "@content", SqlDbType.NText);    myParameter.Value = http://www.mamicode.com/content ;"strSQL"></param>  /// <param name="fs"></param>  /// <returns></returns>  public static int ExecuteSqlInsertImg(string strSQL,byte[] fs)  {     using (SqlConnection connection = new SqlConnection(connectionString))   {    SqlCommand cmd = new SqlCommand(strSQL,connection);     System.Data.SqlClient.SqlParameter  myParameter = new System.Data.SqlClient.SqlParameter ( "@fs", SqlDbType.Image);    myParameter.Value = http://www.mamicode.com/fs ;"strSQL"></param>  /// <returns></returns>  public static int GetCount(string strSQL)  {   using (SqlConnection connection = new SqlConnection(connectionString))   {    SqlCommand cmd = new SqlCommand(strSQL,connection);        try    {     connection.Open();     SqlDataReader result = cmd.ExecuteReader();     int i=0;     while(result.Read())     {      i=result.GetInt32(0);     }     result.Close();         return i;    }    catch(System.Data.SqlClient.SqlException e)    {             throw new Exception(e.Message);    }     finally    {     cmd.Dispose();     connection.Close();    }   }  }   /// <summary>  /// 执行一条计算查询结果语句,返回查询结果(object)。  /// </summary>  /// <param name="SQLString"></param>  /// <returns></returns>  public static object GetSingle(string SQLString)  {   using (SqlConnection connection = new SqlConnection(connectionString))   {    SqlCommand cmd = new SqlCommand(SQLString,connection);      try    {     connection.Open();     object obj = cmd.ExecuteScalar();     if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value)))     {           return null;     }     else     {      return obj;     }        }    catch(System.Data.SqlClient.SqlException e)    {         throw new Exception(e.Message);    }    finally    {     cmd.Dispose();     connection.Close();    }   }  }  /// <summary>  /// 执行查询语句,返回SqlDataReader  /// </summary>  /// <param name="strSQL"></param>  /// <returns></returns>  public static SqlDataReader ExecuteReader(string strSQL)  {   using (SqlConnection connection = new SqlConnection(connectionString))   {    SqlCommand cmd = new SqlCommand(strSQL,connection);     SqlDataReader myReader;       try    {     connection.Open();      myReader = cmd.ExecuteReader();     return myReader;    }    catch(System.Data.SqlClient.SqlException e)    {             throw new Exception(e.Message);    }      finally    {     cmd.Dispose();     connection.Close();    }    }  }    /// <summary>  /// 执行查询语句,返回DataSet  /// </summary>  /// <param name="SQLString"></param>  /// <returns></returns>  public static DataSet Query(string SQLString)  {   using (SqlConnection connection = new SqlConnection(connectionString))   {    DataSet ds = new DataSet();    try    {     connection.Open();     SqlDataAdapter command = new SqlDataAdapter(SQLString,connection);         command.Fill(ds,"ds");    }    catch(System.Data.SqlClient.SqlException ex)    {         throw new Exception(ex.Message);    }       return ds;   }     }  #region 存储过程操作  /// <summary>  /// 运行存储过程  /// </summary>  /// <param name="storedProcName"></param>  /// <param name="parameters"></param>  /// <returns></returns>  public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters )  {   using (SqlConnection connection = new SqlConnection(connectionString))   {    SqlDataReader returnReader;    connection.Open();    SqlCommand command = BuildQueryCommand( connection,storedProcName, parameters );    command.CommandType = CommandType.StoredProcedure;    returnReader = command.ExecuteReader();    //Connection.Close();    return returnReader;   }  }  private static SqlCommand BuildQueryCommand(SqlConnection connection,string storedProcName, IDataParameter[] parameters)  {       SqlCommand command = new SqlCommand( storedProcName, connection );    command.CommandType = CommandType.StoredProcedure;    foreach (SqlParameter parameter in parameters)    {     command.Parameters.Add( parameter );    }    return command;     }    public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName )  {   using (SqlConnection connection = new SqlConnection(connectionString))   {    DataSet dataSet = new DataSet();    connection.Open();    SqlDataAdapter sqlDA = new SqlDataAdapter();    sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters );    sqlDA.Fill( dataSet, tableName );    connection.Close();    return dataSet;   }  }  #endregion  }}--------------------------------------------------------------------------------------------------------------------------------//这是我更新的一个版本,与上面可以说变化相当大//其中FlashGateway是Flash Remoting的一个dll大家可以去掉相关代码,不影响使用using System;using System.Collections.Generic;using System.Text;using System.Collections;using System.Collections.Specialized;using System.Data;using System.Data.SqlClient;using System.Drawing;using mvi.sysmanage;using FlashGateway.IO;namespace mvi.dbaccess{    #region    class sqlcom    /// <summary>    /// sqlcom    /// </summary>     public class sqlCom    {        //FLASHSRV/HIPIHI        //private string DBCnStr = @"Data Source=FLASHSRV;Initial Catalog=hipihi;Persist Security Info=True;User ID=sa;password=1";       private string DBCnStr = @"Data Source=mvi-dpe;Initial Catalog=hipihi;Persist Security Info=True;User ID=sa;password=mvi";        public sqlCom()        {            //DBCnStr = @"Data Source=FLASHSRV;Initial Catalog=hipihi;Persist Security Info=True;User ID=sa;password=1";            DBCnStr = @"Data Source=mvi-dpe;Initial Catalog=hipihi;Persist Security Info=True;User ID=sa;password=mvi";        }        public sqlCom(string connectstring)        {            if (connectstring.Length > 1)            {                DBCnStr = connectstring;            }        }        public string SQLCnStr        {            get            {                return DBCnStr;            }            set            {                DBCnStr = value;            }        }        #region InitSqlConnection    初始化Sql连接字符串        /// <summary>        /// 初始化Sql连接字符串        /// </summary>        /// <param name="DBCnStr">传入的dbconnection</param>        /// <param name="cmdText">传入的cmd text</param>        /// <returns>sql server connection string</returns>        private string InitSqlConnection(string DBCnStr, string cmdText)        {            // temp code            return DBCnStr;            //// final code            //int iD = DBCnStr.IndexOf("Data Source=");            //int iL = DBCnStr.Substring(iD + 12).Split(‘;‘)[0].Length;            //string strSqlServerName = DBCnStr.Substring(iD + 12, iL);            //string strNewSqlServerName = GetSqlServerName(cmdText);            //return DBCnStr.Replace(strSqlServerName, strNewSqlServerName);        }        #endregion        #region GetSqlServerName    由sql string 获取数据库服务器名        /// <summary>        /// 由sql string 获取sql server name        /// </summary>        /// <param name="cmdText">传入的cmd text</param>        /// <returns>sql server name</returns>        private string GetSqlServerName(string cmdText)        {            return cmdText.Substring(cmdText.IndexOf("from") + 5).Split(‘.‘)[0].ToString();        }        #endregion        # region GetDataSet   通过执行SQL语句返回一个状态        /// <summary>        /// 通过执行SQL语句返回一个状态        /// </summary>        /// <param name="cmdText">“SQL 文本”</param>        /// <param name="oCn">"连接对象"</param>        /// <param name="oDs">"引用的DataSet它将在程序中改变内容"</param>        /// <returns>"成功则返回0,否则返回错误代码"</returns>        public int GetDataSet(string cmdText, ref DataSet oDs)        {            DBCnStr = this.InitSqlConnection(DBCnStr, cmdText);            SqlConnection oCn = new SqlConnection(DBCnStr);            try            {                oCn.Open();            }            catch (Exception oErr)            {                //WriteFile(oErr.Message);                Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);                return -1;            }            //定义数据适配对象            SqlDataAdapter oleDataAdapter = new SqlDataAdapter(cmdText, oCn);            int status = -1;            try            {                //填充DataSet                oleDataAdapter.Fill(oDs);                status = 0;            }            catch (Exception oErr)            {                Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);                status = -1;            }            finally            {                oleDataAdapter = null;                if (oCn.State == System.Data.ConnectionState.Open)                {                    oCn.Close();                }            }            return status;        }        #endregion        # region GetDataTable   执行SQL语句并返回一个表        /// <summary>        /// 执行SQL语句并返回一个表        /// </summary>        /// <param name="cmdText">SQL文本</param>        /// <param name="DBCnStr">dbconnect</param>        /// <param name="inDt">返回表</param>        /// <returns>成功则返回0,否则返回错误代码</returns>        public int GetDataTable(string cmdText, ref DataTable inDt)        {            DBCnStr = this.InitSqlConnection(DBCnStr, cmdText);            SqlConnection oCn = new SqlConnection(DBCnStr);            try            {                oCn.Open();            }            catch (Exception oErr)            {                //WriteFile(oErr.Message);                Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);                return -1;            }            //建立数据适配对象            SqlDataAdapter oleDataAdapter = new SqlDataAdapter(cmdText, oCn);            int status = -1;            try            {                //填充数据表                oleDataAdapter.Fill(inDt);                status = 0;            }            catch (Exception oErr)            {                //异常处理                //WriteFile(oErr.Message);                Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);                status = -1;            }            finally            {                oleDataAdapter = null;                if (oCn.State == System.Data.ConnectionState.Open)                {                    oCn.Close();                }            }            return status;        }        #endregion        # region GetDataTable   执行SQL语句并返回一个表        /// <summary>        /// 执行SQL语句并返回一个表        /// </summary>        /// <param name="cmdText">SQL文本</param>        /// <param name="DBCnStr">dbconnect</param>        /// <param name="inDt">返回表</param>        /// <returns>成功则返回0,否则返回错误代码</returns>        public int GetCount(string cmdText)        {            DBCnStr = this.InitSqlConnection(DBCnStr, cmdText);            SqlConnection oCn = new SqlConnection(DBCnStr);            try            {                oCn.Open();            }            catch (Exception oErr)            {                //WriteFile(oErr.Message);                Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);                return -1;            }            //建立数据适配对象            SqlDataAdapter oleDataAdapter = new SqlDataAdapter(cmdText, oCn);            DataTable inDt = new DataTable();            int status = -1;            try            {                //填充数据表                oleDataAdapter.Fill(inDt);                status = inDt.Rows.Count;            }            catch (Exception oErr)            {                Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);                status = -1;            }            finally            {                oleDataAdapter = null;                if (oCn.State == System.Data.ConnectionState.Open)                {                    oCn.Close();                }            }            return status;        }        #endregion        # region    //  GetNVColl   执行SQL语句并返回NameValueCollection        ///// <summary>        ///// 执行SQL语句并返回NameValueCollection        ///// </summary>        ///// <param name="cmdText">SQL文本</param>        ///// <param name="NameValueCollection">nvColl</param>        ///// <returns>成功则返回0,否则返回错误代码</returns>        //public int GetNVColl(string cmdText, ref NameValueCollection nvColl)        //{        //    DBCnStr = this.InitSqlConnection(DBCnStr, cmdText);        //    SqlConnection oCn = new SqlConnection(DBCnStr);        //    try        //    {        //        oCn.Open();        //    }        //    catch (Exception oErr)        //    {        //        //WriteFile(oErr.Message);        //        Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);        //        return -1;        //    }        //    //建立数据读取对象        //    SqlCommand oleCommand = new SqlCommand(cmdText, oCn);        //    //填充SqlDataReader        //    SqlDataReader oleReader;        //    int status = -1;        //    try        //    {        //        oleReader = oleCommand.ExecuteReader();        //        // Always call Read before accessing data.        //        while (oleReader.Read())        //        {        //            for (int i = 0; i < oleReader.FieldCount; i++)        //            {        //                if (oleReader.GetValue(i).ToString() != "")        //                    nvColl.Add(oleReader.GetName(i), oleReader.GetString(i));        //            }        //        }        //        status = 0;        //    }        //    catch (Exception oErr)        //    {        //        //异常处理        //        //WriteFile(oErr.Message);        //        Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);        //        status = -1;        //    }        //    finally        //    {        //        oleReader = null;        //        if (oCn.State == System.Data.ConnectionState.Open)        //        {        //            oCn.Close();        //        }        //    }        //    return status;        //}        #endregion        //        #region GetArrayList     执行SQL语句并返回一个ArrayList        ///// <summary>        ///// 执行SQL语句并返回一个ArrayList        ///// </summary>        ///// <param name="cmdText">SQL文本</param>        ///// <returns>返回ArrayList arraylist[i]为name,arraylist[i+1]为value</returns>        //public ArrayList GetArrayList(string cmdText, ref ArrayList aName, ref ArrayList aValue)        //{        //    ArrayList aNameValue = http://www.mamicode.com/new ArrayList();"")        //                    aName.Add(oleReader.GetName(i));        //                aValue.Add(oleReader.GetString(i + 1));        //            }        //        }        //        status = aValue;        //    }        //    catch (Exception oErr)        //    {        //        //异常处理        //        //WriteFile(oErr.Message);        //        Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);        //        status = null;        //    }        //    finally        //    {        //        oleReader = null;        //        if (oCn.State == System.Data.ConnectionState.Open)        //        {        //            oCn.Close();        //        }        //    }        //    return status;        //}        #endregion        //        #region   GetArrayList   执行SQL语句并返回一个ArrayList        /// <summary>        /// 执行SQL语句并返回一个ArrayList        /// </summary>        /// <param name="cmdText">SQL文本</param>        /// <returns>返回ArrayList arraylist[i]为name,arraylist[i+1]为value</returns>        public int GetArrayList(string cmdText, ref ArrayList aNameValue)        {            DBCnStr = this.InitSqlConnection(DBCnStr, cmdText);            SqlConnection oCn = new SqlConnection(DBCnStr);            try            {                oCn.Open();            }            catch (Exception oErr)            {                //WriteFile(oErr.Message);                Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);                return -1;            }            //建立数据读取对象            SqlCommand oleCommand = new SqlCommand(cmdText, oCn);            //填充SqlDataReader            SqlDataReader oleReader;            int status = -1;            try            {                oleReader = oleCommand.ExecuteReader();                // Always call Read before accessing data.                while (oleReader.Read())                {                    for (int i = 0; i < oleReader.FieldCount - 1; i ++ )                    {                        if (oleReader.GetValue(i).ToString() != "")                            aNameValue.Add(oleReader.GetName(i));                    }                }                status = 1;            }            catch (Exception oErr)            {                //异常处理                //WriteFile(oErr.Message);                Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);                status = -1;            }            finally            {                oleReader = null;                if (oCn.State == System.Data.ConnectionState.Open)                {                    oCn.Close();                }            }            return status;        }        #endregion        #region   GetASObject   执行SQL语句并返回一个包含多条数据的ASObject (name,value)        /// <summary>        /// 执行SQL语句,查询两个字段,并返回一个ASObject        /// </summary>        /// <param name="cmdText">SQL文本</param>        /// <param name="asO">ASObject 对象</param>        /// <returns>返回int  ASObject[i]为(name,value)</returns>        public int GetASObjectMulti(string cmdText, ref ASObject asO)        {            DBCnStr = this.InitSqlConnection(DBCnStr, cmdText);            SqlConnection oCn = new SqlConnection(DBCnStr);            try            {                oCn.Open();            }            catch (Exception oErr)            {                //WriteFile(oErr.Message);                Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);                return -1;            }            //建立数据读取对象            SqlCommand oleCommand = new SqlCommand(cmdText, oCn);            //填充SqlDataReader            SqlDataReader oleReader;            int status = -1;            try            {                int i = 1;                oleReader = oleCommand.ExecuteReader();                // Always call Read before accessing data.                while (oleReader.Read())                {                    for (int j = 0; j < oleReader.FieldCount; j++)                    {                        asO.Add(i+"@"+oleReader.GetName(j),oleReader.GetValue(j));//i@+"name",i为第几条数据的序号                    }                    i++;                }                status = 1;            }            catch (Exception oErr)            {                //异常处理                //WriteFile(oErr.Message);                Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);                status = -1;            }            finally            {                oleReader = null;                if (oCn.State == System.Data.ConnectionState.Open)                {                    oCn.Close();                }            }            return status;        }        #endregion        #region   GetASObjectSingle   执行SQL语句并返回一个包含单条数据的ASObject (name,value)        /// <summary>        /// 执行SQL语句查询一条数据(必须返回一条数据),返回一个ASObject        /// </summary>        /// <param name="cmdText">SQL文本</param>        /// <param name="asO">ASObject 对象</param>        /// <returns>返回int  ASObject[i]为(name,value)</returns>        public int GetASObjectSingle(string cmdText, ref ASObject asO)        {            DBCnStr = this.InitSqlConnection(DBCnStr, cmdText);            SqlConnection oCn = new SqlConnection(DBCnStr);            try            {                oCn.Open();            }            catch (Exception oErr)            {                //WriteFile(oErr.Message);                Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);                return -1;            }            //建立数据读取对象            SqlCommand oleCommand = new SqlCommand(cmdText, oCn);            //填充SqlDataReader            SqlDataReader oleReader;            int status = -1;            try            {                //oleReader = oleCommand.ExecuteScalar(); // modified by apenni 2006-5-6                oleReader = oleCommand.ExecuteReader();                // Always call Read before accessing data.                while (oleReader.Read())                {                    for (int i = 0; i < oleReader.FieldCount; i++)                    {                        asO.Add(oleReader.GetName(i), oleReader.GetValue(i));                    }                }                status = 1;            }            catch (Exception oErr)            {                //异常处理                //WriteFile(oErr.Message);                Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);                status = -1;            }            finally            {                oleReader = null;                if (oCn.State == System.Data.ConnectionState.Open)                {                    oCn.Close();                }            }            return status;        }        #endregion        #region   ExecuteSql    执行SET,DELETE语句时返回影响的行数        /// <summary>        /// 执行SET,DELETE语句时返回影响的行数        /// </summary>        /// <param name="cmdText">“SQL文本”</param>        /// <returns>“返回影响的行数,否则返回错误代码”</returns>        public int ExecuteSql(string cmdText)        {            int intReturn = -1;//返回影响的行数。            SqlCommand oCmd = new SqlCommand();            DBCnStr = this.InitSqlConnection(DBCnStr, cmdText);            SqlConnection oCn = new SqlConnection(DBCnStr);            try            {                oCn.Open();            }            catch (Exception oErr)            {                //WriteFile(oErr.Message);                Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);                return -1;            }            oCmd.Connection = oCn;            oCmd.CommandType = CommandType.Text;            oCmd.CommandText = cmdText;            //定义事务 设定隔离级别            SqlTransaction oTx = oCn.BeginTransaction(IsolationLevel.ReadCommitted);            oCmd.Transaction = oTx;            //处理SQL语句            #region   事务处理            try            {                //支持事务                intReturn = oCmd.ExecuteNonQuery();                oTx.Commit();            }            catch (Exception oErr)            {                //WriteFile(oErr.Message);                Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);                oTx.Rollback();                intReturn = -1;            }            finally            {                oCmd = null;                oTx = null;                if (oCn.State == System.Data.ConnectionState.Open)                {                    oCn.Close();                }            }            #endregion            return intReturn;        }        #endregion        #region   ExecuteSql    执行SET,DELETE语句时返回影响的行数        /// <summary>        /// 执行SET,DELETE语句时返回影响的行数        /// </summary>        /// <param name="cmdText">“SQL文本,支持多sql语句通过‘;‘拼接”</param>        /// <returns>“返回影响的行数,否则返回错误代码”</returns>        public int ExecuteSql(params string[] cmdText)        {            string strSql = string.Empty;            foreach (string strCmd in cmdText)            {                strSql += strCmd;            }            return ExecuteSql(strSql);        }        //added by apenni 2006-5-6        #endregion        #region   CallStoreProc   调用系统存储过程返回一个整数        /// <summary>        /// 调用系统存储过程返回一个整数        /// </summary>        /// <param name = "strSysSPName">“存储过程枚举类型”</param>        /// <param name="InParaName">"in参数名字"</param>        /// <param name = "ParamValue">“参数列表”</param>        /// <param name="OutParaName">"out参数名字"</param>        /// <param name="OutParaValue">"返回的参数值"</param>        /// <param name="IType">"out参数的类型"</param>        /// <returns>"成功则返回所影响的行数,否则返回-1"</returns>        public int CallStoreProc(string strSysSPName, IList InParaName, IList InParamValue, IList OutParaName, ref object[] OutParaValue, DBTYPE[] IType)        {            int inReturn = -1;            SqlCommand oCmd = new SqlCommand();            oCmd.CommandText = strSysSPName;            oCmd.CommandType = CommandType.StoredProcedure;            #region in参数的建立            if (InParamValue != null && InParaName != null)            {                //建立in参数                for (int i = 0; i < InParamValue.Count; i++)                {                    SqlParameter oPara = new SqlParameter();                    //调用SQLParamHelper的CreateParameterWithValue()方法来生成不同的参数                    if (InParaName[i] != null)                    {                        oPara = SQLParamHelper.CreateParameterWithValue(InParaName[i].ToString(), InParamValue[i]);                        oPara.Direction = ParameterDirection.Input;                        oCmd.Parameters.Add(oPara);                    }                }            }            #endregion            #region out参数的建立            if (OutParaName != null && OutParaValue != null && IType != null)            {                //建立in参数                for (int i = 0; i < OutParaName.Count; i++)                {                    SqlParameter oPara = new SqlParameter();                    //调用SQLParamHelper的CreateParameterWithValue()方法来生成不同的参数                    if (OutParaName[i] != null)                    {                        oPara = SQLParamHelper.CreateOutParameterWithValue(OutParaName[i].ToString(), IType[i].ToString());                        oPara.Direction = ParameterDirection.Output;                        oCmd.Parameters.Add(oPara);                    }                }            }            #endregion            SqlConnection oCn = new SqlConnection(DBCnStr);            try            {                oCn.Open();            }            catch (Exception oErr)            {                //WriteFile(oErr.Message);                Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);                return -1;            }            oCmd.Connection = oCn;            //连接数据库和执行存储过程            try            {                inReturn = oCmd.ExecuteNonQuery();                for (int i = 0; i < OutParaValue.Length; i++)                {                    OutParaValue[i] = oCmd.Parameters[OutParaName[i].ToString()].Value;                }            }            catch (Exception oErr)            {                //WriteFile(oErr.Message);                Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);                inReturn = -1;            }            finally            {                if (oCn.State == System.Data.ConnectionState.Open)                {                    oCn.Close();                }                oCmd = null;            }            return inReturn;        }        #endregion        #region   CallStoreProc   调用系统存储过程并影响生成一个object对象值        /// <summary>        /// 调用系统存储过程并影响生成一个object对象值        /// </summary>        /// <param name = "strSysSPName">“存储过程枚举类型”</param>        /// <param name="InParaName">"in参数名字"</param>        /// <param name = "InParamValue">“in参数列表”</param>        /// <param name="OutParaName">"out参数名字"</param>        /// <param name="OutParaValue">"out参数值"</param>        /// <param name="IType">"out参数的类型"</param>        /// <param name="inObject">"引用的值"</param>        /// <returns>成功则返回1,否则返回-1或错误代码</returns>        public int CallStoreProc(string strSysSPName, IList InParaName, IList InParamValue, IList OutParaName, ref object[] OutParaValue, DBTYPE[] IType, ref object objReturn)        {            //建立Command对象            SqlCommand oCmd = new SqlCommand();            oCmd.CommandText = strSysSPName.ToString();            oCmd.CommandType = CommandType.StoredProcedure;            int status = -1;            #region in参数的建立            if (InParamValue != null && InParaName != null)            {                //建立in参数                for (int i = 0; i < InParamValue.Count; i++)                {                    SqlParameter oPara = new SqlParameter();                    //调用SQLParamHelper的CreateParameterWithValue()方法来生成不同的参数                    if (InParaName[i] != null)                    {                        oPara = SQLParamHelper.CreateParameterWithValue(InParaName[i].ToString(), InParamValue[i]);                        oPara.Direction = ParameterDirection.Input;                        oCmd.Parameters.Add(oPara);                    }                }            }            #endregion            #region out参数的建立            if (OutParaName != null && OutParaValue != null && IType != null)            {                //建立in参数                for (int i = 0; i < OutParaName.Count; i++)                {                    SqlParameter oPara = new SqlParameter();                    //调用SQLParamHelper的CreateParameterWithValue()方法来生成不同的参数                    if (OutParaName[i] != null)                    {                        oPara = SQLParamHelper.CreateOutParameterWithValue(OutParaName[i].ToString(), IType[i].ToString());                        oPara.Direction = ParameterDirection.Output;                        oCmd.Parameters.Add(oPara);                    }                }            }            #endregion            SqlConnection oCn = new SqlConnection(DBCnStr);            try            {                oCn.Open();            }            catch (Exception oErr)            {                //WriteFile(oErr.Message);                Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);                return -1;            }            oCmd.Connection = oCn;            //连接数据库和执行存储过程            try            {                //通过SqlDataAdapter来填充Table                objReturn = oCmd.ExecuteScalar();                #region 取得返回参数的值                for (int i = 0; i < OutParaValue.Length; i++)                {                    OutParaValue[i] = oCmd.Parameters[OutParaName[i].ToString()].Value;                }                #endregion                status = 0;            }            catch (Exception oErr)            {                //WriteFile(oErr.Message);                Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);                status = -1;            }            finally            {                if (oCn.State == System.Data.ConnectionState.Open)                {                    oCn.Close();                }                oCmd = null;            }            return status;        }        #endregion               #region    CallStoreProc    调用用户存储过程返回一个DataTable(Select 语句)        /// <summary>        /// 调用用户存储过程返回一个DataTable(Select 语句)        /// </summary>        /// <param name = "strSPName">“存储过程名”</param>        /// <param name="InParaName">"in参数名字"</param>        /// <param name = "InParamValue">“in参数列表”</param>        /// <param name="OutParaName">"out参数名字"</param>        /// <param name="IType">"out参数的类型"</param>        /// <param name="OutParaValue">"out参数值"</param>        /// <param name="oDT">"传入的DataTable引用"</param>        ///<returns>"成功则返回1,否则返回-1或错误代码"</returns>        public int CallStoreProc(string strSPName, IList InParaName, IList InParamValue, IList OutParaName, ref object[] OutParaValue, DBTYPE[] IType, ref DataTable oDT)        {            //建立Command对象            SqlCommand oCmd = new SqlCommand();            oCmd.CommandText = strSPName.ToString();            oCmd.CommandType = CommandType.StoredProcedure;            int status = -1;            #region in参数的建立            if (InParamValue != null && InParaName != null)            {                //建立in参数                for (int i = 0; i < InParamValue.Count; i++)                {                    SqlParameter oPara = new SqlParameter();                    //调用SQLParamHelper的CreateParameterWithValue()方法来生成不同的参数                    if (InParaName[i] != null)                    {                        oPara = SQLParamHelper.CreateParameterWithValue(InParaName[i].ToString(), InParamValue[i]);                        oPara.Direction = ParameterDirection.Input;                        oCmd.Parameters.Add(oPara);                    }                }            }            #endregion            #region out参数的建立            if (OutParaName != null && OutParaValue != null && IType != null)            {                //建立in参数                for (int i = 0; i < OutParaName.Count; i++)                {                    SqlParameter oPara = new SqlParameter();                    //调用SQLParamHelper的CreateParameterWithValue()方法来生成不同的参数                    if (OutParaName[i] != null)                    {                        oPara = SQLParamHelper.CreateOutParameterWithValue(OutParaName[i].ToString(), IType[i].ToString());                        oPara.Direction = ParameterDirection.Output;                        oCmd.Parameters.Add(oPara);                    }                }            }            #endregion            SqlConnection oCn = new SqlConnection(DBCnStr);            try            {                oCn.Open();            }            catch (Exception oErr)            {                //WriteFile(oErr.Message);                Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);                return -1;            }            oCmd.Connection = oCn;            //连接数据库和执行存储过程            try            {                //通过SqlDataAdapter来填充Table                SqlDataAdapter oDp = new SqlDataAdapter(oCmd.CommandText.ToString(), oCn);                //建立SqlDataAdapter与SqlCommand的连接                oDp.SelectCommand = oCmd;                oDp.DeleteCommand = oCmd;                oDp.UpdateCommand = oCmd;                oDp.DeleteCommand = oCmd;                //填充DataTable                oDp.Fill(oDT);                #region 取得返回参数的值                for (int i = 0; i < OutParaValue.Length; i++)                {                    OutParaValue[i] = oCmd.Parameters[OutParaName[i].ToString()].Value;                }                #endregion                status = 0;            }            catch (Exception oErr)            {                //WriteFile(oErr.Message);                Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);                status = -1;            }            finally            {                if (oCn.State == System.Data.ConnectionState.Open)                {                    oCn.Close();                }                oCmd = null;            }            return status;        }        #endregion        #region   CallStoreProc    调用系统存储过程并影响生成一个DataSet对象        /// <summary>        /// 调用系统存储过程并影响生成一个DataSet对象        /// </summary>        /// <param name = "strSysSPName">“存储过程枚举类型”</param>        /// <param name="InParaName">"in参数名字"</param>        /// <param name = "InParamValue">“in参数列表”</param>        /// <param name="OutParaName">"out参数名字"</param>        /// <param name="OutParaValue">"out参数值"</param>        /// <param name="IType">"out参数的类型"</param>        /// <param name=" oDs">"引用的DataSet"</param>        /// <returns>成功则返回1,否则返回-1或错误代码</returns>        public int CallStoreProc(string strSysSPName, IList InParaName, IList InParamValue, IList OutParaName, ref object[] OutParaValue, DBTYPE[] IType, ref DataSet oDs)        {            SqlCommand oCmd = new SqlCommand();            oCmd.CommandText = strSysSPName;            oCmd.CommandType = CommandType.StoredProcedure;            int status = -1;            #region in参数的建立            if (InParamValue != null && InParaName != null)            {                //建立in参数                for (int i = 0; i < InParamValue.Count; i++)                {                    SqlParameter oPara = new SqlParameter();                    //调用SQLParamHelper的CreateParameterWithValue()方法来生成不同的参数                    if (InParaName[i] != null)                    {                        oPara = SQLParamHelper.CreateParameterWithValue(InParaName[i].ToString(), InParamValue[i]);                        oPara.Direction = ParameterDirection.Input;                        oCmd.Parameters.Add(oPara);                    }                }            }            #endregion            #region out参数的建立            if (OutParaName != null && OutParaValue != null && IType != null)            {                //建立in参数                for (int i = 0; i < OutParaName.Count; i++)                {                    SqlParameter oPara = new SqlParameter();                    //调用SQLParamHelper的CreateParameterWithValue()方法来生成不同的参数                    if (OutParaName[i] != null)                    {                        oPara = SQLParamHelper.CreateOutParameterWithValue(OutParaName[i].ToString(), IType[i].ToString());                        oPara.Direction = ParameterDirection.Output;                        oCmd.Parameters.Add(oPara);                    }                }            }            #endregion            SqlConnection oCn = new SqlConnection(DBCnStr);            try            {                oCn.Open();            }            catch (Exception oErr)            {                //WriteFile(oErr.Message);                Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);                return -1;            }            oCmd.Connection = oCn;            //连接数据库和执行存储过程            try            {                //通过SqlDataAdapter来填充Table                SqlDataAdapter oDp = new SqlDataAdapter(oCmd.CommandText.ToString(), oCn);                //建立SqlDataAdapter与SqlCommand的连接                oDp.SelectCommand = oCmd;                oDp.DeleteCommand = oCmd;                oDp.UpdateCommand = oCmd;                oDp.DeleteCommand = oCmd;                //                oDp.Fill(oDs);                #region 取得返回参数的值                for (int i = 0; i < OutParaValue.Length; i++)                {                    OutParaValue[i] = oCmd.Parameters[OutParaName[i].ToString()].Value;                }                #endregion                status = 0;            }            catch (Exception oErr)            {                // WriteFile(oErr.Message);                Errlog.AppLog(oErr.Message, ErrorType.MviDataBase);                status = -1;            }            finally            {                if (oCn.State == System.Data.ConnectionState.Open)                {                    oCn.Close();                }                oCmd = null;            }            return status;        }        #endregion        #region     GetSqlWhere  产生SQL语句        /// <summary>        /// 产生SQL语句        /// </summary>        /// <param name="InName">表字段名</param>        /// <param name="InValue">表字段值</param>        /// <returns>结果SQL语句</returns>        public string GetSqlWhere(ArrayList InName, ArrayList InValue)        {            DataTable DataTableTmp = new DataTable();            string StrSqlWhereTmp = "";            string StrTmp = "";            string StrName = "";            string StrValuehttp://www.mamicode.com/= "";            if (InName == null || InValue =http://www.mamicode.com/= null)"#S") //开始时间                {                    StrName = StrTmp.Substring(2) + " >= ";                    StrValuehttp://www.mamicode.com/= "to_date(‘" + InValue[i].ToString() + "‘,‘yyyy-mm-dd HH24:Mi:ss‘)";                }                else if (StrTmp.Substring(0, 2) == "#E")//结束时间                {                    StrName = StrTmp.Substring(2) + " < ";                    StrValuehttp://www.mamicode.com/= "to_date(‘" + InValue[i].ToString() + "‘,‘yyyy-mm-dd HH24:Mi:ss‘)";                }                else if (StrTmp.Substring(0, 2) == "#N")//<>条件                {                    StrName = StrTmp.Substring(2) + " <> ";                    StrValue = http://www.mamicode.com/InValue[i].ToString();"#D")//大于条件                {                    StrName = StrTmp.Substring(2) + ">";                    StrValue = http://www.mamicode.com/InValue[i].ToString();"#X")//小于条件                {                    StrName = StrTmp.Substring(2) + "<";                    StrValue = http://www.mamicode.com/InValue[i].ToString();"#I")//IN条件                {                    StrName = StrTmp.Substring(2) + " IN (";                    StrValue = http://www.mamicode.com/InValue[i].ToString() +")";                }                else if (StrTmp.Substring(0, 2) == "#0")//没有条件                {                    return InValue[i].ToString();                }                else        //等于条件                {                    StrName = StrTmp + "=";                    StrValue = http://www.mamicode.com/InValue[i].ToString();" and ";            }            StrSqlWhereTmp = StrSqlWhereTmp.Substring(0, StrSqlWhereTmp.Length - 5);            return StrSqlWhereTmp;        }        #endregion    }    #endregion       #region    class SQLParamHelper    /// <summary>    /// SQLParamHelper    /// </summary>    internal class SQLParamHelper    {        #region 创建出入参数        /// <summary>        /// 根据输入的OBJECT对象生成不同的参数        /// </summary>        /// <param name="name">“参数名字”</param>        /// <param name="nValue">“参数值”</param>        /// <returns></returns>        public static SqlParameter CreateParameterWithValue(string name, object nValue)        {            string strType;            SqlParameter param;            int intLenth = 0;            if (nValue != null)            {                strType = nValue.GetType().ToString();                intLenth = nValue.ToString().Trim().Length;                if (intLenth > 0)                {                    switch (strType)                    {                        case "System.Int32":                            {                                param = new SqlParameter(name, SqlDbType.BigInt, intLenth);                                param.Direction = ParameterDirection.Input;                                param.Value = http://www.mamicode.com/nValue;"System.Double":                            {                                param = new SqlParameter(name, SqlDbType.Decimal);                                param.Direction = ParameterDirection.Input;                                param.Value = http://www.mamicode.com/nValue;"System.Single":                            {                                param = new SqlParameter(name, SqlDbType.Float);                                param.Direction = ParameterDirection.Input;                                param.Value = http://www.mamicode.com/nValue;"System.UInt64":                            {                                param = new SqlParameter(name, SqlDbType.BigInt, intLenth);                                param.Direction = ParameterDirection.Input;                                param.Value = http://www.mamicode.com/nValue;"System.Int64":                            {                                param = new SqlParameter(name, SqlDbType.BigInt, intLenth);                                param.Direction = ParameterDirection.Input;                                param.Value = http://www.mamicode.com/nValue;"System.Decimal":                            {                                param = new SqlParameter(name, SqlDbType.Decimal, intLenth);                                param.Direction = ParameterDirection.Input;                                param.Value = http://www.mamicode.com/nValue;"System.Object":                            {                                param = new SqlParameter(name, SqlDbType.Real, intLenth);                                param.Direction = ParameterDirection.Input;                                param.Value = http://www.mamicode.com/nValue;"System.UInt16":                            {                                param = new SqlParameter(name, SqlDbType.BigInt, intLenth);                                param.Direction = ParameterDirection.Input;                                param.Value = http://www.mamicode.com/nValue;"System.Int16":                            {                                param = new SqlParameter(name, SqlDbType.BigInt, intLenth);                                param.Direction = ParameterDirection.Input;                                param.Value = http://www.mamicode.com/nValue;"System.Boolean":                            {                                param = new SqlParameter(name, SqlDbType.Binary);                                param.Direction = ParameterDirection.Input;                                bool bolTemp = (bool)nValue;                                param.Value = http://www.mamicode.com/(bolTemp == true ? 1 : 0);"System.String":                            {                                param = new SqlParameter(name, SqlDbType.VarChar, intLenth);                                param.Direction = ParameterDirection.Input;                                param.Value = http://www.mamicode.com/nValue;"System.DateTime":                            {                                param = new SqlParameter(name, SqlDbType.DateTime, intLenth);                                param.Direction = ParameterDirection.Input;                                param.Value = http://www.mamicode.com/nValue;"System.Char":                            {                                param = new SqlParameter(name, SqlDbType.Char, intLenth);                                param.Direction = ParameterDirection.Input;                                param.Value = http://www.mamicode.com/nValue;"System.SByte":                            {                                param = new SqlParameter(name, SqlDbType.Bit, intLenth);                                param.Direction = ParameterDirection.Input;                                param.Value = http://www.mamicode.com/nValue;"System_XMl":                            {                                param = new SqlParameter(name, SqlDbType.Xml, 300);                                param.Direction = ParameterDirection.Input;                                param.Value = http://www.mamicode.com/nValue;"System.Text":                            {                                if (intLenth < 2000)                                {                                    param = new SqlParameter(name, SqlDbType.Text, intLenth);                                }                                else                                {                                    param = new SqlParameter(name, SqlDbType.Text);                                }                                param.Direction = ParameterDirection.Input;                                param.Value = nValue;                                break;                            }                        default:                            {                                param = new SqlParameter(name, SqlDbType.Variant);                                param.Direction = ParameterDirection.Input;                                param.Value = nValue;                                break;                            }                    }                    param.Direction = ParameterDirection.Input;                }                else                {                    param = new SqlParameter(name, SqlDbType.VarChar, 10);                    param.Direction = ParameterDirection.Input;                    param.Valuehttp://www.mamicode.com/= "";                }            }            else            {                param = new SqlParameter(name, SqlDbType.Variant);                param.Direction = ParameterDirection.Input;                param.Value = http://www.mamicode.com/null;"name">"参数名"</param>        /// <param name="objType">"参数类型"</param>        /// <returns></returns>        public static SqlParameter CreateOutParameterWithValue(string name, string objType)        {            string strType = objType;            SqlParameter param;            switch (strType)            {                case "System_Object":                    {                        param = new SqlParameter(name, SqlDbType.Variant);                        param.Direction = ParameterDirection.Output;                        break;                    }                case "System_Single":                    {                        param = new SqlParameter(name, SqlDbType.Float);                        param.Direction = ParameterDirection.Output;                        break;                    }                case "System_UInt64":                    {                        param = new SqlParameter(name, SqlDbType.BigInt);                        param.Direction = ParameterDirection.Output;                        break;                    }                case "System_Int64":                    {                        param = new SqlParameter(name, SqlDbType.BigInt);                        param.Direction = ParameterDirection.Output;                        break;                    }                case "System_Int32":                    {                        param = new SqlParameter(name, SqlDbType.Int);                        param.Direction = ParameterDirection.Output;                        break;                    }                case "System_UInt16":                    {                        param = new SqlParameter(name, SqlDbType.SmallInt);                        param.Direction = ParameterDirection.Output;                        break;                    }                case "System_Int16":                    {                        param = new SqlParameter(name, SqlDbType.SmallInt);                        param.Direction = ParameterDirection.Output;                        break;                    }                case "System_Double":                    {                        param = new SqlParameter(name, SqlDbType.Float);                        param.Direction = ParameterDirection.Output;                        break;                    }                case "System_Decimal":                    {                        param = new SqlParameter(name, SqlDbType.Decimal);                        param.Direction = ParameterDirection.Output;                        break;                    }                case "System_Boolean":                    {                        param = new SqlParameter(name, SqlDbType.Binary);                        param.Direction = ParameterDirection.Output;                        break;                    }                case "System_String":                    {                        param = new SqlParameter(name, SqlDbType.VarChar, 200);                        param.Direction = ParameterDirection.Output;                        break;                    }                case "System_DateTime":                    {                        param = new SqlParameter(name, SqlDbType.DateTime);                        param.Direction = ParameterDirection.Output;                        break;                    }                case "System_Char":                    {                        param = new SqlParameter(name, SqlDbType.Char, 100);                        param.Direction = ParameterDirection.Output;                        break;                    }                case "System_SByte":                    {                        param = new SqlParameter(name, SqlDbType.NChar, 30);                        param.Direction = ParameterDirection.Output;                        break;                    }                case "System_Text":                    {                        param = new SqlParameter(name, SqlDbType.Text, 300);                        param.Direction = ParameterDirection.Output;                        break;                    }                case "System_XMl":                    {                        param = new SqlParameter(name, SqlDbType.Xml, 300);                        param.Direction = ParameterDirection.Output;                        break;                    }                default:                    {                        param = new SqlParameter(name, SqlDbType.Variant);                        param.Direction = ParameterDirection.Output;                        break;                    }            }            return param;        }        #endregion CreateOutParams            #region CreateParameter    创建形式参数        /// <summary>        /// 转换参数为SQL语句的表达式        /// </summary>        /// <param name="nValue">传入的Object类型值</param>        /// <returns>已经转换好的String</returns>        public static string CreateParameter(SqlParameter oValue)        {            string strTemPara;            object oPara_Value = http://www.mamicode.com/oValue.Value;"VarChar":                        {                            strTemPara = "‘" + CheckMark(oPara_Value) + "‘";                            break;                        }                    case "Char":                        {                            strTemPara = "‘" + CheckMark(oPara_Value) + "‘";                            break;                        }                    case "NChar":                        {                            strTemPara = "‘" + CheckMark(oPara_Value) + "‘";                            break;                        }                    case "NVarChar":                        {                            strTemPara = "‘" + CheckMark(oPara_Value) + "‘";                            break;                        }                    //日期型                    case "DateTime":                        {                            DateTime dt = new DateTime();                            dt = (DateTime)oPara_Value;                            string strTP = "‘" + dt.Year + "-" + dt.Month + "-" + dt.Day;                            strTP += " " + dt.Hour.ToString() + ":" + dt.Minute.ToString();                            strTP += ":" + dt.Second.ToString() + "‘,";                            strTemPara = "TO_DATE(" + strTP + "‘yyyy-mm-dd hh24:mi:ss‘" + ")";                            break;                        }                    case "LongVarChar":                        {                            strTemPara = "‘" + CheckMark(oPara_Value) + "‘";                            break;                        }                    case "Clob":                        {                            strTemPara = "‘" + CheckMark(oPara_Value) + "‘";                            break;                        }                    default:                        {                            strTemPara = oPara_Value.ToString();                            break;                        }                }            }            else            {                //将null传入                strTemPara = "null";            }            return strTemPara;        }        #endregion        #region   CheckMark   替换object的‘为‘‘并转换为String        /// <summary>        /// 替换object的‘为‘‘并转换为String        /// </summary>        /// <param name="objIn">传入的Object类型</param>        /// <returns>已经替换‘为‘‘的String</returns>        private static string CheckMark(object objIn)        {            string strTmp = objIn.ToString();            return strTmp.Replace("‘", "‘‘");  // modified by apenni 06.01.02            //string strRet = "";            //for (int i = 0; i < strTmp.Length; i++)            //{            //    if (strTmp[i].ToString() == "‘")            //    {            //        strRet += "‘‘";            //    }            //    else            //    {            //        strRet += strTmp[i].ToString();            //    }            //}            //return strRet;        }        #endregion    }    #endregion

 

ASP.NET经典的、封装好的ADO.NET类包