.Net---Sql数据库操作通用类库

using System;using System.Data;using System.Data.SqlClient;namespace DataBaseOperate{ /// <summary> /// ConnDB 的摘要说明。 /// </summary> public class ConnDB {  protected SqlConnection Connection;  public string connectionString;  /// <summary>  /// 默认构造函数  /// </summary>  public ConnDB()  {   string connStr;   try   {   connStr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ToString();   connectionString = connStr;   Connection = new SqlConnection(connectionString);   }   catch (Exception)   {   }  }  /// <summary>  /// 带参数的构造函数  /// </summary>  /// <param name="newConnectionString">数据库联接字符串</param>  public ConnDB(string newConnectionString)  {   connectionString = newConnectionString;   Connection = new SqlConnection(connectionString);  }  /// <summary>  /// 完成SqlCommand对象的实例化-------本函数不需要显示调用  /// </summary>  /// <param name="storedProcName"></param>  /// <param name="parameters"></param>  /// <returns></returns>  private SqlCommand BuildCommand(string storedProcName,IDataParameter[] parameters)  {   SqlCommand command = BuildQueryCommand(storedProcName,parameters);   command.Parameters.Add(new SqlParameter("ReturnValue",SqlDbType.Int,4,ParameterDirection.ReturnValue,false,0,0,string.Empty,DataRowVersion.Default,null));   return command;  }  /// <summary>  /// 创建新的SQL命令对象(存储过程)----------在BuildCommand函数中使用,不需要显示调用  /// </summary>  /// <param name="storedProcName"></param>  /// <param name="parameters"></param>  /// <returns></returns>  private SqlCommand BuildQueryCommand(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;  }  /// <summary>  /// 执行存储过程,无返回值  /// </summary>  /// <param name="storedProcName"></param>  /// <param name="parameters"></param>  public void RunProcedure_ReturnNull(string storedProcName,IDataParameter[] parameters)  {   Connection.Open();   SqlCommand command;   command=BuildQueryCommand(storedProcName,parameters);   command.ExecuteNonQuery();   Connection.Close();  }  /// <summary>  /// 执行存储过程，返回执行操作影响的行数目  /// </summary>  /// <param name="storedProcName"></param>  /// <param name="parameters"></param>  /// <param name="rowsAffected"></param>  /// <returns></returns>  public int RunProcedure_ReturnEffectRows(string storedProcName,IDataParameter[] parameters,out int rowsAffected)  {   int result;   Connection.Open();   SqlCommand command = BuildCommand(storedProcName,parameters);   rowsAffected = command.ExecuteNonQuery();   result = (int)command.Parameters["ReturnValue"].Value;   Connection.Close();   return result;  }    /// <summary>  /// 重载RunProcedure把执行存储过程的结果放在SqlDataReader中  /// </summary>  /// <param name="storedProcName"></param>  /// <param name="parameters"></param>  /// <returns></returns>  public SqlDataReader RunProcedure_ReturnDataReader(string storedProcName, IDataParameter[] parameters)  {   SqlDataReader returnReader;   Connection.Open();   SqlCommand command = BuildQueryCommand(storedProcName,parameters);   command.CommandType = CommandType.StoredProcedure;   returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);   return returnReader;  }  /// <summary>  /// 重载RunProcedure把执行存储过程的结果存储在DataSet中和表tableName为可选参数  /// </summary>  /// <param name="storedProcName"></param>  /// <param name="parameters"></param>  /// <param name="tableName"></param>  /// <returns></returns>  public DataSet RunProcedure_ReturnDataSet(string storedProcName,IDataParameter[] parameters,params string[] tableName)  {   DataSet dataSet = new DataSet();   Connection.Open();   SqlDataAdapter sqlDA = new SqlDataAdapter();   sqlDA.SelectCommand = BuildQueryCommand(storedProcName,parameters);   string flag;   flag = "";   for(int i=0;i<tableName.Length;i++)    flag = tableName[i];   if (flag!="")    sqlDA.Fill(dataSet,tableName[0]);   else    sqlDA.Fill(dataSet);   Connection.Close();   return dataSet;  }    /// <summary>  /// 执行SQL语句，返回数据到DataSet中  /// </summary>  /// <param name="sql"></param>  /// <returns></returns>  public DataSet ExecSQL_ReturnDataSet(string sql)  {   DataSet dataSet=new DataSet();   Connection.Open();   SqlDataAdapter sqlDA=new SqlDataAdapter(sql,Connection);   sqlDA.Fill(dataSet,"objDataSet");   Connection.Close();   return dataSet;  }    /// <summary>  /// 执行SQL语句，返回 DataReader  /// </summary>  /// <param name="sql"></param>  /// <returns></returns>  public SqlDataReader ExecSQL_ReturnDataReader(String sql)  {   Connection.Open();   SqlCommand command = new SqlCommand(sql,Connection);   SqlDataReader dataReader = command.ExecuteReader();   return dataReader;  }  /// <summary>  /// 执行SQL语句，返回结果集的记录数  /// </summary>  /// <param name="sql"></param>  /// <returns></returns>  public int ExecSql_ReturnRecordCount(string sql)  {   int recordCount = 0;   Connection.Open();   SqlCommand command = new SqlCommand(sql,Connection);   SqlDataReader dataReader = command.ExecuteReader();   while(dataReader.Read())   {    recordCount++;   }   dataReader.Close();   Connection.Close();   return recordCount;  }  /// <summary>  /// 执行一条非查询的SQL语句  /// </summary>  /// <param name="sql"></param>  /// <returns></returns>  public bool ExecSql_NonQuery(string sql)  {   bool successState = false;   Connection.Open();   //创建事务-------------   SqlTransaction myTrans = Connection.BeginTransaction();   SqlCommand command = new SqlCommand(sql,Connection,myTrans);   try   {    command.ExecuteNonQuery();    myTrans.Commit();//-----------提交事务    successState = true;   }   catch   {    myTrans.Rollback();   }   finally   {    Connection.Close();   }   return successState;  }  /// <summary>  /// 关闭数据库联接  /// </summary>  public void Close()  {   Connection.Close();  } }//end class}//end namespace 

以上是代码 ,大多都是改的别人的代码.总体来说没什么难度!注释已经很详细了!其中的IDataParameter接口需要说一下!

 

  从表面上看，IDataParameter[]的实例就是一个IDataParameter的数组。而IDataParameter实际上是不能直接实例化的，所以这种数组其实很不常见，仅仅会出现在函数的入口参数里面。且多以params的形式出现。这样做的好处是令这个函数可以对付所有的数据库。

       IDataParameter[]简单的说来，就是可以包含任何数据库参数的数组，这些参数可以是SqlParameter或者OleDbParameter，但是，IDataParameter[]不可以直接从SqlParameter[]转换过来。

       下面是三种创建IDataParameter[]的方法：

 

         一、直接构造：
                                IDataParameter[] parameters = new IDataParameter[]{ sqlparameter1, sqlparameter2, new SqlParameter(...) }

                                亦可以这样:

                               

            IDataParameter[] param = new IDataParameter[3];            param[0]=new SqlParameter("@co_code","PQtech");            param[1]=new SqlParameter("@clnt_type","C");            param[2]=new SqlParameter("@clnt_code","");

 

         二、通过ArrayList转换。

                               ArrayList paramlist = new ArrayList()
                               paramlist.Add( sqlparameter1 );
                                              ....
                                              ....

                               IDataParameter param = (IDataParameter[]) paramlist.ToArray( typeof( IDataParameter ) );

 

         三、通过其他Parameter类型的数组创建。

 

                              SqlParameter[] _param = new SqlParameter[]{ .... }

                             IDataParameter[] param = new IDataParameter[ _param.Length ];

                            _param.CopyTo( param, 0 );

 

以上内容出(改)自:IDataParameter