如何在SQL Server 2008中存储超过8000个字符并分配大型连接字符串？ [英] How to store more than 8000 characters and assign large concatenated string in SQL server 2008?

查看：56
本文介绍了如何在SQL Server 2008中存储超过8000个字符并分配大型连接字符串？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在尝试连接大量的id并更新所有id的状态。例如：aclid in（4604019,4604018,4604017,4604016,4604015,4604014,4604013,4604012,4604011,4604010,4604009,4604008,4604007,4604006,4604005,4604004,4604003,4604002,4604001,4604000,4603999,4603998， 4603997,4603996,4603995,4603994,4603993,4603992,4603991,4603990,4603989,4603988）





请检查我的存储过程：
 ALTER PROCEDURE [dbo]。[VT_ACLReportChangeStatus] 
（
 @ChangeStatus nvarchar（50）= null，
 @ACLId nvarchar（max）
）
 AS 
 / * Exec VT_ACLReportChangeStatus'Complete'，'4599473,4599472,4599471,4599469,4599468'* / 
 BEGIN 
 
 UPDATE VT_ACLReport SET Status = @ChangeStatus WHERE ACLId in（Select * from SplitDelimiterString（@ACLId，'，'））
 
结束
 
 
请检查我的代码：
在代码我得到的所有id可能超过2000-3000，但它只更新1000行..如何我可以增加nvarchar（max）大小，采取所有的ID。 
 
 
 ACLId = ACLId.ToString（）。修剪（'，'）; 
 using（SqlConnection con = new SqlConnection（cs））
 {
 cmd = new SqlCommand（VT_ACLReportChangeStatus，con）; 
 cmd.CommandType = System.Data.CommandType.StoredProcedure; 
 cmd.CommandTimeout = 3600; 
 cmd.Parameters.Add（new SqlParameter（@ ACLId，SqlDbType.NVarChar，-1））; 
 cmd.Parameters.Add（new SqlParameter（@ ChangeStatus，SqlDbType.NVarChar，50））; 
 cmd.Parameters [@ ACLId]。Value = ACLId; 
 cmd.Parameters [@ ChangeStatus]。Value = ddlChangeStatus.SelectedItem.Text.ToString（）; 
 con.Open（）; 
 cmd.ExecuteNonQuery（）; 
 con.Close（）; 
}







我正在尝试连接大量的id'd和更新所有id的状态。例如：aclid in（4604019,4604018,4604017,4604016,4604015,4604014,4604013,4604012,4604011,4604010,4604009,4604008,4604007,4604006,4604005,4604004,4604003,4604002,4604001,4604000,4603999,4603998， 4603997,4603996,4603995,4603994,4603993,4603992,4603991,4603990,4603989,4603988）





AclId列类型是bigint是身份。



请你能帮我连接大字符串并更新所有存在aclid的行。



根据我的要求，我必须在同一个表中更新所选id的状态。所以我连接了所有选定的ID，然后尝试一次更新行。但问题是如果超过8000个字符，它在存储过程中不超过8000个字符



我尝试过： < br $> 


请检查我的存储过程：
 ALTER PROCEDURE [dbo]。[VT_ACLReportChangeStatus] 
（
 @ ChangeStatus nvarchar（50）= null，
 @ACLId nvarchar（max）
）
 AS 
 / * Exec VT_ACLReportChangeStatus'Complete'，'4599473,4599472,4599471,4599469,4599468 '* / 
 BEGIN 
 
 UPDATE VT_ACLReport SET Status = @ChangeStatus WHERE ACLId in（Select * from SplitDelimiterString（@ACLId，'，'））
 
结束
 
 
请检查我的代码：
在代码中我得到的所有id可能超过2000-3000，但它只更新了1000行..我可以增加nvarchar（max）size，取所有id。 
 
 
 ACLId = ACLId.ToString（）。修剪（'，'）; 
 using（SqlConnection con = new SqlConnection（cs））
 {
 cmd = new SqlCommand（VT_ACLReportChangeStatus，con）; 
 cmd.CommandType = System.Data.CommandType.StoredProcedure; 
 cmd.CommandTimeout = 3600; 
 cmd.Parameters.Add（new SqlParameter（@ ACLId，SqlDbType.NVarChar，-1））; 
 cmd.Parameters.Add（new SqlParameter（@ ChangeStatus，SqlDbType.NVarChar，50））; 
 cmd.Parameters [@ ACLId]。Value = ACLId; 
 cmd.Parameters [@ ChangeStatus]。Value = ddlChangeStatus.SelectedItem.Text.ToString（）; 
 con.Open（）; 
 cmd.ExecuteNonQuery（）; 
 con.Close（）; 
}

解决方案

在SQL Server 2008中执行此操作的最佳方法可能是使用用户定义的表变量保存id值的集合，而不是连接它们。执行此操作时，您可以将包含ID号的IEnumerable发送到SQL Server，您通常可以根据需要自由捕获和发送。例如：



用户定义表变量：

------------------ ------------ 

 CREATE TYPE [dbo]。[MyIDType] AS TABLE（
 id int，
 status varchar（10）
）





存储过程接受UDT（只有一种可能的方法）：

------------------------------- 

 CREATE PROCEDURE [dbo ]。[VT_ACLReportChangeStatus]（
 @idlist MyIDType READONLY 
）
 AS 
 BEGIN 
; 
 MERGE dbo.VT_ACLReportChangeStatus dst 
使用@idlist src 
 ON src.id = dst.id 
当匹配时，
更新设置
 dst.status = src.status 
; 
结束
 GO





然后在你的代码中，你可以创建一个列表，用id值填充它和状态，并将其作为参数传递。例如（未经测试，只是在我的头顶）相关的行记录可能是：

公共类MyStatusRecord {
 public int id {get ;组; } 
 public string status {get;组; } 
}





这些行必须位于定义SqlMetaData的集合中。这提供了正确的接口和输入，以将IEnumerable作为UDT发送到存储过程：

公共类MyStatusCollection：List< MyStatusRecord>，IEnumerable< SqlDataRecord> {
 IEnumerator< SqlDataRecord> IEnumerable< SqlDataRecord> .GetEnumerator（）{
 var data = new SqlDataRecord（
 new SqlMetaData（id，SqlDbType.Int），
 new SqlMetaData（status，SqlDbType.VarChar， 10）
）; 
 foreach（此行中的var行）{
 data.SetInt32（0，row.id）; 
 data.SetString（1，row.status）; 
 
收益率数据; 
} 
} 
}





然后，您可以生成值列表，例如： 

 MyStatusCollection mylist = new MyStatusCollection {
 new MyStatusRecord {id = 1，status =changed}，
 new MyStatusRecord {id = 2，status =删除}，
 //  - 等等 -  
}





那么你可以将其推送到数据库，如下所示：

 private void UpsertData（）
 {
 using（var conn = Connections.GetConnection（））//  -  - 或者你得到一个连接
 {
使用（var cmd = new SqlCommand（VT_ACLReportChangeStatus，conn））
 {
 cmd.CommandType = CommandType.StoredProcedure; 
 cmd.Parameters.Add（new SqlParameter（@ idlist，SqlDbType.Structured）
 {
 TypeName =dbo.MyIDType，
 Value = mylist 
 }）; 
 conn.Open（）; 
 cmd.ExecuteNonQuery（）; 
} 
} 
}





可以从MSDN sql server收集所有相关信息在线页面，所以不要只复制我在这里创建的内容......查找并弄明白。



祝你好运！ ：）


I am trying to concatenate the large number of id'd and to update the status of all id's. For example: aclid in (4604019,4604018,4604017,4604016,4604015,4604014,4604013,4604012,4604011,4604010,4604009,4604008,4604007,4604006,4604005,4604004,4604003,4604002,4604001,4604000,4603999,4603998,4603997,4603996,4603995,4603994,4603993,4603992,4603991,4603990,4603989,4603988)


Please check my stored Procedure:
ALTER PROCEDURE [dbo].[VT_ACLReportChangeStatus]
(
    @ChangeStatus nvarchar(50)=null,
    @ACLId nvarchar(max)
    )
AS
/* Exec VT_ACLReportChangeStatus 'Complete','4599473,4599472,4599471,4599469,4599468' */
BEGIN 

UPDATE VT_ACLReport SET Status = @ChangeStatus WHERE ACLId in (Select * from SplitDelimiterString(@ACLId,','))

End


Please check my code behind:
In code i am getting all the id's may be more than 2000-3000, but it is updating only 1000 rows..how can i increase nvarchar(max) size , to take all id's.


   ACLId = ACLId.ToString().Trim(',');
                using (SqlConnection con = new SqlConnection(cs))
                {
                    cmd = new SqlCommand("VT_ACLReportChangeStatus", con);
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.CommandTimeout = 3600;
                    cmd.Parameters.Add(new SqlParameter("@ACLId", SqlDbType.NVarChar,-1));
                    cmd.Parameters.Add(new SqlParameter("@ChangeStatus", SqlDbType.NVarChar, 50));
                    cmd.Parameters["@ACLId"].Value = ACLId;
                    cmd.Parameters["@ChangeStatus"].Value = ddlChangeStatus.SelectedItem.Text.ToString();
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
    }




I am trying to concatenate the large number of id'd and to update the status of all id's. For example: aclid in (4604019,4604018,4604017,4604016,4604015,4604014,4604013,4604012,4604011,4604010,4604009,4604008,4604007,4604006,4604005,4604004,4604003,4604002,4604001,4604000,4603999,4603998,4603997,4603996,4603995,4603994,4603993,4603992,4603991,4603990,4603989,4603988)


AclId Column type is bigint is identity.

Please can you help me in concatenating large string and to update all rows whose aclid is present.

According to my requirement , i have to update the status of selected id's in the same table . so i concatenated all selected id's and then trying to update the rows in one go. but problem is coming if the more then 8000 character, it's not taking more than 8000 character in stored procedure

What I have tried:

Please check my stored Procedure:
ALTER PROCEDURE [dbo].[VT_ACLReportChangeStatus]
(
    @ChangeStatus nvarchar(50)=null,
    @ACLId nvarchar(max)
    )
AS
/* Exec VT_ACLReportChangeStatus 'Complete','4599473,4599472,4599471,4599469,4599468' */
BEGIN 

UPDATE VT_ACLReport SET Status = @ChangeStatus WHERE ACLId in (Select * from SplitDelimiterString(@ACLId,','))

End


Please check my code behind:
In code i am getting all the id's may be more than 2000-3000, but it is updating only 1000 rows..how can i increase nvarchar(max) size , to take all id's.


   ACLId = ACLId.ToString().Trim(',');
                using (SqlConnection con = new SqlConnection(cs))
                {
                    cmd = new SqlCommand("VT_ACLReportChangeStatus", con);
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.CommandTimeout = 3600;
                    cmd.Parameters.Add(new SqlParameter("@ACLId", SqlDbType.NVarChar,-1));
                    cmd.Parameters.Add(new SqlParameter("@ChangeStatus", SqlDbType.NVarChar, 50));
                    cmd.Parameters["@ACLId"].Value = ACLId;
                    cmd.Parameters["@ChangeStatus"].Value = ddlChangeStatus.SelectedItem.Text.ToString();
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
    }

解决方案

Perhaps the best way to do this in SQL Server 2008 is to use a user-defined table variable to hold the collection of id values, instead of concatenating them. When you do this, you can send an IEnumerable containing the id numbers to SQL Server and you are generally free to capture and send as many as you need. For example:

User Defined Table Variable:
------------------------------

CREATE TYPE [dbo].[MyIDType] AS TABLE(
    id int,
    status varchar(10)
)



Stored Procedure Accepting UDT (just one possible method):
-------------------------------

CREATE PROCEDURE [dbo].[VT_ACLReportChangeStatus] (
    @idlist MyIDType READONLY
)
AS
BEGIN
    ;
    MERGE dbo.VT_ACLReportChangeStatus dst
    USING @idlist src
    ON src.id = dst.id
    WHEN MATCHED THEN
        UPDATE SET
            dst.status = src.status
    ;
END
GO



Then in your code, you could create a list, fill it with the id values and statuses, and pass it as a parameter. For example (not tested, just off the top of my head) the associated "row" record could be:

public class MyStatusRecord {
    public int id { get; set; }
    public string status { get; set; }
}



The rows need to be in a collection that defines the SqlMetaData. This provides the proper interface and typing to send an IEnumerable to a stored proc as a UDT:

public class MyStatusCollection : List<MyStatusRecord>, IEnumerable<SqlDataRecord> {
    IEnumerator<SqlDataRecord> IEnumerable<SqlDataRecord>.GetEnumerator() {
        var data = new SqlDataRecord(
            new SqlMetaData("id", SqlDbType.Int),
            new SqlMetaData("status", SqlDbType.VarChar, 10)
        );
        foreach (var row in this) {
            data.SetInt32(0, row.id);
            data.SetString(1, row.status);

            yield return data;
        }
    }
}



You can then generate a list of values, something like:

MyStatusCollection mylist = new MyStatusCollection {
    new MyStatusRecord { id = 1, status = "changed" },
    new MyStatusRecord { id = 2, status = "deleted" },
// -- and so forth --
}



Then you can push that to the database like this:

private void UpsertData()
{
    using (var conn = Connections.GetConnection()) // -- or however you get a connection
    {
        using (var cmd = new SqlCommand("VT_ACLReportChangeStatus", conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@idlist", SqlDbType.Structured)
            {
                TypeName = "dbo.MyIDType",
                Value = mylist
            });
            conn.Open();
            cmd.ExecuteNonQuery();
        }
    }
}



All the relevant information can be gleaned from the MSDN sql server pages online, so don't just copy what I created here...look it up and figure it out.

Good luck! :)


这篇关于如何在SQL Server 2008中存储超过8000个字符并分配大型连接字符串？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发语言最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆