你的位置：首页 » 学习收藏 » 正文

C#技术学习之-数据库操作函数

选择字号：超大标准 发布时间：2008-9-28 9:32:37 | 作者：admin | 0个评论 | 人浏览

using System;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.UI;
using System.Data.OleDb;
namespace Mirror.AutoWeb.HelpCSharp.App_Code
{
/// <summary>
/// SqlDB 的摘要说明。
/// </summary>
public class SqlDB
{
  public static readonly string MyConnString = ConfigurationSettings.AppSettings["MyConnString"];
   //this.MyConnString=ConfigurationSettings.AppSettings["MyConnString"];
   //Access:Provider=Microsoft.Jet.OLEDB.4.0; Data Source=F:\Inetpub\wwwroot\AutoWeb\MeHelpCSharp\db\MeHelpCSharp.mdb
   //SQL Server:Data Source=localhost;Initial Catalog=hljxc;User Id=sa;PASSWORD=00000000;
  public SqlDB()
  {
   //
   SqlDbInit();
   //System.Data.OleDb.OleDbConnection
   //System.Data.SqlClient.OleDbParameter
   //

  }
  #region Properties
   /// <summary>
   /// 取得连接数据库的字符串 MyConnString
   /// </summary>
  public string ConnString
  {
   get
   {
    return ConfigurationSettings.AppSettings["MyConnString"];
   }
  }
  #endregion
  #region Fields

  #endregion
  #region Methods
  /// <summary>
  /// 进行类的实例化,包括连接数据库等
  /// </summary>
  private void SqlDbInit()
  {

   //

  }
  /// <summary>
  /// Judge IsDBNull(IsDBNull return "")
  /// </summary>
  /// <param name="obj"></param>
  /// <returns></returns>
   public static string SlqDBNull(object obj)
   {
    if(Convert.IsDBNull(obj))
    {
     return "";
    }
    else
    {
     return Convert.ToString(obj);
    }
   }
  #endregion

  #region un-initializtion and dispose
  public void Dispose()
  {

  }
  #endregion

  /// <summary>
  /// Execute a OleDbCommand (that returns no resultset) against the database specified in the connection string
  /// using the provided parameters.
  /// </summary>
  /// <remarks>
  /// e.g.:
  /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new OleDbParameter("@prodid", 24));
  /// </remarks>
  /// <param name="connectionString">a valid connection string for a OleDbConnection </param>
  /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  /// <param name="commandText">the stored procedure name or T-SQL command</param>
  /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
  /// <returns>an int representing the number of rows affected by the command</returns>
  public static int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
  {
   OleDbCommand cmd = new OleDbCommand();
   using (OleDbConnection conn = new OleDbConnection (connString))
   {
    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
    int val = cmd.ExecuteNonQuery();
    cmd.Parameters.Clear();
    return val;
   }
  }
  public static int ExecuteNonQuery(string connString, string cmdText, params OleDbParameter[] cmdParms)
  {
   return ExecuteNonQuery(connString, CommandType.Text, cmdText, cmdParms);
  }

  /// <summary>
  /// Execute a OleDbCommand (that returns no resultset) against an existing database connection
  /// using the provided parameters.
  /// </summary>
  /// <remarks>
  /// e.g.:
  /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new OleDbParameter("@prodid", 24));
  /// </remarks>
  /// <param name="conn">an existing database connection</param>
  /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  /// <param name="commandText">the stored procedure name or T-SQL command</param>
  /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
  /// <returns>an int representing the number of rows affected by the command</returns>
  public static int ExecuteNonQuery(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
  {
   OleDbCommand cmd = new OleDbCommand();

   PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
   int val = cmd.ExecuteNonQuery();
   cmd.Parameters.Clear();
   return val;
  }

  public static int ExecuteNonQuery(OleDbConnection conn, string cmdText, params OleDbParameter[] cmdParms)
  {
   return ExecuteNonQuery(conn, CommandType.Text, cmdText, cmdParms);
  }

  /// <summary>
  /// Execute a OleDbCommand (that returns no resultset) using an existing SQL Transaction
  /// using the provided parameters.
  /// </summary>
  /// <remarks>
  /// e.g.:
  /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new OleDbParameter("@prodid", 24));
  /// </remarks>
  /// <param name="trans">an existing sql transaction</param>
  /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  /// <param name="commandText">the stored procedure name or T-SQL command</param>
  /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
  /// <returns>an int representing the number of rows affected by the command</returns>
  public static int ExecuteNonQuery(OleDbTransaction trans, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
  {
   OleDbCommand cmd = new OleDbCommand();
   PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
   int val = cmd.ExecuteNonQuery();
   cmd.Parameters.Clear();
   return val;
  }

  public static int ExecuteNonQuery(OleDbTransaction trans, string cmdText, params OleDbParameter[] cmdParms)
  {
   return ExecuteNonQuery(trans, CommandType.Text, cmdText, cmdParms);
  }

  public static DataTable FillDataTable(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
  {
   OleDbCommand cmd = new OleDbCommand();
   using(OleDbConnection conn = new OleDbConnection (connString))
   {
    DataTable dt = new DataTable();
    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
    OleDbDataAdapter sda = new OleDbDataAdapter(cmd);
    sda.Fill(dt);
    cmd.Parameters.Clear();
    sda.Dispose();
    return dt;
   }
  }

  public static DataTable FillDataTable(string cmdText, params OleDbParameter[] cmdParms)
  {
   return FillDataTable(MyConnString, CommandType.Text, cmdText, cmdParms);
  }

  public static DataTable FillDataTable(string connString, string cmdText, params OleDbParameter[] cmdParms)
  {
   return FillDataTable(connString, CommandType.Text, cmdText, cmdParms);
  }

  public static DataSet FillDataSet(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
  {
   OleDbCommand cmd = new OleDbCommand();
   using(OleDbConnection conn = new OleDbConnection (connString))
   {
    DataSet ds = new DataSet();
    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
    OleDbDataAdapter sda = new OleDbDataAdapter(cmd);
    sda.Fill(ds);
    cmd.Parameters.Clear();
    sda.Dispose();
    return ds;
   }
  }

  public static DataSet FillDataSet(string connString, string cmdText, params OleDbParameter[] cmdParms)
  {
   return FillDataSet(connString, CommandType.Text, cmdText, cmdParms);
  }

  /// <summary>
  /// Execute a OleDbCommand that returns a resultset against the database specified in the connection string
  /// using the provided parameters.
  /// </summary>
  /// <remarks>
  /// e.g.:
  /// OleDbDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new OleDbParameter("@prodid", 24));
  /// </remarks>
  /// <param name="connectionString">a valid connection string for a OleDbConnection </param>
  /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  /// <param name="commandText">the stored procedure name or T-SQL command</param>
  /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
  /// <returns>A OleDbDataReader containing the results</returns>
  public static OleDbDataReader ExecuteReader(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
  {
   OleDbCommand cmd = new OleDbCommand();
   OleDbConnection conn = new OleDbConnection (connString);

   // we use a try/catch here because if the method throws an exception we want to
   // close the connection throw code, because no datareader will exist, hence the
   // commandBehaviour.CloseConnection will not work
   try
   {
    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
    OleDbDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
    cmd.Parameters.Clear();
    return rdr;
   }
   catch
   {
    conn.Close();
    throw;
   }
  }

  public static OleDbDataReader ExecuteReader(string connString, string cmdText, params OleDbParameter[] cmdParms)
  {
   return ExecuteReader(connString, CommandType.Text, cmdText,cmdParms);
  }

  /// <summary>
  /// Execute a OleDbCommand that returns the first column of the first record against the database specified in the connection string
  /// using the provided parameters.
  /// </summary>
  /// <remarks>
  /// e.g.:
  /// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new OleDbParameter("@prodid", 24));
  /// </remarks>
  /// <param name="connectionString">a valid connection string for a OleDbConnection </param>
  /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  /// <param name="commandText">the stored procedure name or T-SQL command</param>
  /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
  /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
  public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
  {
   OleDbCommand cmd = new OleDbCommand();

   using (OleDbConnection conn = new OleDbConnection (connString))
   {
    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
    object val = cmd.ExecuteScalar();
    cmd.Parameters.Clear();
    return val;
   }
  }

  public static object ExecuteScalar(string connString, string cmdText, params OleDbParameter[] cmdParms)
  {
   return ExecuteScalar(connString, CommandType.Text, cmdText, cmdParms);
  }

  public static object ExecuteScalar(OleDbTransaction trans, string cmdText, params OleDbParameter[] cmdParms)
  {
   OleDbCommand cmd = new OleDbCommand();
   PrepareCommand(cmd, trans.Connection, trans, CommandType.Text, cmdText, cmdParms);
   object val = cmd.ExecuteScalar();
   cmd.Parameters.Clear();
   return val;
  }

  /// <summary>
  /// Execute a OleDbCommand that returns the first column of the first record against an existing database connection
  /// using the provided parameters.
  /// </summary>
  /// <remarks>
  /// e.g.:
  /// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new OleDbParameter("@prodid", 24));
  /// </remarks>
  /// <param name="conn">an existing database connection</param>
  /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  /// <param name="commandText">the stored procedure name or T-SQL command</param>
  /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
  /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
  public static object ExecuteScalar(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
  {
   OleDbCommand cmd = new OleDbCommand();

   PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
   object val = cmd.ExecuteScalar();
   cmd.Parameters.Clear();
   return val;
  }

  public static object ExecuteScalar(OleDbConnection conn, string cmdText, params OleDbParameter[] cmdParms)
  {
   return ExecuteScalar(conn, CommandType.Text, cmdText, cmdParms);
  }

  /// <summary>
  /// Prepare a command for execution
  /// </summary>
  /// <param name="cmd">OleDbCommand object</param>
  /// <param name="conn">OleDbConnection object</param>
  /// <param name="trans">SqlTransaction object</param>
  /// <param name="cmdType">Cmd type e.g. stored procedure or text</param>
  /// <param name="cmdText">Command text, e.g. Select * from Products</param>
  /// <param name="cmdParms">OleDbParameters to use in the command</param>
  private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, CommandType cmdType, string cmdText, OleDbParameter[] cmdParms)
  {
   if (conn.State != ConnectionState.Open)
    conn.Open();

cmd.Connection = conn;
cmd.CommandText = cmdText;

if (trans != null)
cmd.Transaction = trans;

cmd.CommandType = cmdType;

   if (cmdParms != null)
   {
    foreach (OleDbParameter parm in cmdParms)
     cmd.Parameters.Add(parm);
   }
  }

}
}

标签：C# DataBase

C#技术学习之-数据库操作函数

猜你喜欢

发表评论