protected override Data.DataTableStd QueryByPage(int startRow, int endRow, int toPage, int count_of_page, string sql, string orderby, DBOParameterCollection p)
{
if (_s == DBStatus.Close)
{
DoOpen();
}
string orderby4page = orderby;
if (orderby == null || orderby == "")
{
orderby4page = GetColumnsNameBySql(sql, p)[0] + " ASC ";
}
if (sql.Trim().EndsWith(";"))
{
sql = sql.Trim().Substring(0, sql.Trim().Length - 1);
}
string newsql = @"select * from "
+ "(select table2.*,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]);
}
}
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]);
}
}
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]);
}
}
public override DBDataCollection ExcuteProcedure(string sp_name, bool isReturnDataSet, ref DBOParameterCollection dbp)
{
DBDataCollection rtn = new DBDataCollection();
rtn.IsSuccess = false;
DataSetStd ds = new DataSetStd();
NpgsqlCommand dc = null;//new SqlCommand(p.StoreProcureName, this.sqlconn);
if (this._s == DBStatus.Begin_Trans)
{
dc = new NpgsqlCommand(sp_name, this.conn, this.tran);
}
else
{
dc = new NpgsqlCommand(sp_name, this.conn);
}
//dc.CommandTimeout = 90;
dc.CommandType = CommandType.StoredProcedure;
dc.CommandTimeout = CommandTimeOut;
FillParametersToCommand(dc, dbp);
NpgsqlDataReader ddr = null;
try
{
if (isReturnDataSet)
{
ddr = dc.ExecuteReader();
ds = DataSetStd.FillData(ddr);
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(ParameterFlagChar, ""), sp.Value);
}
}
rtn.IsSuccess = true;
}
finally
{
if (ddr != null)
{
ddr.Close();
ddr.Dispose();
}
dc.Dispose();
dc = null;
}
return(rtn);
}
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 (_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 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;
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)
{
OracleDataAdapter sqlDa = new OracleDataAdapter();
sqlDa.SelectCommand = dc;
sqlDa.Fill(ds);
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
{
dc.Cancel();
dc = null;
}
return(rtn);
}
public override DataSetStd Query(string sql, DBOParameterCollection dbp)
{
var newsql = ConvertSQL(sql);
if (sqlcomm == null)
{
sqlcomm = new NpgsqlCommand(newsql, this.conn);
sqlcomm.CommandTimeout = CommandTimeOut;
}
else
{
sqlcomm.CommandText = newsql;
}
//如果事務開啟，則使用事務的方式
if (this._s == DBStatus.Begin_Trans)
{
sqlcomm.Transaction = this.tran;
}
DataSetStd ds = new DataSetStd();
NpgsqlDataReader 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);
}
public override DataSetStd Query(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();
SqlDataReader ddr = null;
try
{
//如果有參數
if (dbp != null)
{
FillParametersToCommand(sqlcomm, dbp);
}
ddr = sqlcomm.ExecuteReader();
ds = DataSetStd.FillData(ddr);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (ddr != null)
{
ddr.Close();
ddr.Dispose();
}
sqlcomm.Cancel();
sqlcomm = null;
}
return(ds);
}
/// <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;
}
}
}
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);
}
protected override 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));
DataSetStd dss = Query(newsql, p);
if (dss == null)
{
return(null);
}
else
{
return(dss[0]);
}
}
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;
}
}
}
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 Net.Base.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 (" + sql + @") order by " + orderby4page + @" limit " + count_of_page + " offset " + (count_of_page * (toPage - 1));
DataSetStd dss = Query(newsql, p);
if (dss == null)
{
return(null);
}
else
{
return(dss[0]);
}
}
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);
}
/// <summary>
/// 根据数据集批量删除对应table中的数据
/// </summary>
/// <param name="data"></param>
/// <param name="toTable"></param>
public override void Delete(DataTable data, string toTable)
{
if (data == null)
{
return;
}
string[] keycols = DataTableStd.GetPKName(data);
string sql = "delete from " + toTable;
string where = "";
if (keycols.Length <= 0)
{
keycols = DataTableStd.GetColumnName(data);
}
foreach (string key in keycols)
{
if (where == "")
{
where += " where " + key + "=@" + key;
}
else
{
where += " and " + key + "=@" + key;
}
}
sql = sql + where;
SqlCommand tsqlcomm = new SqlCommand("select * from " + toTable + " where 1=0 ", this.sqlconn);
tsqlcomm.CommandTimeout = 90;
if (this._s == DBStatus.Begin_Trans)
{
tsqlcomm.Transaction = this.trans;
}
SqlDataAdapter sda = new SqlDataAdapter(tsqlcomm);
DataSetStd ds = new DataSetStd();
sda.FillSchema(ds, SchemaType.Source);
tsqlcomm.CommandText = "SET NOCOUNT ON " + sql;
sda.DeleteCommand = tsqlcomm;
sda.DeleteCommand.Parameters.Clear();
foreach (string key in keycols)
{
DataColumn dc = data.Columns[key];
sda.DeleteCommand.Parameters.Add("@" + key, ConvertBy(dc.DataType), dc.MaxLength, key);
}
sda.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;
sda.UpdateBatchSize = 0;
SqlCommandBuilder scb = new SqlCommandBuilder();
scb.ConflictOption = ConflictOption.OverwriteChanges;
scb.SetAllValues = false;
scb.DataAdapter = sda;
DataTableStd dtsdata = DataTableStd.ParseStd(data);
try
{
for (int count = 0; count < dtsdata.RowLength; count++)
{
foreach (string colname in ds[0].ColumnNames)
{
ds[0].SetNewRowValue(dtsdata[count, colname], colname);
}
ds[0].AddNewRow();
if (((count + 1) % 200 == 0) || ((count + 1) == dtsdata.RowLength))
{
ds.AcceptChanges();
for (int i = 0; i < 200; i++)
{
if (i >= ds[0].Value.Rows.Count)
{
break;
}
ds[0].Value.Rows[i].BeginEdit();
ds[0].Value.Rows[i].Delete();
ds[0].Value.Rows[i].EndEdit();
}
sda.Update(ds[0].Value);
ds[0].ClearData();
}
}
}
finally
{
tsqlcomm.Cancel();
tsqlcomm.Dispose();
sda.Dispose();
ds.Dispose();
}
}