ASP.NET 存储过程导入（oracle）返回导入成功数和导入失败数

存储过程格式

 1 create or replace procedure 存储过程名 ([参数] [参数类型],[参数] [参数类型])
 2 as
 3 
 4  successc number;
 5  failc number;
 6 begin
 7     insert into [表名](
 8     字段,
 9     .
10     .
11     .
12     字段)
13     (select
14     字段,
15     .
16     .
17     .
18     字段
19     from [表名] where [条件]);
20     
21     update [表名] set xx='xx' where [条件] ;
22     
23     commit;
24     select  count(1) into successc  from [表名] where  Flag='1';
25     select count(1) into failc  from [表名] where  nvl(flag,0)<>1;
26      
27      open cur_arg for  select successc as successc,failc as failc from dual; //打开游标查找
28   end;

View Code

存储过程

create or replace procedure Proc_PX_SchoolInportScore(PlanType nvarchar2,BanQGuid nvarchar2,CountryNums nvarchar2, cur_arg out sys_refcursor)
as

 successc number;
   failc number;
begin
  
  
update PX_SchoolInportScore set CountryNum=CountryNums where nvl( identitynum,'')<>1;
    --导入成绩
   insert into pk_user(RowGuid,
             Name,--姓名
             IdentityNum,--身份证号码
             Age,--年龄
             Sex,--性别
             EducationCode,--学历
             DanWeiName,--单位名称
             WorkType,--工种
             IsEnable,--是否启用
             Status,--状态
             UserType--类别
             )
   (--插入到人员表   条件：临时表里的人员不在人员表中  判断依据 IdentityNum
   select
   LOWER(sys_guid()),
   Name,
   IdentityNum,
   Age,
   (case Sex when cast('男' as nvarchar2(10)) then cast('0' as nvarchar2(10))  else cast('1' as nvarchar2(10))  end) as Sex,
   (select ItemValue from VIEW_CodeMain_CodeItems where CodeName='PX_学历' and ItemText=Education) as EducationCode,
   DanWeiName,
   WorkType,
   '1',
   '1',
   '0'
   from PX_SchoolInportScore
   WHERE not exists (select pk_user.IdentityNum from pk_user WHERE  PX_SchoolInportScore.IdentityNum=pk_user.IdentityNum)
   );

   insert into PX_BaoM(RowGuid,
             Name,
             UserGuid,
             LoginID,
             DanWeiName,
             PXPrograms,
             Note,
             CountryNum,
             Ispay
             )
   (--插入到报名表    条件：从临时表里插入成功到人员表中的人  并且这些人不存在报名表中  条件：人员表的RowGuid   报名表的UserGuid
      select LOWER(sys_guid()),
           a.Name,
           b.RowGuid,
           a.IdentityNum,
           a.DanWeiName,
           '02',
           Note,
           CountryNum,
           '1'
      from PX_SchoolInportScore a inner join pk_user b on a.IdentityNum=b.IdentityNum
      --where b.rowguid not in (select UserGuid from PX_BaoM)
      where not exists (select UserGuid from PX_BaoM where userguid= b.rowguid)
     );


     insert into PX_BaoMDetail(
                               RowGuid,
                               ItemGuid,
                               IsDel,
                               ClassGuid,
                               ParentGuid
                                )
     (--插入到报名子表   条件：插入成功到报名表里的人  并且这些人不在子表中       子表的ParentGuid  报名表的 RowGuid
      select
      LOWER(sys_guid()),
      PlanType,
      '0',
      BanQGuid,
      c.RowGuid
            from PX_SchoolInportScore a
            join pk_user b on a.IdentityNum=b.IdentityNum
            join PX_BaoM c on  b.rowguid =c.UserGuid
            where c.RowGuid not in (select ParentGuid  from PX_BaoMDetail)
     );


 update PX_BaoM set CountryNum=CountryNums,IsPay='1' where RowGuid
         in ( select a.RowGuid from View_Score_UserType a inner join PX_SchoolInportScore b on 
              a.IdentityNum=b.IdentityNum where  a.ClassGuid=BanQGuid and a.TypeGuid=PlanType 
              );

  

update PX_SchoolInportScore set flag=1 where IdentityNum in (select LoginID  from view_schoolimportscore where classGuid=BanQGuid );

commit;

     select  count(1) into successc  from PX_SchoolInportScore where  Flag='1';
     select count(1) into failc  from PX_SchoolInportScore where  nvl(flag,0)<>1;

   open cur_arg for  select successc as successc,failc as failc from dual;
  end;

View Code

PX_SchoolInportScore 临时表

ASP.NET代码

protected void upload1_FileUploadCompleted_Custom(object sender, EventArgsOperate.AttachEventArgs[] args)
        {
            if (!Directory.Exists(Server.MapPath(@"ImportExcel")))
                Directory.CreateDirectory(Server.MapPath(@"ImportExcel"));
            string mark = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString();
            string oldfileName = args[0].FileName;
            string documentType = oldfileName.Substring(oldfileName.LastIndexOf('.'), oldfileName.Length - oldfileName.LastIndexOf('.'));
            string fileName = "Import_" + mark + documentType;
            args[0].CuteArgs.CopyTo(Server.MapPath(@"ImportExcel\") + fileName);

            if (!string.IsNullOrEmpty(DDLBQ.SelectedValue))
                ReadExcel(Server.MapPath(@"ImportExcel\") + fileName);
            else
            {
                this.AlertAjaxMessage("请选择班次");
            }
        }

View Code

public void ReadExcel(string ExcelFile)
        {
            DataSet ds;
            string ms = "0";//记录导入成功数
            string mf = "0";//记录导入失败数
            string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelFile + ";" + "Extended Properties='Excel 12.0';";

            OleDbConnection conn = new OleDbConnection(strConn);
            DataTable dtExcelSchema = new DataTable();
            try
            {
                conn.Open();
                dtExcelSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
                    new object[] { null, null, null, "Table" });//获取需要上传的Excel的Sheet
                conn.Close();
            }
            catch
            {
                throw;
            }
            for (int k = 0; k < dtExcelSchema.Rows.Count; k++)
            {
                try
                {
                    string SheetName = (string)dtExcelSchema.Rows[k]["TABLE_NAME"];
                    if (SheetName.Contains("$") && !SheetName.Replace("'", "").EndsWith("$"))
                        continue;//过滤无效SheetName

                    //OleDbDataAdapter oada = new OleDbDataAdapter("select * from [Sheet" + k + "$]", strConn);
                    OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + SheetName + "]", strConn);
                    ds = new DataSet();
                    oada.Fill(ds);
                }
                catch
                {
                    throw;
                }

                DataTable dt = ds.Tables[0];
                int count = dt.Rows.Count;
                //
                if (count == 0 || (count > 0 && dt.Rows[0][0].ToString() == ""))
                    continue;//过滤无数据的sheet
                string tableName = "PX_SchoolInportScore";
               
                bool flag = false;

                
                BulkToDB(dt, tableName, out flag);
                if (flag)
                {
                    M_PX_ZhuanYe m_zy = b_zy.SelectByZhuanYeNum_PXProgram(plantype, programs);
                    DataSet dsscore = b_bmd.InportSchoolScore(m_zy.RowGuid, DDLBQ.SelectedValue, DDLCountry.SelectedValue);
                    // 展示成功多少个 失败多少个
                    if (dsscore.Tables.Count > 0)
                    {
                        DataView dvs = dsscore.Tables[0].DefaultView;
                        ms = dvs.Count > 0 ? dvs[0]["successc"].ToString() : "0";
                        mf = dvs.Count > 0 ? dvs[0]["failc"].ToString() : "0";
                    }
                    b_bmd.DeleteSchoolScoreAll();//每次导入完成后删除零时表中数据
                }
            }
            string alerterror = "成功导入：" + ms + "人,导入失败：" + mf + "人";
            this.AlertAjaxMessage(alerterror);
        }

View Code

public static void BulkToDB(DataTable dt, string targetTable, out bool flag)
        {
            string connectionString = System.Configuration.ConfigurationManager.AppSettings["CoonString"].ToString();       
            bool flags = false;
            OracleConnection conn = new OracleConnection(connectionString);
             
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            OracleBulkCopy bulkCopy = new OracleBulkCopy(conn, OracleBulkCopyOptions.Default);
            bulkCopy.BatchSize = 100000;
            bulkCopy.BulkCopyTimeout = 260;
            bulkCopy.DestinationTableName = targetTable;
            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                if (dt != null && dt.Rows.Count != 0)
                {
                    bulkCopy.ColumnMappings.Add("姓名", "Name");
                    bulkCopy.ColumnMappings.Add("性别", "Sex");
                    bulkCopy.ColumnMappings.Add("年龄", "Age");
                    bulkCopy.ColumnMappings.Add("学历", "Education");
                    bulkCopy.ColumnMappings.Add("工作单位", "DanWeiName");
                    bulkCopy.ColumnMappings.Add("工种", "WorkType");
                    bulkCopy.ColumnMappings.Add("身份证号码", "IdentityNum");
                    bulkCopy.WriteToServer(dt);
                    flags = true;

                }
            }
            catch (Exception ex)
            {
                flags = false;
                Epoint.Frame.Common.LogOperate.WriteLog(ex.ToString());
            }
            finally
            {
                flag = flags;
                conn.Close();
                if (bulkCopy != null)
                    bulkCopy.Close();

            }

        }

View Code

posted on 2017-01-24 15:44 努力向上的小蜗牛阅读(294) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

导航

ASP.NET 存储过程导入（oracle）返回导入成功数和导入失败数