批量上传 excel表格 循环插入 求解决。代码如下 (新号 虽然分少了点,热情不减!!!希望大家支持 )
表结构如下:
Name Type Nullable Default Comments
----------------------- --------------- -------- ------- --------
POLICYCODE VARCHAR2(30)
CASENUMBER VARCHAR2(30)
TID INTEGER Y
ACCIDENTTIME DATE Y
BAOANTIME DATE Y
CASECARNUMBER NVARCHAR2(20) Y
CASECARCOLOR NVARCHAR2(10) Y
CARNUMCOLOR NVARCHAR2(10) Y
CASECARFRAMENUMBER NVARCHAR2(30) Y
public static void casemessage(DataTable zh, List<OracleType> DbTypeList, string casemessage)
{
int count = 0;
string strfields = "";
string strvalues = "";
string filed="";
string strvalue=""; for (int i = 0; i < zh.Columns.Count; i++)
{
string strfiled = zh.Columns[i].ColumnName;
strfields += strfiled + ",";
strvalues += "@" + strfiled + ",";
}
if (strfields != "")
{
strfields = strfields.Substring(0, strfields.LastIndexOf(","));
strvalues = strvalues.Substring(0, strvalues.LastIndexOf(","));
}
try
{
OracleConnection sqlconn = new OracleConnection(ConfigurationManager.AppSettings[connectdate.getconnect()]);
sqlconn.Open();
OracleDataAdapter da = new OracleDataAdapter();
//建立InsertCommand
da.InsertCommand = new OracleCommand();
StringBuilder sb = new StringBuilder("");
da.InsertCommand.CommandText = sb.ToString();
sb.Append("INSERT INTO " + casemessage + " (" + strfields + ") VALUES(");
sb.Append(strvalues + ")");
for (int i = 0; i < zh.Columns.Count; i++)
{
filed = zh.Columns[i].ColumnName;
strvalue = "@" + filed;
OracleParameter oparam = new OracleParameter();
oparam.ParameterName = strvalue;
oparam.OracleType = DbTypeList[i];
oparam.SourceVersion = DataRowVersion.Current;
oparam.SourceColumn = filed;
odad.InsertCommand.Parameters.Add(oparam);
}
count = odad.Update(zh);
OracleCommand cmd = new OracleCommand();
//删除掉重复记录,在casemessage 表中当 casenumber 相同时则认为该数据重复......
string sql = "delete from casemessage where tid not in (select min(tid) from casemessage group by casenumber)";
da.DataCom(sql);
sqlconn.Close();
} catch (Exception ex)
{
count = 0;
}
finally
{ sqlconn.Close();
}
return count;
}
表结构如下:
Name Type Nullable Default Comments
----------------------- --------------- -------- ------- --------
POLICYCODE VARCHAR2(30)
CASENUMBER VARCHAR2(30)
TID INTEGER Y
ACCIDENTTIME DATE Y
BAOANTIME DATE Y
CASECARNUMBER NVARCHAR2(20) Y
CASECARCOLOR NVARCHAR2(10) Y
CARNUMCOLOR NVARCHAR2(10) Y
CASECARFRAMENUMBER NVARCHAR2(30) Y
public static void casemessage(DataTable zh, List<OracleType> DbTypeList, string casemessage)
{
int count = 0;
string strfields = "";
string strvalues = "";
string filed="";
string strvalue=""; for (int i = 0; i < zh.Columns.Count; i++)
{
string strfiled = zh.Columns[i].ColumnName;
strfields += strfiled + ",";
strvalues += "@" + strfiled + ",";
}
if (strfields != "")
{
strfields = strfields.Substring(0, strfields.LastIndexOf(","));
strvalues = strvalues.Substring(0, strvalues.LastIndexOf(","));
}
try
{
OracleConnection sqlconn = new OracleConnection(ConfigurationManager.AppSettings[connectdate.getconnect()]);
sqlconn.Open();
OracleDataAdapter da = new OracleDataAdapter();
//建立InsertCommand
da.InsertCommand = new OracleCommand();
StringBuilder sb = new StringBuilder("");
da.InsertCommand.CommandText = sb.ToString();
sb.Append("INSERT INTO " + casemessage + " (" + strfields + ") VALUES(");
sb.Append(strvalues + ")");
for (int i = 0; i < zh.Columns.Count; i++)
{
filed = zh.Columns[i].ColumnName;
strvalue = "@" + filed;
OracleParameter oparam = new OracleParameter();
oparam.ParameterName = strvalue;
oparam.OracleType = DbTypeList[i];
oparam.SourceVersion = DataRowVersion.Current;
oparam.SourceColumn = filed;
odad.InsertCommand.Parameters.Add(oparam);
}
count = odad.Update(zh);
OracleCommand cmd = new OracleCommand();
//删除掉重复记录,在casemessage 表中当 casenumber 相同时则认为该数据重复......
string sql = "delete from casemessage where tid not in (select min(tid) from casemessage group by casenumber)";
da.DataCom(sql);
sqlconn.Close();
} catch (Exception ex)
{
count = 0;
}
finally
{ sqlconn.Close();
}
return count;
}
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货