SMB.DATA.Utility.cs

using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data;
using System.Collections.Generic;
using System;
namespace SMB.Data
{
 /// <summary>
 /// Utility 的摘要说明。
 /// </summary>
 public sealed class Utility
 {
  //public SMB.Data.Config config=new Config();
  public static string ErrorMessage="";
        public Utility()
  {
  }
  public static  System.Data.DataTable getTable(string TableName)
  {
   return (getTable(TableName, "", ""));
  }
  public static System.Data.DataTable getTable(string TableName, string WhereString)
  {
   return (getTable(TableName, WhereString, ""));
  }
  public static DataTable getTableWithOrderString(string TableName, string OrderString)
  {
   return (getTable(TableName, "", OrderString));
  }
  public static System.Data.DataTable getTable(string TableName, string WhereString , string OrderString)
  {
   string sql;
   System.Data.DataTable tb=new System.Data.DataTable();
   sql = "Select * from " + TableName;
   if (WhereString!= "")
   { 
    sql = sql + " Where " + WhereString;
   }
   if (OrderString!="")
   {
    sql = sql + " Order By " + OrderString;
   }
   tb = ExecuteQuery(sql);
   return (tb);
  }
  public static System.Data.DataRow getRecord(string TableName, string KeyFieldName, string KeyValue)
  {
   System.Data.DataTable tb;
   System.Data.DataRow r;
   tb = getTable(TableName, KeyFieldName + "='" + KeyValue + "'");
   if (tb.Rows.Count>0)
   {
    r = tb.Rows[0];
   }
   else
   {
    r=null;
   }
   return(r);
  }
  public static System.Data.DataRow getRecord(string TableName, string KeyFieldName, int KeyValue)
  {
   System.Data.DataTable tb;
   System.Data.DataRow r;
   tb = getTable(TableName, KeyFieldName + "=" + KeyValue);
   if (tb.Rows.Count>0)
   {
    r = tb.Rows[0];
   }
   else
   {
    r=null;
   }
   return(r);
  }
  public static DataRow getRecord(string TableName,string WhereString)
  {
   DataTable tb=getTable(TableName,WhereString);
   DataRow r;
   if(tb.Rows.Count>0)
    r=tb.Rows[0];
   else
    r=null;
   return r;
  }

  //20060509 Mark
  public static DataRow getRecord(string SelectString)
  {
   DataTable tb=ExecuteQuery(SelectString);
   DataRow r;
   if(tb.Rows.Count>0)
    r=tb.Rows[0];
   else
    r=null;
   return r;
  }
  public static System.Data.IDbDataAdapter NewDataAdapter(string SelectCommandString)
  {
   //SMB.Data.Config config=new Config();
   return (NewDataAdapter(SelectCommandString ,SMB.Data.Config.ConnetionString));
  }
  public static System.Data.IDbDataAdapter NewDataAdapter(string SelectCommandString, string ConnectionString)
  {
   //SMB.Data.Config config=new Config();
   switch(SMB.Data.Config.DataProvider)
   {
    case Config.Provider.MSSQL:
     return (new System.Data.SqlClient.SqlDataAdapter(SelectCommandString, ConnectionString));
    case Config.Provider.OLEDB:
     return (new System.Data.OleDb.OleDbDataAdapter(SelectCommandString, ConnectionString));
    default:
     return (new System.Data.SqlClient.SqlDataAdapter(SelectCommandString, ConnectionString));
   }   
  }
 
  private static  System.Data.IDbConnection getConnection(SMB.Data.Config.Provider xProvider)
  {
   IDbConnection conn;
   //SMB.Data.Config config=new Config();
   if (xProvider== Config.Provider.OLEDB)
   {
    conn =new System.Data.OleDb.OleDbConnection(SMB.Data.Config.ConnetionString);
    return(conn);

   }
   else if(xProvider==Config.Provider.MSSQL)
   {
    conn = new System.Data.SqlClient.SqlConnection(SMB.Data.Config.ConnetionString);
    return(conn);
   }
   conn =new System.Data.OleDb.OleDbConnection(SMB.Data.Config.ConnetionString);
   return(conn);
   
  }
  public static System.Data.IDbConnection getConnection()
  {
   //SMB.Data.Config config=new Config();
   return (getConnection(SMB.Data.Config.DataProvider));
  }
  public static IDbCommand NewCommand()
  {
   IDbCommand cmd;
   //SMB.Data.Config config=new Config();
   switch(SMB.Data.Config.DataProvider)
   {
    case Config.Provider.MSSQL:
     cmd =new System.Data.SqlClient.SqlCommand();
     break;
    case Config.Provider.OLEDB:
     cmd =new System.Data.OleDb.OleDbCommand();
     break;
    default:
        cmd =new System.Data.SqlClient.SqlCommand();
     break;
   }
   return(cmd);
  }
  public static IDbCommand NewCommand(string QueryString,IDbConnection Conn)
  {
   IDbCommand cmd;
   cmd = NewCommand();
   cmd.CommandText = QueryString;
   cmd.Connection = Conn;
   return (cmd);
  }
  public static IDataReader ExecuteReader(string sql,IDbConnection conn)
  {
   //Dim cmd As IDbCommand, rd As IDataReader
   IDbCommand cmd;
   IDataReader rd;
   cmd = NewCommand(sql,conn);
   rd = cmd.ExecuteReader();
   return (rd);
  }
  /// <summary>
  /// ExecuteReader
  /// </summary>
  /// <param name="sql"></param>
  /// <returns></returns>
        public static SqlDataReader ExecuteReader(string sql) //Gary 20061026    //Dragon   20061029
  {
   SqlConnection conn = new SqlConnection(SMB.Data.Config.ConnetionString);
   conn.Open();                                      //Dragon   20061029
            SqlCommand cmd = new SqlCommand(sql,conn);

   // 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
   {
              
                SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                //SqlDataReader rdr = cmd.ExecuteReader();
    return rdr;
   }
   catch
   {
    conn.Close();
    throw  ;
   }
  }

 

//  public static IDataReader ExecuteReader(string sql)
//  {
//   return ExecuteReader(sql,getConnection());
//  }

 

        public static DataTable ExecuteQuery(string sql)
  {
   IDbDataAdapter DA;
   //SMB.Data.Config config=new Config();
   DA = NewDataAdapter(sql,SMB.Data.Config.ConnetionString);
   DataTable tb;
   DataSet ds=new DataSet();
   switch(SMB.Data.Config.DataProvider)
   {
    case Config.Provider.MSSQL:
     DA.Fill(ds);
     tb=ds.Tables[0];
     break;
    case Config.Provider.OLEDB:
     DA.Fill(ds);
     tb=ds.Tables[0];
     break;
    default:
     DA.Fill(ds);
     tb=ds.Tables[0];
     break;

   }
   return (tb);
  }
        //Add By Luoke 20070524
        public static DataTable ExecuteQuery(string sql,int cmdTimeout)
        {
            IDbCommand cmd;
            IDbConnection conn;
            conn = SMB.Data.Utility.getConnection();
            cmd = SMB.Data.Utility.NewCommand(sql, conn);
            conn.Open();
            cmd.CommandTimeout = cmdTimeout;
            IDbDataAdapter Ada = new SqlDataAdapter();
            Ada.SelectCommand = cmd;

            DataSet ds = new DataSet();
            Ada.Fill(ds);
            conn.Close();
            conn.Dispose();
            cmd.Dispose();
            return ds.Tables[0];
        }

  public static int ExecuteNonQuery(string sql)
  {
   IDbCommand cmd;
   IDbConnection conn;
   int i;
   conn = getConnection();
   cmd = NewCommand(sql, conn);
   conn.Open();
   i = cmd.ExecuteNonQuery();
   conn.Close();
   conn.Dispose();
   cmd.Dispose();
   return (i);
  }
  public static object ExecuteScalar(string sql)
  {
   IDbCommand cmd;
   IDbConnection conn;
   object r;
   conn = getConnection();
   conn.Open();
   cmd = NewCommand(sql, conn);
   r = cmd.ExecuteScalar();
   conn.Close();
   conn.Dispose();
   cmd.Dispose();
   return (r);
  }
  public static int RecordCount(string TableName, string WhereString)
  {
   string sql;  
   sql = "Select count(*) from " + TableName + " Where " + WhereString;
   IDbCommand cmd;
   IDbConnection conn;
   int i;
   conn = getConnection();
   cmd = NewCommand(sql, conn);
   conn.Open();
   i = (int)cmd.ExecuteScalar();
   conn.Close();
   conn.Dispose();
   cmd.Dispose();
   return (i);
  }
  public static int RecordCount(string TableName, string FieldName,string StringOperator, string Value)
  {
   string s;
   int i;
   s = MakeWhereString(FieldName, StringOperator, Value);
   i = RecordCount(TableName, s);
   return (i);
  }

  public static string MakeWhereString(string FieldName,string Operator,string Value)
  {
   string s;
   s = FieldName + Operator + "'" + Value + "'";
   return (s);
  }
  //20060209
  public static IDbCommand NewCommandWithTransaction()
  {
   IDbConnection conn;
   IDbCommand cmd;
   IDbTransaction ts;
   cmd=SMB.Data.Utility.NewCommand();
   conn=SMB.Data.Utility.getConnection();
   conn.Open();
   ts=conn.BeginTransaction();
   cmd.Connection=conn;//
   cmd.Transaction=ts;
   return cmd;
  }
  public static void Commit(ref IDbCommand cmd)
  {
   if(cmd.Transaction == null)
   {
    throw new Exception("No Transaction in the Command object.");
   }
   else
   {
    cmd.Transaction.Commit();
    cmd.Connection.Close();
   }
  }
  public static void Rollback(ref IDbCommand cmd)
  {
   if(cmd.Transaction == null)
   {
    throw new Exception("No Transaction in the Command object.");
   }
   else
   {
    cmd.Transaction.Rollback();
    cmd.Connection.Close();
   }
  }

  //20060508 Mark
  public static string getSettingValue(string SysID,string SettingID)
  {
   DataRow r=Utility.getRecord("Settings","SysID='" + SysID + "' AND SettingID='" + SettingID + "'");
   if(r != null)
   {
    return (r["SettingValue"]!=DBNull.Value)?r["SettingValue"].ToString():"0";
   }
   return "0";
  }
  public static void setSettingValue(string SysID,string SettingID,string SettingValue)
  {
   string s="UPDATE Settings SET SettingValue='" + SettingValue + "'";
   s+=" WHERE SysID='" + SysID + "' AND SettingID='" + SettingID + "'";
   if(Utility.ExecuteNonQuery(s)==0)
   {
    s="INSERT INTO Settings(SysID,SettingID,SettingValue,Note)";
    s+=" VALUES ('" + SysID + "','" + SettingID + "','" + SettingValue + "','')";
    if(Utility.ExecuteNonQuery(s)==0)
    { throw new Exception("Error when setSettingValue, SysID=" + SysID + ", SettingID=" + SettingID); }
   }
  }

 

        #region --SqlParameter(Add By Weck 20070527)--
        public static DataTable ExecuteQuery(string sql, SqlParameter[] parameters)
        {
            SqlDataAdapter DA;
            //SMB.Data.Config config=new Config();
            DA = new System.Data.SqlClient.SqlDataAdapter(sql, SMB.Data.Config.ConnetionString);
            for (int i = 0; i < parameters.Length; i++)
            {
                DA.SelectCommand.Parameters.Add(parameters[i]);
            }
            DataTable tb;
            DataSet ds = new DataSet();
            DA.Fill(ds);
            tb = ds.Tables[0];
            return (tb);
        }

        public static DataTable ExecuteQuery(string sql, List<SqlParameter> sp)  //add by dragon
        {
            SqlDataAdapter DA;
            DA = new System.Data.SqlClient.SqlDataAdapter(sql, SMB.Data.Config.ConnetionString);
            foreach (SqlParameter s in sp)
            {
                DA.SelectCommand.Parameters.Add(s);
            }
            DA.SelectCommand.CommandType = CommandType.StoredProcedure;
            DataTable tb;
            DataSet ds = new DataSet();
            DA.Fill(ds);
            tb = ds.Tables[0];
            return (tb);
        }

        public static int ExecuteNonQuery(string sql, List<SqlParameter> sp)  //add by dragon
        {
            SqlDataAdapter DA;
            DA = new System.Data.SqlClient.SqlDataAdapter(sql, SMB.Data.Config.ConnetionString);
            foreach (SqlParameter s in sp)
            {
                DA.SelectCommand.Parameters.Add(s);
            }
            DA.SelectCommand.CommandType = CommandType.StoredProcedure;
            DataTable tb;
            DataSet ds = new DataSet();
            DA.Fill(ds);
            tb = ds.Tables[0];
            return (tb.Rows.Count);
        }

        public static DataRow getRecord(string sql, List<SqlParameter> sp)  //add by dragon
        {
            SqlDataAdapter DA;
            DA = new System.Data.SqlClient.SqlDataAdapter(sql, SMB.Data.Config.ConnetionString);
            foreach (SqlParameter s in sp)
            {
                DA.SelectCommand.Parameters.Add(s);
            }
            DA.SelectCommand.CommandType = CommandType.StoredProcedure;
            DataTable tb;
            DataSet ds = new DataSet();
            DA.Fill(ds);
            tb = ds.Tables[0];
            if (tb.Rows.Count > 0)
            {
                return (tb.Rows[0]);
            }
            else
            {
                return null;
            }
        }

        public static DataTable ExecuteQuery(string sql, SqlParameter[] parameters,SqlCommand cmd)
        {
            SqlDataAdapter DA;
            DA = new System.Data.SqlClient.SqlDataAdapter(sql, SMB.Data.Config.ConnetionString);
            for (int i = 0; i < parameters.Length; i++)
            {
                DA.SelectCommand.Parameters.Add(parameters[i]);
            }
            DataTable tb;
            DataSet ds = new DataSet();
            DA.Fill(ds);
            tb = ds.Tables[0];
            return (tb);
        }

        public static int ExecuteNonQuery(string sql, SqlParameter[] parameters,SqlCommand cmd)
        {
            for (int j = 0; j < parameters.Length; j++)
            {
                cmd.Parameters.Add(parameters[j]);
            }

            return (cmd.ExecuteNonQuery());
        }

        public static int ExecuteNonQuery(string sql, SqlParameter[] parameters)
        {
            SqlCommand cmd;
            SqlConnection conn;
            int i;
            conn = new SqlConnection(Config.ConnetionString);
            cmd = new SqlCommand(sql, conn);
            for (int j = 0; j < parameters.Length; j++)
            {
                cmd.Parameters.Add(parameters[j]);
            }
            conn.Open();
            i = cmd.ExecuteNonQuery();
            conn.Close();
            conn.Dispose();
            cmd.Dispose();
            return (i);
        }
        public static object ExecuteScalar(string sql, SqlParameter[] parameters)
        {
            SqlCommand cmd;
            SqlConnection conn;
            conn = new SqlConnection(Config.ConnetionString);
            cmd = new SqlCommand(sql, conn);
            for (int j = 0; j < parameters.Length; j++)
            {
                cmd.Parameters.Add(parameters[j]);
            }
            conn.Open();
            object r = cmd.ExecuteScalar();
            conn.Close();
            conn.Dispose();
            cmd.Dispose();
            return (r);
        }

        public static object ExecuteScalar(string sql, SqlParameter[] parameters,SqlCommand cmd)
        {
            for (int j = 0; j < parameters.Length; j++)
            {
                cmd.Parameters.Add(parameters[j]);
            }
            object r = cmd.ExecuteScalar();
            return (r);
        }

        public static SqlDataReader ExecuteReader(string sql, SqlParameter[] parameters, SqlCommand cmd)
        {
            for (int j = 0; j < parameters.Length; j++)
            {
                cmd.Parameters.Add(parameters[j]);
            }
            SqlDataReader dr = cmd.ExecuteReader();
            return (dr);
        }

        public static SqlCommand NewSqlCommandWithTransaction()
        {
            SqlConnection conn;
            SqlCommand cmd;
            SqlTransaction ts;
            cmd = new SqlCommand();
            conn = new SqlConnection(Config.ConnetionString);
            conn.Open();
            ts = conn.BeginTransaction();
            cmd.Connection = conn;//
            cmd.Transaction = ts;
            return cmd;
        }
        public static void Commit(SqlCommand cmd)
        {
            if (cmd.Transaction == null)
            {
                throw new Exception("No Transaction in the Command object.");
            }
            else
            {
                cmd.Transaction.Commit();
                cmd.Connection.Close();
            }
        }
        public static void Rollback(SqlCommand cmd)
        {
            if (cmd.Transaction == null)
            {
                throw new Exception("No Transaction in the Command object.");
            }
            else
            {
                cmd.Transaction.Rollback();
                cmd.Connection.Close();
            }
        }

        #endregion --SqlParameter--
  //
 }
 
}

转载于:https://www.cnblogs.com/s021368/articles/826692.html