public static DataTable GetCoalChartDataTable(int reportId, string key)
{
//check params
if (string.IsNullOrEmpty(key))
{
return(new DataTable());
}
COALCHARTLEGEND chartL;
using (var db = new CMAEntities())
{
chartL = db.COALCHARTLEGENDs.FirstOrDefault(o => o.REPORTID == reportId);
}
var sqlWhere = key.Replace("^|^", "=").Replace("||", " AND ");
var sqlStr = string.Format(chartL.CHARTSQL, sqlWhere);
var ds = new DataSet();
using (var zcxdb = new CNE_ZCXNewEntities())
{
using (var adp = new OracleDataAdapter(sqlStr, zcxdb.Database.Connection.ConnectionString))
{
adp.Fill(ds);
}
}
return(ds.Tables[0]);
}
public List <PartitionBallanceTableData> GetPartitionBallenceData(string areaCode, string productCode, List <string> years)
{
var ds = new DataTable();
var paras = new[] { new OracleParameter("P_AreaCode", areaCode),
new OracleParameter("P_productCode", productCode),
new OracleParameter("P_myear1", years[2]),
new OracleParameter("P_myear2", years[1]),
new OracleParameter("P_myear3", years[0]),
new OracleParameter("P_CUR", OracleDbType.RefCursor)
{
Direction = ParameterDirection.Output
} };
using (var zcxDb = new CNE_ZCXNewEntities())
{
using (var spCmd = new OracleCommand())
{
zcxDb.Database.Connection.Open();
spCmd.Connection = (OracleConnection)zcxDb.Database.Connection;
spCmd.CommandText = "GetPartitionBallenceData";
spCmd.CommandType = CommandType.StoredProcedure;
spCmd.CommandTimeout = 0;
spCmd.Parameters.AddRange(paras);
var da = new OracleDataAdapter(spCmd);
da.Fill(ds);
}
}
return(DataTableSerializer.ToList <PartitionBallanceTableData>(ds));
}
public DataTable GetHomeItemAll()
{
var paramArray = new[]
{
new OracleParameter("P_CUR", OracleDbType.RefCursor)
{
Direction = ParameterDirection.Output
}
};
using (var cmaDb = new CMAEntities())
{
using (OracleCommand spCmd = new OracleCommand())
{
cmaDb.Database.Connection.Open();
spCmd.Connection = new OracleConnection(cmaDb.Database.Connection.ConnectionString);
spCmd.CommandText = "GetHomeItemAll";
spCmd.CommandType = CommandType.StoredProcedure;
spCmd.CommandTimeout = 0;
spCmd.Parameters.AddRange(paramArray);
OracleDataAdapter da = new OracleDataAdapter(spCmd);
var ds = new DataSet();
da.Fill(ds);
return(ds.Tables[0]);
}
}
}
public List <BallanceFilter> GetAreaFilters(int reportId, string productCode)
{
var ds = new DataTable();
var paras = new[] { new OracleParameter("P_reportID", reportId),
new OracleParameter("P_ProductCode", productCode),
new OracleParameter("P_CUR", OracleDbType.RefCursor)
{
Direction = ParameterDirection.Output
} };
using (var zcxDb = new CNE_ZCXNewEntities())
{
using (var spCmd = new OracleCommand())
{
zcxDb.Database.Connection.Open();
spCmd.Connection = (OracleConnection)zcxDb.Database.Connection;
spCmd.CommandText = "GetAreaFiltes";
spCmd.CommandType = CommandType.StoredProcedure;
spCmd.CommandTimeout = 0;
spCmd.Parameters.AddRange(paras);
var da = new OracleDataAdapter(spCmd);
da.Fill(ds);
}
}
return(DataTableSerializer.ToList <BallanceFilter>(ds));
}
/// <summary>
/// Get data by store procedure
/// </summary>
/// <param name="inName">sp name</param>
/// <param name="inParms">parameters</param>
/// <returns></returns>
protected DataSet GetDataSetBySp(string inName, OracleParameter[] inParms)
{
using (var cnEDB = new CneNewEntities())
{
using (OracleCommand spCmd = new OracleCommand())
{
DataSet ds = null;
cnEDB.Database.Connection.Open();
spCmd.Connection = new OracleConnection(cnEDB.Database.Connection.ConnectionString);
spCmd.CommandText = inName;
spCmd.CommandType = CommandType.StoredProcedure;
spCmd.CommandTimeout = 0;
if (inParms != null)
{
spCmd.Parameters.AddRange(inParms);
}
OracleDataAdapter da = new OracleDataAdapter(spCmd);
ds = new DataSet();
da.Fill(ds);
return(ds);
}
}
}
public static List <CoalTrafficPortChartEntity> GetCoalTrafficPortData(string key)
{
const string proName = "CoalTrafficPort_ChartData";
var paras = new[] {
new OracleParameter("P_key", key),
new OracleParameter("P_CUR", OracleDbType.RefCursor)
{
Direction = ParameterDirection.Output
}
};
var chartTable = new DataTable();
using (var cnE = new CNE_ZCXNewEntities())
{
using (var spCmd = new OracleCommand())
{
cnE.Database.Connection.Open();
spCmd.Connection = (OracleConnection)(cnE.Database.Connection);
spCmd.CommandText = proName;
spCmd.CommandType = CommandType.StoredProcedure;
spCmd.CommandTimeout = 0;
spCmd.Parameters.AddRange(paras);
var da = new OracleDataAdapter(spCmd);
da.Fill(chartTable);
}
}
return(DataTableSerializer.ToList <CoalTrafficPortChartEntity>(chartTable));
}
/// <summary>
/// 執行SQL語句返回DataTable
/// </summary>
/// <param name="SQL">SQL語句</param>
/// <param name="DBUrl">數據庫鏈接地址</param>
/// <returns></returns>
public DataTable GetDataTableBySQL(string SQL, string DBUrl)
{
if (DBUrl.ToString().Trim() == "" || DBUrl == null)
{
throw new Exception("數據庫鏈接地址不能為空");
}
// 获取与数据库的连接对象並且绑定连接字符串
Oracle.ManagedDataAccess.Client.OracleConnection conn = new Oracle.ManagedDataAccess.Client.OracleConnection(DBUrl);
conn.Open(); //打開資源
//获取数据库操作对象
Oracle.ManagedDataAccess.Client.OracleCommand cmd = conn.CreateCommand();
try
{
cmd.CommandText = SQL;
Oracle.ManagedDataAccess.Client.OracleDataAdapter adapter = new Oracle.ManagedDataAccess.Client.OracleDataAdapter(cmd);
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
dataTable.TableName = "數據集";
cmd.Dispose(); //釋放資源
conn.Dispose(); //釋放資源
conn.Close(); //關閉
return(dataTable);
}
catch (Exception ex)
{
cmd.Dispose(); //釋放資源
conn.Dispose(); //釋放資源
conn.Close(); //關閉
throw ex;
}
}
public static DataTable GetDataTableBySqlSettle(string conStr, string sql)
{
DataSet ds = new DataSet();
OracleConnection con = new OracleConnection(conStr);
OracleCommand cmd = new OracleCommand(sql, con);
OracleDataAdapter da = new OracleDataAdapter(cmd);
da.Fill(ds);
return(ds.Tables[0]);
}
public static DataTable GetDataTableBySql2(string sql)
{
DataSet ds = new DataSet();
//string conStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=124.207.105.120)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME = orcl11g.us.oracle.com)));User Id=settle;Password=settle;";
string conStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.130.23)(PORT=8903))(CONNECT_DATA=(SERVICE_NAME = settle_primary)));User Id=settle;Password=settle;";
OracleConnection con = new OracleConnection(conStr);
OracleCommand cmd = new OracleCommand(sql, con);
OracleDataAdapter da = new OracleDataAdapter(cmd);
da.Fill(ds);
return(ds.Tables[0]);
}
/// <summary>
/// 調用存儲返回String字符串信息和DataTable數據表格（最後兩個位置必須為返回參數，一個為輸出字符串另一個為游標，位置不能顛倒）
/// <para>obj使用方法：new{ v_data=value, v_data1=value1,out_string="",out_cursor=""}</para>
/// <para>注意：obj中v_data為存儲參數名稱，value為對應的值，out_string為輸出參數不需要輸入值， out_cursor為游標不需要輸入值</para>
/// </summary>
/// <param name="storageName">存儲名稱</param>
/// <param name="DBUrl">數據庫鏈接地址</param>
/// <param name="obj">存儲參數對象</param>
/// <param name="dataTable">返回結果集</param>
/// <returns></returns>
public string GetStringAndDataTableByStorageName(string storageName, string DBUrl, object obj, out DataTable dataTable)
{
if (DBUrl.ToString().Trim() == "" || DBUrl == null)
{
throw new Exception("數據庫鏈接地址不能為空");
}
// 获取与数据库的连接对象並且绑定连接字符串
Oracle.ManagedDataAccess.Client.OracleConnection conn = new Oracle.ManagedDataAccess.Client.OracleConnection(DBUrl);
conn.Open();//打開資源
//获取数据库操作对象
Oracle.ManagedDataAccess.Client.OracleCommand cmd = conn.CreateCommand();
try
{
cmd.CommandText = storageName; //存儲名稱
cmd.CommandType = CommandType.StoredProcedure;
PropertyInfo[] properties = obj.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public); //獲取object中的字段名和值
for (int i = 0; i < properties.Length; i++)
{
if (i == (properties.Length - 2))
{ //設定輸出的類型和值
cmd.Parameters.Add(properties[i].Name, Oracle.ManagedDataAccess.Client.OracleDbType.Varchar2, short.MaxValue).Direction = ParameterDirection.Output;
cmd.Parameters[properties[i].Name].Value = DBNull.Value; //賦值
}
else if (i == (properties.Length - 1))
{ //設定輸出的類型和值
cmd.Parameters.Add(properties[i].Name, Oracle.ManagedDataAccess.Client.OracleDbType.RefCursor).Direction = ParameterDirection.Output;
cmd.Parameters[properties[i].Name].Value = DBNull.Value; //賦值
}
else
{ //設定輸入的類型和值
cmd.Parameters.Add(properties[i].Name, GetOracleDbType(properties[i], obj)).Direction = ParameterDirection.Input;
cmd.Parameters[properties[i].Name].Value = properties[i].GetValue(obj, null); //賦值
}
}
DataTable dt = new DataTable();
dt.TableName = "數據集";
Oracle.ManagedDataAccess.Client.OracleDataAdapter oda = new Oracle.ManagedDataAccess.Client.OracleDataAdapter(cmd);
oda.Fill(dt);
dataTable = dt; //返回數據結果集
string message = cmd.Parameters[properties[properties.Length - 2].Name].Value.ToString(); //獲取輸出的字符串
cmd.Dispose(); //釋放資源
conn.Dispose(); //釋放資源
conn.Close(); //關閉
return(message);
}
catch (Exception ex)
{
cmd.Dispose(); //釋放資源
conn.Dispose(); //釋放資源
conn.Close(); //關閉
throw ex;
}
}
public DataTable GetHomeItems(int moduleId, string itemName, int startPage, int pageSize, out int total)
{
var paramArray = new[]
{
new OracleParameter("P_ModuleId", OracleDbType.Int32)
{
Value = moduleId
},
new OracleParameter("P_ItemName", OracleDbType.Varchar2)
{
Value = itemName
},
new OracleParameter("P_StartPage", OracleDbType.Int32)
{
Value = startPage
},
new OracleParameter("P_PageSize", OracleDbType.Int32)
{
Value = pageSize
},
new OracleParameter("P_Total", OracleDbType.Int32, ParameterDirection.Output),
new OracleParameter("P_CUR", OracleDbType.RefCursor)
{
Direction = ParameterDirection.Output
}
};
using (var cmaDb = new CMAEntities())
{
using (OracleCommand spCmd = new OracleCommand())
{
cmaDb.Database.Connection.Open();
spCmd.Connection = new OracleConnection(cmaDb.Database.Connection.ConnectionString);
spCmd.CommandText = "GetHomeItems";
spCmd.CommandType = CommandType.StoredProcedure;
spCmd.CommandTimeout = 0;
spCmd.Parameters.AddRange(paramArray);
OracleDataAdapter da = new OracleDataAdapter(spCmd);
var ds = new DataSet();
da.Fill(ds);
object value;
value = spCmd.Parameters["P_Total"].Value;
total = value.ToString() != "null" ? Convert.ToInt32(value.ToString()) : 0;
return(ds.Tables[0]);
}
}
}
public List <string> GetBallanceColumnNames(int reportId, string areacode, string productCode)
{
var ds = new DataTable();
using (var zcxDb = new CNE_ZCXNewEntities())
{
using (var spCmd = new OracleCommand())
{
string str = "select distinct MARKET_YEAR from GetBalanceTableData where length(MARKET_YEAR)=9 ";
if (!string.IsNullOrEmpty(areacode))
{
str += " and areacode=" + areacode;
}
if (!string.IsNullOrEmpty(productCode))
{
str += " and productCode=" + productCode;
}
str += " and reportid=" + reportId + " order by MARKET_YEAR desc";
zcxDb.Database.Connection.Open();
spCmd.Connection = (OracleConnection)zcxDb.Database.Connection;
spCmd.CommandText = str;
spCmd.CommandTimeout = 0;
var da = new OracleDataAdapter(spCmd);
da.Fill(ds);
}
}
var columnNames = new List <string>();
var rowItor = ds.Rows.GetEnumerator();
while (rowItor.MoveNext())
{
string years = ((DataRow)rowItor.Current)[0].ToString();
int startYear = Convert.ToInt32(years.Substring(0, 4));
int endYear = Convert.ToInt32(years.Substring(5, 4));
if (endYear == startYear + 1)
{
columnNames.Add(years);
}
}
if (columnNames.Count == 0)
{
columnNames.Add((DateTime.Now.Year - 1) + "/" + DateTime.Now.Year);
columnNames.Add((DateTime.Now.Year - 2) + "/" + (DateTime.Now.Year - 1));
columnNames.Add((DateTime.Now.Year - 3) + "/" + (DateTime.Now.Year - 2));
}
return(columnNames);
}
public DataTable GetEnergyInvntoryData(string tableName, string columns, string order, string filter = "1=1")
{
using (var cnE = new CneNewEntities())
{
using (var spCmd = new OracleCommand())
{
var ds = new DataSet();
cnE.Database.Connection.Open();
spCmd.Connection = (OracleConnection)cnE.Database.Connection;
spCmd.CommandText = "SELECT " + columns + " FROM " + tableName + " where " + filter + " order by " + order;
spCmd.CommandTimeout = 0;
var da = new OracleDataAdapter(spCmd);
da.Fill(ds);
return(ds.Tables.Count > 0 ? ds.Tables[0] : new DataTable());
}
}
}
public string getCode(int type, int order, OracleConnection conn)
{
string sql = "select * from SmmCode where TYPEC=" + type + "and ORDERC=" + order;
using (OracleCommand cmd = new OracleCommand(sql, conn))
{
OracleDataAdapter sda = new OracleDataAdapter(cmd);
DataTable tb = new DataTable();
sda.Fill(tb);
if (tb != null && tb.Rows.Count == 1)
{
return(tb.Rows[0]["CODE"].ToString());
}
else
{
return("");
}
}
}
private List <string> GetCategoryIdWhichHasData(string tableName, string categoryName)
{
string proName = "select distinct " + categoryName + " from " + tableName;
var dt = new DataTable();
using (var cnE = new CNE_ZCXNewEntities())
{
using (var spCmd = new OracleCommand())
{
cnE.Database.Connection.Open();
spCmd.Connection = (OracleConnection)(cnE.Database.Connection);
spCmd.CommandText = proName;
spCmd.CommandType = CommandType.Text;
spCmd.CommandTimeout = 0;
var da = new OracleDataAdapter(spCmd);
da.Fill(dt);
}
}
return((from DataRow dr in dt.Rows select dr[0] == null ? "" : dr[0].ToString()).ToList());
}
public void ImportData(DataTable tb, string[] filters, StringBuilder sb)
{
int updateRows = 0, insertRows = 0;
using (OracleConnection conn = new OracleConnection(connstr))
{
conn.Open();
using (OracleCommand cmd = new OracleCommand())
{
cmd.Connection = conn;
foreach (DataRow dr in tb.Rows)
{
string strWhere = CreateFilter(filters, dr);
string sqlExists = "select * from " + tb.TableName + strWhere;
cmd.CommandText = sqlExists;
OracleDataAdapter sda = new OracleDataAdapter(cmd);
DataTable t = new DataTable();
sda.Fill(t);
if (t != null && t.Rows.Count == 1)
{
//更新数据；
string updatesql = CreateUpdateSql(tb, dr, strWhere);
cmd.CommandText = updatesql;
cmd.ExecuteNonQuery();
updateRows++;
}
else
{
//插入数据；
string updatesql = CreateInsertSql(tb, dr);
cmd.CommandText = updatesql;
cmd.ExecuteNonQuery();
insertRows++;
}
}
}
conn.Close();
sb.Append(" update:" + updateRows + " insert:" + insertRows + "\r\n");
}
}
private void button3_Click(object sender, EventArgs e)
{
try
{
string connString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.252.42)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)));Persist Security Info=True;User ID=admin;Password=renda#weixin;";
Oracle.ManagedDataAccess.Client.OracleConnection con = new Oracle.ManagedDataAccess.Client.OracleConnection(connString);
con.Open();
string sql = "SELECT * FROM TB_USERS"; // DemoOP是表T_TEST的user
Oracle.ManagedDataAccess.Client.OracleCommand cmd = new Oracle.ManagedDataAccess.Client.OracleCommand(sql, con);
cmd.CommandType = CommandType.Text;
DataSet ds = new DataSet();
Oracle.ManagedDataAccess.Client.OracleDataAdapter da = new Oracle.ManagedDataAccess.Client.OracleDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds);
var a = 0;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public void AddUserToDb(USER user)
{
var userNameParam = new OracleParameter("USERNAME", OracleDbType.Varchar2, user.USER_NAME, ParameterDirection.Input);
var userPassParam = new OracleParameter("PASSWORD", OracleDbType.Varchar2, user.USER_PASSWORD, ParameterDirection.Input);
var userGenderParam = new OracleParameter("USERGENDER", OracleDbType.Varchar2, user.GENDER, ParameterDirection.Input);
using (OracleConnection connection = new OracleConnection(_connectionString))
{
OracleCommand cmd = new OracleCommand("CREATEUSER", connection);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(userNameParam);
cmd.Parameters.Add(userPassParam);
cmd.Parameters.Add(userGenderParam);
connection.Open();
OracleDataAdapter adapter = new OracleDataAdapter(cmd);
cmd.ExecuteNonQuery();
// context.SaveChanges();
}
}
// retourne un DataSet avec le contenu d'une procedure qui retourne plusieurs résultats
public DataSet GetSelectProc(string Nom_Procedure)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
DataSet dataset = new DataSet();
OracleCommand cmd = new OracleCommand();
cmd.Connection = connection;
cmd.CommandText = Nom_Procedure;
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter curseur =
new OracleParameter("curseur", OracleDbType.RefCursor, ParameterDirection.Output);
cmd.Parameters.Add(curseur);
connection.Open();
cmd.ExecuteNonQuery();
OracleDataAdapter da = new OracleDataAdapter(cmd);
da.Fill(dataset);
cmd.Dispose();
connection.Close();
return(dataset);
}
}
public DataSet GetDataSetBySpFromCma(string inName, params OracleParameter[] inParams)
{
using (var vav = new CMAEntities())
{
using (var spCmd = new OracleCommand())
{
vav.Database.Connection.Open();
spCmd.Connection = (OracleConnection)(vav.Database.Connection);
spCmd.CommandText = inName;
spCmd.CommandType = CommandType.StoredProcedure;
spCmd.CommandTimeout = 0;
if (inParams != null)
{
spCmd.Parameters.AddRange(inParams);
}
var da = new OracleDataAdapter(spCmd);
var ds = new DataSet();
da.Fill(ds);
return(ds);
}
}
}
private DataSet GetDataSetBySp(string inName, OracleParameter[] inParms, string outName, out object outValue)
{
using (var IPPDB = new IPPEntities())
{
using (var spCmd = new OracleCommand())
{
IPPDB.Database.Connection.Open();
spCmd.Connection = new OracleConnection(IPPDB.Database.Connection.ConnectionString);
spCmd.CommandText = inName;
spCmd.CommandType = CommandType.StoredProcedure;
spCmd.CommandTimeout = 0;
if (inParms != null)
{
spCmd.Parameters.AddRange(inParms);
}
var da = new OracleDataAdapter(spCmd);
var ds = new DataSet();
da.Fill(ds);
outValue = spCmd.Parameters[outName].Value;
return(ds);
}
}
}
public DataTable GetPagedTableData(IEnumerable <REPORTCOLUMNDEFINITION> columns, string tableName, string strOrder, string strWhere, int pageIndex, out int recordCount)
{
var strGetFields =
columns.Select(x => x.COLUMN_NAME).Aggregate((a, b) => a + "," + string.Format("{0}", b));
if (string.IsNullOrEmpty(tableName))
{
recordCount = 0;
return(new DataTable());
}
else//20141027 yy Localization
{
if (IsEnglishCulture())
{
tableName = tableName + "_EN";
}
}
if (string.IsNullOrEmpty(strOrder))
{
strOrder = "parcode";
}
var paramArray = new[]
{
new OracleParameter("tblName", OracleDbType.NVarchar2)
{
Value = tableName
},
new OracleParameter("strGetFields", OracleDbType.NVarchar2)
{
Value = strGetFields
},
new OracleParameter("strOrder", OracleDbType.NVarchar2)
{
Value = strOrder
},
new OracleParameter("strWhere", OracleDbType.NVarchar2)
{
Value = strWhere
},
new OracleParameter("pageIndex", OracleDbType.Int32)
{
Value = pageIndex
},
new OracleParameter("recordCount", OracleDbType.Int32, ParameterDirection.Output)
{
Value = 0
},
new OracleParameter("O_CUR", OracleDbType.RefCursor)
{
Direction = ParameterDirection.Output
}
};
using (var cnEdb = new CNE_ZCXNewEntities())
{
using (var spCmd = new OracleCommand())
{
cnEdb.Database.Connection.Open();
spCmd.Connection = (OracleConnection)(cnEdb.Database.Connection);
spCmd.CommandText = "GetDataPaged";
spCmd.CommandType = CommandType.StoredProcedure;
spCmd.CommandTimeout = 0;
spCmd.Parameters.AddRange(paramArray);
var da = new OracleDataAdapter(spCmd);
var ds = new DataSet();
da.Fill(ds);
recordCount = Convert.ToInt32(spCmd.Parameters["recordCount"].Value.ToString());
return(ds.Tables[0]);
}
}
}
public DbDataAdapter CreateDataAdapter(DbCommand command)
{
var adapter = new Oracle.ManagedDataAccess.Client.OracleDataAdapter((Oracle.ManagedDataAccess.Client.OracleCommand)command);
return(adapter);
}
public DataSet Execute()
{
var ds = new DataSet();
if (_sourceDb.Type.ToString() == "ORACLE")
{
using (var con = new OracleConnection(_sourceDb.Conn))
{
foreach (var mapping in TableMappings)
{
using (var cmd = new OracleCommand())
{
cmd.Connection = con;
var sb = new StringBuilder();
sb.Append("select ");
foreach (var column in mapping.ColumnMappings)
{
sb.Append(column.Source + ",");
}
//_lastSyncTime = Convert.ToDateTime("2015-01-10 11:00:00 PM");
sb.Remove(sb.Length - 1, 1);
sb.Append(" from " + mapping.Source);
sb.Append(" where " + mapping.Filter.Replace("{LastSyncTime}", _lastSyncTime.ToString(_oracleFormatString, CultureInfo.InvariantCulture)).Replace("{CurrentSyncTime}", _currentSyncTime.ToString(_oracleFormatString, CultureInfo.InvariantCulture)));
cmd.CommandText = sb.ToString();
cmd.CommandTimeout = _sqlCommandTimeout;
var da = new OracleDataAdapter(cmd);
da.Fill(ds, mapping.Destination);
}
}
}
}
else
{
using (var con = new SqlConnection(_sourceDb.Conn))
{
foreach (var mapping in TableMappings)
{
using (var cmd = new SqlCommand())
{
cmd.Connection = con;
var sb = new StringBuilder();
sb.Append("select ");
foreach (var column in mapping.ColumnMappings)
{
sb.Append(column.Source + ",");
}
//_lastSyncTime = Convert.ToDateTime("2015-01-10 11:00:00 PM");
sb.Remove(sb.Length - 1, 1);
sb.Append(" from " + mapping.Source);
sb.Append(" where " + mapping.Filter.Replace("{LastSyncTime}", _lastSyncTime.ToString(_formatString, CultureInfo.InvariantCulture)).Replace("{CurrentSyncTime}", _currentSyncTime.ToString(_formatString, CultureInfo.InvariantCulture)));
cmd.CommandText = sb.ToString();
cmd.CommandTimeout = _sqlCommandTimeout;
var da = new SqlDataAdapter(cmd);
da.Fill(ds, mapping.Destination);
}
}
}
}
return(ds);
}
public DataTable GetAbsBondList(DateTime startDate, DateTime endDate, string bondClass, string couponClass, string option, string bondRating, string isBondCode, string bondCodeOrIss, string columnList, int startPage, int pageSize, out int total)
{
var paramArray = new[]
{
new OracleParameter("P_StartDate", OracleDbType.TimeStamp)
{
Value = (OracleTimeStamp)startDate
},
new OracleParameter("P_EndDate", OracleDbType.TimeStamp)
{
Value = (OracleTimeStamp)endDate
},
new OracleParameter("P_BondClass", OracleDbType.Varchar2)
{
Value = bondClass
},
new OracleParameter("P_CouponClass", OracleDbType.Varchar2)
{
Value = couponClass
},
new OracleParameter("P_Option", OracleDbType.Varchar2)
{
Value = option
},
new OracleParameter("P_BondRating", OracleDbType.Varchar2)
{
Value = bondRating
},
new OracleParameter("P_IsBondeCode", OracleDbType.Varchar2)
{
Value = isBondCode
},
new OracleParameter("P_BondCodeOrIss", OracleDbType.NVarchar2)
{
Value = bondCodeOrIss
},
new OracleParameter("P_Culture", OracleDbType.Varchar2)
{
Value = Thread.CurrentThread.CurrentUICulture.Name
},
new OracleParameter("P_ColumnList", OracleDbType.Varchar2)
{
Value = columnList
},
new OracleParameter("P_StartPage", OracleDbType.Int32)
{
Value = startPage
},
new OracleParameter("P_PageSize", OracleDbType.Int32)
{
Value = pageSize
},
new OracleParameter("P_TOTAL", OracleDbType.Int32, ParameterDirection.Output)
{
Value = 0
},
new OracleParameter("P_CUR", OracleDbType.RefCursor)
{
Direction = ParameterDirection.Output
}
};
using (var cmaDb = new ZCXEntities())
{
using (OracleCommand spCmd = new OracleCommand())
{
cmaDb.Database.Connection.Open();
spCmd.Connection = new OracleConnection(cmaDb.Database.Connection.ConnectionString);
spCmd.CommandText = "GetAbsListBondInfo";
spCmd.CommandType = CommandType.StoredProcedure;
spCmd.CommandTimeout = 0;
spCmd.Parameters.AddRange(paramArray);
OracleDataAdapter da = new OracleDataAdapter(spCmd);
var ds = new DataSet();
da.Fill(ds);
total = Convert.ToInt32(spCmd.Parameters["P_TOTAL"].Value.ToString());
return(ds.Tables[0]);
}
}
}
public DataTable GetDataPaged(string tableName, string strGetFields, string strOrder, string strWhere, int pageIndex, int pageSize, int doCount, int isExcel, out int recordCount)
{
var paramArray = new[]
{
new OracleParameter("tblName", OracleDbType.NVarchar2)
{
Value = tableName
},
new OracleParameter("strGetFields", OracleDbType.NVarchar2)
{
Value = strGetFields
},
new OracleParameter("strOrder", OracleDbType.NVarchar2)
{
Value = strOrder
},
new OracleParameter("strWhere", OracleDbType.NVarchar2)
{
Value = strWhere
},
new OracleParameter("pageIndex", OracleDbType.Int32)
{
Value = pageIndex
},
new OracleParameter("pageSize", OracleDbType.Int32)
{
Value = pageSize
},
new OracleParameter("recordCount", OracleDbType.Int32, ParameterDirection.Output)
{
Value = pageSize
},
new OracleParameter("doCount", OracleDbType.Int32)
{
Value = doCount
},
new OracleParameter("isExcelReport", OracleDbType.Int32)
{
Value = isExcel
},
new OracleParameter("O_CUR", OracleDbType.RefCursor)
{
Direction = ParameterDirection.Output
}
};
using (var cnEDB = new CneNewEntities())
{
using (var spCmd = new OracleCommand())
{
cnEDB.Database.Connection.Open();
spCmd.Connection = (OracleConnection)(cnEDB.Database.Connection);
spCmd.CommandText = "GetDataPaged";
spCmd.CommandType = CommandType.StoredProcedure;
spCmd.CommandTimeout = 0;
spCmd.Parameters.AddRange(paramArray);
var da = new OracleDataAdapter(spCmd);
var ds = new DataSet();
da.Fill(ds);
recordCount = Convert.ToInt32(spCmd.Parameters["recordCount"].Value.ToString());
return(ds.Tables[0]);
}
}
}
public DataTable GetChinaSecurities(string category, string title, string code, DateTime startDate, DateTime endDate, out int total, int startPage = 1, int pageSize = 50)
{
var paramArray = new[]
{
new OracleParameter("P_Category", OracleDbType.Varchar2)
{
Value = category
},
new OracleParameter("P_Title", OracleDbType.Varchar2)
{
Value = title
},
new OracleParameter("P_Code", OracleDbType.Varchar2)
{
Value = code
},
new OracleParameter("P_StartDate", OracleDbType.TimeStamp)
{
Value = (OracleTimeStamp)startDate
},
new OracleParameter("P_EndDate", OracleDbType.TimeStamp)
{
Value = (OracleTimeStamp)endDate
},
new OracleParameter("P_StartPage", OracleDbType.Int32)
{
Value = startPage
},
new OracleParameter("P_PageSize", OracleDbType.Int32)
{
Value = pageSize
},
new OracleParameter("P_TOTAL", OracleDbType.Int32, ParameterDirection.Output)
{
Value = 0
},
new OracleParameter("P_CUR", OracleDbType.RefCursor)
{
Direction = ParameterDirection.Output
}
};
using (var cmaDb = new CMAEntities())
{
using (OracleCommand spCmd = new OracleCommand())
{
cmaDb.Database.Connection.Open();
spCmd.Connection = new OracleConnection(cmaDb.Database.Connection.ConnectionString);
spCmd.CommandText = "GetChinaSecurities";
spCmd.CommandType = CommandType.StoredProcedure;
spCmd.CommandTimeout = 0;
spCmd.Parameters.AddRange(paramArray);
OracleDataAdapter da = new OracleDataAdapter(spCmd);
var ds = new DataSet();
da.Fill(ds);
total = Convert.ToInt32(spCmd.Parameters["P_TOTAL"].Value.ToString());
return(ds.Tables[0]);
}
}
}