数据库操作类

 

using  System;
using  System.Collections;
using  System.Data;
using  System.Data.SqlClient;

namespace  JRJC.WEB.DLL
... {
    /**//// <summary>
    /// DbControl 的摘要说明。
    /// </summary>
    数据库链接类#region 数据库链接类
    public class DbControl
    ...{

        private SqlConnection conn;
        public DbControl()
        ...{
            this.open();
        }

        public DbControl(string strConn)
        ...{
            this.open(strConn);
        }

        方法：打开数据库连接#region 方法：打开数据库连接
        public void open()    
        ...{
            this.conn = new SqlConnection();
            this.conn.ConnectionString = System.Configuration.ConfigurationSettings.AppSettings["strConnNews2006"].ToString();
            this.conn.Open();
        }

        public void open(string strConn)
        ...{
            this.conn = new SqlConnection();
            this.conn.ConnectionString = strConn;
            this.conn.Open();
        }
        #endregion    

        方法：关闭数据库连接#region 方法：关闭数据库连接
        public void close()
        ...{
            if (conn.State != ConnectionState.Closed)
            ...{
                this.conn.Close();
            }
        }
        #endregion

        方法：执行SQL语句返回DS#region 方法：执行SQL语句返回DS
        public DataSet execSqlDS(string sql)
        ...{
            if (conn.State != ConnectionState.Open)
            ...{
                conn.Open();
            }
            SqlDataAdapter da = new SqlDataAdapter(sql,conn);
            da.SelectCommand.CommandTimeout = 6000;
            DataSet ds = new DataSet();
            da.Fill(ds);
            conn.Close();
            return ds;            
        }

    public DataSet execSqlDS(string sql,bool IsPid)
    ...{
      if (conn.State != ConnectionState.Open)
      ...{
        conn.Open();
      }
      SqlDataAdapter da = new SqlDataAdapter(sql,conn);
      da.SelectCommand.CommandTimeout = 6000;
      DataSet ds = new DataSet();
      da.Fill(ds);
      if(IsPid)
      ...{
        ds.Tables[0].Columns.Add("PID");
        for (int i=0; i<ds.Tables[0].Rows.Count; i++)
        ...{
          ds.Tables[0].Rows[i]["PID"] = i+1;
        }
      }
      conn.Close();
      return ds;            
    }
        #endregion

    方法：执行SQL语句返回DataTable#region 方法：执行SQL语句返回DataTable
    public DataTable execSqlTb(string sql)
    ...{
      if (conn.State != ConnectionState.Open)
      ...{
        conn.Open();
      }
      SqlDataAdapter da = new SqlDataAdapter(sql,conn);
      da.SelectCommand.CommandTimeout = 6000;
      DataSet ds = new DataSet();
      da.Fill(ds);
      ds.Tables[0].Columns.Add("PID");
      for (int i=0; i<ds.Tables[0].Rows.Count; i++)
      ...{
        ds.Tables[0].Rows[i]["PID"] = i+1;
      }
      conn.Close();
      return ds.Tables[0];            
    }
    #endregion

        方法：执行SQL语句返回string#region 方法：执行SQL语句返回string
        public string execSqlString(string sql)
        ...{
            if (conn.State != ConnectionState.Open)
            ...{
                conn.Open();
            }
            SqlDataAdapter da = new SqlDataAdapter(sql,conn);
            da.SelectCommand.CommandTimeout = 6000;
            DataSet ds = new DataSet();
            da.Fill(ds);
            conn.Close();
            string stringValue = "";
      if(ds.Tables.Count==0)
      ...{
        stringValue = "0";
      }
      else
      ...{
        if(ds.Tables[0].Rows.Count==0)
        ...{
          stringValue = "0";
        }
        else
        ...{
          stringValue = ds.Tables[0].Rows[0][0].ToString();
        }
      }

            return stringValue;            
        }
        #endregion

        public SqlDataReader execSqlDr(string sql)
        ...{
            if (conn.State != ConnectionState.Open)
            ...{
                conn.Open();
            }
            SqlCommand sqlCmd = new SqlCommand(sql,conn);
            SqlDataReader dr = sqlCmd.ExecuteReader();
            conn.Close();
            return dr;
        }

        方法：执行SQL语句，执行结果int不等于0，执行成功#region 方法：执行SQL语句，执行结果int不等于0，执行成功
        public int execSqlInt(string sql)
        ...{
            if (conn.State != ConnectionState.Open)
            ...{
                conn.Open();
            }
            SqlCommand sqlCmd = new SqlCommand(sql,conn);
            int a = sqlCmd.ExecuteNonQuery();
            conn.Close();
            return a;
        }
        #endregion

        方法：执行存储过程，返回DS#region 方法：执行存储过程，返回DS
        public DataSet execSpDS(string spName, Hashtable parameters )
        ...{
            if (conn.State != ConnectionState.Open)
            ...{
                conn.Open();
            }
            SqlCommand sqlCmd = new SqlCommand(spName,conn);
            sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
            System.Collections.IDictionaryEnumerator myE = parameters.GetEnumerator();//接口遍历哈希表所有的值和参数
            SqlParameter p;
            while(myE.MoveNext())
            ...{
                p = new SqlParameter();
                switch(myE.Value.GetType().ToString())
                ...{
                    case "System.Int32":
                        p.SqlDbType  = SqlDbType.Int;
                        break;
                    case "System.String":
                        p.SqlDbType  = SqlDbType.VarChar;
                        break;
                    case "System.Boolean":
                        p.SqlDbType  = SqlDbType.Bit;
                        break;
                    case "System.Byte":
                        p.SqlDbType  = SqlDbType.Binary;
                        break;
                    case "System.Double":
                        p.SqlDbType  = SqlDbType.Float;
                        break;
                    case "System.DateTime":
                        p.SqlDbType  = SqlDbType.DateTime;
                        break;
                    default:
                        p.SqlDbType  = SqlDbType.VarChar;
                        break;
                }
                p.ParameterName = myE.Key.ToString();
                p.Value = myE.Value.ToString();
                sqlCmd.Parameters.Add(p);
            }
            SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            this.conn.Close();
            return ds;
        }
        #endregion

        方法：执行存储过程，返回string#region 方法：执行存储过程，返回string
        public  string execSpString(string spName, Hashtable parameters )
        ...{
            if (conn.State != ConnectionState.Open)
            ...{
                conn.Open();
            }
            SqlCommand sqlCmd = new SqlCommand(spName,conn);
            sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
            System.Collections.IDictionaryEnumerator myE = parameters.GetEnumerator();
            SqlParameter p;
            while(myE.MoveNext())
            ...{
                p = new SqlParameter();
                switch(myE.Value.GetType().ToString())
                ...{
                    case "System.Int32":
                        p.SqlDbType  = SqlDbType.Int;
                        break;
                    case "System.String":
                        p.SqlDbType  = SqlDbType.VarChar;
                        break;
                    case "System.Boolean":
                        p.SqlDbType  = SqlDbType.Bit;
                        break;
                    case "System.Byte":
                        p.SqlDbType  = SqlDbType.Binary;
                        break;
                    case "System.Double":
                        p.SqlDbType  = SqlDbType.Float;
                        break;
                    default:
                        p.SqlDbType  = SqlDbType.VarChar;
                        break;
                        
                }
                p.ParameterName = myE.Key.ToString();
                p.Value = myE.Value.ToString();
                sqlCmd.Parameters.Add(p);
            }
            string Value = sqlCmd.ExecuteScalar().ToString();
            this.conn.Close();
            return Value;
        }
        #endregion

        方法：执行SQL语句，不返回结果。保留使用#region 方法：执行SQL语句，不返回结果。保留使用
        public void execSqlNo(string sql)
        ...{
            if (conn.State != ConnectionState.Open)
            ...{
                conn.Open();
            }
            SqlCommand sqlCmd = new SqlCommand(sql,conn);
            sqlCmd.CommandTimeout = 60000;
            sqlCmd.ExecuteNonQuery();
            conn.Close();
        }
        #endregion

    }
    #endregion
}