using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OracleClient;
using System.Data.OleDb;
using System.IO;
using System.Text;
/// <summary>
/// DBHelper的摘要描述 -- 所有跟数据库访问相关的函数
/// </summary>
///
namespace SysHelper
{
public class DBHelper
{
public static string ErrorMessage = "";
public DBHelper()
{
//构造函数,暂时不用写
}
public static readonly string DBString = ConfigurationManager.ConnectionStrings["ConnStr"].ToString();
//创建数据库连接
public static OracleConnection NewConnection()
{
return new OracleConnection(DBString);
}
//开始数据库交易
public static OracleTransaction BeginTransaction(OracleConnection Conn)
{
Conn.Open();
OracleTransaction Tran = Conn.BeginTransaction();
return Tran;
}
//下面是ExecNoQry的N多重载函数
//使用ExecuteNonQuery
public static int ExecNoQry(string ConnStr, string SqlStr)
{
return ExecNoQry(ConnStr, CommandType.Text, SqlStr);
}
public static int ExecNoQry(string ConnStr, CommandType CmdType, string SqlStr)
{
return ExecNoQry(ConnStr, CmdType, SqlStr, (OracleParameter[])null);
}
public static int ExecNoQry(string SqlStr)
{
return ExecNoQry(null, CommandType.Text, SqlStr);
}
public static int ExecNoQry(CommandType CmdType, string SqlStr)
{
return ExecNoQry((string)null, CmdType, SqlStr, (OracleParameter[])null);
}
public static int ExecNoQry(CommandType CmdType, string SqlStr, OracleParameter[] Params)
{
return ExecNoQry((string)null, CmdType, SqlStr, Params);
}
public static int ExecNoQry(string ConnStr, CommandType CmdType, string SqlStr, OracleParameter[] Params)
{
if (ConnStr == null)
{
ConnStr = DBString;
}
int result = -1;
OracleConnection Conn = new OracleConnection(ConnStr);
try
{
Conn.Open();
result = ExecNoQry(Conn, CmdType, SqlStr, Params);
}
catch (Exception e)
{
// throw e;
// new Exception(e.Message + "\n" + SqlStr);
ErrorMessage = e.Message.ToString();
}
finally
{
Conn.Close();
}
return result;
}
public static int ExecNoQry(OracleConnection Conn, CommandType CmdType, string SqlStr, OracleParameter[] Params)
{
OracleCommand Cmd = new OracleCommand(SqlStr, (OracleConnection)Conn);
Cmd.CommandType = CmdType;
if (Params != null)
{
for (int i = 0; i < Params.Length; i++)
{
Cmd.Parameters.Add((OracleParameter)Params[i]);
}
}
int Rc = Cmd.ExecuteNonQuery();
return Rc;
}
public static int ExecNoQry(OracleTransaction Tran, OracleConnection Conn, CommandType CmdType, string SqlStr, OracleParameter[] Params)
{
OracleCommand Cmd = new OracleCommand(SqlStr, (OracleConnection)Conn, (OracleTransaction)Tran);
Cmd.CommandType = CmdType;
if (Params != null)
{
for (int i = 0; i < Params.Length; i++)
{
Cmd.Parameters.Add((OracleParameter)Params[i]);
}
}
int Rc = Cmd.ExecuteNonQuery();
return Rc;
}
public static void ExecNoQry(string[] Sqls)
{
ExecNoQry(null, Sqls);
}
public static void ExecNoQry(string ConnStr, string[] Sqls)
{
if (ConnStr == null)
{
ConnStr = DBString;
}
using (OracleConnection Conn = new OracleConnection(ConnStr))
{
Conn.Open();
OracleTransaction Tran = Conn.BeginTransaction();
try
{
OracleCommand Cmd = null;
for (int i = 0; i < Sqls.Length; i++)
{
Cmd = new OracleCommand(Sqls[i], Conn, Tran);
Cmd.ExecuteNonQuery();
}
Tran.Commit();
}
catch (Exception e)
{
Tran.Rollback();
throw e;
}
finally
{
Conn.Close();
}
}
}
//下面是ExecScalar的N多重载函数
//使用ExecuteScalar
public static object ExecScalar(string SqlStr)
{
return ExecScalar(null, SqlStr);
}
public static object ExecScalar(string ConnStr, string SqlStr)
{
if (ConnStr == null)
{
ConnStr = DBString;
}
OracleConnection Conn = new OracleConnection(ConnStr);
try
{
OracleCommand Cmd = new OracleCommand(SqlStr, Conn);
object Obj = null;
Conn.Open();
Obj = Cmd.ExecuteScalar();
Conn.Close();
return Obj;
}
catch (Exception e)
{
throw new Exception(e.Message + "\n" + SqlStr);
}
finally
{
Conn.Close();
}
}
//下面是ExecReader的N多重载函数
public static OracleDataReader ExecReader(string ConnStr, string SqlStr)
{
return ExecReader(ConnStr, SqlStr, null);
}
public static OracleDataReader ExecReader(string SqlStr)
{
return ExecReader(null, SqlStr, null);
}
public static OracleDataReader ExecReader(string ConnStr, string SqlStr, OracleParameterCollection Params)
{
if (ConnStr == null)
{
ConnStr = DBString;
}
OracleConnection Conn = new OracleConnection(ConnStr);
try
{
if (Params != null)
{
OracleParameter Param = null;
for (int i = 0; i < Params.Count; i++)
{
Param = Params[i];
SqlStr = SqlStr.Replace(Param.ParameterName, ComPublic.uPublic.QuoteStr(Param.Value.ToString()));
}
}
OracleCommand Cmd = new OracleCommand(SqlStr, Conn);
Conn.Open();
OracleDataReader dr = Cmd.ExecuteReader(CommandBehavior.CloseConnection);
return dr;
}
catch (Exception e)
{
throw new Exception(e.Message + "\n" + SqlStr);
}
finally
{
//con.Close();
}
}
//下面是ExecDataSet的N多重载函数
public static DataSet ExecDataSet(string ConnStr, string SqlStr)
{
return ExecDataSet(ConnStr, SqlStr, null);
}
public static DataSet ExecDataSet(string SqlStr)
{
return ExecDataSet(null, SqlStr, null);
}
public static DataSet ExecDataSet(string ConnStr, string SqlStr, OracleParameterCollection Parameters)
{
if (ConnStr == null)
{
ConnStr = DBString;
}
using (OracleConnection Conn = new OracleConnection(ConnStr))
{
if (Parameters != null)
{
OracleParameter Param = null;
for (int i = 0; i < Parameters.Count; i++)
{
Param = Parameters[i];
SqlStr = SqlStr.Replace(Param.ParameterName, ComPublic.uPublic.QuoteStr(Param.Value.ToString()));
}
}
OracleCommand Cmd = new OracleCommand(SqlStr, Conn);
Conn.Open();
OracleDataAdapter Oda = new OracleDataAdapter(Cmd);
DataSet Ds = new DataSet();
Oda.Fill(Ds);
Conn.Close();
return Ds;
}
}
//下面是ExecDataTable的N多重载函数
public static DataTable ExecDataTable(string ConnStr, string SqlStr)
{
return ExecDataTable(ConnStr, SqlStr, null);
}
public static DataTable ExecDataTable(string SqlStr)
{
return ExecDataTable(null, SqlStr, null);
}
public static DataTable ExecDataTable(string ConnStr, string SqlStr, OracleParameterCollection Parameters)
{
if (ConnStr == null)
{
ConnStr = DBString;
}
using (OracleConnection Conn = new OracleConnection(ConnStr))
{
if (Parameters != null)
{
OracleParameter Param = null;
for (int i = 0; i < Parameters.Count; i++)
{
Param = Parameters[i];
SqlStr = SqlStr.Replace(Param.ParameterName, ComPublic.uPublic.QuoteStr(Param.Value.ToString()));
}
}
OracleCommand Cmd = new OracleCommand(SqlStr, Conn);
Conn.Open();
OracleDataAdapter Oda = new OracleDataAdapter(Cmd);
DataTable Dt = new DataTable();
Oda.Fill(Dt);
Conn.Close();
return Dt;
}
}
public static DataTable ExecDataTable(string ConnStr, CommandType CmdType, string SqlStr, OracleParameter[] Parameters)
{
if (ConnStr == null)
{
ConnStr = DBString;
}
using (OracleConnection Conn = new OracleConnection(ConnStr))
{
OracleCommand Cmd = new OracleCommand(SqlStr, Conn);
Cmd.CommandType = CmdType;
if (Parameters != null)
{
for (int i = 0; i < Parameters.Length; i++)
{
Cmd.Parameters.Add((OracleParameter)Parameters[i]);
}
}
Conn.Open();
OracleDataAdapter Oda = new OracleDataAdapter(Cmd);
DataTable Dt = new DataTable();
Oda.Fill(Dt);
Conn.Close();
return Dt;
}
}
//把数据库取出来的数据,给到输入框内,以便在上面修改记录
public static void DBInfoToControls(string sSql, string[] sqlFieldName, System.Web.UI.Control[] initcontrol)
{
OracleDataReader datareader = SysHelper.DBHelper.ExecReader(sSql);
if (datareader.Read())
{
for (int i = 0; i < sqlFieldName.Length; i++)
{
if (initcontrol[i] is TextBox)
{
(initcontrol[i] as TextBox).Text = datareader[sqlFieldName[i].ToString()].ToString();
}
else if (initcontrol[i] is DropDownList)
{
try
{
(initcontrol[i] as DropDownList).SelectedValue = datareader[sqlFieldName[i].ToString()].ToString().Trim();
}
catch(Exception e)
{
(initcontrol[i] as DropDownList).SelectedValue = "";
}
}
else
{
;
}
}
}
}
public static void DBInfoToControl(string sSql, string[] sqlFieldName, System.Web.UI.Control[] initcontrol)
{
OracleDataReader datareader = SysHelper.DBHelper.ExecReader(sSql);
if (datareader.Read())
{
for (int i = 0; i < sqlFieldName.Length; i++)
{
if (sqlFieldName[i].Trim().Length > 3)
{
if (initcontrol[i] is TextBox)
{
if (sqlFieldName[i].Trim().ToUpper().Substring(sqlFieldName[i].Trim().Length - 3, 3).Equals("DATE") || sqlFieldName[i].Trim().ToUpper().Substring(sqlFieldName[i].Trim().Length - 4, 4).Equals("DATE"))
{
if (datareader[sqlFieldName[i].ToString()].ToString().Trim() == "")
(initcontrol[i] as TextBox).Text = datareader[sqlFieldName[i].ToString()].ToString().Trim();
else
{
DateTime date = System.DateTime.Parse(datareader[sqlFieldName[i].ToString()].ToString().Trim());
(initcontrol[i] as TextBox).Text = string.Format("{0:yyyy-MM-dd}", date);
}
}
else
(initcontrol[i] as TextBox).Text = datareader[sqlFieldName[i].ToString()].ToString().Trim();
}
else if (initcontrol[i] is DropDownList)
{
(initcontrol[i] as DropDownList).SelectedValue = datareader[sqlFieldName[i].ToString()].ToString().Trim();
}
else if (initcontrol[i] is CheckBox)
{
if (datareader[sqlFieldName[i].ToString()].ToString().Trim().Equals("Y"))
{
(initcontrol[i] as CheckBox).Checked = true;
}
else if (datareader[sqlFieldName[i].ToString()].ToString().Trim().Equals("N"))
{
(initcontrol[i] as CheckBox).Checked = false;
}
}
else if (initcontrol[i] is Label)
{
(initcontrol[i] as Label).Text = datareader[sqlFieldName[i].ToString()].ToString().Trim();
}
else if (initcontrol[i] is RadioButtonList)
{
(initcontrol[i] as RadioButtonList).SelectedValue = datareader[sqlFieldName[i].ToString()].ToString().Trim();
}
}
else
{
if (initcontrol[i] is TextBox)
{
(initcontrol[i] as TextBox).Text = datareader[sqlFieldName[i].ToString()].ToString();
}
else if (initcontrol[i] is DropDownList)
{
(initcontrol[i] as DropDownList).SelectedValue = datareader[sqlFieldName[i].ToString()].ToString();
}
else if (initcontrol[i] is CheckBox)
{
if (datareader[sqlFieldName[i].ToString()].ToString().Trim().Equals("Y"))
{
(initcontrol[i] as CheckBox).Checked = true;
}
else if (datareader[sqlFieldName[i].ToString()].ToString().Trim().Equals("N"))
{
(initcontrol[i] as CheckBox).Checked = false;
}
}
else if (initcontrol[i] is Label)
{
(initcontrol[i] as Label).Text = datareader[sqlFieldName[i].ToString()].ToString().Trim();
}
else if (initcontrol[i] is RadioButtonList)
{
(initcontrol[i] as RadioButtonList).SelectedValue = datareader[sqlFieldName[i].ToString()].ToString().Trim();
}
}
}
}
}
public static DataSet Class_ExcelSql(string excelPath, string sheetName)
{
OleDbConnection myConn = new OleDbConnection();
OleDbCommand myComm = new OleDbCommand();
OleDbDataAdapter myAdap = new OleDbDataAdapter();
DataSet myDs = new DataSet();
if (!File.Exists(excelPath))
{
//Response.Write("<script>alter('excel文件不能为空')</scirpt>");
return null;
}
string Conn_Str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelPath + ";Extended Properties='Excel 8.0;HDR=Yes;'";
try
{
myConn.ConnectionString = Conn_Str;
myConn.Open();
myComm.Connection = myConn;
myComm.CommandText = "select * from [" + sheetName + "$]";
// Mydr = myComm.ExecuteReader();
myAdap.SelectCommand = myComm;
myAdap.Fill(myDs);
}
catch (OleDbException Err_My)
{
// Response.Write("<script>alter('提示')</scirpt>");
//MessageBox.Show(Err_My.Message, "提示");
}
if (myConn.State == ConnectionState.Open)
{
myConn.Close();
myConn.Dispose();
}
myComm.Dispose();
myAdap.Dispose();
return myDs;
}
public static string importExcelToDB(string excelPath, string sheetName, string table_Name, string change_no)
{
string fieldvalue = "";
DataTable dt = null;
DataTableReader dr = Class_ExcelSql(excelPath, sheetName).Tables[0].CreateDataReader();
using (OracleConnection conn = new OracleConnection(DBString))
{
conn.Open();
OracleTransaction transaction = conn.BeginTransaction();
string sql = "INSERT INTO " + table_Name + "(RPT_ITEM,";
string insert_sql = "";
for (int j = 0; j < dr.FieldCount; j++)
{
if (j != dr.FieldCount - 1)
sql += dr.GetName(j) + ",";
else
sql += dr.GetName(j) + ") VALUES(";
}
sql += "'" + change_no + "'";
int i = 0;
while (dr.Read() && (!dr.GetValue(0).ToString().Equals("")))
{
insert_sql = sql + ",";
for (int j = 0; j < dr.FieldCount; j++)
{
fieldvalue = dr.GetValue(j).ToString().Trim();
if (fieldvalue.Contains("'"))
fieldvalue = fieldvalue.Replace("'", "''");
if (dr.GetName(j).Length > 4)
{
if (!dr.GetName(j).Substring(dr.GetName(j).Length - 4, 4).Equals("DATE"))
{
if (j != dr.FieldCount - 1)
insert_sql += "'" + fieldvalue + "',";
else
insert_sql += "'" + fieldvalue + "')";
}
else
{
if (j != dr.FieldCount - 1)
if (dr.GetValue(j).ToString().Trim().Contains(" "))
insert_sql += "TO_DATE('" + dr.GetValue(j).ToString().Trim().Substring(0, dr.GetValue(j).ToString().Trim().IndexOf(" ")) + "','YYYY-MM-DD'),";
else
insert_sql += "TO_DATE('" + dr.GetValue(j).ToString().Trim() + "','YYYY-MM-DD'),";
else
if (dr.GetValue(j).ToString().Trim().Contains(" "))
insert_sql += "TO_DATE('" + dr.GetValue(j).ToString().Trim().Substring(0, dr.GetValue(j).ToString().Trim().IndexOf(" ")) + "','YYYY-MM-DD'))";
else
insert_sql += "TO_DATE('" + dr.GetValue(j).ToString().Trim() + "','YYYY-MM-DD'))";
}
}
else
{
if (j != dr.FieldCount - 1)
insert_sql += "'" + fieldvalue + "',";
else
insert_sql += "'" + fieldvalue + "')";
}
}
try
{
ExecNoQry(insert_sql);
i++;
}
catch (Exception ex)
{
transaction.Rollback();
return ex.Message;
}
}
transaction.Commit();
return "Import Success";
}
}
}
}