首页 > 代码库 > 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类包
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。