public DataTable OracleExecuteDataTable(string SqlStatment)
{
Oracle.ManagedDataAccess.Client.OracleConnection Cn = new Oracle.ManagedDataAccess.Client.OracleConnection(SQL_CONN_STRING);
try
{
if (Cn.State != ConnectionState.Open)
{
Cn.Open();
}
Oracle.ManagedDataAccess.Client.OracleDataAdapter adtp = new Oracle.ManagedDataAccess.Client.OracleDataAdapter(SqlStatment, Cn);
DataSet Ds = new DataSet();
adtp.Fill(Ds);
Cn.Close();
return(Ds.Tables[0]);
}
catch (Exception)
{
Cn.Close();
return(null);
}
finally
{
Cn.Close();
}
}
/// <summary>
/// atualiza somente a situação atual da solicitação.
/// </summary>
/// <param name="codSituacaoAtual"></param>
/// <returns></returns>
public string AtualizarSituacao(int codSolicitacao, int codSituacaoAtual)
{
var connStr = ConfigurationManager.ConnectionStrings["Sapiens"].ToString();
using (var conn = new Oracle.ManagedDataAccess.Client.OracleConnection(connStr))
{
var cmdText = "UPDATE USU_TSOLCLI SET USU_CODSIT = " + codSituacaoAtual + " WHERE USU_CODSOL = " + codSolicitacao;
var cmd = new Oracle.ManagedDataAccess.Client.OracleCommand(cmdText, conn);
if (conn.State == System.Data.ConnectionState.Closed)
{
try
{
conn.Open();
cmd.ExecuteNonQuery();
return "OK";
}
catch (Exception ex)
{
return ex.Message;
}
finally
{
conn.Close();
}
}
return "";
}
}
private async Task ReadApexItemsAsync(Func <ApexItemDto, CancellationToken, Task> onReadItemAsync,
CancellationToken cancellationToken = default(CancellationToken))
{
EnsureArg.IsNotNull(onReadItemAsync);
using (var con = new Oracle.ManagedDataAccess.Client.OracleConnection(_options.Value.ApexConnectionString))
{
var cmd = con.CreateCommand();
cmd.CommandText = "SELECT BTIR_ID, BTIR_CREATED_BY, BTIR_BUSS_CONTACT_EMAIL, BTIR_PROJECT_NAME, BTIR_PROJECT_DESC FROM OCI.BTIR_REQUEST";
con.Open();
var reader = cmd.ExecuteReader();
try
{
while (await reader.ReadAsync(cancellationToken))
{
await onReadItemAsync(new ApexItemDto()
{
ApexId = reader.GetInt32(0),
Requestor3and3 = reader.GetString(1),
BusinessContactEmail = reader.GetString(2),
ProjectName = reader.GetString(3),
ProjectDescription = reader.GetString(4)
}, cancellationToken);
}
}
finally
{
con.Close();
}
}
}
/// <summary>
/// 从Oracle数据库查询数据，返回类型为DateSet
/// </summary>
/// <param name="oraStr">Oracle查询字符串</param>
/// <returns></returns>
/*public static DataSet SelectByOracle(string oraStr)
* {
* //select info by condition
* SqlParameter[] para = new SqlParameter[1];
* oraStr = oraStr.Replace("'", "''");
* para[0] = new SqlParameter("@oraStr", oraStr);
*
* return DbAccess.DataAdapterByCmd(CommandType.StoredProcedure, "SelectByOracle", para);
*
* //2010年11月9日修改
* //OracleConnection oraconn = new OracleConnection(connOral);
* //try
* //{
* // if (oraconn.State != ConnectionState.Open)
* // oraconn.Open();
* // OracleDataAdapter da = new OracleDataAdapter(oraStr, oraconn);
* // DataSet ds = new DataSet();
* // da.Fill(ds);
* // return ds;
* //}
* //catch(Exception ex)
* //{
* // throw new Exception(ex.Message);
* //}
* //finally
* //{
* // oraconn.Close();
* //}
* }
*/
public static DataSet SelectByOracle(string oraStr)
{
//2010年11月9日修改
Oracle.ManagedDataAccess.Client.OracleConnection oraconn = new Oracle.ManagedDataAccess.Client.OracleConnection(connOral);
try
{
if (oraconn.State != ConnectionState.Open)
{
oraconn.Open();
}
Oracle.ManagedDataAccess.Client.OracleDataAdapter da = new Oracle.ManagedDataAccess.Client.OracleDataAdapter(oraStr, oraconn);
DataSet ds = new DataSet();
da.Fill(ds);
return(ds);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
oraconn.Close();
}
}
public static XVar Exec(dynamic strSQL)
{
Oracle.ManagedDataAccess.Client.OracleConnection connection = new Oracle.ManagedDataAccess.Client.OracleConnection();
connection.ConnectionString = DB.connectionString;
connection.Open();
Oracle.ManagedDataAccess.Client.OracleCommand dbCommand = new Oracle.ManagedDataAccess.Client.OracleCommand(strSQL, connection);
dbCommand.CommandType = System.Data.CommandType.Text;
dbCommand.ExecuteNonQuery();
dbCommand.Dispose();
dbCommand = null;
connection.Close();
return(null);
}
static void Main(string[] args) //2D Y0 X0
{
string dbServer = args[0];
string dbPort = args[1];
string dbSID = args[2];
string dbUsername = args[3];
string dbPassword = args[4];
try
{
float y0 = 0; // -1102200; //-478400;
float x0 = 0; // -478400; //-1102200;
if (args.Length > 2)
{
float.TryParse(args[7], out y0);
float.TryParse(args[6], out x0);
}
string connectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=" + dbServer + ")(PORT=" + dbPort + "))(CONNECT_DATA=(SID=" + dbSID + ")));User Id=" + dbUsername + "; Password="******";";
var connection = new Oracle.ManagedDataAccess.Client.OracleConnection(connectionString);
connection.Open();
var command = new Oracle.ManagedDataAccess.Client.OracleCommand();
command.Connection = connection;
command.CommandType = CommandType.Text;
switch (args[5].ToCharArray()[0])
{
case '2':
{
load2DParcels(y0, x0, command);
break;
}
case '3':
{
load3DParcels(y0, x0, command);
break;
}
default:
{
break;
}
}
connection.Close();
} catch (Exception e)
{
Console.WriteLine("Chyba v loaderu: " + e.Message);
}
}
protected override void ExecuteInternal(JobExecutionContext context)
{
var startTime = DateTime.UtcNow;
var logEntity = new SCHEDULERLOG {
STARTTIME = startTime
};
var strInfo = new StringBuilder();
string connStr = ConfigurationManager.AppSettings["mergeData"];
strInfo.AppendFormat("Source [Type: {0} Address: {1}]\n", "CusteelService",
"http://db.custeel.com/services/dataCenterMTOMVerifyService");
strInfo.AppendFormat("Destination [Type: {0} Address: {1}]\n", "Oracle",
connStr);
var conn = new OracleConnection(connStr);
conn.Open();
var tran = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
try
{
#region 执行数据同步程序
var ch = new CusteelData();
ch.SyncCusteelData(strInfo);
#endregion
var merge = new MergeData();
merge.ExecuteMetals(conn, tran);
tran.Commit();
var endTime = DateTime.UtcNow;
logEntity.ENDTIME = endTime;
logEntity.JobStatus = JobStatus.Success;
logEntity.RUNDETAIL = strInfo.ToString();
WriteLogEntity(logEntity);
}
catch (Exception exception)
{
tran.Rollback();
logEntity.ENDTIME = DateTime.UtcNow.AddDays(-1);
logEntity.JobStatus = JobStatus.Fail;
logEntity.RUNDETAIL = strInfo + "\n" + exception;
WriteLogEntity(logEntity);
}
finally
{
conn.Close();
}
}
public static dynamic Query(dynamic strSQL)
{
Oracle.ManagedDataAccess.Client.OracleConnection connection = new Oracle.ManagedDataAccess.Client.OracleConnection();
connection.ConnectionString = DB.connectionString;
connection.Open();
Oracle.ManagedDataAccess.Client.OracleCommand dbCommand = new Oracle.ManagedDataAccess.Client.OracleCommand(strSQL, connection);
dbCommand.CommandType = System.Data.CommandType.Text;
try {
dbCommand.Prepare();
string commandStr = strSQL.ToLower().Substring(0, 6);
string[] stopCommandList = { "insert", "update", "delete", "create", "drop", "rename", "alter" };
if (stopCommandList.Any(x => commandStr.Substring(0, x.Length) == x))
{
dbCommand.ExecuteNonQuery();
dbCommand.Connection.Close();
return(null);
}
else
{
RunnerDBReader rdr = dbCommand.ExecuteReader();
rdr.Connection = dbCommand.Connection;
return(new QueryResult(dbCommand.Connection, rdr));
}
} catch (Exception e) {
if (dbCommand != null)
{
dbCommand.Connection.Close();
}
throw e;
return(null);
}
dbCommand.Dispose();
dbCommand = null;
connection.Close();
}
public String ObterDataUltimaSolicitacaoNoSICADV2(int codCliente)
{
Oracle.ManagedDataAccess.Client.OracleDataReader dr = null;
DateTime dataUltimaSolicitacao = new DateTime();
var connStr = ConfigurationManager.ConnectionStrings["Sapiens"].ToString();
using (var conn = new Oracle.ManagedDataAccess.Client.OracleConnection(connStr))
{
var cmdText = String.Format("select max(usu_dataini) as dataini from usu_tsolicit where usu_codcli = {0}",codCliente);
var cmd = new Oracle.ManagedDataAccess.Client.OracleCommand(cmdText, conn);
if (conn.State == System.Data.ConnectionState.Closed)
{
try
{
conn.Open();
dr = cmd.ExecuteReader();
while (dr.Read())
{
dataUltimaSolicitacao = Convert.ToDateTime(dr["dataini"]);
}
return dataUltimaSolicitacao.ToShortDateString();
}
catch (Exception ex)
{
return "Não Disponível";
}
finally
{
conn.Close();
}
}
return null;
}
}
internal static void GetData(int num, bool readClob, bool readBlob)
{
//using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["connstr"].ConnectionString))
//{
// DataTable table = new DataTable();
// SqlDataAdapter adp = new SqlDataAdapter(string.Format("select top {0} * from TEMP_TestTKK0719-- order by newid()", num), conn);
// adp.Fill(table);
//}
switch (ConfigurationManager.AppSettings["dbtype"].ToUpper().Trim())
{
case "SYSTEM.DATA.ORACLECLIENT":
using (OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["connstr"].ConnectionString))
{
OracleCommand cmd = conn.CreateCommand();
conn.Open();
cmd.CommandText = string.Format("select 1 from user_tables t where t.table_name = upper('TEMP_TestTKK0719')");
object tableInited = cmd.ExecuteScalar();
if (tableInited == null || tableInited == DBNull.Value)
{
cmd.CommandText = @"CREATE TABLE TEMP_TestTKK0719(
code varchar2(50) NOT NULL,
name varchar2(100) NULL,
age int NULL,
birthday timestamp NULL,
salary decimal(10, 2) NULL,
summary varchar2(1000) NULL,
remark clob NULL,
extends blob NULL,
created timestamp,
CONSTRAINT PK__TEMP_TestTKK0719 PRIMARY KEY (code)
)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_Name on TEMP_TestTKK0719(name)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_age on TEMP_TestTKK0719(age)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_birthday on TEMP_TestTKK0719(birthday)";
cmd.ExecuteNonQuery();
}
conn.Close();
cmd.CommandText = string.Format("select code, name, age, birthday, salary, summary, created {1} {2} from TEMP_TestTKK0719 where rownum <= {0}", num, (readClob ? ", remark" : string.Empty), (readBlob ? ", extends" : string.Empty));
conn.Open();
using (OracleDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
object[] objs = new object[500];
dr.GetValues(objs);
}
}
conn.Close();
}
break;
case "ODP.NET":
using (IDbConnection conn = ODPClientFactory.CreateConnection())
{
conn.ConnectionString = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
IDbCommand cmd = conn.CreateCommand();
conn.Open();
cmd.CommandText = string.Format("select 1 from user_tables t where t.table_name = upper('TEMP_TestTKK0719')");
object tableInited = cmd.ExecuteScalar();
if (tableInited == null || tableInited == DBNull.Value)
{
cmd.CommandText = @"CREATE TABLE TEMP_TestTKK0719(
code varchar2(50) NOT NULL,
name varchar2(100) NULL,
age int NULL,
birthday timestamp NULL,
salary decimal(10, 2) NULL,
summary varchar2(1000) NULL,
remark clob NULL,
extends blob NULL,
created timestamp,
CONSTRAINT PK__TEMP_TestTKK0719 PRIMARY KEY (code)
)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_Name on TEMP_TestTKK0719(name)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_age on TEMP_TestTKK0719(age)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_birthday on TEMP_TestTKK0719(birthday)";
cmd.ExecuteNonQuery();
}
conn.Close();
cmd.CommandText = string.Format("select code, name, age, birthday, salary, summary, created {1} {2} from TEMP_TestTKK0719 where rownum <= {0}", num, (readClob ? ", remark" : string.Empty), (readBlob ? ", extends" : string.Empty));
conn.Open();
using (IDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
object[] objs = new object[500];
dr.GetValues(objs);
}
}
conn.Close();
}
break;
case "ODAC":
using (ManagedOralceConnection conn = new ManagedOralceConnection(ConfigurationManager.ConnectionStrings["connstr"].ConnectionString))
{
ManagedOracleCommand cmd = conn.CreateCommand();
conn.Open();
cmd.CommandText = string.Format("select 1 from user_tables t where t.table_name = upper('TEMP_TestTKK0719')");
object tableInited = cmd.ExecuteScalar();
if (tableInited == null || tableInited == DBNull.Value)
{
cmd.CommandText = @"CREATE TABLE TEMP_TestTKK0719(
code varchar2(50) NOT NULL,
name varchar2(100) NULL,
age int NULL,
birthday timestamp NULL,
salary decimal(10, 2) NULL,
summary varchar2(1000) NULL,
remark clob NULL,
extends blob NULL,
created timestamp,
CONSTRAINT PK__TEMP_TestTKK0719 PRIMARY KEY (code)
)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_Name on TEMP_TestTKK0719(name)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_age on TEMP_TestTKK0719(age)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_birthday on TEMP_TestTKK0719(birthday)";
cmd.ExecuteNonQuery();
}
conn.Close();
cmd.CommandText = string.Format("select code, name, age, birthday, salary, summary, created {1} {2} from TEMP_TestTKK0719 where rownum <= {0}", num, (readClob ? ", remark" : string.Empty), (readBlob ? ", extends" : string.Empty));
conn.Open();
using (ManagedOracleDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
object[] objs = new object[500];
dr.GetValues(objs);
}
}
conn.Close();
}
break;
default:
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["connstr"].ConnectionString))
{
SqlCommand cmd = conn.CreateCommand();
conn.Open();
cmd.CommandText = string.Format("select 1 from sys.tables t where t.name = 'TEMP_TestTKK0719'");
object tableInited = cmd.ExecuteScalar();
if (tableInited == null || tableInited == DBNull.Value)
{
cmd.CommandText = @"CREATE TABLE TEMP_TestTKK0719(
code varchar(50) NOT NULL,
name varchar(100) NULL,
age int NULL,
birthday datetime NULL,
salary decimal(10, 2) NULL,
summary nvarchar(1000) NULL,
remark varchar(max) NULL,
extends varbinary(max) NULL,
created datetime,
CONSTRAINT PK__TEMP_TestTKK0719 PRIMARY KEY (code)
)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_Name on TEMP_TestTKK0719(name)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_age on TEMP_TestTKK0719(age)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_birthday on TEMP_TestTKK0719(birthday)";
cmd.ExecuteNonQuery();
}
conn.Close();
cmd.CommandText = string.Format("select top {0} code, name, age, birthday, salary, summary, created {1} {2} from TEMP_TestTKK0719", num, (readClob ? ", remark" : string.Empty), (readBlob ? ", extends" : string.Empty));
conn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
object[] objs = new object[500];
dr.GetValues(objs);
}
}
conn.Close();
}
break;
}
}
internal static void Insert(int num, bool writeClob, bool writeBlob)
{
Stopwatch sw = new Stopwatch();
Random rnd = new Random();
for (int i = 0; i < num; i++)
{
switch (ConfigurationManager.AppSettings["dbtype"].ToUpper().Trim())
{
case "SYSTEM.DATA.ORACLECLIENT":
using (OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["connstr"].ConnectionString))
{
OracleCommand cmd = conn.CreateCommand();
conn.Open();
cmd.CommandText = string.Format("select 1 from user_tables t where t.table_name = upper('TEMP_TestTKK0719')");
object tableInited = cmd.ExecuteScalar();
if (tableInited == null || tableInited == DBNull.Value)
{
cmd.CommandText = @"CREATE TABLE TEMP_TestTKK0719(
code varchar2(50) NOT NULL,
name varchar2(100) NULL,
age int NULL,
birthday timestamp NULL,
salary decimal(10, 2) NULL,
summary varchar2(1000) NULL,
remark clob NULL,
extends blob NULL,
created timestamp,
CONSTRAINT PK__TEMP_TestTKK0719 PRIMARY KEY (code)
)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_Name on TEMP_TestTKK0719(name)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_age on TEMP_TestTKK0719(age)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_birthday on TEMP_TestTKK0719(birthday)";
cmd.ExecuteNonQuery();
}
conn.Close();
cmd.CommandText = string.Format("insert into TEMP_TestTKK0719(code, name, age, birthday, salary, summary, created {0} {1}) values(:code, :name, :age, :birthday, :salary, :summary, sysdate {2} {3})", (writeClob ? ", remark" : string.Empty), (writeBlob ? ", extends" : string.Empty), (writeClob ? ", :remark" : string.Empty), (writeBlob ? ", :extends" : string.Empty));
cmd.Parameters.Add(":code", Guid.NewGuid().ToString());
cmd.Parameters.Add(":name", StringUtils.GetRandomString(rnd.Next(10, 50), false, true, true, false, string.Empty));
cmd.Parameters.Add(":age", rnd.Next(20, 65));
cmd.Parameters.Add(":birthday", DateTime.Now.AddYears(-1 * rnd.Next(20, 60)));
cmd.Parameters.Add(":salary", Math.Round(50000 * rnd.NextDouble(), 2));
cmd.Parameters.Add(":summary", new string('x', rnd.Next(10, 200)));
if (writeClob)
{
cmd.Parameters.Add(":remark", bool.Parse(ConfigurationManager.AppSettings["randomLob"]) ? new string('y', rnd.Next(1, int.Parse(ConfigurationManager.AppSettings["clobMax"]))) : new string('y', int.Parse(ConfigurationManager.AppSettings["clobMax"])));
}
if (writeBlob)
{
cmd.Parameters.Add(":extends", Encoding.Default.GetBytes(bool.Parse(ConfigurationManager.AppSettings["randomLob"]) ? new string('z', rnd.Next(1, int.Parse(ConfigurationManager.AppSettings["blobMax"]))) : new string('z', int.Parse(ConfigurationManager.AppSettings["blobMax"]))));
}
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
break;
case "ODP.NET":
using (IDbConnection conn = ODPClientFactory.CreateConnection())
{
conn.ConnectionString = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
IDbCommand cmd = conn.CreateCommand();
conn.Open();
cmd.CommandText = string.Format("select 1 from user_tables t where t.table_name = upper('TEMP_TestTKK0719')");
object tableInited = cmd.ExecuteScalar();
if (tableInited == null || tableInited == DBNull.Value)
{
cmd.CommandText = @"CREATE TABLE TEMP_TestTKK0719(
code varchar2(50) NOT NULL,
name varchar2(100) NULL,
age int NULL,
birthday timestamp NULL,
salary decimal(10, 2) NULL,
summary varchar2(1000) NULL,
remark clob NULL,
extends blob NULL,
created timestamp,
CONSTRAINT PK__TEMP_TestTKK0719 PRIMARY KEY (code)
)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_Name on TEMP_TestTKK0719(name)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_age on TEMP_TestTKK0719(age)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_birthday on TEMP_TestTKK0719(birthday)";
cmd.ExecuteNonQuery();
}
conn.Close();
cmd.CommandText = string.Format("insert into TEMP_TestTKK0719(code, name, age, birthday, salary, summary, created {0} {1}) values(:code, :name, :age, :birthday, :salary, :summary, sysdate {2} {3})", (writeClob ? ", remark" : string.Empty), (writeBlob ? ", extends" : string.Empty), (writeClob ? ", :remark" : string.Empty), (writeBlob ? ", :extends" : string.Empty));
//cmd.Parameters.Add(":code", Guid.NewGuid().ToString());
var p0 = cmd.CreateParameter();
p0.DbType = DbType.String;
p0.ParameterName = ":code";
p0.Value = Guid.NewGuid().ToString();
cmd.Parameters.Add(p0);
//cmd.Parameters.Add(":name", StringUtils.GetRandomString(rnd.Next(10, 50), false, true, true, false, string.Empty));
var p1 = cmd.CreateParameter();
p1.DbType = DbType.String;
p1.ParameterName = ":name";
p1.Value = StringUtils.GetRandomString(rnd.Next(10, 50), false, true, true, false, string.Empty);
cmd.Parameters.Add(p1);
//cmd.Parameters.Add(":age", rnd.Next(20, 65));
var p2 = cmd.CreateParameter();
p2.DbType = DbType.Int32;
p2.ParameterName = ":age";
p2.Value = rnd.Next(20, 65);
cmd.Parameters.Add(p2);
//cmd.Parameters.Add(":birthday", DateTime.Now.AddYears(-1 * rnd.Next(20, 60)));
var p3 = cmd.CreateParameter();
p3.DbType = DbType.DateTime;
p3.ParameterName = ":birthday";
p3.Value = DateTime.Now.AddYears(-1 * rnd.Next(20, 60));
cmd.Parameters.Add(p3);
//cmd.Parameters.Add(":salary", Math.Round(50000 * rnd.NextDouble(), 2));
var p4 = cmd.CreateParameter();
p4.DbType = DbType.Double;
p4.ParameterName = ":salary";
p4.Value = Math.Round(50000 * rnd.NextDouble(), 2);
cmd.Parameters.Add(p4);
//cmd.Parameters.Add(":summary", new string('x', rnd.Next(10, 200)));
var p5 = cmd.CreateParameter();
p5.DbType = DbType.String;
p5.ParameterName = ":summary";
p5.Value = new string('x', rnd.Next(10, 200));
cmd.Parameters.Add(p5);
if (writeClob)
{
//cmd.Parameters.Add(":remark", bool.Parse(ConfigurationManager.AppSettings["randomLob"]) ? new string('y', rnd.Next(1, int.Parse(ConfigurationManager.AppSettings["clobMax"]))) : new string('y', int.Parse(ConfigurationManager.AppSettings["clobMax"])));
var p7 = cmd.CreateParameter();
p7.ParameterName = ":remark";
p7.DbType = DbType.String;
p7.Value = bool.Parse(ConfigurationManager.AppSettings["randomLob"]) ? new string('y', rnd.Next(1, int.Parse(ConfigurationManager.AppSettings["clobMax"]))) : new string('y', int.Parse(ConfigurationManager.AppSettings["clobMax"]));
cmd.Parameters.Add(p7);
}
if (writeBlob)
{
//cmd.Parameters.Add(":extends", Encoding.Default.GetBytes(bool.Parse(ConfigurationManager.AppSettings["randomLob"]) ? new string('z', rnd.Next(1, int.Parse(ConfigurationManager.AppSettings["blobMax"]))) : new string('z', int.Parse(ConfigurationManager.AppSettings["blobMax"]))));
var p8 = cmd.CreateParameter();
p8.ParameterName = ":extends";
p8.DbType = DbType.Binary;
p8.Value = Encoding.Default.GetBytes(bool.Parse(ConfigurationManager.AppSettings["randomLob"]) ? new string('z', rnd.Next(1, int.Parse(ConfigurationManager.AppSettings["blobMax"]))) : new string('z', int.Parse(ConfigurationManager.AppSettings["blobMax"])));
cmd.Parameters.Add(p8);
}
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
break;
case "ODAC":
using (ManagedOralceConnection conn = new ManagedOralceConnection(ConfigurationManager.ConnectionStrings["connstr"].ConnectionString))
{
ManagedOracleCommand cmd = conn.CreateCommand();
conn.Open();
cmd.CommandText = string.Format("select 1 from user_tables t where t.table_name = upper('TEMP_TestTKK0719')");
object tableInited = cmd.ExecuteScalar();
if (tableInited == null || tableInited == DBNull.Value)
{
cmd.CommandText = @"CREATE TABLE TEMP_TestTKK0719(
code varchar2(50) NOT NULL,
name varchar2(100) NULL,
age int NULL,
birthday timestamp NULL,
salary decimal(10, 2) NULL,
summary varchar2(1000) NULL,
remark clob NULL,
extends blob NULL,
created timestamp,
CONSTRAINT PK__TEMP_TestTKK0719 PRIMARY KEY (code)
)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_Name on TEMP_TestTKK0719(name)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_age on TEMP_TestTKK0719(age)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_birthday on TEMP_TestTKK0719(birthday)";
cmd.ExecuteNonQuery();
}
conn.Close();
cmd.CommandText = string.Format("insert into TEMP_TestTKK0719(code, name, age, birthday, salary, summary, created {0} {1}) values(:code, :name, :age, :birthday, :salary, :summary, sysdate {2} {3})", (writeClob ? ", remark" : string.Empty), (writeBlob ? ", extends" : string.Empty), (writeClob ? ", :remark" : string.Empty), (writeBlob ? ", :extends" : string.Empty));
cmd.Parameters.Add(":code", Guid.NewGuid().ToString());
cmd.Parameters.Add(":name", StringUtils.GetRandomString(rnd.Next(10, 50), false, true, true, false, string.Empty));
cmd.Parameters.Add(":age", rnd.Next(20, 65));
cmd.Parameters.Add(":birthday", DateTime.Now.AddYears(-1 * rnd.Next(20, 60)));
cmd.Parameters.Add(":salary", Math.Round(50000 * rnd.NextDouble(), 2));
cmd.Parameters.Add(":summary", new string('x', rnd.Next(10, 200)));
if (writeClob)
{
cmd.Parameters.Add(":remark", bool.Parse(ConfigurationManager.AppSettings["randomLob"]) ? new string('y', rnd.Next(1, int.Parse(ConfigurationManager.AppSettings["clobMax"]))) : new string('y', int.Parse(ConfigurationManager.AppSettings["clobMax"])));
}
if (writeBlob)
{
cmd.Parameters.Add(":extends", Encoding.Default.GetBytes(bool.Parse(ConfigurationManager.AppSettings["randomLob"]) ? new string('z', rnd.Next(1, int.Parse(ConfigurationManager.AppSettings["blobMax"]))) : new string('z', int.Parse(ConfigurationManager.AppSettings["blobMax"]))));
}
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
break;
default:
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["connstr"].ConnectionString))
{
SqlCommand cmd = conn.CreateCommand();
conn.Open();
cmd.CommandText = string.Format("select 1 from sys.tables t where t.name = 'TEMP_TestTKK0719'");
object tableInited = cmd.ExecuteScalar();
if (tableInited == null || tableInited == DBNull.Value)
{
cmd.CommandText = @"CREATE TABLE TEMP_TestTKK0719(
code varchar(50) NOT NULL,
name varchar(100) NULL,
age int NULL,
birthday datetime NULL,
salary decimal(10, 2) NULL,
summary nvarchar(1000) NULL,
remark varchar(max) NULL,
extends varbinary(max) NULL,
created datetime,
CONSTRAINT PK__TEMP_TestTKK0719 PRIMARY KEY (code)
)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_Name on TEMP_TestTKK0719(name)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_age on TEMP_TestTKK0719(age)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_birthday on TEMP_TestTKK0719(birthday)";
cmd.ExecuteNonQuery();
}
conn.Close();
cmd.CommandText = string.Format("insert into TEMP_TestTKK0719(code, name, age, birthday, salary, summary, created {0} {1}) values(@code, @name, @age, @birthday, @salary, @summary, getdate() {2} {3})", (writeClob ? ", remark" : string.Empty), (writeBlob ? ", extends" : string.Empty), (writeClob ? ", @remark" : string.Empty), (writeBlob ? ", @extends" : string.Empty));
cmd.Parameters.AddWithValue("@code", Guid.NewGuid().ToString());
cmd.Parameters.AddWithValue("@name", StringUtils.GetRandomString(rnd.Next(10, 50), false, true, true, false, string.Empty));
cmd.Parameters.AddWithValue("@age", rnd.Next(20, 65));
cmd.Parameters.AddWithValue("@birthday", DateTime.Now.AddYears(-1 * rnd.Next(20, 60)));
cmd.Parameters.AddWithValue("@salary", Math.Round(50000 * rnd.NextDouble(), 2));
cmd.Parameters.AddWithValue("@summary", new string('x', rnd.Next(10, 200)));
if (writeClob)
{
cmd.Parameters.AddWithValue("@remark", bool.Parse(ConfigurationManager.AppSettings["randomLob"]) ? new string('y', rnd.Next(1, int.Parse(ConfigurationManager.AppSettings["clobMax"]))) : new string('y', int.Parse(ConfigurationManager.AppSettings["clobMax"])));
}
if (writeBlob)
{
cmd.Parameters.AddWithValue("@extends", Encoding.Default.GetBytes(bool.Parse(ConfigurationManager.AppSettings["randomLob"]) ? new string('z', rnd.Next(1, int.Parse(ConfigurationManager.AppSettings["blobMax"]))) : new string('z', int.Parse(ConfigurationManager.AppSettings["blobMax"]))));
}
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
break;
}
}
}