拍拍尘土

public DataSet ReadDB(string SqlCmd)
    {
      SqlConnection conn=new SqlConnection(SqlConn);
      SqlDataAdapter da=new SqlDataAdapter(SqlCmd,conn);
      DataSet ds=new DataSet();
      da.Fill(ds);
      da.Dispose();
      return ds;
    }
    public DataSet ReadDB_OLEDB(string OleCmd)
    {
      OleDbConnection conn=new OleDbConnection(OleConn);
      OleDbDataAdapter da=new OleDbDataAdapter(OleCmd,conn);
      DataSet ds=new DataSet();
      da.Fill(ds);
      da.Dispose();
      return ds;
    }
    public void ReadDB2(DataSet ds,string TableName,string SqlCmd)
    {
      SqlConnection conn=new SqlConnection(SqlConn);
      SqlDataAdapter da=new SqlDataAdapter(SqlCmd,conn);
      if (TableName=="") da.Fill(ds,"table1"); else da.Fill(ds,TableName);
      da.Dispose();
    }
    public void ReadDB2_OLEDB(DataSet ds,string TableName,string OleCmd)
    {
      OleDbConnection conn=new OleDbConnection(OleConn);
      OleDbDataAdapter da=new OleDbDataAdapter(OleCmd,conn);
      if (TableName=="") da.Fill(ds,"table1"); else da.Fill(ds,TableName);
      da.Dispose();
    }
    public void WriteDB(DataTable dt, string TableName)
    {
      foreach (DataRow dr in dt.Rows)
      {
        string cmd="INSERT INTO "+TableName+" (";
        foreach (DataColumn dc in dt.Columns)
          cmd+=dc.ColumnName+",";
        cmd=cmd.Substring(0,cmd.Length-1)+") VALUES (";
        foreach (DataColumn dc in dt.Columns)
          cmd+="'"+dr[dc.ColumnName].ToString().Replace("'","''")+"',";
        cmd=cmd.Substring(0,cmd.Length-1)+")";
        RunSqlCmd(cmd);
      }
    }
    public void WriteDB_OLEDB(DataTable dt, string TableName)
    {
      foreach (DataRow dr in dt.Rows)
      {
        string cmd="INSERT INTO "+TableName+" (";
        foreach (DataColumn dc in dt.Columns)
          cmd+=dc.ColumnName+",";
        cmd=cmd.Substring(0,cmd.Length-1)+") VALUES (";
        foreach (DataColumn dc in dt.Columns)
          cmd+="'"+dr[dc.ColumnName].ToString().Replace("'","''")+"',";
        cmd=cmd.Substring(0,cmd.Length-1)+")";
        RunOleCmd(cmd);
      }
    }
    public decimal GetSum(string SqlCmd)
    {
      decimal result=0;
      DataSet ds=ReadDB(SqlCmd);
      string sum=ds.Tables[0].Rows[0][0].ToString();
      if (sum!="") result=Convert.ToDecimal(sum);
      ds.Dispose();
      return result;
    }

    public decimal GetSum_OLEDB(string OleCmd)
    {
      decimal result=0;
      DataSet ds=ReadDB_OLEDB(OleCmd);
      string sum=ds.Tables[0].Rows[0][0].ToString();
      if (sum!="") result=Convert.ToDecimal(sum);
      ds.Dispose();
      return result;
    }

    public bool RecordExist(string SqlCmd)
    {
      bool result=false;
      DataSet ds=ReadDB(SqlCmd);
      if (ds.Tables[0].Rows.Count>0) result=true;
      return result;
    }
    public bool RecordExist_OLEDB(string OleCmd)
    {
      bool result=false;
      DataSet ds=ReadDB_OLEDB(OleCmd);
      if (ds.Tables[0].Rows.Count>0) result=true;
      return result;
    }
    public void RunSqlCmd(string SqlCmd)
    {
      string str=SqlCmd.Trim().Substring(0,6).ToUpper();
      if (str!="SELECT")
        FlagDBChanged=true;
      SqlConnection conn=new SqlConnection(SqlConn);
      conn.Open();
      SqlCommand cmd=new SqlCommand(SqlCmd,conn);
      cmd.ExecuteNonQuery();
      cmd.Dispose();
      conn.Close();
    }
    public void RunOleCmd(string OleCmd)
    {
      string str=OleCmd.Trim().Substring(0,6).ToUpper();
      if (str!="SELECT")
        FlagDBChanged=true;
      OleDbConnection conn=new OleDbConnection(OleConn);
      conn.Open();
      OleDbCommand cmd=new OleDbCommand(OleCmd,conn);
      cmd.ExecuteNonQuery();
      cmd.Dispose();
      conn.Close();
    }
    //组装INSERT语句 - BuildInsertCommand(tablename,field1,field2,field3....,value1,value2,value3...)
    public string BuildInsertCommand(string TableName, params string[] SqlCmd)
    {
      string result="";
      int i;

      if (SqlCmd.Length % 2 == 0)  
      {
        result="INSERT INTO "+TableName+" (";
        for (i=0; i<SqlCmd.Length/2-1;i++)
          result+="["+SqlCmd[i].Trim()+"],";
        result+="["+SqlCmd[i].Trim()+"]) VALUES (";
        for (i=SqlCmd.Length/2; i<SqlCmd.Length-1;i++)
          result+="'"+SqlCmd[i].Trim().Replace("'","''")+"',";
        result+="'"+SqlCmd[i].Trim().Replace("'","''")+"')";
      }
      return result;
    }

    //组装UPDATE语句 - BuildUpdateCommand(tablename, "a=b and c>d",field1,value1,field2,value2......
    public string BuildUpdateCommand(string TableName, string where, params string[] SqlCmd)
    {
      string result="";
      if (where.Trim()=="") where="1=1";
      if (SqlCmd.Length % 2 == 0)  
      {
        result="UPDATE "+TableName+" SET ";
        for (int i=0; i<SqlCmd.Length;i+=2)
          result+="["+SqlCmd[i].Trim()+"]='"+SqlCmd[i+1].Trim().Replace("'","''")+"',";
        result=result.Substring(0,result.Length-1)+" where "+where;
      }
      return result;
    }

    //把DataTable的内容导出到指定Excel文件，需要自己预先建立这个空Excel文件。
    public void ExportToExcel(DataTable dt,string FilePath)
    {
      string tname=dt.TableName.Trim(); //检测表名
      if (tname=="Table") tname=DateTime.Today.Date.ToString("XyyyyMMdd");
      string cmdstr,connstr="Provider=Microsoft.Jet.OLEDB.4.0; Data Source="+FilePath+"; Extended Properties=Excel 8.0";
      OleDbConnection conn=new OleDbConnection(connstr);
      conn.Open();
      OleDbCommand cmd=new OleDbCommand("",conn);
      try //如果有同名表则删除
      {
        cmd.CommandText="DROP TABLE "+tname;
        cmd.ExecuteNonQuery();
      }
      catch
      {
      }
      cmdstr="CREATE TABLE "+tname+" (";
      foreach (DataColumn dc in dt.Columns)
        cmdstr+="["+dc.ColumnName+"] "+dc.DataType.ToString().Substring(7,dc.DataType.ToString().Length-7)+",";
      cmdstr=cmdstr.Substring(0,cmdstr.Length-1)+")";
      cmd.CommandText=cmdstr;
      cmd.ExecuteNonQuery();
      foreach (DataRow dr in dt.Rows)
      {
        cmdstr="INSERT INTO "+tname+" (";
        foreach (DataColumn dc in dt.Columns)
          cmdstr+="["+dc.ColumnName+"],";
        cmdstr=cmdstr.Substring(0,cmdstr.Length-1)+") VALUES (";
        foreach (DataColumn dc in dt.Columns)
          cmdstr+="'"+dr[dc.ColumnName].ToString().Replace("'","''")+"',";
        cmdstr=cmdstr.Substring(0,cmdstr.Length-1)+")";
        cmd.CommandText=cmdstr;
        cmd.ExecuteNonQuery();
      }
      cmd.Dispose();
      conn.Close();
    }
    //读入Excel文件到DataSet
    public DataSet ImportFromExcel(string FilePath,string Cmd)
    {
      if (!File.Exists(FilePath)) return null;
      DataSet ds=new DataSet();
      string connstr="Provider=Microsoft.Jet.OLEDB.4.0; Data Source="+FilePath+"; Extended Properties=Excel 8.0";
      OleDbConnection conn=new OleDbConnection(connstr);
      conn.Open();
      OleDbDataAdapter da=new OleDbDataAdapter(Cmd,conn);
      da.Fill(ds);
      da.Dispose();
      conn.Close();
      return ds;
    }
    public bool DBChanged()
    {
      bool result=FlagDBChanged;
      if (result) FlagDBChanged=false;
      return result;
    }
    public void ResetFlag()
    {
      FlagDBChanged=false;
    }