public static DatabaseEnum TransDatabaseSoft(DatabaseSoft p_soft, bool p_isAdvancedSetting = false)
{
var e = DatabaseEnum.OleDb;
if (!p_isAdvancedSetting)
{
switch (p_soft)
{
case DatabaseSoft.SQLServer:
e = DatabaseEnum.SQLServer;
break;
case DatabaseSoft.Oracle:
e = DatabaseEnum.Oracle;
break;
case DatabaseSoft.MySql:
e = DatabaseEnum.MySql;
break;
case DatabaseSoft.PostgreSQL:
e = DatabaseEnum.PostgreSQL;
break;
}
}
return(e);
}
public string GetTestPatientInfoSql(DatabaseSoft databaseSoft)
{
var sb = new StringBuilder();
foreach (TableFieldMapModel field in PatientTableMap)
{
if (string.IsNullOrEmpty(field.TargetField))
{
continue;
}
if (!field.TargetField.StartsWith("[") || !field.TargetField.EndsWith("]"))
{
return(null);
}
string target = field.TargetField.Replace("[", "");
target = target.Replace("]", "");
if (sb.Length > 0)
{
sb.Append(",");
}
//sb.Append(field.TargetField);
if (databaseSoft == DatabaseSoft.PostgreSQL)
{
sb.AppendFormat("\"{0}\" as {1}", target, field.LocalField);
}
else
{
sb.AppendFormat("{0} as {1}", target, field.LocalField);
}
}
string sql = string.Format("select {0} from {1}", sb, PatientTableName);
if (databaseSoft == DatabaseSoft.PostgreSQL)
{
sql = string.Format("select {0} from \"{1}\"", sb, PatientTableName);
}
return(sql);
}
public CallBackSqlAndParam GetSqlStringByParam(DatabaseSoft p_soft)
{
var cbsap = new CallBackSqlAndParam();
cbsap.Parameters = new List <DbParameterAndKey>();
foreach (TableFieldMapModel item in CallbackTabelMap)
{
if (string.IsNullOrEmpty(item.LocalField))
{
continue;
}
if (string.IsNullOrEmpty(item.TargetField) && CallbackType != "存储过程")
{
continue;
}
//string pn = item.TargetField;
//pn = pn.Replace("[", "").Replace("]", "");
var pk = new DbParameterAndKey();
pk.Key = item.TargetField;
;
pk.IsWhere = item.IsWhere;
DbParameter p = null;
switch (p_soft)
{
case DatabaseSoft.SQLServer:
var sqlParam = new SqlParameter();
sqlParam.SqlDbType = GetSqlServerType(item.Type);
p = sqlParam;
break;
case DatabaseSoft.Oracle:
var oracleParam = new OracleParameter();
oracleParam.OracleType = GetOracleType(item.Type);
p = oracleParam;
break;
case DatabaseSoft.MySql:
var mySqlParam = new MySqlParameter();
mySqlParam.MySqlDbType = GetMySqlType(item.Type);
p = mySqlParam;
break;
case DatabaseSoft.PostgreSQL:
var npSqlParameter = new NpgsqlParameter();
npSqlParameter.NpgsqlDbType = GetPostgreSQLType(item.Type);
p = npSqlParameter;
break;
}
p.ParameterName = item.LocalField;
if (item.Size > 0)
{
p.Size = item.Size;
}
pk.Parameter = p;
cbsap.Parameters.Add(pk);
}
string sign = "@";
if (p_soft == DatabaseSoft.Oracle)
{
sign = ":";
}
if (CallbackType == "表")
{
if (TargetTableUpdateType == "更新")
{
//sb.AppendFormat(" Update {0}", this.TargetTableName);
var sb = new StringBuilder();
var whereSb = new StringBuilder();
foreach (DbParameterAndKey p in cbsap.Parameters)
{
string key = p.Key.Trim();
if (string.IsNullOrEmpty(key))
{
continue;
}
if (p.IsWhere)
{
if (whereSb.Length > 0)
{
whereSb.Append(" and ");
}
if (p_soft == DatabaseSoft.PostgreSQL)
{
whereSb.AppendFormat("\"{0}\"={1}{0}", p.Parameter.ParameterName, sign);
}
else
{
whereSb.AppendFormat("{0}={1}{0}", p.Parameter.ParameterName, sign);
}
}
else
{
if (sb.Length > 0)
{
sb.Append(" , ");
}
if (key.ToUpper().StartsWith("@TARGET"))
{
sb.AppendFormat("{0}={0}{1}", p.Parameter.ParameterName, key.Substring(7));
continue;
}
else
{
if (p_soft == DatabaseSoft.PostgreSQL)
{
sb.AppendFormat("\"{0}\"={1}{0}", p.Parameter.ParameterName, sign);
}
else
{
sb.AppendFormat("{0}={1}{0}", p.Parameter.ParameterName, sign);
}
}
}
}
if (p_soft == DatabaseSoft.PostgreSQL)
{
cbsap.UpdateSql = string.Format(" update \"{0}\" Set {1} where {2}", TargetTableName, sb, whereSb);
}
else
{
cbsap.UpdateSql = string.Format(" update {0} Set {1} where {2}", TargetTableName, sb, whereSb);
}
}
//sb.AppendFormat(" Insert into {0}", this.TargetTableName);
var field = new StringBuilder();
var sbParams = new StringBuilder();
foreach (DbParameterAndKey p in cbsap.Parameters)
{
if (string.IsNullOrEmpty(p.Key))
{
continue;
}
string pn = p.Parameter.ParameterName;
string tmpfield = pn;
if (p_soft == DatabaseSoft.PostgreSQL)
{
tmpfield = string.Format("\"{0}\"", tmpfield);
}
if (string.IsNullOrEmpty(pn))
{
continue;
}
if (p.Key.Trim().ToUpper().StartsWith("@TARGET"))
{
//sb.AppendFormat("{0}={0}{1}", p.Parameter.ParameterName, key.Substring(6));
string tempKey = p.Key.Trim().Substring(7).Trim();
if (tempKey.Length > 1)
{
p.Key = tempKey.Substring(1).Trim();
}
else
{
p.Key = string.Empty;
}
}
if (field.Length > 0)
{
field.Append(',');
sbParams.Append(',');
}
field.Append(tmpfield);
//if (p_soft != DatabaseSoft.MySql)
//{
// sbParams.AppendFormat("?");
//}
//else
//{
sbParams.AppendFormat("{0}{1}", sign, pn);
//}
}
cbsap.InsertSql = string.Format("Insert into {0} ({1}) values ({2})", TargetTableName, field, sbParams);
if (p_soft == DatabaseSoft.PostgreSQL)
{
cbsap.InsertSql = string.Format("Insert into \"{0}\" ({1}) values ({2})", TargetTableName, field, sbParams);
}
}
else if (CallbackType == "存储过程")
{
var sb = new StringBuilder();
foreach (DbParameterAndKey p in cbsap.Parameters)
{
if (sb.Length > 0)
{
sb.Append(',');
}
if (p_soft != DatabaseSoft.SQLServer)
{
sb.Append(sign);
}
sb.AppendFormat("{0}", p.Parameter.ParameterName);
}
string exec = " EXEC {0} {1} ";
if (p_soft != DatabaseSoft.SQLServer)
{
exec = " Call {0} ({1})";
}
cbsap.InsertSql = string.Format(exec, TargetProcName, sb);
}
// = sql;
return(cbsap);
}
public string GetPatientInfoSql(DatabaseSoft databaseSoft)
{
var sb = new StringBuilder();
var sbWhere = new StringBuilder();
int index = 0;
foreach (TableFieldMapModel field in PatientTableMap)
{
if (string.IsNullOrEmpty(field.TargetField))
{
continue;
}
if (sb.Length > 0)
{
sb.Append(",");
}
string targetField = field.TargetField.Trim();
if (targetField.StartsWith("[") && targetField.EndsWith("]"))
{
string tempField = targetField.Replace("[", string.Empty).Replace("]", string.Empty);
string localField = field.LocalField.ToUpper();
if (databaseSoft == DatabaseSoft.PostgreSQL)
{
tempField = string.Format("\"{0}\"", tempField);
localField = string.Format("\"{0}\"", localField);
}
if (field.LocalField.ToUpper() == "GENDER")
{
sb.AppendFormat(
"(case {0} when '男' then 0 when '女' then 1 when '1' then 1 else 0 end) as {1} ", tempField,
localField);
}
else
{
sb.AppendFormat("{0} as {1}", tempField, localField);
}
if (field.IsWhere)
{
if (sbWhere.Length > 0)
{
sbWhere.Append(" AND ");
}
if (field.LocalField.ToUpper() == "PATIENTID")
{
sbWhere.AppendFormat(" {0} Like '{1}%'", tempField, "{" + index++ + "}");
}
else if (field.LocalField.ToUpper() == "CHECKTYPE")
{
sbWhere.AppendFormat(" {0} = '{1}'", tempField, "{" + index++ + "}");
}
else
{
sbWhere.AppendFormat(" {0} = {1}", tempField, "{" + index++ + "}");
}
}
}
else if ((field.TargetField.StartsWith("<") && field.TargetField.EndsWith(">")))
{
}
else
{
sb.AppendFormat("'{0}' as {1}", field.TargetField, field.LocalField.ToUpper());
}
}
string sql = string.Format("select {0} from {1} where {2}", sb, PatientTableName, sbWhere);
if (databaseSoft == DatabaseSoft.PostgreSQL)
{
sql = string.Format("select {0} from \"{1}\" where {2}", sb, PatientTableName, sbWhere);
}
bool isExist =
PatientTableMap.Any(
i => !string.IsNullOrEmpty(i.TargetField) && i.LocalField.ToUpper().Contains("PATIENTID"));
if (isExist)
{
if (databaseSoft == DatabaseSoft.PostgreSQL)
{
sql += string.Format(" order by \"{0}\" desc", "PATIENTID");
}
else
{
sql += " order by PATIENTID desc";
}
}
return(sql);
}
