public static bool EditFeature(string labelid,string title,string content)
{
StringBuilder sb = new StringBuilder();
using (OracleConnection conn = new OracleConnection(OraHelper.str))
{
conn.Open();
OracleCommand comm = conn.CreateCommand();
OracleParameter[] parms = new OracleParameter[]{
new OracleParameter(":LABELID",OracleType.VarChar),
new OracleParameter(":TITLE",OracleType.VarChar),
new OracleParameter(":CONTENT",OracleType.VarChar)
};
parms[0].Value = labelid;
parms[1].Value = title;
parms[2].Value = content;
comm.CommandText = SQL_UPDATE_LABEL;
comm.CommandType = CommandType.Text;
foreach (OracleParameter parm in parms)
{
comm.Parameters.Add(parm);
}
comm.ExecuteNonQuery();
conn.Close();
return true;
}
}
//,注意此处条件个数与hash里参数个数应该一致
//用法：Del("test","Id=:Id",ht)
public int Del(string TableName, string ht_Where, Hashtable ht)
{
OracleParameter[] Parms = new OracleParameter[ht.Count];
IDictionaryEnumerator et = ht.GetEnumerator();
DataTable dt = GetTabType(TableName);
System.Data.OracleClient.OracleType otype;
int i = 0;
int size = 0;
// 作哈希表循环
while (et.MoveNext())
{
GetoType(et.Key.ToString().ToUpper(), dt, out otype, out size);
System.Data.OracleClient.OracleParameter op =
MakeParam(":" + et.Key.ToString(), et.Value.ToString());
Parms[i] = op; // 添加SqlParameter对象
i = i + 1;
}
string str_Sql = GetDelSqlbyHt(TableName, ht_Where, ht); // 获得删除sql语句
int val = ExecuteNonQuery(str_Sql, Parms);
return(val);
}
/// <summary>
/// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
/// </summary>
/// <param name="strSQL">SQL语句</param>
/// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
OracleCommand cmd = new OracleCommand(strSQL, connection);
System.Data.OracleClient.OracleParameter myParameter = new System.Data.OracleClient.OracleParameter("@fs", OracleType.LongRaw);
myParameter.Value = fs;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return(rows);
}
catch (System.Data.OracleClient.OracleException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
public string NONE_get_kqua_xly(string source, string ma_dvi, string loai_khang, string tu_ngay, string den_ngay, out DataSet ds)
{
ds = new DataSet();
try
{
con = new OracleConnection(conStr(source));
con.Open();
string type = "=";
if (ma_dvi == "ALL") type = "!=";
selectCmd = string.Format("select A.ma_ycau_knai, ten_nguoiycau, dchi_nguoiycau, to_char(ngay_tnhan, 'DD/MM/YY') t_nhan,"
+ " to_char(ngay_thien, 'DD/MM/YY') t_hien from cmis01.dv_kqua_xly A, cmis01.dv_ycau_knai B where A.ma_ycau_knai=B.ma_ycau_knai"
+ " and B.ma_dviqly{0}:ma_dvi and A.kqua_id in (select max(kqua_id) from cmis01.dv_kqua_xly group by ma_ycau_knai)"
+ " and A.ma_cviec{1} and ngay_tnhan between to_date(:tu_ngay, 'DD/MM/YYYY') and to_date(:den_ngay, 'DD/MM/YYYY')+1"
+ " order by ngay_tnhan desc, ngay_thien desc", type, loai_khang);
OracleParameter[] pa = new OracleParameter[3];
pa[0] = new OracleParameter("ma_dvi", ma_dvi);
pa[1] = new OracleParameter("tu_ngay", tu_ngay);
pa[2] = new OracleParameter("den_ngay", den_ngay);
da = new OracleDataAdapter(selectCmd, con);
da.SelectCommand.CommandType = CommandType.Text;
da.SelectCommand.Parameters.Add(pa[0]);
da.SelectCommand.Parameters.Add(pa[1]);
da.SelectCommand.Parameters.Add(pa[2]);
da.Fill(ds);
con.Close();
return "";
}
catch (Exception ex) { return ex.Message; }
}
public void Add(string parParameterName, ParameterDirection parDirection)
{
OracleParameter oracleParameter = new OracleParameter();
oracleParameter.ParameterName = parParameterName;
oracleParameter.Direction = ParameterDirection.Output;
this.listParameter.Add(oracleParameter);
}
/// <summary>
/// 将生成的材料信息更新到数据库中
/// </summary>
/// <param name="sql"></param>
/// <param name="excelinfo"></param>
public static void UpdateExcelInfo(string sql,byte[] excelinfo)
{
try
{
byte[] file = excelinfo;
using (OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr))
{
conn.Open();
using (OracleCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
OracleParameter op = new OracleParameter("dfd", OracleType.Blob);
op.Value = file;
if (file.Length == 0)
{
MessageBox.Show("插入信息表不能为空！", "WARNNING", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
return;
}
else
{
cmd.Parameters.Add(op);
cmd.ExecuteNonQuery();
}
}
conn.Close();
}
}
catch (IOException ee)
{
MessageBox.Show(ee.Message.ToString());
return;
}
}
/// <summary>
/// 执行带一个存储过程参数的的SQL语句。
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSql(string SQLString, string content)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
OracleCommand cmd = new OracleCommand(SQLString, connection);
System.Data.OracleClient.OracleParameter myParameter = new System.Data.OracleClient.OracleParameter("@content", OracleType.NVarChar);
myParameter.Value = content;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return(rows);
}
catch (System.Data.OracleClient.OracleException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
/// <summary>
/// 执行带一个存储过程参数的的SQL语句。
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSql(string SQLString, string content)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
OracleCommand cmd = new OracleCommand(SQLString, connection);
System.Data.OracleClient.OracleParameter myParameter = new System.Data.OracleClient.OracleParameter(":content", System.Data.OracleClient.OracleType.LongVarChar);
myParameter.Value = content;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
LogDB.Info("USER="******" SQL=" + SQLString + "#" + connection);
return(rows);
}
catch (System.Data.OracleClient.OracleException E)
{
LogDB.Error("USER="******"SQL=" + SQLString + "#" + connection, E);
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
override internal IDataParameter CreateIDataParameter(string name, object value)
{
OracleParameter p = new OracleParameter();
p.ParameterName = name;
p.Value = value;
return p;
}
public static ApplicantList GetList()
{
ApplicantList applicants = new ApplicantList();
using (OracleConnection localDbConn =
new OracleConnection(
ConnStringFactory.getConnString(
ConnStringFactory.ConnStringType.Oracle))) {
localDbConn.Open();
using (OracleCommand getAllApplicantCommand = new OracleCommand()) {
getAllApplicantCommand.CommandType = CommandType.StoredProcedure;
getAllApplicantCommand.CommandText = "ApplicantsPKG.getAllApplicants";
getAllApplicantCommand.Connection = localDbConn;
OracleParameter outputCursor = new OracleParameter("IO_CURSOR", OracleType.Cursor);
outputCursor.Direction = ParameterDirection.Output;
getAllApplicantCommand.Parameters.Add(outputCursor);
using (OracleDataReader applicantListReader =
getAllApplicantCommand.ExecuteReader()) {
if (applicantListReader.HasRows) {
while (applicantListReader.Read()) {
applicants.Add(FillDataRecord(applicantListReader));
}
}
}
}
}
return applicants;
}
public override int Add(DB_OPT dbo)
{
StringBuilder builder = new StringBuilder();
builder.Append("insert into gov_tc_db_ServicesConnection(");
builder.Append("OperationPK,ServicePK,Name,ServiceStep,CompanyPK,BranchPK,ConnectionTypePK,FatherPK,ConnectionPath,Grade,IsHasBaby,TopLevelConnectionPK,IfTax,IfFee,IfLast,Memo)");
builder.Append(" values (");
builder.Append(":OperationPK,:ServicePK,:Name,:ServiceStep,:CompanyPK,:BranchPK,:ConnectionTypePK,:FatherPK,:ConnectionPath,:Grade,:IsHasBaby,:TopLevelConnectionPK,:IfTax,:IfFee,:IfLast,:Memo)");
OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":OperationPK", OracleType.VarChar, 40), new OracleParameter(":ServicePK", OracleType.VarChar, 40), new OracleParameter(":Name", OracleType.VarChar, 100), new OracleParameter(":ServiceStep", OracleType.Int32, 4), new OracleParameter(":CompanyPK", OracleType.VarChar, 40), new OracleParameter(":BranchPK", OracleType.VarChar, 40), new OracleParameter(":ConnectionTypePK", OracleType.VarChar, 40), new OracleParameter(":FatherPK", OracleType.VarChar, 40), new OracleParameter(":ConnectionPath", OracleType.VarChar, 0xff), new OracleParameter(":Grade", OracleType.Int32, 4), new OracleParameter(":IsHasBaby", OracleType.Char, 1), new OracleParameter(":TopLevelConnectionPK", OracleType.VarChar, 40), new OracleParameter(":IfTax", OracleType.Char, 1), new OracleParameter(":IfFee", OracleType.Char, 1), new OracleParameter(":IfLast", OracleType.Char, 1), new OracleParameter(":Memo", OracleType.VarChar, 0xff) };
parameters[0].Value = base.OperationPK;
parameters[1].Value = base.ServicePK;
parameters[2].Value = base.Name;
parameters[3].Value = base.ServiceStep;
parameters[4].Value = base.CompanyPK;
parameters[5].Value = base.BranchPK;
parameters[6].Value = base.ConnectionTypePK;
parameters[7].Value = base.FatherPK;
parameters[8].Value = base.ConnectionPath;
parameters[9].Value = base.Grade;
parameters[10].Value = base.IsHasBaby;
parameters[11].Value = base.TopLevelConnectionPK;
parameters[12].Value = base.IfTax;
parameters[13].Value = base.IfFee;
parameters[14].Value = base.IfLast;
parameters[15].Value = base.Memo;
return dbo.ExecutionIsSucess(builder.ToString(), parameters, "");
}
public PD_PROJECT_KAOPING_Model GetModel(int AUTO_ID)
{
StringBuilder builder = new StringBuilder();
builder.Append("select AUTO_ID,KP_TYPEID,KP_DETAILID,KP_YEAR,KP_COMPANYPK,KH_TYPE,KP_SCORE from PD_PROJECT_KAOPING ");
builder.Append(" where AUTO_ID=:AUTO_ID ");
OracleParameter[] cmdParms = new OracleParameter[] { new OracleParameter(":AUTO_ID", OracleType.Number, 4) };
cmdParms[0].Value = AUTO_ID;
PD_PROJECT_KAOPING_Model model = new PD_PROJECT_KAOPING_Model();
DataSet set = DbHelperOra.Query(builder.ToString(), cmdParms);
if (set.Tables[0].Rows.Count <= 0)
{
return null;
}
if (set.Tables[0].Rows[0]["AUTO_ID"].ToString() != "")
{
model.AUTO_ID = new int?(int.Parse(set.Tables[0].Rows[0]["AUTO_ID"].ToString()));
}
if (set.Tables[0].Rows[0]["KP_TYPEID"].ToString() != "")
{
model.KP_TYPEID = new int?(int.Parse(set.Tables[0].Rows[0]["KP_TYPEID"].ToString()));
}
if (set.Tables[0].Rows[0]["KP_DETAILID"].ToString() != "")
{
model.KP_DETAILID = new int?(int.Parse(set.Tables[0].Rows[0]["KP_DETAILID"].ToString()));
}
model.KP_YEAR = set.Tables[0].Rows[0]["KP_YEAR"].ToString();
model.KP_COMPANYPK = set.Tables[0].Rows[0]["KP_COMPANYPK"].ToString();
model.KH_TYPE = set.Tables[0].Rows[0]["KH_TYPE"].ToString();
if (set.Tables[0].Rows[0]["KP_SCORE"].ToString() != "")
{
model.KP_SCORE = new int?(int.Parse(set.Tables[0].Rows[0]["KP_SCORE"].ToString()));
}
return model;
}
public static Applicant GetItem(int Id)
{
Applicant applicant = null;
using (OracleConnection loanDbConn =
new OracleConnection(
ConnStringFactory.getConnString(
ConnStringFactory.ConnStringType.Oracle))) {
loanDbConn.Open();
using (OracleCommand getApplicantByIdCommand = new OracleCommand()) {
getApplicantByIdCommand.CommandType = CommandType.StoredProcedure;
getApplicantByIdCommand.CommandText = "ApplicantsPKG.getApplicantById";
getApplicantByIdCommand.Connection = loanDbConn;
getApplicantByIdCommand.Parameters.AddWithValue("Id", Id);
OracleParameter outputCursor = new OracleParameter("IO_CURSOR", OracleType.Cursor);
outputCursor.Direction = ParameterDirection.Output;
getApplicantByIdCommand.Parameters.Add(outputCursor);
using (OracleDataReader applicantReader =
getApplicantByIdCommand.ExecuteReader()) {
if (applicantReader.Read()) {
applicant = FillDataRecord(applicantReader);
}
}
}
}
return applicant;
}
/// <summary>
/// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
/// </summary>
/// <param name="strSQL">SQL语句</param>
/// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
/// <returns>影响的记录数</returns>
public int ExecuteSqlInsertImg(string strSQL, byte[] fs)
{
Log.Info("DbHelperOra->ExecuteSqlInsertImg(string,byte[]) SQL: " + strSQL);
using (OracleConnection connection = base._dbAccess.GetConnection)
{
OracleCommand cmd = new OracleCommand(strSQL, connection);
System.Data.OracleClient.OracleParameter myParameter = new System.Data.OracleClient.OracleParameter("@fs", System.Data.OracleClient.OracleType.LongRaw);
myParameter.Value = fs;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return(rows);
}
catch (System.Data.OracleClient.OracleException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
/// <summary>
/// 执行带一个存储过程参数的的SQL语句。
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
/// <returns>影响的记录数</returns>
public int ExecuteSql(string SQLString, string content)
{
Log.Info("DbHelperOra->ExecuteSql(string,string) SQL: " + SQLString + "; content : " + content);
using (OracleConnection connection = base._dbAccess.GetConnection)
{
OracleCommand cmd = new OracleCommand(SQLString, connection);
System.Data.OracleClient.OracleParameter myParameter = new System.Data.OracleClient.OracleParameter("@content", System.Data.OracleClient.OracleType.VarChar);
myParameter.Value = content;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return(rows);
}
catch (System.Data.OracleClient.OracleException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
public void createPost(Member member)
{
try
{
String connstr = "Data source = studentoracle.students.ittralee.ie/orcl;User id = t00171832;Password="******";";
OracleConnection conn = new OracleConnection(connstr);
conn.Open();
string query = "insert into posts values('" + member.Username + "', '" + member.Email + "' , " + member.Phone + " , :BlobParameter , '" + member.Image + "' , '"
+ member.JoinDate + "' , " + member.MsgPrivate + " , '" + member.LastSeen + ")";
MessageBox.Show(query);
//insert the byte as oracle parameter of type blob
OracleParameter blobParameter = new OracleParameter();
blobParameter.OracleType = OracleType.Blob;
blobParameter.ParameterName = "BlobParameter";
blobParameter.Value = member.Image;
OracleCommand cmnd = new OracleCommand(query, conn);
cmnd.Parameters.Add(blobParameter);
cmnd.ExecuteNonQuery();
cmnd.Dispose();
conn.Close();
conn.Dispose();
}
catch (Exception ex)
{
}
}
public PD_BASE_KAOPINGTYPE_Model GetModel(int AUTO_ID)
{
StringBuilder builder = new StringBuilder();
builder.Append("select AUTO_ID,KHTYPENAME,KHTYPEPER,ORDERID,REMARK,ISCOMFIRM,KHYEAR from PD_BASE_KAOPINGTYPE ");
builder.Append(" where AUTO_ID=:AUTO_ID ");
OracleParameter[] cmdParms = new OracleParameter[] { new OracleParameter(":AUTO_ID", OracleType.Number, 4) };
cmdParms[0].Value = AUTO_ID;
PD_BASE_KAOPINGTYPE_Model model = new PD_BASE_KAOPINGTYPE_Model();
DataSet set = DbHelperOra.Query(builder.ToString(), cmdParms);
if (set.Tables[0].Rows.Count <= 0)
{
return null;
}
if (set.Tables[0].Rows[0]["AUTO_ID"].ToString() != "")
{
model.AUTO_ID = new int?(int.Parse(set.Tables[0].Rows[0]["AUTO_ID"].ToString()));
}
model.KHTYPENAME = set.Tables[0].Rows[0]["KHTYPENAME"].ToString();
if (set.Tables[0].Rows[0]["KHTYPEPER"].ToString() != "")
{
model.KHTYPEPER = new int?(int.Parse(set.Tables[0].Rows[0]["KHTYPEPER"].ToString()));
}
if (set.Tables[0].Rows[0]["ORDERID"].ToString() != "")
{
model.ORDERID = new int?(int.Parse(set.Tables[0].Rows[0]["ORDERID"].ToString()));
}
model.REMARK = set.Tables[0].Rows[0]["REMARK"].ToString();
model.ISCOMFIRM = set.Tables[0].Rows[0]["ISCOMFIRM"].ToString();
model.KHYEAR = set.Tables[0].Rows[0]["KHYEAR"].ToString();
return model;
}
public void run()
{
Exception exp = null;
OracleParameter param = new OracleParameter("myParam",10);
try
{
BeginCase("ctor");
Compare(param != null, true);
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
try
{
BeginCase("name");
Compare(param.ParameterName ,"myParam");
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
try
{
BeginCase("value");
Compare(param.Value, 10);
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
}
public bool Add(PD_PROJECT_GKGS_Model model)
{
StringBuilder builder = new StringBuilder();
builder.Append("insert into PD_PROJECT_GKGS(");
builder.Append("AUTO_NO,PD_PROJECT_CODE,PD_PROJECT_TYPE,PD_GS_TYPE,PD_GS_STYLE,PD_GS_ZHUTI,PD_GS_DATE,PD_GS_ADDR,PD_GS_FILENAME,PD_GS_FILENAME_SYSTEM,PD_GS_DETAIL,PD_GS_DATE_END)");
builder.Append(" values (");
builder.Append(":AUTO_NO,:PD_PROJECT_CODE,:PD_PROJECT_TYPE,:PD_GS_TYPE,:PD_GS_STYLE,:PD_GS_ZHUTI,:PD_GS_DATE,:PD_GS_ADDR,:PD_GS_FILENAME,:PD_GS_FILENAME_SYSTEM,:PD_GS_DETAIL,:PD_GS_DATE_END)");
builder.Append(" RETURNING AUTO_NO INTO :R_Auto_No ");
OracleParameter[] cmdParms = new OracleParameter[] { new OracleParameter(":AUTO_NO", OracleType.Number, 4), new OracleParameter(":PD_PROJECT_CODE", OracleType.NVarChar), new OracleParameter(":PD_PROJECT_TYPE", OracleType.Number, 4), new OracleParameter(":PD_GS_TYPE", OracleType.Number, 4), new OracleParameter(":PD_GS_STYLE", OracleType.NVarChar), new OracleParameter(":PD_GS_ZHUTI", OracleType.NVarChar), new OracleParameter(":PD_GS_DATE", OracleType.DateTime), new OracleParameter(":PD_GS_ADDR", OracleType.NVarChar), new OracleParameter(":PD_GS_FILENAME", OracleType.NVarChar), new OracleParameter(":PD_GS_FILENAME_SYSTEM", OracleType.NVarChar), new OracleParameter(":PD_GS_DETAIL", OracleType.NVarChar), new OracleParameter(":PD_GS_DATE_END", OracleType.DateTime), new OracleParameter(":R_Auto_No", OracleType.Number, 20) };
cmdParms[0].Value = model.AUTO_NO;
cmdParms[1].Value = model.PD_PROJECT_CODE;
cmdParms[2].Value = model.PD_PROJECT_TYPE;
cmdParms[3].Value = model.PD_GS_TYPE;
cmdParms[4].Value = model.PD_GS_STYLE;
cmdParms[5].Value = model.PD_GS_ZHUTI;
cmdParms[6].Value = model.PD_GS_DATE;
cmdParms[7].Value = model.PD_GS_ADDR;
cmdParms[8].Value = model.PD_GS_FILENAME;
cmdParms[9].Value = model.PD_GS_FILENAME_SYSTEM;
cmdParms[10].Value = model.PD_GS_DETAIL;
cmdParms[11].Value = model.PD_GS_DATE_END;
cmdParms[12].Direction = ParameterDirection.Output;
int num = DbHelperOra.ExecuteSql(builder.ToString(), cmdParms);
model.AUTO_NO = int.Parse(cmdParms[12].Value.ToString());
return (num > 0);
}
/// <summary>
/// Read an order from the database
/// </summary>
/// <param name="orderId">Order Id</param>
/// <returns>Details of the Order</returns>
public OrderInfo GetOrder(int orderId)
{
//Create a parameter
OracleParameter parm = new OracleParameter(PARM_ORDER_ID, OracleType.Number);
parm.Value = orderId;
//Execute a query to read the order
using (OracleDataReader rdr = OracleHelper.ExecuteReader(OracleHelper.ConnectionStringOrderDistributedTransaction, CommandType.Text, SQL_SELECT_ORDER, parm)) {
if (rdr.Read()) {
//Generate an order header from the first row
AddressInfo billingAddress = new AddressInfo(rdr.GetString(5), rdr.GetString(6), rdr.GetString(7), rdr.GetString(8), rdr.GetString(9), rdr.GetString(10), rdr.GetString(11), rdr.GetString(12), null, "email");
AddressInfo shippingAddress = new AddressInfo(rdr.GetString(13), rdr.GetString(14), rdr.GetString(15), rdr.GetString(16), rdr.GetString(17), rdr.GetString(18), rdr.GetString(19), rdr.GetString(20), null, "email");
OrderInfo order = new OrderInfo(orderId, rdr.GetDateTime(0), rdr.GetString(1), null, billingAddress, shippingAddress, rdr.GetDecimal(21), null, null);
IList<LineItemInfo> lineItems = new List<LineItemInfo>();
LineItemInfo item = null;
//Create the lineitems from the first row and subsequent rows
do {
item = new LineItemInfo(rdr.GetString(22), string.Empty, rdr.GetInt32(23), rdr.GetInt32(24), rdr.GetDecimal(25));
lineItems.Add(item);
} while (rdr.Read());
order.LineItems = new LineItemInfo[lineItems.Count];
lineItems.CopyTo(order.LineItems, 0);
return order;
}
}
return null;
}
public void ProcessRequest(HttpContext context)
{
string htmltext = string.Empty;
string guid = string.Empty;
int ww;
int wh;
htmltext = context.Request["HTML"];
ww = Convert.ToInt32(context.Request["ww"]);
wh = Convert.ToInt32(context.Request["wh"]);
guid = Guid.NewGuid().ToString();
using (OracleConnection conn = new OracleConnection(context.Cache["ORASTR"].ToString()))
{
conn.Open();
OracleCommand comm = conn.CreateCommand();
OracleParameter[] parms = new OracleParameter[]{
new OracleParameter(":GUID",OracleType.VarChar),
new OracleParameter(":DATA",OracleType.NClob),
};
parms[0].Value = guid;
parms[1].Value = htmltext;
comm.CommandText = SQL_INSERT;
comm.CommandType = CommandType.Text;
foreach (OracleParameter parm in parms)
{
comm.Parameters.Add(parm);
}
comm.ExecuteNonQuery();
string url = context.Request.Url.AbsoluteUri.Substring(0, context.Request.Url.AbsoluteUri.IndexOf("SVR/ShareMap.ashx"));
context.Response.Write("window.open(\'"+url+"Delegate.aspx?q="+guid+"&w="+ww+"&h="+wh+"\')");
}
}
public override int Add(DB_OPT dbo)
{
StringBuilder builder = new StringBuilder();
builder.Append("insert into gov_tc_db_ServicesRegister(");
builder.Append("ServiceTypePK,Name,Path,ClassName,ContParameters,IfTwoCont,Method,IfTwoMet,Parameters,Discription,GetServiceType,StartSign,InTime,OutTime,FatherPK,IsHasBaby,Grade,PKPath,MaxNum)");
builder.Append(" values (");
builder.Append(":ServiceTypePK,:Name,:Path,:ClassName,:ContParameters,:IfTwoCont,:Method,:IfTwoMet,:Parameters,:Discription,:GetServiceType,:StartSign,:InTime,:OutTime,:FatherPK,:IsHasBaby,:Grade,:PKPath,:MaxNum)");
OracleParameter[] parameters = new OracleParameter[] {
new OracleParameter(":ServiceTypePK", OracleType.Char, 40), new OracleParameter(":Name", OracleType.VarChar, 40), new OracleParameter(":Path", OracleType.VarChar, 0xff), new OracleParameter(":ClassName", OracleType.VarChar, 40), new OracleParameter(":ContParameters", OracleType.VarChar, 0xff), new OracleParameter(":IfTwoCont", OracleType.Char, 1), new OracleParameter(":Method", OracleType.VarChar, 40), new OracleParameter(":IfTwoMet", OracleType.Char, 1), new OracleParameter(":Parameters", OracleType.VarChar, 0xff), new OracleParameter(":Discription", OracleType.VarChar, 0xff), new OracleParameter(":GetServiceType", OracleType.VarChar, 1), new OracleParameter(":StartSign", OracleType.VarChar, 1), new OracleParameter(":InTime", OracleType.DateTime, 40), new OracleParameter(":OutTime", OracleType.DateTime, 40), new OracleParameter(":FatherPK", OracleType.VarChar, 40), new OracleParameter(":IsHasBaby", OracleType.Char, 1),
new OracleParameter(":Grade", OracleType.Int32, 4), new OracleParameter(":PKPath", OracleType.Clob), new OracleParameter(":MaxNum", OracleType.Int32, 4)
};
parameters[0].Value = base.ServiceTypePK;
parameters[1].Value = base.Name;
parameters[2].Value = base.Path;
parameters[3].Value = base.ClassName;
parameters[4].Value = base.ContParameters;
parameters[5].Value = base.IfTwoCont;
parameters[6].Value = base.Method;
parameters[7].Value = base.IfTwoMet;
parameters[8].Value = base.Parameters;
parameters[9].Value = base.Discription;
parameters[10].Value = base.GetServiceType;
parameters[11].Value = base.StartSign;
parameters[12].Value = base.InTime;
parameters[13].Value = base.OutTime;
parameters[14].Value = base.FatherPK;
parameters[15].Value = base.IsHasBaby;
parameters[0x10].Value = base.Grade;
parameters[0x11].Value = base.PKPath;
parameters[0x12].Value = base.MaxNum;
return dbo.ExecutionIsSucess(builder.ToString(), parameters, "");
}
public static bool AddFeature(string usrid,string usrname,string password,int privilege)
{
StringBuilder sb = new StringBuilder();
using (OracleConnection conn = new OracleConnection(OraHelper.str))
{
conn.Open();
OracleCommand comm = conn.CreateCommand();
OracleParameter[] parms = new OracleParameter[]{
new OracleParameter(":USRID",OracleType.VarChar),
new OracleParameter(":USRNAME",OracleType.VarChar),
new OracleParameter(":PASSWORD",OracleType.VarChar),
new OracleParameter(":PRIVILEGE",OracleType.UInt32)
};
parms[0].Value = usrid;
parms[1].Value = usrname;
parms[2].Value = password;
parms[3].Value = privilege;
comm.CommandText = SQL_INSERT_USER;
comm.CommandType = CommandType.Text;
foreach (OracleParameter parm in parms)
{
comm.Parameters.Add(parm);
}
comm.ExecuteNonQuery();
conn.Close();
return true;
}
}
/// <summary>
/// 执行带一个存储过程参数的的SQL语句。
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
/// <returns>影响的记录数</returns>
public static object ExecuteSqlGet(string SQLString, string content)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
OracleCommand cmd = new OracleCommand(SQLString, connection);
System.Data.OracleClient.OracleParameter myParameter = new System.Data.OracleClient.OracleParameter("@content", SqlDbType.NText);
myParameter.Value = content;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
object obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return(null);
}
else
{
return(obj);
}
}
catch (System.Data.OracleClient.OracleException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
public SMZJ.Model.TB_QUOTA_DETAIL_TMP GetModel(int AUTO_NO)
{
StringBuilder builder = new StringBuilder();
builder.Append("select AUTO_NO,PD_QUOTA_CODE,COMPANY_NAME,FILE_NAME,FILE_SYSNAME,COMPANY_CODE,FILE_TYPE from TB_QUOTA_DETAIL_TMP ");
builder.Append(" where AUTO_NO=:AUTO_NO ");
OracleParameter[] cmdParms = new OracleParameter[] { new OracleParameter(":AUTO_NO", OracleType.Number, 4) };
cmdParms[0].Value = AUTO_NO;
SMZJ.Model.TB_QUOTA_DETAIL_TMP tb_quota_detail_tmp = new SMZJ.Model.TB_QUOTA_DETAIL_TMP();
DataSet set = DbHelperOra.Query(builder.ToString(), cmdParms);
if (set.Tables[0].Rows.Count <= 0)
{
return null;
}
if (set.Tables[0].Rows[0]["AUTO_NO"].ToString() != "")
{
tb_quota_detail_tmp.AUTO_NO = int.Parse(set.Tables[0].Rows[0]["AUTO_NO"].ToString());
}
tb_quota_detail_tmp.PD_QUOTA_CODE = set.Tables[0].Rows[0]["PD_QUOTA_CODE"].ToString();
tb_quota_detail_tmp.COMPANY_NAME = set.Tables[0].Rows[0]["COMPANY_NAME"].ToString();
tb_quota_detail_tmp.FILE_NAME = set.Tables[0].Rows[0]["FILE_NAME"].ToString();
tb_quota_detail_tmp.FILE_SYSNAME = set.Tables[0].Rows[0]["FILE_SYSNAME"].ToString();
tb_quota_detail_tmp.COMPANY_CODE = set.Tables[0].Rows[0]["COMPANY_CODE"].ToString();
tb_quota_detail_tmp.FILE_TYPE = set.Tables[0].Rows[0]["FILE_TYPE"].ToString();
return tb_quota_detail_tmp;
}
/// <summary>
/// 执行带参数sql语句或存储过程，返回所影响的行数
/// </summary>
/// <param name="cmdText">带参数的sql语句和存储过程名</param>
/// <param name="cmdType">命令类型</param>
/// <param name="cmdParms">参数集合</param>
/// <returns>返回所影响的行数</returns>
public static int ExecuteNonQuery(string cmdText, CommandType cmdType, OracleParameter[] cmdParms)
{
int count;
try
{
init();
cmd = new OracleCommand();
SetCommand(cmd, cmdText, cmdType, cmdParms);
count = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
conn.Close();
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString());
}
return count;
}
protected DataSet cmis_search(string ma_khang, string so_cto, out string txt_err)
{
DataSet ds = new DataSet();
try
{
string txt_cmd = string.Format("Data Source={0}; User ID={1}; Password={1}; Unicode=True", "THAINGUYEN", "CMIS01PC1");
OracleConnection ora_con = new OracleConnection(txt_cmd);
txt_cmd = @"select TEN_KHANG, DCHI_KHANG, DCHI_TTOAN, KY, THANG, NAM, NGAY_DKY, NGAY_CKY, LOAI_HDON, TONG_TIEN, DIEN_TTHU, NGAY_PHANH
from cmis01.hdn_hdon
where MA_KHANG=:ma_khang or SO_CTO=:so_cto
order by NGAY_CKY desc";
OracleParameter[] pa = new OracleParameter[2];
pa[0] = new OracleParameter("ma_khang", ma_khang.ToUpper());
pa[1] = new OracleParameter("so_cto", so_cto);
OracleDataAdapter ora_da = new OracleDataAdapter(txt_cmd, ora_con);
ora_da.SelectCommand.CommandType = CommandType.Text;
ora_da.SelectCommand.Parameters.Add(pa[0]);
ora_da.SelectCommand.Parameters.Add(pa[1]);
ora_con.Open();
ora_da.Fill(ds);
ora_con.Close();
txt_err = "";
}
catch (Exception ex) { txt_err = ex.Message; }
return ds;
}
public void Add(string parParameterName, object parValue)
{
OracleParameter oracleParameter = new OracleParameter();
oracleParameter.ParameterName = parParameterName;
oracleParameter.Value = parValue;
this.listParameter.Add(oracleParameter);
}
public static OracleParameter AddOutParamToSqlCommand(OracleCommand comm, string pName, OracleType pType)
{
OracleParameter param = new OracleParameter(pName, pType);
param.Direction = ParameterDirection.Output;
comm.Parameters.Add(param);
return param;
}
public void AllTypes()
{
exp = null;
OracleParameter param = null;
foreach (OracleType dbtype in Enum.GetValues(typeof(OracleType)))
{
param = new OracleParameter("myParam",dbtype);
try
{
BeginCase("ctor " + dbtype.ToString());
Compare(param != null, true);
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
try
{
BeginCase("name " + dbtype.ToString());
Compare(param.ParameterName ,"myParam");
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
}
}
/// <summary>
/// This method opens (if necessary) and assigns a connection, transaction, command type and parameters
/// to the provided command.
/// </summary>
/// <param name="command">the OracleCommand to be prepared</param>
/// <param name="connection">a valid OracleConnection, on which to execute this command</param>
/// <param name="transaction">a valid OracleTransaction, or 'null'</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or PL/SQL command</param>
/// <param name="commandParameters">an array of OracleParameters to be associated with the command or 'null' if no parameters are required</param>
private static void PrepareCommand(OracleCommand command, OracleConnection connection, OracleTransaction transaction, CommandType commandType, string commandText, OracleParameter[] commandParameters)
{
//if the provided connection is not open, we will open it
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
//associate the connection with the command
command.Connection = connection;
//set the command text (stored procedure name or Oracle statement)
command.CommandText = commandText;
command.CommandTimeout = 200000;
//if we were provided a transaction, assign it.
if (transaction != null)
{
command.Transaction = transaction;
}
//set the command type
command.CommandType = commandType;
//attach the command parameters if they are provided
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
return;
}
private void stdnt_payment_info_btn_Click(object sender, EventArgs e)
{
dbConnect conObj = new dbConnect();
OracleConnection conn = conObj.connFunc();
OracleParameter parOut = new OracleParameter();
parOut.ParameterName = "res_cur";
parOut.OracleType = OracleType.Cursor;
parOut.Size = 32;
parOut.Direction = System.Data.ParameterDirection.Output;
OracleCommand cmd = conn.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "student_payment_procedure";
cmd.Parameters.Add(parOut);
conn.Open();
OracleDataAdapter adapter = new OracleDataAdapter(cmd);
DataSet myset = new DataSet("res_cur");
adapter.Fill(myset);
stdnt_payment_dataGridView.DataSource = myset;
stdnt_payment_dataGridView.DataSource = myset.Tables[0];
conn.Close();
}
public bool Add(PD_PROJECT_CONTRACT_BAK_Model model)
{
StringBuilder builder = new StringBuilder();
builder.Append("insert into PD_PROJECT_CONTRACT_BAK(");
builder.Append("ID,PD_PROJECT_CODE,PD_CONTRACT_TYPE,PD_CONTRACT_NO,PD_CONTRACT_DATE,PD_CONTRACT_COMPANY,PD_CONTRACT_MOENY,PD_CONTRACT_MOENY_CHANGE,PD_CONTRACT_ASK_LIMIT,PD_CONTRACT_ASK_PROCEED,PD_CONTRACT_ASK_PAYMENT,PD_CONTRACT_NOTE,PD_CONTRACT_FILENAME,PD_CONTRACT_FILENAME_SYSTEM,PD_CONTRACT_NAME,PD_DB_LOOP,PD_YEAR,PD_NOW_SERVERPK)");
builder.Append(" values (");
builder.Append(":ID,:PD_PROJECT_CODE,:PD_CONTRACT_TYPE,:PD_CONTRACT_NO,:PD_CONTRACT_DATE,:PD_CONTRACT_COMPANY,:PD_CONTRACT_MOENY,:PD_CONTRACT_MOENY_CHANGE,:PD_CONTRACT_ASK_LIMIT,:PD_CONTRACT_ASK_PROCEED,:PD_CONTRACT_ASK_PAYMENT,:PD_CONTRACT_NOTE,:PD_CONTRACT_FILENAME,:PD_CONTRACT_FILENAME_SYSTEM,:PD_CONTRACT_NAME,:PD_DB_LOOP,:PD_YEAR,:PD_NOW_SERVERPK)");
OracleParameter[] cmdParms = new OracleParameter[] {
new OracleParameter(":ID", OracleType.Number, 20), new OracleParameter(":PD_PROJECT_CODE", OracleType.VarChar, 0x24), new OracleParameter(":PD_CONTRACT_TYPE", OracleType.VarChar, 30), new OracleParameter(":PD_CONTRACT_NO", OracleType.VarChar, 30), new OracleParameter(":PD_CONTRACT_DATE", OracleType.DateTime), new OracleParameter(":PD_CONTRACT_COMPANY", OracleType.VarChar, 50), new OracleParameter(":PD_CONTRACT_MOENY", OracleType.Number, 0x12), new OracleParameter(":PD_CONTRACT_MOENY_CHANGE", OracleType.Number, 0x12), new OracleParameter(":PD_CONTRACT_ASK_LIMIT", OracleType.VarChar, 500), new OracleParameter(":PD_CONTRACT_ASK_PROCEED", OracleType.VarChar, 500), new OracleParameter(":PD_CONTRACT_ASK_PAYMENT", OracleType.VarChar, 500), new OracleParameter(":PD_CONTRACT_NOTE", OracleType.VarChar, 500), new OracleParameter(":PD_CONTRACT_FILENAME", OracleType.VarChar, 50), new OracleParameter(":PD_CONTRACT_FILENAME_SYSTEM", OracleType.VarChar, 50), new OracleParameter(":PD_CONTRACT_NAME", OracleType.VarChar, 100), new OracleParameter(":PD_DB_LOOP", OracleType.Char, 1),
new OracleParameter(":PD_YEAR", OracleType.Number, 4), new OracleParameter(":PD_NOW_SERVERPK", OracleType.VarChar, 50)
};
cmdParms[0].Value = model.ID;
cmdParms[1].Value = model.PD_PROJECT_CODE;
cmdParms[2].Value = model.PD_CONTRACT_TYPE;
cmdParms[3].Value = model.PD_CONTRACT_NO;
cmdParms[4].Value = model.PD_CONTRACT_DATE;
cmdParms[5].Value = model.PD_CONTRACT_COMPANY;
cmdParms[6].Value = model.PD_CONTRACT_MOENY;
cmdParms[7].Value = model.PD_CONTRACT_MOENY_CHANGE;
cmdParms[8].Value = model.PD_CONTRACT_ASK_LIMIT;
cmdParms[9].Value = model.PD_CONTRACT_ASK_PROCEED;
cmdParms[10].Value = model.PD_CONTRACT_ASK_PAYMENT;
cmdParms[11].Value = model.PD_CONTRACT_NOTE;
cmdParms[12].Value = model.PD_CONTRACT_FILENAME;
cmdParms[13].Value = model.PD_CONTRACT_FILENAME_SYSTEM;
cmdParms[14].Value = model.PD_CONTRACT_NAME;
cmdParms[15].Value = model.PD_DB_LOOP;
cmdParms[0x10].Value = model.PD_YEAR;
cmdParms[0x11].Value = model.PD_NOW_SERVERPK;
return (DbHelperOra.ExecuteSql(builder.ToString(), cmdParms) > 0);
}
public void Add(MESS_BACK_Model model)
{
StringBuilder builder = new StringBuilder();
builder.Append("insert into MESS_BACK(");
builder.Append("MES_ID,MES_COMPANY,MES_DEPT,MES_MAN,MES_DATE,MES_KUNAN,MES_JIANYI,MES_XZYJ,MES_PZ,MES_TITLE,MES_FLAG,MES_SHCOMPANY,MES_SHDEPT,MES_SHMAN,MES_SHDATE,PD_NOW_SERVERPK,PD_PROJECT_CODE,PD_QUOTA_DEPART,PD_YEAR,PD_BASE_FKLX,MES_YWGSYJ,MES_XCJYJ)");
builder.Append(" values (");
builder.Append(":MES_ID,:MES_COMPANY,:MES_DEPT,:MES_MAN,:MES_DATE,:MES_KUNAN,:MES_JIANYI,:MES_XZYJ,:MES_PZ,:MES_TITLE,:MES_FLAG,:MES_SHCOMPANY,:MES_SHDEPT,:MES_SHMAN,:MES_SHDATE,:PD_NOW_SERVERPK,:PD_PROJECT_CODE,:PD_QUOTA_DEPART,:PD_YEAR,:PD_BASE_FKLX,:MES_YWGSYJ,:MES_XCJYJ)");
OracleParameter[] cmdParms = new OracleParameter[] {
new OracleParameter(":MES_ID", OracleType.VarChar, 30), new OracleParameter(":MES_COMPANY", OracleType.VarChar, 50), new OracleParameter(":MES_DEPT", OracleType.VarChar, 50), new OracleParameter(":MES_MAN", OracleType.VarChar, 50), new OracleParameter(":MES_DATE", OracleType.DateTime), new OracleParameter(":MES_KUNAN", OracleType.VarChar, 100), new OracleParameter(":MES_JIANYI", OracleType.VarChar, 100), new OracleParameter(":MES_XZYJ", OracleType.VarChar, 100), new OracleParameter(":MES_PZ", OracleType.VarChar, 200), new OracleParameter(":MES_TITLE", OracleType.VarChar, 100), new OracleParameter(":MES_FLAG", OracleType.Char, 1), new OracleParameter(":MES_SHCOMPANY", OracleType.VarChar, 50), new OracleParameter(":MES_SHDEPT", OracleType.VarChar, 50), new OracleParameter(":MES_SHMAN", OracleType.VarChar, 50), new OracleParameter(":MES_SHDATE", OracleType.DateTime), new OracleParameter(":PD_NOW_SERVERPK", OracleType.VarChar, 50),
new OracleParameter(":PD_PROJECT_CODE", OracleType.VarChar, 50), new OracleParameter(":PD_QUOTA_DEPART", OracleType.VarChar, 50), new OracleParameter(":PD_YEAR", OracleType.VarChar, 6), new OracleParameter(":PD_BASE_FKLX", OracleType.VarChar, 10), new OracleParameter(":MES_YWGSYJ", OracleType.VarChar, 0xff), new OracleParameter(":MES_XCJYJ", OracleType.VarChar, 0xff)
};
cmdParms[0].Value = model.MES_ID;
cmdParms[1].Value = model.MES_COMPANY;
cmdParms[2].Value = model.MES_DEPT;
cmdParms[3].Value = model.MES_MAN;
cmdParms[4].Value = model.MES_DATE;
cmdParms[5].Value = model.MES_KUNAN;
cmdParms[6].Value = model.MES_JIANYI;
cmdParms[7].Value = model.MES_XZYJ;
cmdParms[8].Value = model.MES_PZ;
cmdParms[9].Value = model.MES_TITLE;
cmdParms[10].Value = model.MES_FLAG;
cmdParms[11].Value = model.MES_SHCOMPANY;
cmdParms[12].Value = model.MES_SHDEPT;
cmdParms[13].Value = model.MES_SHMAN;
cmdParms[14].Value = model.MES_SHDATE;
cmdParms[15].Value = model.PD_NOW_SERVERPK;
cmdParms[0x10].Value = model.PD_PROJECT_CODE;
cmdParms[0x11].Value = model.PD_QUOTA_DEPART;
cmdParms[0x12].Value = model.PD_YEAR;
cmdParms[0x13].Value = model.PD_BASE_FKLX;
cmdParms[20].Value = model.MES_YWGSYJ;
cmdParms[0x15].Value = model.MES_XCJYJ;
DbHelperOra.ExecuteSql(builder.ToString(), cmdParms);
}
public static string ExecuteNonQuery(string sql, OracleParameter[] param)
{
string ret = "false";
try
{
OracleConnection conn = GetConnection();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 240;
if (param != null)
{
foreach (OracleParameter p in param)
{
if (p != null)
{
cmd.Parameters.Add(p);
}
}
}
cmd.ExecuteNonQuery();
ret = "true";
}
catch (Exception ex)
{
ret = "false|Exception " + ex.Message + "\n" + ex.StackTrace.ToString();
}
return ret;
}
internal override void AddCustomePara(string paraName, ParaType paraType)
{
OracleParameter para = new OracleParameter();
para.ParameterName = paraName;
switch (paraType)
{
case ParaType.Cursor:
case ParaType.OutPut:
if (paraType == ParaType.Cursor)
{
para.OracleType = OracleType.Cursor;
}
else
{
para.OracleType = OracleType.Int32;
}
para.Direction = ParameterDirection.Output;
break;
case ParaType.ReturnValue:
para.OracleType = OracleType.Int32;
para.Direction = ParameterDirection.ReturnValue;
break;
}
Com.Parameters.Add(para);
}
public static LoanList GetList(int applicantId)
{
LoanList loans = new LoanList();
using(OracleConnection oraDbConn =
new OracleConnection(
ConnStringFactory.getConnString(
ConnStringFactory.ConnStringType.Oracle))){
oraDbConn.Open();
using(OracleCommand getLoansByAppIdCommand = new OracleCommand()){
getLoansByAppIdCommand.CommandType = CommandType.StoredProcedure;
getLoansByAppIdCommand.CommandText = "LoansPKG.getLoansByAppId";
getLoansByAppIdCommand.Connection = oraDbConn;
getLoansByAppIdCommand.Parameters.AddWithValue("AppId", applicantId);
OracleParameter outputCursor = new OracleParameter("IO_CURSOR", OracleType.Cursor);
outputCursor.Direction = ParameterDirection.Output;
getLoansByAppIdCommand.Parameters.Add(outputCursor);
using (OracleDataReader loanListReader =
getLoansByAppIdCommand.ExecuteReader()) {
while(loanListReader.Read()){
loans.Add(FillDataRecord(loanListReader));
}
}
}
}
return loans;
}
/// <summary>
/// 深拷贝参数数组
/// </summary>
/// <param name="originalParameters">源参数数组</param>
/// <returns>返回参数数组的副本</returns>
private static DbParameter[] CloneParameters(DbParameter[] originalParameters)
{
DbParameter[] clonedParameters = new DbParameter[originalParameters.Length];
for (int i = 0, j = originalParameters.Length; i < j; i++)
{
clonedParameters[i] = (DbParameter)((ICloneable)originalParameters[i]).Clone();
}
return(clonedParameters);
}
/// <summary>
///
/// </summary>
/// <param name="name"></param>
/// <param name="value"></param>
public override void AddClobParameter(string name, object value)
{
System.Data.OracleClient.OracleParameter clobParam = new System.Data.OracleClient.OracleParameter(name, OracleType.Clob);
clobParam.Value = value;
this.command.Parameters.Add(clobParam);
//this.command.Parameters.Add
//(
// CreateParameter(name,
// System.Data.OracleClient.OracleType.Clob,
// 0,
// ParameterDirection.Input,
// false, 0, 0, string.Empty, null, value)
//);
}
private DbParameter[] DiscoverSpParameterSet(string strProcedureName, bool includeReturnValueParameter)
{
DbCommand dbCommand = new DbCommand();
dbCommand.Connection = mConn;
dbCommand.CommandText = strProcedureName;
dbCommand.CommandType = CommandType.StoredProcedure;
try
{
if (mConn.State == ConnectionState.Closed)
{
mConn.Open();
}
// 查找存储过程的参数
DbCommandBuilder.DeriveParameters(dbCommand);
// 如果包含返回Result value参数（自动生成的，不需要传进去）,如果有，则删除
// Oracle中，function产生此参数，procedure则不产生
// SQL SERVER中，procedure 产生此参数,function 未测试，应该也会有
if (includeReturnValueParameter)
{
dbCommand.Parameters.RemoveAt(0);
}
// 返回找到的参数的副本
DbParameter[] discoveredParameters = new DbParameter[dbCommand.Parameters.Count];
dbCommand.Parameters.CopyTo(discoveredParameters, 0);
// 参数一刀两断,没有关系了.
dbCommand.Parameters.Clear();
// 初始化各参数为DBNull
foreach (DbParameter discoveredParameter in discoveredParameters)
{
discoveredParameter.Value = DBNull.Value;
}
return(discoveredParameters);
}
catch (Exception e)
{
WriteLogInfo("error on " + this.GetType().Name + " GetStoredProcedureParamters:" + strProcedureName + "\r\n" + e.Message);
return(null);
}
}
internal void UpdateParameterValues()
{
moreResults = -1;
if (Parameters.Count > 0)
{
bool foundCursor = false;
for (int p = 0; p < Parameters.Count; p++)
{
OracleParameter parm = Parameters[p];
if (parm.OracleType.Equals(OracleType.Cursor))
{
if (!foundCursor && parm.Direction != ParameterDirection.Input)
{
// if there are multiple REF CURSORs,
// you only can get the first cursor for now
// because user of OracleDataReader
// will do a NextResult to get the next
// REF CURSOR (if it exists)
foundCursor = true;
parm.Update(this);
if (p + 1 == Parameters.Count)
{
moreResults = -1;
}
else
{
moreResults = p;
}
}
}
else
{
parm.Update(this);
}
}
}
}
private void AddImage(int employeeID, string fileName)
{
string OrgID = this.hfOrgID.Value;
//FileStream fs = new FileStream(fileName, FileMode.Open);
//int len = int.Parse(fs.Length.ToString());
//byte[] byteImage = new Byte[len];
//fs.Read(byteImage, 0, len);
//fs.Close();
System.Drawing.Image image = System.Drawing.Image.FromFile(fileName);
System.Drawing.Image thumbnail = image.GetThumbnailImage(120, 150, null, IntPtr.Zero);
MemoryStream ms = new MemoryStream();
thumbnail.Save(ms, ImageFormat.Jpeg);
byte[] byteImage = ms.ToArray();
//添加
XmlDocument doc = new XmlDocument();
doc.Load(Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath, "web.config"));
XmlNode node = doc.SelectSingleNode("configuration/dataConfiguration/@defaultDatabase");
string value = node.Value;
if (value == "Oracle")
{
System.Data.OracleClient.OracleParameter para1 = new System.Data.OracleClient.OracleParameter("p_photo", OracleType.Blob);
System.Data.OracleClient.OracleParameter para2 = new System.Data.OracleClient.OracleParameter("p_id", OracleType.Number);
para1.Value = byteImage;
para2.Value = employeeID;
IDataParameter[] paras = new IDataParameter[] { para1, para2 };
Pub.RunAddProcedureBlob(false, "USP_EMPLOYEE_IMAGE", paras, byteImage);
}
}
private DataSet GetCodApparecchiature(string CodEdificio, string CodServizio, string Apparecchiatura)
{
System.Data.OracleClient.OracleParameterCollection Coll = new System.Data.OracleClient.OracleParameterCollection();
System.Data.OracleClient.OracleParameter p_Bl_Id = new System.Data.OracleClient.OracleParameter();
p_Bl_Id.ParameterName = "p_Bl_Id";
p_Bl_Id.Size = 50;
p_Bl_Id.Direction = ParameterDirection.Input;
p_Bl_Id.OracleType = OracleType.VarChar;
p_Bl_Id.Value = "";
Coll.Add(p_Bl_Id);
OracleParameter s_p_campus = new OracleParameter();
s_p_campus.ParameterName = "p_campus";
s_p_campus.Direction = ParameterDirection.Input;
s_p_campus.OracleType = OracleType.VarChar;
s_p_campus.Value = CodEdificio;
Coll.Add(s_p_campus);
OracleParameter s_p_Servizio = new OracleParameter();
s_p_Servizio.ParameterName = "p_Servizio";
s_p_Servizio.Direction = ParameterDirection.Input;
s_p_Servizio.OracleType = OracleType.Int32;
s_p_Servizio.Value = (CodServizio == "")? 0:Int32.Parse(CodServizio);
Coll.Add(s_p_Servizio);
OracleParameter s_p_eqstdid = new OracleParameter();
s_p_eqstdid.ParameterName = "p_eqstdid";
s_p_eqstdid.Direction = ParameterDirection.Input;
s_p_eqstdid.OracleType = OracleType.Int32;
s_p_eqstdid.Value = (Apparecchiatura == "")? 0:Int32.Parse(Apparecchiatura);
s_p_eqstdid.Size = 8;
Coll.Add(s_p_eqstdid);
OracleParameter s_p_eq_id = new OracleParameter();
s_p_eq_id.ParameterName = "p_eq_id";
s_p_eq_id.Direction = ParameterDirection.Input;
s_p_eq_id.OracleType = OracleType.VarChar;
s_p_eq_id.Value = ""; //(cmbEQ.SelectedValue==string.Empty)? "":cmbEQ.Items[cmbEQ.SelectedIndex].Text;
s_p_eq_id.Size = 50;
Coll.Add(s_p_eq_id);
// 19-07-2005 Armando: aggiunto parametro
OracleParameter p_dismesso = new OracleParameter();
p_dismesso.ParameterName = "p_dismesso";
p_dismesso.Direction = ParameterDirection.Input;
p_dismesso.OracleType = OracleType.Int32;
p_dismesso.Value = 0;
Coll.Add(p_dismesso);
// Armando fine
OracleParameter p_Username = new OracleParameter();
p_Username.ParameterName = "p_Username";
p_Username.Size = 50;
p_Username.Direction = ParameterDirection.Input;
p_Username.OracleType = OracleType.VarChar;
p_Username.Value = this.userName;
Coll.Add(p_Username);
OracleParameter PaCursor = new OracleParameter();
PaCursor.ParameterName = "IO_CURSOR";
PaCursor.Direction = ParameterDirection.Output;
PaCursor.OracleType = OracleType.Cursor;
Coll.Add(PaCursor);
DataSet _MyDs = base.GetData(Coll, "PACK_APPARECCHIATURE.SP_RICERCAAPPARECCHIATURA");
return(_MyDs);
}
internal OracleParameterBinding(OracleCommand command, OracleParameter parameter)
{
this._command = command;
this._parameter = parameter;
}
public OracleDBParameter(System.Data.OracleClient.OracleParameter par)
{
this.par = par;
}
internal void PrepareForBind(OracleConnection connection, ref int offset)
{
OracleParameter parameter = this.Parameter;
bool flag = false;
object obj2 = parameter.Value;
if (!IsDirection(parameter, ParameterDirection.Output) && (obj2 == null))
{
this._bufferLength = 0;
}
else
{
this._bindingMetaType = parameter.GetMetaType(obj2);
if ((OCI.DATATYPE.RSET == this._bindingMetaType.OciType) && System.Data.Common.ADP.IsDirection(parameter.Direction, ParameterDirection.Input))
{
throw System.Data.Common.ADP.InputRefCursorNotSupported(parameter.ParameterName);
}
parameter.SetCoercedValueInternal(obj2, this._bindingMetaType);
this._coercedValue = parameter.GetCoercedValueInternal();
switch (this._bindingMetaType.OciType)
{
case OCI.DATATYPE.CLOB:
case OCI.DATATYPE.BLOB:
case OCI.DATATYPE.BFILE:
if ((!System.Data.Common.ADP.IsNull(this._coercedValue) && !(this._coercedValue is OracleLob)) && !(this._coercedValue is OracleBFile))
{
if (!connection.HasTransaction)
{
this._bindingMetaType = MetaType.GetMetaTypeForType(this._bindingMetaType.DbType);
flag = true;
break;
}
this._freeTemporaryLob = true;
this._coercedValue = this.CreateTemporaryLobForValue(connection, this._bindingMetaType.OracleType, this._coercedValue);
}
break;
}
this._bindSize = this._bindingMetaType.BindSize;
if (((IsDirection(parameter, ParameterDirection.Output) && this._bindingMetaType.IsVariableLength) || ((this._bindSize == 0) && !System.Data.Common.ADP.IsNull(this._coercedValue))) || (this._bindSize > 0x7fff))
{
int bindSize = parameter.BindSize;
if (bindSize != 0)
{
this._bindSize = bindSize;
}
if (((this._bindSize == 0) || (0x7fffffff == this._bindSize)) && !this.IsEmpty(this._coercedValue))
{
throw System.Data.Common.ADP.ParameterSizeIsMissing(parameter.ParameterName, this._bindingMetaType.BaseType);
}
}
this._bufferLength = this._bindSize;
if (this._bindingMetaType.IsCharacterType && connection.ServerVersionAtLeastOracle8)
{
this._bindAsUCS2 = true;
this._bufferLength *= System.Data.Common.ADP.CharSize;
}
if (!System.Data.Common.ADP.IsNull(this._coercedValue) && ((this._bindSize > this._bindingMetaType.MaxBindSize) || flag))
{
switch (this._bindingMetaType.OciType)
{
case OCI.DATATYPE.RAW:
case OCI.DATATYPE.LONGRAW:
this._bindingMetaType = MetaType.oracleTypeMetaType_LONGVARRAW;
break;
case OCI.DATATYPE.CHAR:
case OCI.DATATYPE.VARCHAR2:
case OCI.DATATYPE.LONG:
this._bindingMetaType = this._bindingMetaType.UsesNationalCharacterSet ? MetaType.oracleTypeMetaType_LONGNVARCHAR : MetaType.oracleTypeMetaType_LONGVARCHAR;
break;
}
this._bufferLength += 4;
}
if (0 > this._bufferLength)
{
throw System.Data.Common.ADP.ParameterSizeIsTooLarge(parameter.ParameterName);
}
this._indicatorOffset = offset;
offset += IntPtr.Size;
this._lengthOffset = offset;
offset += IntPtr.Size;
this._valueOffset = offset;
offset += this._bufferLength;
offset = (offset + (IntPtr.Size - 1)) & ~(IntPtr.Size - 1);
}
}
public bool Contains(OracleParameter value)
{
return(base.Contains(value));
}
OracleDataReader ExecuteReader(CommandBehavior behavior)
{
AssertConnectionIsOpen();
AssertTransactionMatch();
AssertCommandTextIsSet();
moreResults = -1;
bool hasRows = false;
this.behavior = behavior;
if (Transaction != null)
{
Transaction.AttachToServiceContext();
}
OciStatementHandle statement = GetStatementHandle();
OracleDataReader rd = null;
try
{
if (preparedStatement == null)
{
PrepareStatement(statement);
}
else
{
preparedStatement = null; // OracleDataReader releases the statement handle
}
bool isNonQuery = IsNonQuery(statement);
BindParameters(statement);
if (isNonQuery)
{
ExecuteNonQueryInternal(statement, false);
}
else
{
if ((behavior & CommandBehavior.SchemaOnly) != 0)
{
statement.ExecuteQuery(true);
}
else
{
hasRows = statement.ExecuteQuery(false);
}
UpdateParameterValues();
}
if (Parameters.Count > 0)
{
for (int p = 0; p < Parameters.Count; p++)
{
OracleParameter parm = Parameters[p];
if (parm.OracleType.Equals(OracleType.Cursor))
{
if (parm.Direction != ParameterDirection.Input)
{
rd = (OracleDataReader)parm.Value;
break;
}
}
}
}
if (rd == null)
{
rd = new OracleDataReader(this, statement, hasRows, behavior);
}
}
finally
{
if (statement != null && rd == null)
{
statement.Dispose();
}
}
return(rd);
}
public void Remove(OracleParameter value)
{
base.Remove(value);
}
public void Insert(int index, OracleParameter value)
{
base.Insert(index, value);
}
public static bool EnviaGraficaCorreo(string NomRemitente, string Destinatario, string NombDestinatario, string Remitente, int IdEncuesta)
{
bool blnBand;
string strSQL = string.Empty;
int intresp;
System.Data.OracleClient.OracleParameter[] Parametros = new System.Data.OracleClient.OracleParameter[5];
try
{
Parametros[0] = new System.Data.OracleClient.OracleParameter();
Parametros[0].OracleType = System.Data.OracleClient.OracleType.VarChar;
Parametros[0].Value = Remitente;
Parametros[0].Direction = System.Data.ParameterDirection.Input;
Parametros[0].ParameterName = "CorreoRemitente";
Parametros[1] = new System.Data.OracleClient.OracleParameter();
Parametros[1].OracleType = System.Data.OracleClient.OracleType.VarChar;
Parametros[1].Value = NomRemitente;
Parametros[1].Direction = System.Data.ParameterDirection.Input;
Parametros[1].ParameterName = "NombreRemitente";
Parametros[2] = new System.Data.OracleClient.OracleParameter();
Parametros[2].OracleType = System.Data.OracleClient.OracleType.VarChar;
Parametros[2].Value = Destinatario;
Parametros[2].Direction = System.Data.ParameterDirection.Input;
Parametros[2].ParameterName = "CorreoDestinatario";
Parametros[3] = new System.Data.OracleClient.OracleParameter();
Parametros[3].OracleType = System.Data.OracleClient.OracleType.VarChar;
Parametros[3].Value = NombDestinatario;
Parametros[3].Direction = System.Data.ParameterDirection.Input;
Parametros[3].ParameterName = "NombreDestinatario";
Parametros[4] = new System.Data.OracleClient.OracleParameter();
Parametros[4].OracleType = System.Data.OracleClient.OracleType.Number;
Parametros[4].Value = IdEncuesta;
Parametros[4].Direction = System.Data.ParameterDirection.Input;
Parametros[4].ParameterName = "IdEncuesta";
intresp = NHibernateHelperORACLE.ExecuteNonQuery("ENVIA_CORREO_GRAFICA", Parametros);
if (intresp == 1)
{
blnBand = true;
}
else
{
blnBand = false;
}
}
catch (Exception ex)
{
MngDatosLogErrores.GuardaError(ex, "MngDatosGraficas");
blnBand = false;
}
finally
{
Parametros = null;
}
return(blnBand);
}
public OracleParameter Add(OracleParameter value)
{
base.Add(value);
return(value);
}
public OracleParameter Add(string parameterName, OracleType sqlDbType, int size, string sourceColumn)
{
OracleParameter param = new OracleParameter(parameterName, sqlDbType, size, sourceColumn);
return(Add(param));
}
public OracleParameter Add(string parameterName, OracleType sqlDbType)
{
OracleParameter param = new OracleParameter(parameterName, sqlDbType);
return(Add(param));
}
public OracleParameter Add(string parameterName, object value)
{
OracleParameter param = new OracleParameter(parameterName, value);
return(Add(param));
}
/// <summary>
/// Fetch a VADIR formatted MOS report for a patient given the EDIPI or Name, SSN and DOB
/// </summary>
/// <param name="patient"></param>
/// <returns>TextReport with report text set to VADIR report</returns>
public TextReport getMOSReport(Patient patient)
{
if (!isValidMosPatient(patient))
{
throw new ArgumentException("Invalid patient. Need name, SSN and DOB or EDIPI");
}
//System.Data.OracleClient. OracleQuery query = new OracleQuery();
System.Data.OracleClient.OracleCommand command = new System.Data.OracleClient.OracleCommand();
command.CommandText = "BLUE_BUTTON.FETCHREPORT";
command.CommandType = System.Data.CommandType.StoredProcedure;
OracleParameter idParam = new System.Data.OracleClient.OracleParameter("VA_ID_IN", OracleType.Number);
//OracleParameter idParam = new OracleParameter("VA_ID_IN", OracleDbType.Decimal);
idParam.Direction = System.Data.ParameterDirection.Input;
idParam.Value = Convert.ToDecimal(patient.EDIPI);
command.Parameters.Add(idParam);
OracleParameter lNameParam = new System.Data.OracleClient.OracleParameter("LNAME_IN", OracleType.VarChar, 26);
//OracleParameter lNameParam = new OracleParameter("LNAME_IN", OracleDbType.Varchar2, 26);
lNameParam.Direction = System.Data.ParameterDirection.Input;
lNameParam.Value = "";
command.Parameters.Add(lNameParam);
OracleParameter ssnParam = new System.Data.OracleClient.OracleParameter("SSN_IN", OracleType.VarChar, 9);
//OracleParameter ssnParam = new OracleParameter("SSN_IN", OracleDbType.Varchar2, 9);
ssnParam.Direction = System.Data.ParameterDirection.Input;
ssnParam.Value = "";
command.Parameters.Add(ssnParam);
OracleParameter dobParam = new System.Data.OracleClient.OracleParameter("DOB_IN", OracleType.DateTime);
//OracleParameter dobParam = new OracleParameter("DOB_IN", OracleDbType.TimeStamp);
dobParam.Direction = System.Data.ParameterDirection.Input;
dobParam.Value = DBNull.Value;
command.Parameters.Add(dobParam);
OracleParameter returnParam = new OracleParameter("v_Return", OracleType.Clob);
//OracleParameter returnParam = new OracleParameter("v_Return", OracleDbType.Clob);
returnParam.Direction = System.Data.ParameterDirection.ReturnValue;
command.Parameters.Add(returnParam);
System.Data.OracleClient.OracleConnection cxn = new OracleConnection(_cxn.DataSource.ConnectionString);
cxn.Open();
try
{
command.Connection = cxn;
OracleDataReader reader = command.ExecuteReader();
//_cxn.connect();
//executeReader executeReader = delegate() { return command.ExecuteReader(); };
//OracleDataReader reader = (OracleDataReader)_cxn.query(query, executeReader);
//if (query.Command.Parameters["v_Return"] == null || query.Command.Parameters["v_Return"].Value == DBNull.Value)
//{
// return null;
//}
string text = ((System.Data.OracleClient.OracleLob)command.Parameters["v_Return"].Value).Value.ToString();
return(new TextReport()
{
Text = text
});
}
catch (Exception)
{
throw;
}
finally
{
cxn.Close();
}
}
internal int PutOracleValue(object value, NativeBuffer buffer, int bufferOffset, MetaType metaType, OracleConnection connection, ref SafeHandle handleToBind)
{
handleToBind = null;
OCI.DATATYPE ociType = metaType.OciType;
OracleParameter parameter = this.Parameter;
switch (ociType)
{
case OCI.DATATYPE.VARCHAR2:
case OCI.DATATYPE.LONG:
case OCI.DATATYPE.LONGVARCHAR:
case OCI.DATATYPE.CHAR:
return(OracleString.MarshalToNative(value, parameter.Offset, parameter.GetActualSize(), buffer, bufferOffset, ociType, this._bindAsUCS2));
case OCI.DATATYPE.INTEGER:
case OCI.DATATYPE.FLOAT:
case OCI.DATATYPE.UNSIGNEDINT:
buffer.StructureToPtr(bufferOffset, value);
return(metaType.BindSize);
case OCI.DATATYPE.VARNUM:
return(OracleNumber.MarshalToNative(value, buffer, bufferOffset, connection));
case OCI.DATATYPE.DATE:
return(OracleDateTime.MarshalDateToNative(value, buffer, bufferOffset, ociType, connection));
case OCI.DATATYPE.RAW:
case OCI.DATATYPE.LONGRAW:
case OCI.DATATYPE.LONGVARRAW:
{
int num;
byte[] buffer2;
if (this._coercedValue is OracleBinary)
{
OracleBinary binary = (OracleBinary)this._coercedValue;
buffer2 = binary.Value;
}
else
{
buffer2 = (byte[])this._coercedValue;
}
int num2 = buffer2.Length - parameter.Offset;
int actualSize = parameter.GetActualSize();
if (actualSize != 0)
{
num2 = Math.Min(num2, actualSize);
}
if (OCI.DATATYPE.LONGVARRAW == ociType)
{
buffer.WriteInt32(bufferOffset, num2);
bufferOffset += 4;
num = num2 + 4;
}
else
{
num = num2;
}
buffer.WriteBytes(bufferOffset, buffer2, parameter.Offset, num2);
return(num);
}
case OCI.DATATYPE.CLOB:
case OCI.DATATYPE.BLOB:
if (!(value is OracleLob))
{
throw System.Data.Common.ADP.BadBindValueType(value.GetType(), metaType.OracleType);
}
handleToBind = ((OracleLob)value).Descriptor;
return(IntPtr.Size);
case OCI.DATATYPE.BFILE:
if (!(value is OracleBFile))
{
throw System.Data.Common.ADP.BadBindValueType(value.GetType(), metaType.OracleType);
}
handleToBind = ((OracleBFile)value).Descriptor;
return(IntPtr.Size);
case OCI.DATATYPE.INT_TIMESTAMP:
case OCI.DATATYPE.INT_TIMESTAMP_LTZ:
if (value is OracleDateTime)
{
OracleDateTime time = (OracleDateTime)value;
if (!time.HasTimeInfo)
{
throw System.Data.Common.ADP.UnsupportedOracleDateTimeBinding(metaType.OracleType);
}
}
this._dateTimeDescriptor = OracleDateTime.CreateDescriptor(ociType, connection, value);
handleToBind = this._dateTimeDescriptor;
return(IntPtr.Size);
case OCI.DATATYPE.INT_TIMESTAMP_TZ:
if (value is OracleDateTime)
{
OracleDateTime time2 = (OracleDateTime)value;
if (!time2.HasTimeZoneInfo)
{
throw System.Data.Common.ADP.UnsupportedOracleDateTimeBinding(OracleType.TimestampWithTZ);
}
}
this._dateTimeDescriptor = OracleDateTime.CreateDescriptor(ociType, connection, value);
handleToBind = this._dateTimeDescriptor;
return(IntPtr.Size);
case OCI.DATATYPE.INT_INTERVAL_YM:
return(OracleMonthSpan.MarshalToNative(value, buffer, bufferOffset));
case OCI.DATATYPE.INT_INTERVAL_DS:
return(OracleTimeSpan.MarshalToNative(value, buffer, bufferOffset));
}
throw System.Data.Common.ADP.TypeNotSupported(ociType);
}
//private Transaction CreateTransaction()
//{
// Transaction tr=mConn.BeginTransaction();
// tr.Commit();
//}
#endregion
#endregion
#region 公开方法：存贮过程相关
#region CallProc
public bool CallProc(IStoredProc objProc, Transaction tr)
{
#region 创建命令
DbCommand dbCommand = new DbCommand();
dbCommand.Connection = mConn;
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = objProc.GetProcName();
dbCommand.Transaction = tr;
#endregion
#region 参数赋值
#if DT_ORACLE
DbParameter[] dbparams = this.DeriveParameters(objProc.GetProcName(), false);
#elif DT_MSSQL
DbParameter[] dbparams = this.DeriveParameters(objProc.GetProcName(), true);
#elif DT_OLEDB
DbParameter[] dbparams = new DbParameter[0];
this.WriteLogInfo(this.GetType().Name + "OLEDB方式不支持取存贮过程参数");
return(false);
#endif
Hashtable htParam = objProc.GetFields();
foreach (DbParameter dbparam in dbparams)
{
if (dbparam.Direction == ParameterDirection.Input || dbparam.Direction == ParameterDirection.InputOutput)
{
dbparam.Value = htParam[dbparam.ParameterName.ToUpper()];
}
else
{
dbparam.Value = DBNull.Value;
}
dbCommand.Parameters.Add(dbparam);
}
#endregion
#region 执行
DataSet mDataSet = new DataSet();
using (DataAdapter mDataAdapter = new DataAdapter(dbCommand))
{
try
{
mDataAdapter.Fill(mDataSet);
}
catch (Exception e)
{
WriteLogInfo("error on " + this.GetType().Name + " " + objProc.GetProcName() + ".excute:\r\n" + e.Message);
return(false);
}
}
#endregion
#region 读取参数
foreach (DbParameter dbparam in dbparams)
{
if (dbparam.Direction == ParameterDirection.Output || dbparam.Direction == ParameterDirection.InputOutput)
{
htParam[dbparam.ParameterName.ToUpper()] = dbparam.Value;
}
}
if (objProc.GetCursorFieldList() != "")
{
string[] cursorFields = objProc.GetCursorFieldList().Split(new char[] { ',' });
if (mDataSet.Tables.Count != cursorFields.Length)
{
WriteLogInfo("error on " + this.GetType().Name + " " + objProc.GetProcName() + ".excute:\r\n 返回的游标数量与参数不匹配："
+ "\r\n\t游标参数:" + objProc.GetCursorFieldList()
+ "\r\n\t游标返回数:" + mDataSet.Tables.Count);
return(false);
}
int idx = 0;
foreach (string cursorField in cursorFields)
{
htParam[cursorField.ToUpper()] = mDataSet.Tables[idx++];
}
}
#endregion
return(true);
}
private void ImportPhoto(string[] imgFileNames)
{
string jsBlock = string.Empty;
string templateFileName = Server.MapPath("/RailExamBao/RandomExam/ProgressBar.htm");
StreamReader reader = new StreamReader(@templateFileName, System.Text.Encoding.GetEncoding("gb2312"));
string html = reader.ReadToEnd();
reader.Close();
Response.Write(html);
Response.Flush();
System.Threading.Thread.Sleep(200);
// 添加滚动条效果
jsBlock = "<script>SetPorgressBar('正准备导入照片数据','0.00'); </script>";
Response.Write(jsBlock);
Response.Flush();
OracleAccess db = new OracleAccess();
string errorMessage = string.Empty;
// 循环批量导入照片文件
for (int i = 0; i < imgFileNames.Length; i++)
{
// 滚动条效果
System.Threading.Thread.Sleep(10);
jsBlock = "<script>SetPorgressBar('正在导入照片数据','" +
((double)((i + 1) * 100) / (double)imgFileNames.Length).ToString("0.00") + "'); </script>";
Response.Write(jsBlock);
Response.Flush();
string f_n = imgFileNames[i];
int lastPathChar = f_n.LastIndexOf("\\");
string filename = f_n.Substring(lastPathChar + 1, f_n.LastIndexOf(".") - lastPathChar - 1);
// TODO: 导入单张照片
try
{
System.Drawing.Image image = System.Drawing.Image.FromFile(imgFileNames[i]);
System.Drawing.Image thumbnail = image.GetThumbnailImage(120, 150, null, IntPtr.Zero);
MemoryStream ms = new MemoryStream();
thumbnail.Save(ms, ImageFormat.Jpeg);
byte[] byteImage = ms.ToArray();
string workno = filename.Substring(0, 8);
string name = filename.Replace(workno, "");
string strSql = "select * from Employee where Work_No='" + workno + "' and Employee_Name='" + name + "'";
DataSet ds = db.RunSqlDataSet(strSql);
if (ds.Tables[0].Rows.Count > 0)
{
//添加
XmlDocument doc = new XmlDocument();
doc.Load(Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath, "web.config"));
XmlNode node = doc.SelectSingleNode("configuration/dataConfiguration/@defaultDatabase");
string value = node.Value;
if (value == "Oracle")
{
System.Data.OracleClient.OracleParameter para1 = new System.Data.OracleClient.OracleParameter("p_photo", OracleType.Blob);
System.Data.OracleClient.OracleParameter para2 = new System.Data.OracleClient.OracleParameter("p_id", OracleType.Number);
para1.Value = byteImage;
para2.Value = Convert.ToInt32(ds.Tables[0].Rows[0]["Employee_ID"]);
IDataParameter[] paras = new IDataParameter[] { para1, para2 };
Pub.RunAddProcedureBlob(false, "USP_EMPLOYEE_IMAGE", paras, byteImage);
}
}
else
{
errorMessage += "图片【" + filename + "】在系统中查询不到对应的员工信息\n";
}
image.Dispose();
ms.Dispose();
thumbnail.Dispose();
}
catch
{
errorMessage += "文件【" + filename + "】不是图片或者文件名错误\n";
}
System.Threading.Thread.Sleep(10);
jsBlock = "<script>SetPorgressBar('正在导入图片','" + ((double)((i + 1) * 100) / (double)imgFileNames.Length) + "'); </script>";
Response.Write(jsBlock);
Response.Flush();
}
if (errorMessage != string.Empty)
{
Response.Write("<script>window.returnValue='" + errorMessage + "',window.close();</script>");
return;
}
// 处理完成
jsBlock = "<script>SetCompleted('照片数据导入完毕'); </script>";
Response.Write(jsBlock);
Response.Flush();
}
