调用存储过程实例

[WebMethod]
public DataSet EmrGetLongOrderData(string strPatientID, string intVisitID)
{
DataSet dsOrder = new DataSet();
//转化服务器日期格式
System.Globalization.CultureInfo myCI = new System.Globalization.CultureInfo("zh-CN", true);
myCI.DateTimeFormat.ShortDatePattern = "yyyy-M-d";
System.Threading.Thread.CurrentThread.CurrentCulture = myCI;
//---添加日志机制
string strExceptionSql = string.Empty;
string strDBType = string.Empty;
//连接HIS数据库串
ConnectionStringSettings strHis = ConfigurationManager.ConnectionStrings[strHISConnectionString];
ConnectionStringSettings strEmr = ConfigurationManager.ConnectionStrings[strEMRConnectionString];

DbProviderFactory factHis = DbProviderFactories.GetFactory(strHis.ProviderName);
DbProviderFactory factEmr = DbProviderFactories.GetFactory(strEmr.ProviderName);

using (DbConnection connEmr = factEmr.CreateConnection())
{
connEmr.ConnectionString = strEmr.ConnectionString;
connEmr.Open();
try
{
DbCommand cmdEmr = connEmr.CreateCommand();
DbDataAdapter daEmr = factEmr.CreateDataAdapter();
//科室不取mr_on_line 当前ward_code，改成取pat_visit的ward_admission_to 李菲2011-12-14
cmdEmr.CommandText = "select a.inp_no,a.ward_admission_to,a.baby_flag from pat_visit a "
+ " where a.patient_id='" + strPatientID + "' and a.visit_id='" + intVisitID + "'";
strExceptionSql = cmdEmr.CommandText;//记录日志
strDBType = strDBEmr;
DataSet dsInpNo = new DataSet();
daEmr.SelectCommand = cmdEmr;
daEmr.Fill(dsInpNo);

string strInpNo = "", strWardCode = "";
int iBabyFlag = 0;
if (dsInpNo.Tables.Count > 0)
{
if (dsInpNo.Tables[0].Rows.Count > 0)
{
strInpNo = dsInpNo.Tables[0].Rows[0]["inp_no"].ToString();
strWardCode = dsInpNo.Tables[0].Rows[0]["ward_admission_to"].ToString();
iBabyFlag = Convert.ToInt32(dsInpNo.Tables[0].Rows[0]["baby_flag"].ToString());
}
}
using (DbConnection connHis = factHis.CreateConnection())
{
connHis.ConnectionString = strHis.ConnectionString;
connHis.Open();
DbCommand cmdHis = connHis.CreateCommand();
DbDataAdapter daHis = factHis.CreateDataAdapter();
daHis.SelectCommand = cmdHis;
daHis.SelectCommand.Connection = connHis;
daHis.SelectCommand.CommandText = "SP_HS_ORDERS_SELECT_EMR";
daHis.SelectCommand.CommandType = CommandType.StoredProcedure;

DbParameter param1 = factHis.CreateParameter();
param1.ParameterName = "V_BINID";
param1.DbType = DbType.Int64;
param1.Direction = ParameterDirection.Input;
param1.Value = Convert.ToInt64(strInpNo);

DbParameter param2 = factHis.CreateParameter();
if (iBabyFlag == 0)
{
param2.ParameterName = "V_BABYID";
param2.DbType = DbType.Int32;
param2.Direction = ParameterDirection.Input;
param2.Value = 0;
}
else
{
iBabyFlag = Convert.ToInt32(strPatientID.Substring(3));
param2.ParameterName = "V_BABYID";
param2.DbType = DbType.Int32;
param2.Direction = ParameterDirection.Input;
param2.Value = iBabyFlag;
}

//V_SELTYPE 0-长嘱 1,5-临嘱
DbParameter param3 = factHis.CreateParameter();
param3.ParameterName = "V_SELTYPE";
param3.DbType = DbType.Int32;
param3.Direction = ParameterDirection.Input;
param3.Value = 0;

//V_SELKIND 0-无效 1有效
DbParameter param4 = factHis.CreateParameter();
param4.ParameterName = "V_SELKIND";
param4.DbType = DbType.Int32;
param4.Direction = ParameterDirection.Input;
param4.Value = 1;

DbParameter param5 = factHis.CreateParameter();
param5.ParameterName = "V_DOC";
param5.DbType = DbType.Int64;
param5.Direction = ParameterDirection.Input;
param5.Value = 0;

DbParameter param6 = factHis.CreateParameter();
param6.ParameterName = "V_EXECDATE";
param6.DbType = DbType.DateTime;
param6.Direction = ParameterDirection.Input;
param6.Value = DateTime.Today;

DbParameter param7 = factHis.CreateParameter();
param7.ParameterName = "V_WARDID";
param7.DbType = DbType.String;
param7.Direction = ParameterDirection.Input;
param7.Value = strWardCode;

daHis.SelectCommand.Parameters.Add(param1);
daHis.SelectCommand.Parameters.Add(param2);
daHis.SelectCommand.Parameters.Add(param3);
daHis.SelectCommand.Parameters.Add(param4);
daHis.SelectCommand.Parameters.Add(param5);
daHis.SelectCommand.Parameters.Add(param6);
daHis.SelectCommand.Parameters.Add(param7);

daHis.SelectCommand = cmdHis;
daHis.Fill(dsOrder);
}
}
catch (Exception ex)
{
this.ExceptionLoger(strDBType, strExceptionSql, ex, "同步医嘱错误");
}
}
return dsOrder;
}