public override void ExecuteNoQuery(string sql, DBOParameterCollection dbp)
{
if (sqlcomm == null)
{
sqlcomm = new SqlCommand(sql, this.sqlconn);
sqlcomm.CommandTimeout = 90;
}
else
{
sqlcomm.CommandText = sql;
}
if (dbp != null)
{
FillParametersToCommand(sqlcomm, dbp);
}
if (_s == DBStatus.Begin_Trans)
{
sqlcomm.Transaction = this.trans;
}
try
{
sqlcomm.ExecuteNonQuery();
}
finally
{
sqlcomm.Cancel();
sqlcomm = null;
}
}
public override void ExecuteNoQuery(string sql, DBOParameterCollection dbp)
{
if (sqlcomm == null)
{
sqlcomm = new MySqlCommand(sql, this.conn);
sqlcomm.CommandTimeout = CommandTimeOut;
}
else
{
sqlcomm.CommandText = sql;
}
if (dbp != null)
{
FillParametersToCommand(sqlcomm, dbp);
}
if (_s == DBStatus.Begin_Trans)
{
sqlcomm.Transaction = this.tran;
}
using (sqlcomm)
{
sqlcomm.ExecuteNonQuery();
}
}
public override void Delete(object data, string toTable)
{
if (data == null)
{
return;
}
string sql = "delete from " + toTable;
string where = "";
DBOParameterCollection dpc = new DBOParameterCollection();
if (data is FrameDLRObject)
{
var dobj = (FrameDLRObject)data;
var keycols = dobj.Keys;
foreach (var key in keycols)
{
dpc.SetValue(key, dobj.GetValue(key));
where += (where == "" ? " where " : " and ") + $" {key}=@{key}";
}
}
else
{
var fields = data.GetType().GetTypeInfo().GetFields(BindingFlags.Instance | BindingFlags.Public).ToList();
foreach (var f in fields)
{
dpc.SetValue(f.Name, f.GetValue(data));
where += (where == "" ? " where " : " and ") + $" {f.Name}=@{f.Name}";
}
}
sql = sql + where;
this.ExecuteNoQuery(sql, dpc);
}
public override Data.DataSetStd Query(string sql, DBOParameterCollection dbp)
{
if (_s == DBStatus.Close)
{
DoOpen();
}
OracleCommand cmd;
DataSetStd ds = new DataSetStd();
OracleDataReader ddr = null;
var sqlarr = ToSQLArray(sql);
var index = 1;
foreach (string s in sqlarr)
{
if (s.Trim() == "")
{
continue;
}
using (cmd = new OracleCommand(s, conn))
{
try
{
//如果事務開啟，則使用事務的方式
if (this._s == DBStatus.Begin_Trans)
{
cmd.Transaction = this.tran;
}
//如果有參數
if (dbp != null)
{
FillParametersToCommand(cmd, dbp);
}
if (s.Trim().ToLower().StartsWith("select") || s.ToLower().IndexOf(" into ") < 0)
{
ddr = cmd.ExecuteReader();
ds.Tables.AddRange(DataSetStd.FillData(ddr).Tables);
index++;
}
else
{
cmd.ExecuteNonQuery();
}
}
finally
{
cmd.Dispose();
cmd = null;
}
}
}
return(ds);
}
public override void ExecuteNoQuery(string sql, DBOParameterCollection dbp)
{
if (_s == DBStatus.Close)
{
DoOpen();
}
OracleCommand cmd;
DataSetStd ds = new DataSetStd();
var sqlarr = ToSQLArray(sql);
foreach (var s in sqlarr)
{
if (s.Trim() == "")
{
continue;
}
using (cmd = new OracleCommand(s, conn))
{
if (cmd.Connection.State == ConnectionState.Closed)
{
cmd.Connection.Open();
}
try
{
//如果事務開啟，則使用事務的方式
if (this._s == DBStatus.Begin_Trans)
{
cmd.Transaction = this.tran;
}
cmd.CommandText = s;
//如果有參數
if (dbp != null)
{
FillParametersToCommand(cmd, dbp, s);
}
if (_s == DBStatus.Begin_Trans)
{
cmd.Transaction = this.tran;
}
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
cmd = null;
}
}
}
}
public override Data.DataSetStd Query(string sql, DBOParameterCollection dbp)
{
if (sqlcomm == null)
{
sqlcomm = new MySqlCommand(sql, this.conn);
sqlcomm.CommandTimeout = CommandTimeOut;
}
else
{
sqlcomm.CommandText = sql;
}
//如果事務開啟，則使用事務的方式
if (this._s == DBStatus.Begin_Trans)
{
sqlcomm.Transaction = this.tran;
}
DataSetStd ds = new DataSetStd();
MySqlDataReader ddr = null;
try
{
//如果有參數
if (dbp != null)
{
FillParametersToCommand(sqlcomm, dbp);
}
ddr = sqlcomm.ExecuteReader();
ds = DataSetStd.FillData(ddr);
}
//catch (Exception ex)
//{
// Console.WriteLine(ex.Message);
// throw ex;
//}
finally
{
if (ddr != null)
{
ddr.Close();
ddr.Dispose();
}
sqlcomm.Dispose();
sqlcomm = null;
}
return(ds);
}
private void BulkInsert(object data, string toTable)
{
if (data == null)
{
return;
}
var sql = $"INSERT INTO {toTable}(#col#)VALUES";
var parameters = new DBOParameterCollection();
if (data is IEnumerable <FrameDLRObject> )
{
var cols = "";
var values = new StringBuilder();
var param = "";
var list = (IEnumerable <FrameDLRObject>)data;
if (list.Count() <= 0)
{
return;
}
if (list.ElementAt(0).Keys.Count <= 0)
{
return;
}
foreach (var k in list.ElementAt(0).Keys)
{
cols += $",{k}";
param += $",{ParameterFlagChar}{k}#no#";
}
sql = sql.Replace("#col#", cols.Substring(1));
var index = 0;
foreach (var item in (IEnumerable <FrameDLRObject>)data)
{
values.AppendLine($",({param.Substring(1).Replace("#no#", index + "")})");
foreach (var key in item.Keys)
{
parameters.Add($"{key}{index}", item.GetValue(key));
}
index++;
}
sql += values.ToString().Substring(1);
}
ExecuteNoQuery(sql, parameters);
}
/// <summary>
/// 通過sql獲得相關的欄位列表
/// </summary>
/// <param name="sql"></param>
/// <param name="p"></param>
/// <returns></returns>
private string[] GetColumnsNameBySql(string sql, DBOParameterCollection p)
{
if (sql != sql4PageOrder || columnsName4PageOrder == null)
{
sql4PageOrder = sql;
DataSetStd dss = Query(sql, p);
if (dss != null)
{
columnsName4PageOrder = dss[0].ColumnNames;
}
}
return(columnsName4PageOrder);
}
/// <summary>
/// 設定翻頁條件，啟動翻頁機制
/// </summary>
/// <param name="p"></param>
public void StartPageByCondition(DBAPageP p)
{
this._Count_of_OnePage = 0;
this._current_page = 0;
this._source_sql_page = "";
this._source_sql_parameters_page = null;
this._to_Page = 0;
this._total_page = 0;
this._is_page = false;
this._rownumber_order_by = "";
this._row_count = 0;
this._Count_of_OnePage = p.Count_of_OnePage;
this._source_sql_page = p.SQL;
this._source_sql_parameters_page = p.SQL_Parameters;
if (!string.IsNullOrEmpty(p.OrderBy))
{
this._rownumber_order_by = p.OrderBy;
}
this._is_page = true;
if (p.CurrentPage >= 0)
{
this._current_page = p.CurrentPage;
}
if (this._source_sql_page.Trim().EndsWith(";"))
{
this._source_sql_page = this._source_sql_page.Trim().Substring(0, this._source_sql_page.Trim().Length - 1);
}
string sql = "select count(1) from (" + this._source_sql_page + ") t";
DBOParameterCollection SQL_Parameters = this._source_sql_parameters_page;
DataSetStd dss = Query(sql, SQL_Parameters);
IntStd totalcount = IntStd.ParseStd(dss.GetValue(0, 0));
if (totalcount != null && totalcount != 0)
{
_row_count = totalcount;
if (totalcount.Value % this._Count_of_OnePage == 0)
{
this._total_page = (int)(totalcount.Value / this._Count_of_OnePage);
}
else
{
this._total_page = (int)(totalcount.Value / this._Count_of_OnePage) + 1;
}
}
}
private void FillParametersToCommand(DbCommand command, DBOParameterCollection parameters, string sql)
{
DBOParameterCollection filtercollection = new DBOParameterCollection();
string regstr = @"(?<=:)[A-Za-z0-9_]+\d*";
Regex reg = new Regex(regstr);
foreach (Match m in reg.Matches(sql))
{
if (parameters.ContainsKey(m.Value))
{
filtercollection.Add(m.Value, parameters[m.Value]);
}
}
FillParametersToCommand(command, filtercollection);
}
/// <summary>
/// 依據Dictionary填充Command物件參數集
/// </summary>
/// <param name="command">命令物件</param>
/// <param name="parameters">參數</param>
private void FillParametersToCommand(DbCommand command, DBOParameterCollection parameters)
{
foreach (string parameterName in parameters.Keys)
{
DbParameter idparameter = null;
if (!command.Parameters.Contains("@" + parameters[parameterName].ParameterName))
{
idparameter = command.CreateParameter();
idparameter.ParameterName = string.Concat("@", parameters[parameterName].ParameterName);
command.Parameters.Add(idparameter);
}
else
{
idparameter = command.Parameters["@" + parameterName];
}
if (parameters[parameterName].DataLength > 0)
{
idparameter.Direction = parameters[parameterName].Direction;
idparameter.DbType = parameters[parameterName].DataType;
idparameter.Size = parameters[parameterName].DataLength;
}
if (parameters[parameterName].ParameterValue == null)
{
idparameter.Value = DBNull.Value;
}
else if (ComFunc.nvl(parameters[parameterName].ParameterValue) == "")
{
if (parameters[parameterName].DataType != DbType.String)
{
idparameter.Value = DBNull.Value;
}
else
{
idparameter.Value = parameters[parameterName].ParameterValue;
}
}
else
{
idparameter.Value = parameters[parameterName].ParameterValue;
}
}
}
public DataSetStd QueryWithKey(string sql, DBOParameterCollection dbp)
{
if (sqlcomm == null)
{
sqlcomm = new SqlCommand(sql, this.sqlconn);
sqlcomm.CommandTimeout = 90;
}
else
{
sqlcomm.CommandText = sql;
}
//如果事務開啟，則使用事務的方式
if (this._s == DBStatus.Begin_Trans)
{
sqlcomm.Transaction = this.trans;
}
DataSetStd ds = new DataSetStd();
try
{
//如果有參數
if (dbp != null)
{
FillParametersToCommand(sqlcomm, dbp);
}
SqlDataAdapter sqlAdapter = new SqlDataAdapter(this.sqlcomm);
sqlAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
sqlAdapter.Fill(ds);
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlcomm.Cancel();
sqlcomm = null;
}
return(ds);
}
public override void ExecuteNoQuery(string sql, DBOParameterCollection dbp)
{
DB2Command cmd;
DataSetStd ds = new DataSetStd();
using (cmd = new DB2Command(sql, conn))
{
try
{
//如果事務開啟，則使用事務的方式
if (this._s == DBStatus.Begin_Trans)
{
cmd.Transaction = this.tran;
}
cmd.CommandText = sql;
//如果有參數
if (dbp != null)
{
FillParametersToCommand(cmd, dbp);
}
if (_s == DBStatus.Begin_Trans)
{
cmd.Transaction = this.tran;
}
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Cancel();
cmd = null;
}
}
}
public override Data.DataSetStd Query(string sql, DBOParameterCollection dbp)
{
DB2Command cmd;
DataSetStd ds = new DataSetStd();
using (cmd = new DB2Command(sql, conn))
{
try
{
//如果事務開啟，則使用事務的方式
if (this._s == DBStatus.Begin_Trans)
{
cmd.Transaction = this.tran;
}
DB2DataAdapter rd = new DB2DataAdapter(cmd);
//如果有參數
if (dbp != null)
{
FillParametersToCommand(cmd, dbp);
}
rd.Fill(ds);
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Cancel();
cmd = null;
}
}
return(ds);
}
public override DBDataCollection ExcuteProcedure(string sp_name, bool isReturnDataSet, ref DBOParameterCollection dbp)
{
DBDataCollection rtn = new DBDataCollection();
rtn.IsSuccess = false;
DataSetStd ds = new DataSetStd();
SqlCommand dc = null;//new SqlCommand(p.StoreProcureName, this.sqlconn);
if (this._s == DBStatus.Begin_Trans)
{
dc = new SqlCommand(sp_name, this.sqlconn, this.trans);
}
else
{
dc = new SqlCommand(sp_name, this.sqlconn);
}
dc.CommandTimeout = 90;
dc.CommandType = CommandType.StoredProcedure;
FillParametersToCommand(dc, dbp);
try
{
if (isReturnDataSet)
{
SqlDataAdapter sqlDa = new SqlDataAdapter();
sqlDa.SelectCommand = dc;
sqlDa.Fill(ds);
rtn.ReturnDataSet = ds;
}
else
{
dc.ExecuteNonQuery();
}
//獲取返回值
foreach (SqlParameter sp in dc.Parameters)
{
if (sp.Direction == ParameterDirection.Output || sp.Direction == ParameterDirection.InputOutput || sp.Direction == ParameterDirection.ReturnValue)
{
rtn.SetValue(sp.ParameterName.Replace("@", ""), sp.Value);
}
}
rtn.IsSuccess = true;
}
finally
{
dc.Cancel();
dc = null;
}
return(rtn);
}
protected override Data.DataTableStd QueryByPage(int startRow, int endRow, int toPage, int count_of_page, string sql, string orderby, DBOParameterCollection p)
{
var newsql = sql;
if (orderby != null && orderby != "")
{
newsql += " order by " + orderby;
}
newsql = string.Format(newsql + " limit {0} offset {1}", count_of_page, startRow - 1);
//GlobalCommon.Logger.WriteLog(LoggerLevel.DEBUG, $"MySql QueryByPage Sql={newsql}");
DataSetStd dss = Query(newsql, p);
if (dss == null)
{
return(null);
}
else
{
return(dss[0]);
}
}
protected override DataTableStd QueryByPage(int startRow, int endRow, int toPage, int count_of_page, string sql, string orderby, DBOParameterCollection p)
{
string orderby4page = orderby;
if (orderby == null || orderby == "")
{
orderby4page = GetColumnsNameBySql(sql, p)[0] + " ASC ";
}
string newsql = @"WITH [test] AS "
+ "(select *,ROW_NUMBER() OVER (order by " + orderby4page + " ) as RowNumber from (" + sql + ") table2)"
+ "SELECT TOP " + count_of_page + " * "
+ "FROM [test] "
+ "WHERE (RowNumber > "
+ "(SELECT ISNULL(max(RowNumber),0) "
+ "FROM (SELECT TOP (" + (count_of_page * (toPage - 1)) + ") RowNumber "
+ "FROM [test] "
+ "ORDER BY RowNumber) AS T)) ";
DataSetStd dss = Query(newsql, p);
if (dss == null)
{
return(null);
}
else
{
return(dss[0]);
}
}
/// <summary> /// 執行存儲過程 /// </summary> /// <param name="sp_name"></param> /// <param name="isReturnDataSet"></param> /// <param name="dbp"></param> /// <returns></returns> public abstract DBDataCollection ExcuteProcedure(string sp_name, bool isReturnDataSet, ref DBOParameterCollection dbp);
protected override DataTableStd QueryByPage(int startRow, int endRow, int toPage, int count_of_page, string sql, string orderby, DBOParameterCollection p)
{
string orderby4page = orderby;
if (orderby == null || orderby == "")
{
orderby4page = GetColumnsNameBySql(sql, p)[0] + " ASC ";
}
string newsql = @"select *,NEWID() as _page_row_guid into #tmptt from(
" + sql + @")a
SELECT TOP " + count_of_page + @" *
FROM #tmptt
WHERE _page_row_guid NOT IN
(
SELECT TOP " + (count_of_page * (toPage - 1)) + @" _page_row_guid FROM #tmptt ORDER BY " + orderby4page + @"
)
ORDER BY " + orderby4page + @"
drop table #tmptt";
DataSetStd dss = Query(newsql, p);
if (dss == null)
{
return(null);
}
else
{
return(dss[0]);
}
}
/// <summary> /// 执行非查询操作 /// </summary> /// <param name="sql"></param> /// <param name="dbp"></param> public abstract void ExecuteNoQuery(string sql, DBOParameterCollection dbp);
public override DBDataCollection ExcuteProcedure(string sp_name, bool isReturnDataSet, ref DBOParameterCollection dbp)
{
DBDataCollection rtn = new DBDataCollection();
rtn.IsSuccess = false;
if (_s == DBStatus.Close)
{
DoOpen();
}
DataSetStd ds = new DataSetStd();
OracleCommand dc = null;
OracleDataReader ddr = null;
if (this._s == DBStatus.Begin_Trans)
{
dc = new OracleCommand(sp_name, conn);
dc.Transaction = tran;
}
else
{
dc = new OracleCommand(sp_name, conn);
}
dc.CommandType = CommandType.StoredProcedure;
FillParametersToCommand(dc, dbp);
try
{
if (isReturnDataSet)
{
ddr = dc.ExecuteReader();
ds = DataSetStd.FillData(ddr);
rtn.ReturnDataSet = ds;
}
else
{
dc.ExecuteNonQuery();
}
//獲取返回值
foreach (OracleParameter sp in dc.Parameters)
{
if (sp.Direction == ParameterDirection.Output || sp.Direction == ParameterDirection.InputOutput || sp.Direction == ParameterDirection.ReturnValue)
{
rtn.SetValue(sp.ParameterName.Replace(":", ""), sp.Value);
}
}
rtn.IsSuccess = true;
}
finally
{
if (ddr != null)
{
ddr.Close();
ddr.Dispose();
}
dc.Dispose();
dc = null;
}
return(rtn);
}
/// <summary> /// 執行翻頁查詢，V1.0.0.0新增 /// </summary> /// <param name="startRow"></param> /// <param name="endRow"></param> /// <param name="toPage"></param> /// <param name="count_of_page"></param> /// <param name="sql"></param> /// <param name="orderby"></param> /// <param name="p"></param> /// <returns></returns> protected abstract DataTableStd QueryByPage(int startRow, int endRow, int toPage, int count_of_page, string sql, string orderby, DBOParameterCollection p);
protected override Data.DataTableStd QueryByPage(int startRow, int endRow, int toPage, int count_of_page, string sql, string orderby, DBOParameterCollection p)
{
string orderby4page = orderby;
if (orderby == null || orderby == "")
{
orderby4page = GetColumnsNameBySql(sql, p)[0] + " ASC ";
}
string newsql = @"select * from "
+ "(select *,ROW_NUMBER() OVER (order by " + orderby4page + " ) as RowNumber from (" + sql + ") table2) a "
+ " where RowNumber > " + (count_of_page * (toPage - 1)) + " and RowNumber <=" + (count_of_page * toPage);
DataSetStd dss = Query(newsql, p);
if (dss == null)
{
return(null);
}
else
{
return(dss[0]);
}
}
/// <summary> /// 查询接口 /// </summary> /// <param name="sql"></param> /// <param name="dbp"></param> /// <returns></returns> public abstract DataSetStd Query(string sql, DBOParameterCollection dbp);
protected override Data.DataTableStd QueryByPage(int startRow, int endRow, int toPage, int count_of_page, string sql, string orderby, DBOParameterCollection p)
{
var newsql = sql;
if (orderby != null && orderby != "")
{
newsql += "order by " + orderby;
}
newsql = string.Format(newsql + " limit {0},{1}", startRow - 1, count_of_page);
DataSetStd dss = Query(newsql, p);
if (dss == null)
{
return(null);
}
else
{
return(dss[0]);
}
}