public DataTable ExecuteSelectQuery(string sQuery)
{
string sFuncName = "ExecuteSelectQuery()";
string sConstr = ConfigurationManager.ConnectionStrings["adapterConnection"].ToString();
System.Data.Odbc.OdbcConnection oCon = new System.Data.Odbc.OdbcConnection(sConstr);
System.Data.Odbc.OdbcCommand oCmd = new System.Data.Odbc.OdbcCommand();
DataSet oDs = new DataSet();
try
{
oCon.Open();
oCmd.CommandType = CommandType.Text;
oCmd.CommandText = sQuery;
oCmd.Connection = oCon;
oCmd.CommandTimeout = 120;
System.Data.Odbc.OdbcDataAdapter da = new System.Data.Odbc.OdbcDataAdapter(oCmd);
da.Fill(oDs);
}
catch (Exception ex)
{
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToLogFile_Debug("Completed with Error", sFuncName);
}
oCon.Dispose();
throw new Exception(ex.Message);
}
return(oDs.Tables[0]);
}
public static object ExecuteScalarOnDB(string sSQL, string s_ConnectionString)
{
System.Data.Odbc.OdbcConnection QConnection = null;
System.Data.Odbc.OdbcCommand QCommand = null;
try
{
QConnection = new System.Data.Odbc.OdbcConnection(s_ConnectionString);
QCommand = new System.Data.Odbc.OdbcCommand(sSQL, QConnection);
QConnection.Open();
return(QCommand.ExecuteScalar());
}
finally
{
if (QCommand != null)
{
QCommand.Dispose();
}
QCommand = null;
if (QConnection != null && QConnection.State != System.Data.ConnectionState.Closed)
{
QConnection.Close();
}
if (QConnection != null)
{
QConnection.Dispose();
}
QConnection = null;
}
}
public string ExecuteNonQuery(string sQuery)
{
string sFuncName = "ExecuteNonQuery()";
string sConstr = ConfigurationManager.ConnectionStrings["adapterConnection"].ToString();
System.Data.Odbc.OdbcConnection oCon = new System.Data.Odbc.OdbcConnection(sConstr);
System.Data.Odbc.OdbcCommand oCmd = new System.Data.Odbc.OdbcCommand();
try
{
oCon.Open();
oCmd.CommandType = CommandType.Text;
oCmd.CommandText = sQuery;
oCmd.Connection = oCon;
oCmd.CommandTimeout = 120;
oCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToLogFile_Debug("Completed with ERROR", sFuncName);
}
oCon.Dispose();
throw new Exception(ex.Message);
}
return("SUCCESS");
}
public DataTable ExecuteSQLQuery(string sQuery, string sCompanyCode, System.Data.Odbc.OdbcParameter[] param)
{
string sFuncName = "ExecuteSQLQuery()";
string sConstr = ConfigurationManager.ConnectionStrings["DBSSG"].ToString();
string[] sArray = sConstr.Split(';');
string sSplitCompany = sConstr.Split(';').Last();
string sSplit1 = sSplitCompany.Split('=').First();
string sCompanyGenerate = sSplit1 + "=" + sCompanyCode;
sConstr = sArray[0] + ";" + sArray[1] + ";" + sArray[2] + ";" + sArray[3] + ";" + sCompanyGenerate;
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToLogFile_Debug("Connection String : " + sConstr, sFuncName);
}
System.Data.Odbc.OdbcConnection oCon = new System.Data.Odbc.OdbcConnection(sConstr);
System.Data.Odbc.OdbcCommand oCmd = new System.Data.Odbc.OdbcCommand();
DataSet oDs = new DataSet();
try
{
oCon.Open();
oCmd.CommandType = CommandType.Text;
oCmd.CommandText = sQuery;
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToLogFile_Debug("SQL Query : " + sQuery, sFuncName);
}
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToLogFile_Debug("Before adding Parameters", sFuncName);
}
foreach (var item in param)
{
oCmd.Parameters.Add(item);
}
//oCmd.Parameters.Add((param);
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToLogFile_Debug("After adding parameters", sFuncName);
}
oCmd.Connection = oCon;
oCmd.CommandTimeout = 120;
System.Data.Odbc.OdbcDataAdapter da = new System.Data.Odbc.OdbcDataAdapter(oCmd);
da.Fill(oDs);
}
catch (Exception ex)
{
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToLogFile_Debug("Completed With Error", sFuncName);
}
oCon.Dispose();
throw new Exception(ex.Message);
}
return(oDs.Tables[0]);
}
protected override void SavePersonalizationBlob
(WebPartManager webPartManager, string path, string userName,
byte[] dataBlob)
{
System.Data.Odbc.OdbcCommand updateCommand = null;
System.Data.Odbc.OdbcConnection updateConnection = null;
string sSQL = null;
try
{
sSQL = "SELECT COUNT(`username`) FROM `personalization` WHERE `username` = '" + userName + "' AND `path` = '" + path + "' and `applicationname` = '" + m_ApplicationName + "';";
updateConnection = new System.Data.Odbc.OdbcConnection(System.Configuration.ConfigurationManager.ConnectionStrings[m_ConnectionStringName].ToString());
if (int.Parse(RawDBQuery.ExecuteScalarOnDB(sSQL, System.Configuration.ConfigurationManager.ConnectionStrings[m_ConnectionStringName].ToString()).ToString()) > 0)
{
sSQL = "UPDATE `personalization` SET `personalizationblob` = ? WHERE `username` = ? AND `applicationname` = ? AND `path` = ?;";
updateCommand = new System.Data.Odbc.OdbcCommand(sSQL, updateConnection);
updateCommand.Parameters.Clear();
updateCommand.Parameters.Add(new System.Data.Odbc.OdbcParameter("personalizationblob", dataBlob));
updateCommand.Parameters.Add(new System.Data.Odbc.OdbcParameter("username", userName));
updateCommand.Parameters.Add(new System.Data.Odbc.OdbcParameter("applicationname", m_ApplicationName));
updateCommand.Parameters.Add(new System.Data.Odbc.OdbcParameter("path", path));
}
else
{
sSQL = "INSERT INTO `personalization` (`username`,`path`,`applicationname`,`personalizationblob`) VALUES (?, ?, ?, ?);";
updateCommand = new System.Data.Odbc.OdbcCommand(sSQL, updateConnection);
updateCommand.Parameters.Clear();
updateCommand.Parameters.Add(new System.Data.Odbc.OdbcParameter("username", userName));
updateCommand.Parameters.Add(new System.Data.Odbc.OdbcParameter("path", path));
updateCommand.Parameters.Add(new System.Data.Odbc.OdbcParameter("applicationname", m_ApplicationName));
updateCommand.Parameters.Add(new System.Data.Odbc.OdbcParameter("personalizationblob", dataBlob));
}
updateConnection.Open();
updateCommand.ExecuteNonQuery();
}
finally
{
if (updateConnection != null)
{
if (updateConnection.State != System.Data.ConnectionState.Closed)
{
updateConnection.Close();
}
else
{
updateConnection.Dispose();
}
}
updateConnection = null;
if (updateCommand != null)
{
updateCommand.Dispose();
}
updateCommand = null;
sSQL = null;
}
}
// Code changes done here with Param
public DataTable ExecuteSelectQuery(string sQuery, System.Data.Odbc.OdbcParameter[] param)
{
string sFuncName = "ExecuteSelectQuery()";
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToLogFile_Debug("Starting Function", sFuncName);
}
string sConstr = ConfigurationManager.ConnectionStrings["adapterConnection"].ToString();
System.Data.Odbc.OdbcConnection oCon = new System.Data.Odbc.OdbcConnection(sConstr);
System.Data.Odbc.OdbcCommand oCmd = new System.Data.Odbc.OdbcCommand();
DataSet oDs = new DataSet();
try
{
oCon.Open();
oCmd.CommandType = CommandType.Text;
oCmd.CommandText = sQuery;
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToLogFile_Debug("SQL Query : " + sQuery, sFuncName);
}
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToLogFile_Debug("Before adding Parameters", sFuncName);
}
foreach (var item in param)
{
oCmd.Parameters.Add(item);
}
//oCmd.Parameters.Add((param);
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToLogFile_Debug("After adding parameters", sFuncName);
}
oCmd.Connection = oCon;
oCmd.CommandTimeout = 120;
System.Data.Odbc.OdbcDataAdapter da = new System.Data.Odbc.OdbcDataAdapter(oCmd);
da.Fill(oDs);
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToLogFile_Debug("Completed with Success", sFuncName);
}
}
catch (Exception ex)
{
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToLogFile_Debug("Completed with Error", sFuncName);
}
oCon.Dispose();
throw new Exception(ex.Message);
}
return(oDs.Tables[0]);
}
public void Close()
{
if (_odbcConnection == null)
{
return;
}
try
{
_odbcConnection.Close();
_odbcConnection.Dispose();
_odbcConnection = null;
}
catch (System.Exception ex)
{
_odbcConnection = null;
throw ex;
}
}
public string ExecuteQuery(string sQuery, string sCompanyCode)
{
string sFuncName = "ExecuteQuery()";
string sConstr = ConfigurationManager.ConnectionStrings["DBSSG"].ToString();
string[] sArray = sConstr.Split(';');
string sSplitCompany = sConstr.Split(';').Last();
string sSplit1 = sSplitCompany.Split('=').First();
string sCompanyGenerate = sSplit1 + "=" + sCompanyCode;
sConstr = sArray[0] + ";" + sArray[1] + ";" + sArray[2] + ";" + sArray[3] + ";" + sCompanyGenerate;
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToLogFile_Debug("Connection String : " + sConstr, sFuncName);
}
System.Data.Odbc.OdbcConnection oCon = new System.Data.Odbc.OdbcConnection(sConstr);
System.Data.Odbc.OdbcCommand oCmd = new System.Data.Odbc.OdbcCommand();
try
{
oCon.Open();
oCmd.CommandType = CommandType.Text;
oCmd.CommandText = sQuery;
oCmd.Connection = oCon;
oCmd.CommandTimeout = 120;
oCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToLogFile_Debug("Completed with ERROR", sFuncName);
}
oCon.Dispose();
throw new Exception(ex.Message);
}
return("SUCCESS");
}
/// <summary>
/// 获取医保结算结果
/// </summary>
/// <returns></returns>
public int GetSiResult(string path, string tablename, ref Neusoft.HISFC.Models.RADT.PatientInfo p, ref string errTxt)
{
//string path = filePath.Substring(0, filePath.LastIndexOf(@"\") + 1);
//string tablename = filePath.Substring(filePath.LastIndexOf(@"\") + 1, filePath.Length - filePath.LastIndexOf(@"\") - 1);
string connect = @"Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277; Dbq=" + path;
System.Data.Odbc.OdbcConnection myconn = new System.Data.Odbc.OdbcConnection(connect);
string select = "select GRSHBZH ,ZYH ,ZYZJE ,SBZFJE ,GWYBZJE ,GRZFJE ,ZFYY from "+ tablename;
System.Data.Odbc.OdbcCommand cmSelect = new System.Data.Odbc.OdbcCommand(select, myconn);
System.Data.Odbc.OdbcDataReader cmReader;
try
{
myconn.Open();
cmReader = cmSelect.ExecuteReader();
}
catch (Exception ex)
{
errTxt = "导出医保信息失败！" + ex.Message;
return(-1);
}
if (!cmReader.Read())
{
errTxt = "医保结算数据不存在！";
return(-1);
}
p.IDCard = cmReader[0].ToString();
p.PID.PatientNO = cmReader[1].ToString();
p.SIMainInfo.TotCost = Neusoft.FrameWork.Function.NConvert.ToDecimal(cmReader[2]);
p.SIMainInfo.PubCost = Neusoft.FrameWork.Function.NConvert.ToDecimal(cmReader[3]);
p.SIMainInfo.OfficalCost = Neusoft.FrameWork.Function.NConvert.ToDecimal(cmReader[4]);
p.SIMainInfo.OwnCost = Neusoft.FrameWork.Function.NConvert.ToDecimal(cmReader[5]);
p.SIMainInfo.ID = cmReader[6].ToString();//自费原因
cmReader.Close();
cmSelect.Dispose();
myconn.Dispose();
return(1);
}
public static System.Data.Odbc.OdbcDataReader ExecuteReaderQueryOnDB(string sSQL, string s_ConnectionString)
{
System.Data.Odbc.OdbcConnection QConnection = null;
System.Data.Odbc.OdbcCommand QCommand = null;
try
{
QConnection = new System.Data.Odbc.OdbcConnection(s_ConnectionString);
QCommand = new System.Data.Odbc.OdbcCommand(sSQL, QConnection);
QConnection.Open();
return QCommand.ExecuteReader();
}
finally
{
if (QCommand != null) QCommand.Dispose();
QCommand = null;
if (QConnection != null && QConnection.State != System.Data.ConnectionState.Closed) QConnection.Close();
if (QConnection != null) QConnection.Dispose();
QConnection = null;
}
}
protected override void SavePersonalizationBlob(WebPartManager webPartManager, string path, string userName,
byte[] dataBlob)
{
System.Data.Odbc.OdbcCommand updateCommand = null;
System.Data.Odbc.OdbcConnection updateConnection = null;
string sSQL = null;
try
{
sSQL = "SELECT COUNT(`username`) FROM `personalization` WHERE `username` = '"+userName+"' AND `path` = '"+path+"' and `applicationname` = '"+m_ApplicationName+"';";
updateConnection = new System.Data.Odbc.OdbcConnection(System.Configuration.ConfigurationManager.ConnectionStrings[m_ConnectionStringName].ToString());
if (int.Parse(RawDBQuery.ExecuteScalarOnDB(sSQL, System.Configuration.ConfigurationManager.ConnectionStrings[m_ConnectionStringName].ToString()).ToString()) > 0)
{
sSQL = "UPDATE `personalization` SET `personalizationblob` = ? WHERE `username` = ? AND `applicationname` = ? AND `path` = ?;";
updateCommand = new System.Data.Odbc.OdbcCommand(sSQL,updateConnection);
updateCommand.Parameters.Clear();
updateCommand.Parameters.Add(new System.Data.Odbc.OdbcParameter("personalizationblob",dataBlob));
updateCommand.Parameters.Add(new System.Data.Odbc.OdbcParameter("username",userName));
updateCommand.Parameters.Add(new System.Data.Odbc.OdbcParameter("applicationname",m_ApplicationName));
updateCommand.Parameters.Add(new System.Data.Odbc.OdbcParameter("path",path));
}
else
{
sSQL = "INSERT INTO `personalization` (`username`,`path`,`applicationname`,`personalizationblob`) VALUES (?, ?, ?, ?);";
updateCommand = new System.Data.Odbc.OdbcCommand(sSQL,updateConnection);
updateCommand.Parameters.Clear();
updateCommand.Parameters.Add(new System.Data.Odbc.OdbcParameter("username",userName));
updateCommand.Parameters.Add(new System.Data.Odbc.OdbcParameter("path",path));
updateCommand.Parameters.Add(new System.Data.Odbc.OdbcParameter("applicationname",m_ApplicationName));
updateCommand.Parameters.Add(new System.Data.Odbc.OdbcParameter("personalizationblob",dataBlob));
}
updateConnection.Open();
updateCommand.ExecuteNonQuery();
}
finally
{
if (updateConnection != null)
if (updateConnection.State != System.Data.ConnectionState.Closed)
updateConnection.Close();
else
updateConnection.Dispose();
updateConnection = null;
if (updateCommand != null) updateCommand.Dispose();
updateCommand = null;
sSQL = null;
}
}