protected override ExeResEdm UpdateDtToDB(DataTable dtInfos, string strComFields = "*")
{
ExeResEdm dBResEdm = new ExeResEdm();
string strTableName = dtInfos.TableName;
try
{
using (System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection(connstr))
{
conn.Open();
System.Data.OracleClient.OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = GetColumnsNameSql(strTableName, strComFields);
System.Data.OracleClient.OracleDataAdapter adapter = new System.Data.OracleClient.OracleDataAdapter(cmd);
adapter.UpdateCommand = new System.Data.OracleClient.OracleCommandBuilder(adapter).GetUpdateCommand();
adapter.Update(dtInfos.GetChanges());
dtInfos.AcceptChanges();
}
}
catch (Exception ex)
{
dBResEdm.Module = "UpdateDtToDB方法";
dBResEdm.ExBody = ex;
dBResEdm.ErrCode = 1;
return(dBResEdm);
}
return(dBResEdm);
}
protected override ExeResEdm SqlCMD_DT(string cmdText, CommandType commandType, Func <DbDataAdapter, int> fun, params DbParameter[] parameters)
{
ExeResEdm dBResEdm = new ExeResEdm();
try
{
parameters = ParameterPrepare(parameters);
using (System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection(connstr))
{
conn.Open();
System.Data.OracleClient.OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = cmdText;
cmd.CommandType = commandType;
if (parameters != null && parameters.Length > 0)
{
cmd.Parameters.AddRange((parameters));
}
var da = new System.Data.OracleClient.OracleDataAdapter(cmd);
var res = fun(da);
dBResEdm.ExeNum = res;
}
}
catch (Exception ex)
{
dBResEdm.Module = "SqlCMD_DT 方法";
dBResEdm.ExBody = ex;
dBResEdm.ErrCode = 1;
return(dBResEdm);
}
return(dBResEdm);
}
public ComCommand CreateCommand()
{
ComCommand cmd = new ComCommand();
cmd.Command = _con.CreateCommand();
return(cmd);
}
protected override ExeResEdm ExecuteNonQueryFromSqlContianer(List <SqlContianer> ltSqls)
{
ExeResEdm dBResEdm = new ExeResEdm();
string curSQL = "";
try
{
using (System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection(connstr))
{
conn.Open();
System.Data.OracleClient.OracleTransaction oraOprate = conn.BeginTransaction();
try
{
System.Data.OracleClient.OracleCommand cmd = conn.CreateCommand();
cmd.Transaction = oraOprate;
foreach (SqlContianer objOraSqlCon in ltSqls)
{
cmd.CommandText = objOraSqlCon.strSqlTxt;
curSQL = objOraSqlCon.strSqlTxt;
cmd.Parameters.Clear();
cmd.Parameters.AddRange(objOraSqlCon.ltOraParams.ToArray());
int intRes = cmd.ExecuteNonQuery();
dBResEdm.ExeNum += intRes;
if (objOraSqlCon.intExpectNums >= 0)
{
if (intRes != objOraSqlCon.intExpectNums)
{
throw new Exception("Update records not match the expect nums");
}
}
else if (objOraSqlCon.intExpectNums != Int16.MinValue)
{
if (intRes != 0 && intRes != objOraSqlCon.intExpectNums * -1)
{
throw new Exception("Update records not match the expect nums");
}
}
}
oraOprate.Commit();
}
catch (Exception ex)
{
oraOprate.Rollback();
dBResEdm.Module = "ExecuteNonQueryFromSqlContianer方法";
dBResEdm.ExBody = ex;
dBResEdm.ErrCode = 1;
return(dBResEdm);
}
}
}
catch (Exception ex)
{
dBResEdm.Module = "ExecuteNonQueryFromSqlContianer方法";
dBResEdm.ExBody = ex;
dBResEdm.ErrCode = 1;
return(dBResEdm);
}
return(dBResEdm);
}
public int GenerateNewId(string keyName, string connection, int defaultValue = 1)
{
int value = 0;
Guid guid = Guid.NewGuid();
System.Diagnostics.Trace.WriteLine(connection);
//TODO 不知道为什么 数据库连接不对
connection = "User ID=oahr;Password=oahr;Data Source=oahr";
using (var sqlConnection = new System.Data.OracleClient.OracleConnection(connection))
{
if (sqlConnection.State != ConnectionState.Open)
{
sqlConnection.Open();
}
using (var command = sqlConnection.CreateCommand())
{
command.CommandType = CommandType.Text;
for (int i = 0; i < 3; i++)
{
command.CommandText =
(string.Format("select MaxRecID from Sys_MaxRecId where TableName ='{0}'", keyName));
value = int.Parse((command.ExecuteScalar() ?? "0").ToString());
if (value <= 0)
{
value = defaultValue;
command.CommandText =
string.Format(
"insert into Sys_MaxRecId(TableName,MaxRecID,Remark) values('{0}', {1}, '{2}')",
keyName, defaultValue, guid);
command.ExecuteNonQuery();
}
else
{
value += 1;
command.CommandText =
(string.Format(
"update Sys_MaxRecId set MaxRecID = MaxRecID + 1, Remark = '{0}' where TableName = '{1}'",
guid, keyName));
command.ExecuteNonQuery();
}
command.CommandText =
(string.Format("select Remark from Sys_MaxRecId where TableName ='{0}'",
keyName));
if ((command.ExecuteScalar() ?? Guid.Empty).ToString() == guid.ToString())
{
break;
}
}
return(value);
}
}
}
private string GetDDLForTable(string tableName, string owner)
{
using (System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection(strCon1))
{
System.Data.OracleClient.OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = string.Format("select SYS.DBMS_METADATA.GET_DDL('TABLE','{0}','{1}') from dual", tableName, owner);
conn.Open();
object result = cmd.ExecuteScalar();
return(result.ToString());
}
}
public int GenerateNewId(string keyName, string connection, int defaultValue = 1)
{
int value = 0;
Guid guid = Guid.NewGuid();
System.Diagnostics.Trace.WriteLine(connection);
//TODO 不知道为什么 数据库连接不对
connection = "User ID=oahr;Password=oahr;Data Source=oahr";
using (var sqlConnection = new System.Data.OracleClient.OracleConnection(connection))
{
if (sqlConnection.State != ConnectionState.Open)
sqlConnection.Open();
using (var command = sqlConnection.CreateCommand())
{
command.CommandType = CommandType.Text;
for (int i = 0; i < 3; i++)
{
command.CommandText =
(string.Format("select MaxRecID from Sys_MaxRecId where TableName ='{0}'", keyName));
value = int.Parse((command.ExecuteScalar() ?? "0").ToString());
if (value <= 0)
{
value = defaultValue;
command.CommandText =
string.Format(
"insert into Sys_MaxRecId(TableName,MaxRecID,Remark) values('{0}', {1}, '{2}')",
keyName, defaultValue, guid);
command.ExecuteNonQuery();
}
else
{
value += 1;
command.CommandText =
(string.Format(
"update Sys_MaxRecId set MaxRecID = MaxRecID + 1, Remark = '{0}' where TableName = '{1}'",
guid, keyName));
command.ExecuteNonQuery();
}
command.CommandText =
(string.Format("select Remark from Sys_MaxRecId where TableName ='{0}'",
keyName));
if ((command.ExecuteScalar() ?? Guid.Empty).ToString() == guid.ToString())
{
break;
}
}
return value;
}
}
}
string Run(string db,string user,string password,string sql)
{
string oradb = String.Format("Data Source={0};User Id={1};Password={2}",db,user,password);
string result="";
if(sql=="") return "";
using(System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection(oradb))
{
try
{
conn.Open();
using(System.Data.OracleClient.OracleCommand cmd=conn.CreateCommand())
{
//cmd.BindByName=true;
cmd.CommandText=sql.Replace("\r\n","\n");
System.Data.OracleClient.OracleDataReader dr=cmd.ExecuteReader();
string[] fields=new string[dr.FieldCount];
if(dr.Read())
{
for(int i=0;i<dr.FieldCount;i++)
{
fields[i]=dr.GetValue(i).ToString();
}
}
dr.Close();
result=string.Join(",",fields);
}
return result;
}
catch(Exception e)
{
return "Error: "+e.Message;
}
}
}
protected override ExeResEdm GetDataSets(List <SqlContianer> ltSqls)
{
ExeResEdm dBResEdm = new ExeResEdm();
DataSet ds = new DataSet();
try
{
using (System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection(connstr))
{
conn.Open();
System.Data.OracleClient.OracleTransaction tsOprate = conn.BeginTransaction();
try
{
System.Data.OracleClient.OracleCommand cmd = conn.CreateCommand();
cmd.Transaction = tsOprate;
List <string> tbNames = new List <string>();
foreach (var objOraSqlCon in ltSqls)
{
DataTable dt = new DataTable();
if (!string.IsNullOrEmpty(objOraSqlCon.tableName))
{
dt.TableName = objOraSqlCon.tableName;
}
else
{
string tb = GetTableNameFromSelectSql(objOraSqlCon.strSqlTxt);
if (!string.IsNullOrEmpty(tb))
{
dt.TableName = tb;
}
}
if (tbNames.Contains(dt.TableName))
{
dt.TableName = dt.TableName + "_" + (tbNames.Count() + 1);
}
tbNames.Add(dt.TableName);
cmd.CommandText = objOraSqlCon.strSqlTxt;
cmd.Parameters.Clear();
if (objOraSqlCon.ltOraParams != null && objOraSqlCon.ltOraParams.Count > 0)
{
cmd.Parameters.AddRange(objOraSqlCon.ltOraParams.ToArray());
}
System.Data.OracleClient.OracleDataAdapter adapter = new System.Data.OracleClient.OracleDataAdapter(cmd);
adapter.Fill(dt);
ds.Tables.Add(dt);
}
tsOprate.Commit();
}
catch (Exception ex)
{
tsOprate.Rollback();
dBResEdm.Module = "GetDataSets方法";
dBResEdm.ExBody = ex;
dBResEdm.ErrCode = 1;
return(dBResEdm);
}
}
}
catch (Exception ex)
{
dBResEdm.Module = "GetDataSets方法";
dBResEdm.ExBody = ex;
dBResEdm.ErrCode = 1;
return(dBResEdm);
}
dBResEdm.ExeModel = ds;
return(dBResEdm);
}
protected override ExeResEdm UpdateDsToDB(DataSet dsTables, Dictionary <string, string> dicDtMainFields = null)
{
ExeResEdm dBResEdm = new ExeResEdm();
int n = 0;
try
{
using (System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection(connstr))
{
conn.Open();
System.Data.OracleClient.OracleTransaction tsOprate = conn.BeginTransaction();
try
{
System.Data.OracleClient.OracleCommand cmd = conn.CreateCommand();
cmd.Transaction = tsOprate;
foreach (DataTable dtTemp in dsTables.Tables)
{
string strComFields = "*";
if (dicDtMainFields != null && dicDtMainFields.Count > 0 && dicDtMainFields.ContainsKey(dtTemp.TableName))
{
strComFields = !string.IsNullOrEmpty(dicDtMainFields[dtTemp.TableName]) ? dicDtMainFields[dtTemp.TableName]: strComFields;
}
cmd.CommandText = GetColumnsNameSql(dtTemp.TableName, strComFields);
System.Data.OracleClient.OracleDataAdapter adapter = new System.Data.OracleClient.OracleDataAdapter(cmd);
var dtChanges = dtTemp.GetChanges();
adapter.FillSchema(dtChanges, SchemaType.Mapped); //new added
if (dtChanges != null) //是添加或更新
{
adapter.UpdateCommand = new System.Data.OracleClient.OracleCommandBuilder(adapter).GetUpdateCommand();
n += adapter.Update(dtChanges);
dtTemp.AcceptChanges();
}
else //是删除
{
adapter.DeleteCommand = new System.Data.OracleClient.OracleCommandBuilder(adapter).GetDeleteCommand();
for (int i = dtTemp.Rows.Count - 1; i >= 0; i--)
{
dtTemp.Rows[i].Delete();
}
n += adapter.Update(dtTemp);
}
}
dsTables.AcceptChanges();
tsOprate.Commit();
}
catch (Exception ex)
{
tsOprate.Rollback();
dBResEdm.Module = "UpdateDsToDB方法";
dBResEdm.ExBody = ex;
dBResEdm.ErrCode = 1;
return(dBResEdm);
}
}
}
catch (Exception ex)
{
dBResEdm.Module = "UpdateDsToDB方法";
dBResEdm.ExBody = ex;
dBResEdm.ErrCode = 1;
return(dBResEdm);
}
dBResEdm.ExeNum = n;
return(dBResEdm);
}
static void Main(string[] args)
{
Console.WriteLine("1. Test dotNetCore.Data.OracleClient with native OCI client");
Console.WriteLine("2. Test Oracle.ManagedDataAccess.Client");
Console.WriteLine("Other - exit");
var key = Console.ReadKey().KeyChar;
if (key == '1')
{
Console.WriteLine("Test dotNetCore.Data.OracleClient with Oci client.");
Console.WriteLine("create OracleConnection object...");
//https://stackoverflow.com/questions/9218847/how-do-i-handle-database-connections-with-dapper-in-net
using (System.Data.Common.DbConnection connection = new System.Data.OracleClient.OracleConnection("Data Source = RHEL5; User ID = TEST; Password = Passw0rd1 "))
{
Console.WriteLine("Open connection...");
connection.Open();
Console.WriteLine("Create command...");
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT table_name, tablespace_name, num_rows FROM user_tables WHERE tablespace_name is not null and num_rows > 0 order by num_rows desc";
Console.WriteLine("Execute reader...");
using (var reader = command.ExecuteReader())
{
Console.WriteLine("*** User tables (sample): ***");
while (reader.Read())
{
string tableName = reader.GetString(reader.GetOrdinal("TABLE_NAME"));
string tablespace_name = reader.GetString(reader.GetOrdinal("TABLESPACE_NAME"));
int rows = reader.GetInt32(reader.GetOrdinal("NUM_ROWS"));
Console.WriteLine(tableName + " in tablespace " + tablespace_name + " has " + rows.ToString() + " rows.");
}
;
}
Console.WriteLine("End reader...");
Console.WriteLine();
Console.WriteLine("*** Test NLS_LANG settings ***");
command.CommandText = "select 'some text in English language' as a, '储物组合带门/抽屉, 白色 卡维肯, 因维肯 白蜡木贴面' as b, 'ľščťžýáííéô§úä' as c from dual";
var reader1 = command.ExecuteReader();
reader1.Read();
string sEnglish = reader1.GetString(0);
string sChinese = reader1.GetString(1);
string sSlovak = reader1.GetString(2);
Console.WriteLine("English from db: " + sEnglish);
Console.WriteLine("Chinese from db: " + sChinese);
Console.WriteLine("Slovak from db: " + sSlovak);
Console.WriteLine("Chinese from the code: 储物组合带门 / 抽屉, 白色 卡维肯, 因维肯 白蜡木贴面");
Console.WriteLine("Slovak from the code: ľščťžýáííéô§úä");
}
}
Console.WriteLine("Done Oci Client");
Console.WriteLine("Press key to exit");
key = Console.ReadKey().KeyChar;
}
else if (key == '2')
{
Console.WriteLine("Test Oracle.ManagedDataAccess.Client.");
Console.WriteLine("create OracleConnection object...");
//1. Full TNS string in connect string
using (System.Data.Common.DbConnection connection = new OracleConnection("Data Source = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.103.67)(PORT = 1521)) )(CONNECT_DATA = (SID = ORCL))); User ID = TEST; Password = Passw0rd1 "))
//2 Withouth TNS , with service name = " select value from v$parameter where name='service_names' "
// using (System.Data.Common.DbConnection connection = new OracleConnection("Data Source = 10.100.103.67:1521/orcl.test.sk; User ID = TEST; Password = Passw0rd1 "))
// With alias from TNS
// using (OracleConnection connection = new OracleConnection(" User ID = TEST; Password = Passw0rd1; Data Source = RHEL5;"))
{
Console.WriteLine("Open connection...");
connection.Open();
Console.WriteLine("Create command...");
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT table_name, tablespace_name, num_rows FROM user_tables WHERE tablespace_name is not null and num_rows > 0 order by num_rows desc";
Console.WriteLine("Execute reader...");
using (var reader = command.ExecuteReader())
{
Console.WriteLine("*** User tables (sample): ***");
while (reader.Read())
{
string tableName = reader.GetString(reader.GetOrdinal("TABLE_NAME"));
string tablespace_name = reader.GetString(reader.GetOrdinal("TABLESPACE_NAME"));
int rows = reader.GetInt32(reader.GetOrdinal("NUM_ROWS"));
Console.WriteLine(tableName + " in tablespace " + tablespace_name + " has " + rows.ToString() + " rows.");
}
;
}
Console.WriteLine("End reader...");
Console.WriteLine();
Console.WriteLine("*** Test NLS_LANG settings ***");
command.CommandText = "select 'some text in English language' as a, '储物组合带门/抽屉, 白色 卡维肯, 因维肯 白蜡木贴面' as b, 'ľščťžýáííéô§úä' as c from dual";
var reader1 = command.ExecuteReader();
reader1.Read();
string sEnglish = reader1.GetString(0);
string sChinese = reader1.GetString(1);
string sSlovak = reader1.GetString(2);
Console.WriteLine("English from db: " + sEnglish);
Console.WriteLine("Chinese from db: " + sChinese);
Console.WriteLine("Slovak from db: " + sSlovak);
Console.WriteLine("Chinese from the code: 储物组合带门 / 抽屉, 白色 卡维肯, 因维肯 白蜡木贴面");
Console.WriteLine("Slovak from the code: ľščťžýáííéô§úä");
}
}
Console.WriteLine("Done Oracle.ManagedDataAccess.Client");
Console.WriteLine("Press key to exit");
key = Console.ReadKey().KeyChar;
}
}
