個人資料庫幫助類：提供兩種訪問方式OleDb(需安裝Oracle客戶端)和 Oracle.ManagedDataAccess.Client（需Oracle.ManagedDataAccess.dll) 多說無益上代碼: #region Info/* ************************* ...
個人資料庫幫助類：提供兩種訪問方式OleDb(需安裝Oracle客戶端)和 Oracle.ManagedDataAccess.Client（需Oracle.ManagedDataAccess.dll)
多說無益上代碼:
#region Info
/*
******************************【此類描述】*******************************
*
*作者:高發金
*
*創建時間:2017/11/7 10:20:35
*
*文件名稱:DbHelper
*
*版本信息:V1.0.0
*
*修改者: 高發金 修改時間:2017/11/7 10:20:35
*
*當前的CLR版本:4.0.30319.42000
*
*修改說明:
*
*
*
*************************************************************************
*/
#endregion
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Oracle.ManagedDataAccess.Client;
using Oracle.ManagedDataAccess.Types;
using System.Data;
using System.Data.OleDb;
using System.Data.Common;
using Gaofajin.Attribute;
namespace Gaofajin.Data
{
public class DbHelper
{
public bool IsOledb { get; set; } = false;
DbConnection connection;
DbCommand cmd;
DbCommandBuilder cmb;
DbDataAdapter adp;
DbTransaction transaction;
public String DBVersion
{
get
{
if (connection != null)
return connection.ServerVersion;
return "未知";
}
}
public DbConnection GetConnection() => connection;
string Try_catch(Action a)
{
try
{
a();
return "操作成功！";
}
catch (Exception ex)
{
return ex.Message;
}
}
public void SetConnectionString(string constr)
{
if (IsOledb)
connection = new OleDbConnection();
else connection = new OracleConnection();
connection.ConnectionString = constr;
}
public string Open()=> Try_catch(connection.Open);
public string Close()=>Try_catch(connection.Close);
public DbResult GetData(string sql)
{
try
{
if (IsOledb)
{
cmd = new OleDbCommand(sql, (OleDbConnection)connection);
adp = new OleDbDataAdapter((OleDbCommand)cmd);
}
else
{
cmd = new OracleCommand(sql, (OracleConnection)connection);
adp = new OracleDataAdapter((OracleCommand)cmd);
}
DataTable dt = new DataTable();
int m = adp.Fill(dt);
return new DbResult(m, "執行查詢成功！", dt);
}
catch (Exception ex)
{
return new DbResult(-1, ex.Message, null);
}
}
public DbResult GetDataforUpdate(string sql)
{
try
{
if (IsOledb)
{
cmd = new OleDbCommand(sql, (OleDbConnection)connection);
adp = new OleDbDataAdapter((OleDbCommand)cmd);
cmb = new OleDbCommandBuilder((OleDbDataAdapter)adp);
}
else
{
cmd = new OracleCommand(sql, (OracleConnection)connection);
adp = new OracleDataAdapter((OracleCommand)cmd);
cmb = new OracleCommandBuilder((OracleDataAdapter)adp);
}
DataTable dt = new DataTable();
int m = adp.Fill(dt);
return new DbResult(m, "執行查詢成功！", dt);
}
catch (Exception ex)
{
return new DbResult(-1, ex.Message, null);
}
}
public void UpdateFrom(DataTable dt)
{
if (adp != null)
adp.Update(dt);
}
public DbResult ExeSql(string sql)
{
try
{
if (IsOledb)
{
cmd = new OleDbCommand(sql, (OleDbConnection)connection);
}
else
{
cmd = new OracleCommand(sql, (OracleConnection)connection);
}
int m = cmd.ExecuteNonQuery();
return new DbResult(m, "執行SQL成功！", null);
}
catch (Exception ex)
{
return new DbResult(-1, ex.Message, null);
}
}
public DbResult Proc(string ProcName, IDataParameter[] paras)
{
try
{
if (IsOledb)
{
cmd = new OleDbCommand
{
Connection = (OleDbConnection)connection,
CommandText = ProcName,
CommandType = CommandType.StoredProcedure
};
}
else
{
cmd = new OracleCommand
{
Connection = (OracleConnection)connection,
CommandText = ProcName,
CommandType = CommandType.StoredProcedure
};
}
foreach (var para in paras)
{
cmd.Parameters.Add(para);
}
int m = cmd.ExecuteNonQuery();
return new DbResult(m, "執行存儲過程成功！", null);
}
catch (Exception ex)
{
return new DbResult(-1, ex.Message, null);
}
}
public DbResult Procselect(string ProcName, IDataParameter[] paras)
{
try
{
if (IsOledb)
{
adp = new OleDbDataAdapter
{
SelectCommand = new OleDbCommand
{
Connection = (OleDbConnection)connection,
CommandText = ProcName,
CommandType = CommandType.StoredProcedure
}
};
}
else
{
adp = new OracleDataAdapter
{
SelectCommand = new OracleCommand
{
Connection = (OracleConnection)connection,
CommandText = ProcName,
CommandType = CommandType.StoredProcedure
}
};
}
adp.SelectCommand.Parameters.AddRange(paras);
DataTable dt = new DataTable();
int m = adp.Fill(dt);
return new DbResult(m, "執行存儲過程成功！", dt);
}
catch (Exception ex)
{
return new DbResult(-1, ex.Message, null);
}
}
public void BeginTrans()
{
transaction = connection.BeginTransaction();
}
public void CommitTrans()
{
if (transaction != null)
transaction.Commit();
}
public void RollBack()
{
if (transaction != null)
transaction.Rollback();
}
public List<string> GetAllTableName(string schemaName, String CollName, string User)
{
DataTable dt = connection.GetSchema(schemaName);
List<string> Tablesname = new List<string>();
if (dt != null)
{
DataRow[] drs = dt.Select(string.Format("TABLE_SCHEMA='{0}'and TABLE_TYPE LIKE'%{1}%'", User, CollName));
foreach (var dr in drs)
{
Tablesname.Add(dr["TABLE_NAME"].ToString());
}
}
return Tablesname;
}
string GetConnecttionString(string host, string port, string service_name, string user, string pwd)
{
if (string.IsNullOrEmpty(service_name))
{
System.Windows.Forms.MessageBox.Show("Service_Name不能為空或者null", "錯誤", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
return null;
}
if (string.IsNullOrEmpty(user))
{
System.Windows.Forms.MessageBox.Show("userName不能為空或者null", "錯誤", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
return null;
}
if (string.IsNullOrEmpty(pwd))
{
System.Windows.Forms.MessageBox.Show("password不能為空或者null", "錯誤", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
return null;
}
string str = "DATA SOURCE=\"(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = {0})(PORT = {1})))(CONNECT_DATA = (SERVICE_NAME = {2})))\";USER ID={3};PASSWORD={4}";
return string.Format(str, (string.IsNullOrEmpty(host) ? "localhost" : host), (string.IsNullOrEmpty(port)? 1521 :int.Parse(port)), service_name, user, pwd);
}
string GetConnecttionString(string Name, string user, string pwd)
{
if (string.IsNullOrEmpty(Name))
{
System.Windows.Forms.MessageBox.Show("Name不能為空或者null", "錯誤", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
return null;
}
if (string.IsNullOrEmpty(user))
{
System.Windows.Forms.MessageBox.Show("user不能為空或者null", "錯誤", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
return null;
}
if (string.IsNullOrEmpty(pwd))
{
System.Windows.Forms.MessageBox.Show("password不能為空或者null", "錯誤", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
return null;
}
string str = "Provider = MSDAORA.1; User ID = {0}; password ={1}; Data Source = {2}";
return string.Format(str, user, pwd, Name);
}
public string GetConnecttionString(params string[] str) => (IsOledb == true) ? GetConnecttionString(str[0], str[1], str[2]) : GetConnecttionString(str[0], str[1], str[2], str[3], str[4]);
}
public class DbResult
{
public int ErrCode { get; }
public string ErrMsg { get; }
public DataTable Data { get; }
public DbResult(int ecode, string emsg, DataTable dt)
{
ErrCode = ecode;
ErrMsg = emsg;
Data = dt;
}
}
}
