private object execute_scalar(string sql, DB_SERVER ent)
{
if (!IsRun())
{
return(0);
}
object reObj = 0;
string connStr = GetDbConnStr(ent);
try
{
switch (ent.TYPE.ToUpper())
{
case "DB2":
reObj = DbHelper.DB2Helper.ExecuteScalar(connStr, CommandType.Text, sql);
break;
case "ORACLE":
reObj = DbHelper.OracleHelper.ExecuteScalar(connStr, CommandType.Text, sql);
break;
}
log(string.Format("执行操作语句成功【{0}】在【{1}】", sql, ent.NICKNAME));
}
catch (Exception e)
{
ErrorScriptTask(e);
log(string.Format("执行操作语句失败【{0}】在【{1}】", sql, ent.NICKNAME));
log(string.Format("错误原因【{0}】", e.Message));
}
return(reObj);
}
public void truncate_table(string tables, DB_SERVER ent)
{
foreach (string table in SplitString(tables))
{
string schema = ent.UID;
string scTable = GetSchemaTableName(table, schema);
if (is_table_exists(tables, ent))
{
try
{
switch (ent.TYPE.ToUpper())
{
case "DB2":
execute("ALTER TABLE " + scTable + " ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE", ent);
break;
case "Sql":
break;
default:
execute("TRUNCATE TABLE " + scTable, ent);
break;
}
log(string.Format("清除表资料{0}成功", scTable));
}
catch (Exception e)
{
_isRun = false;
log(string.Format("清除表资料{0}失败", scTable));
log(string.Format("失败原因：{0}", e.Message));
}
}
}
}
public int execute(string sql, DB_SERVER ent)
{
if (!IsRun())
{
return(0);
}
int reObj = 0;
try
{
log(string.Format("在【{1}】执行语句【{0}】", sql, _dbNickName));
string connStr = GetDbConnStr(ent);
switch (ent.TYPE.ToUpper())
{
case "ORACLE":
reObj = DbHelper.OracleHelper.ExecuteNonQuery(connStr, CommandType.Text, sql);
break;
}
log(string.Format("执行成功", sql, _dbNickName));
}
catch (Exception e)
{
ErrorScriptTask(e);
log(string.Format("在【{1}】执行语句失败【{0}】", sql, _dbNickName));
log(string.Format("错误原因【{0}】", e.Message));
}
return(reObj);
}
public void DB_SERVERConstructorTest()
{
DB_SERVER db = new DB_SERVER();
var u = db.User.FirstOrDefault(e => e.UserId == "1");
Assert.Inconclusive("TODO: Implement code to verify target");
}
/// <summary>
/// 获取连接字符串
/// </summary>
/// <param name="ent"></param>
/// <returns></returns>
private string GetDbConnStr(DB_SERVER ent)
{
switch (ent.TYPE.ToUpper())
{
case "DB2":
return(string.Format("Server={0}:{1};Database={2};UID={3};PWD={4};Connection Timeout =3600", ent.IP, ent.PORT, ent.DBNAME, ent.UID, ent.PASSWORD));
case "ORACLE":
return(string.Format("Data Source={0}:{1}/{2};User Id={3};Password={4};Connection Timeout =3600", ent.IP, ent.PORT, ent.DBNAME, ent.UID, ent.PASSWORD));
}
return("");
}
public DataSet blFill_With_Schema(SortedList <String, String> Parameters, String spname)
{
DataSet ds = new DataSet();
try
{
ds = DB_SERVER.dlFill_Schema(Parameters, spname);
}
catch (Exception err)
{
err.GetBaseException();
}
return(ds);
}
public DataSet blFill_Combo_name(List <string> para_name, List <string> para_value, String spname)
{
DataSet ds = new DataSet();
try
{
ds = DB_SERVER.dlFill_combo_name(para_name, para_value, spname);
}
catch (Exception err)
{
err.GetBaseException();
}
return(ds);
}
public DataSet blFill_Para_Name(Dictionary <String, String> para, String spname)
{
DataSet ds = new DataSet();
try
{
ds = DB_SERVER.ExecuteStoredProcedureWithParametersWithName(para, spname);
}
catch (Exception err)
{
err.GetBaseException();
}
return(ds);
}
public DataSet blFill_para(List <string> para, String spname)
{
DataSet ds = new DataSet();
try
{
ds = DB_SERVER.dlFill1(para, spname);
}
catch (Exception err)
{
err.GetBaseException();
}
return(ds);
}
public DataSet blFill_Para_Name(List <String> paraname, List <String> parameterList, String spname)
{
DataSet ds = new DataSet();
try
{
ds = DB_SERVER.dlFill1(paraname, parameterList, spname);
}
catch (Exception err)
{
err.GetBaseException();
}
return(ds);
}
public bool is_table_exists(string tables, DB_SERVER ent)
{
// Oracle系统表存放的是大写的表名，查找时须转换为大写
foreach (string table in SplitString(tables.ToUpper()))
{
string schema = ent.UID;
string scTable = table;
if (execute_scalar(GetTableExistsCommand(scTable, ent), ent) == null)
{
return(false);
}
}
return(true);
}
public DataSet blFill_With_Schema(String spname)
{
DataSet ds = new DataSet();
try
{
ds = DB_SERVER.dlFill_Schema(spname);
}
catch (Exception err)
{
err.GetBaseException();
}
return(ds);
}
public void drop_table(string tables, DB_SERVER ent)
{
log(string.Format("开始删除表【{0}】在【{1}】", tables, ent.NICKNAME));
truncate_table(tables, ent);
foreach (string table in SplitString(tables))
{
string schema = ent.UID;
string scTable = table;
if (is_table_exists(scTable, ent))
{
execute("DROP TABLE " + scTable, ent);
}
}
log(string.Format("结束删除表【{0}】在【{1}】", tables, ent.NICKNAME));
}
private ObservableCollection <tPatient> LoadPatient()
{
var listPatient = new ObservableCollection <tPatient>();
DB_SERVER db = new DB_SERVER();
var listPatientFromDB = db.Patient;
foreach (var item in listPatientFromDB)
{
listPatient.Add(new tPatient {
PatientId = item.PatientId, FirstName = item.FirstName, LastName = item.LastName
});
}
return(listPatient);
}
private ObservableCollection <tDoctor> LoadDoctor()
{
var listDoctor = new ObservableCollection <tDoctor>();
DB_SERVER db = new DB_SERVER();
var listDoctorFromDB = db.Doctor;
foreach (var item in listDoctorFromDB)
{
listDoctor.Add(new tDoctor {
DoctorId = item.DoctorId, FirstName = item.FirstName, LastName = item.LastName
});
}
return(listDoctor);
}
private string GetTableExistsCommand(string table, DB_SERVER ent)
{
string result;
string schema = ent.UID;
string tableName = table;
switch (ent.TYPE.ToUpper())
{
case "DB2":
result = string.Format(SqlSelectIsTableWithOwnerExistsDB2, schema, tableName);
break;
default:
result = string.Format(SqlSelectIsTableWithOwnerExists, schema, tableName);
break;
}
return(result);
}
public bool checkConnection()
{
return(DB_SERVER.checkConnection() == true ? true : false);
}
public bool connectedYN()
{
return(DB_SERVER.OpenConnection());
}
public int ExecuteNonQuery(Dictionary <string, string> Parameter, string Query)
{
return(DB_SERVER.ExecuteNonQuery(Parameter, Query));
}
public DataSet ExecuteQuery(string Query)
{
return(DB_SERVER.ExecuteQuery(Query));
}
public bool checkConnection(string newconnectionstring)
{
return(DB_SERVER.checkConnection(newconnectionstring) == true ? true : false);
}
public int ExecuteNonQuery(string Query)
{
return(DB_SERVER.ExecuteNonQuery(Query));
}
