SQL拆分字符串

有时候我们可能会用到批量添加数据的操作,比如从文本文件导入客户资料等,可以用循环插入的方式来处理,不过那样的话性能损失非常大,因为我们会频繁的连接数据库多次,当然也可以把数据先加载到内存,再使用数据适配器来批量更新;这里我使用另外一种方法:交给存储过程,在存储过程里面来完成循环插入数据的操作.这样就只需要打开一次数据库连接.

             

string strTels = string.Empty;

            string strName = string.Empty;

             if (fileuser.Value.Trim() == "")

            {

                 JScriptHelper.MessageBox(this.Page, "请指定文件路径！");

                 return;

            }

            string readStr = Tools.ReadFile(Tools.UpLoadFile(fileuser));

            string[] strTemp = readStr.Split(("/r/n").ToCharArray());

             string temp = Tools.ValidateTel(strTemp);

             if (temp != null)

             {

                JScriptHelper.MessageBox(this.Page, "存在非法手机号：" + temp);

                return;

             }

            if (ddlGroup.SelectedValue == "0")

             {

                JScriptHelper.MessageBox(this.Page, "未选择号码组或号码组不存在！");

                 return;

             }

            temp = ValidateTelExist(strTemp);

             if (temp != null)

            {

                JScriptHelper.MessageBox(this.Page, "手机号码已存在：" + temp);

                 return;

            }

            foreach (string str in strTemp)

            {

                 if (str != "")

                {

                     string[] tels = str.Split(',');

                    strTels += tels[0] + ",";

                    if (tels.Length > 1)

                        strName += tels[1] + ",";

                    //SendCustomerEntity _ce = new SendCustomerEntity();

                    //_ce.Tel = tels[0];

                    //if (tels.Length>1)

                    //    _ce.Name = tels[1];

                     //_ce.UserName = CookieHelper.GetLoginName();

                    //_ce.FirstDate = DateTime.Now;

                    //_ce.EndDate = DateTime.Now;

                    //SendGroupCusEntity _sce = new SendGroupCusEntity();

                    //_sce.Tel = _ce.Tel;

                     //_sce.GroupID = int.Parse(ddlGroup.SelectedValue);                     //DataFactory<SendCustomerSqlProvider>.CreateProvider.InsertEntity(_ce);                     //DataFactory<SendGroupCusSqlProvider>.CreateProvider.InsertEntity(_sce);

                }

            }

             if (strTels.Length > 8000 || strName.Length > 8000)

            {

                JScriptHelper.MessageBox(this.Page, "一次导入的号码太多，请分多次导入！");

                return;

            }

            if (strTels.Length <= 0)

             {

                JScriptHelper.MessageBox(this.Page, "文件中没有号码！");

                return;

             }

            try

             {

                ImportCustomerEntity ice = new ImportCustomerEntity();

                 ice.StrTel = strTels.Substring(0, strTels.Length - 1);

                if (strName.Length > 0)

                    strName = strName.Substring(0, strName.Length - 1);

                ice.StrName = strName;

                 ice.StrUserName = CookieHelper.GetLoginName();

                ice.GroupID = int.Parse(ddlGroup.SelectedValue); 

                 StoreProcedureProvider<ImportCustomerEntity>.ExecStoreProcduce(ice);         JScriptHelper.MessageBox(this.Page, "导入成功！");

             }

            catch

            {

                 JScriptHelper.MessageBox(this.Page, "导入失败！");

            }

假如我在文本文件里面保存有客户手机号和名字,现在需要导入到数据库里面去,上面这段代码就是实现了这个功能,如果我的文本文件里面有10000个客户资料,那么现在程序会打开10000次的数据库连接,这对性能的影响是相当大的,所以我选择了把手机号码和客户姓名组成一个字符串,中间用逗号隔开,然后交给存储过程来处理,下面是存储过程的代码:

 

set ANSI_NULLS ON

 set QUOTED_IDENTIFIER ON

 GO

 -- =============================================

-- Author:        Allen

-- Create date: 2008-6-11

 -- Description:    批量导入客户

 -- =============================================

Create PROCEDURE [dbo].[ImportCustomer]

     @strTel varchar(8000),

    @strName varchar(8000),

    @strUserName varchar(50),

     @groupID int

 AS

 BEGIN

     declare @count int

     declare @tempTel varchar(20)

    declare @tempName varchar(20)

     create  table  #temp(id int IDENTITY(1,1) NOT NULL primary key,tel  varchar(20)) 

     declare  @strsql  as  varchar(8000) 

     select  @strsql=''      select  @strsql='insert  into  #temp  values  ('''+replace(@strTel,',',''');  insert  into  #temp  values  (''')+''')'

      exec  (@strsql) 

        create  table  #temp1(id int IDENTITY(1,1) NOT NULL primary key,[name]  varchar(20)) 

     declare  @strsq2  as  varchar(8000) 

     select  @strsq2=''      select  @strsq2='insert  into  #temp1  values  ('''+replace(@strName,',',''');  insert  into  #temp1  values  (''')+''')'

      exec  (@strsq2)

     select @count = count(*) from #temp

     while(@count>0) 

      BEGIN 

        select @tempTel = tel from #temp where id=@count

         select @tempName = [Name] from #temp1 where id=@count 

        insert into dbo.SendCustomer(Tel,[Name],FirstDate,EndDate,UserName)         values(@tempTel,@tempName,getdate(),getdate(),@strUserName) 

        insert into dbo.SendGroupCus(Tel,GroupID) values(@tempTel,@groupID)

         Set @count = @count-1

     END

     drop table #temp

     drop table #temp1

 END

 

这样处理以后程序只需要连接一次数据库,性能提高了很多.