blocks|key|807724|text|你真的应该有一个单独的。重用连接是由connection+pooling处理的。第二个问题，就像其他人所说的，可能可以通过启用MARS来解决。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|807725|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx^0|I|I|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

You should have a separate one really. Reusing connections is handled with <a href="http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx" rel="nofollow noreferrer">connection pooling</a>. The 2nd issue like others have said can probably be resolved by enabling MARS.

blocks|key|4026344|text|这个错误绝对与身份验证无关。在关闭从ExecuteReader()返回的SqlDataReader之前，您正在重用中间的连接。这是被禁止的。您必须检查您的代码并消除您的问题。有一些替代方案可以使用MARS+(multiple+active+record+sets+)，但我强烈建议您这样做。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|4026345|在您的应用程序中使用多个连接可能会更糟，因为显然您不知道正在使用什么连接以及何时使用，所以当您将使用单独的连接时，您将遇到事务一致性问题。|4026346|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms345109(SQL.90%2529.aspx)^0|2W|R|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]]|C|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|C|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]]]

That error has absolutely nothing to do with authentication. You are reusing a connection from the middle before you closed your SqlDataReader returned from an ExecuteReader(). This is forbidden. You must check your code and eliminate your problem. There are alternatives to use MARS (<a href="http://msdn.microsoft.com/en-us/library/ms345109(SQL.90).aspx" rel="nofollow noreferrer">multiple active record sets</a>) but I would strongly discourage that.

Using multiple connections in your application will probably be even worse because apparently you don't know what connection are in use and when, so when you'll use separate connections you will run into transaction consistency problems.

blocks|key|4871437|text|其中很大一部分可能是dup+from+here；简而言之，在大多数情况下，保持连接是短暂的和本地的。Re是数据读取器；也许启用MARS(http://msdn.microsoft.com/en-us/library/cfa084cz(VS.80%2529.aspx)?|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|4871438|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/861552/when-should-i-open-and-close-a-connection-to-sql-server|1|http://msdn.microsoft.com/en-us/library/cfa084cz(VS.80%2529.aspx)?^0|A|D|0|1W|1S|1|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]|$A|R|B|S|1|T]]|C|$]]|$1|D|3|-4|5|6|7|U|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]|L|$5|G|H|I|C|$J|M]]]]

A lot of this may be a <a href="https://stackoverflow.com/questions/861552/when-should-i-open-and-close-a-connection-to-sql-server">dup from here</a>; in short, keep connections short lived and local under most circumstances. Re the data reader; perhaps enable <a href="http://msdn.microsoft.com/en-us/library/cfa084cz(VS.80).aspx" rel="nofollow noreferrer">MARS</a>?

blocks|key|4871576|text|您的问题是您正在尝试使用SqlDataReader当前正在使用的连接。你不能这么做。当您使用SqlDataReader时，必须在重用正在使用的连接之前将其关闭。我建议您在每次访问数据库时创建一个不同的连接(任何时候，不仅仅是使用SqlDataReaders)。如果您启用了池，那么框架本身(或者它是SqlServer?)将在可能的情况下重用连接。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4871577|如果您需要按顺序访问数据库，假设您依次执行select、select和update，您可以重用连接(我的意思是，相同的SqlConnection实例)，但是如果您需要在从SqlDataReader读取数据时访问数据库，那么您将需要两个不同的连接。|4871578|当然，你必须考虑到并发问题。如果您使用事务，那么某些记录可能会在某些操作期间被锁定，并且当您将SqlDataReader与其他查询并行使用时，您应该将隔离级别设置为不会干扰其余查询的级别。|4871579|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

Your problem is that you are trying to use a connection that is currently being used by a SqlDataReader. You can't do that. When you use a SqlDataReader you must close it before reusing the connection that's being consumed. I suggest you to create a different connection every time you access the DB (anytime, not only with SqlDataReaders). If you have the pooling enabled, then the framework itself (or is it SqlServer?) will reuse the connections when possible.

If you need to access the DB sequentially, let's say you do a select, then another select, and then an update, you may reuse the connection (I mean, the same SqlConnection instance), but if you need to access the DB while reading from a SqlDataReader, then you'll need 2 different connections.

Of course you have to have in mind concurrency issues. If you use transactions then some records may be locked during certain operations, and when you use the SqlDataReader in parallel with other queries, you should set the isolation level to a level that doesn't mess with the rest of the queries.

blocks|key|1270000|text|虽然对于单个线程来说，使用单个SqlConnection对象可能会执行得更好，但它会通过挂起连接来占用宝贵的资源，即数据库服务器，从而占用数据库服务器资源，这比它需要的时间要长。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1270001|实例化SqlConnection的时间越短越好。连接池减少了设置连接的大部分开销，并确保最有效地使用数据库资源。|1270002|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

While for a single thread it may perform better to use a single SqlConnection object, it puts demands on the precious resource that is your database server by hanging onto a connection, and thus db server resources, longer than it needs to.

It is better to instantiate the SqlConnection for as short a period of time as possible. Connection pooling mitigates most of the expense of setting up the connection, and ensures the most efficient use of the database resources.

blocks|key|4026190|text|如果您使用的是SQL+Server2000，this可能会回答您的问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|4026191|获胜的答案似乎是：“这是因为MARs的默认设置发生了变化。它过去默认是开的，我们在RC1后将其默认改为关。所以只需更改连接字符串以将其添加回来(将MultipleActiveResultSets=True添加到连接字符串)。”|4026192|entityMap|0|LINK|mutability|MUTABLE|url|http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/78d3989a-8975-4930-998d-1eb907966f57^0|M|4|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]]|C|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|C|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]]]

If you're using SQL Server 2000, <a href="http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/78d3989a-8975-4930-998d-1eb907966f57" rel="nofollow noreferrer">this</a> may answer your question.

The winning answer there appears to be "This is due to a change in the default setting for MARs. It used to be on by default and we changed it to off by default post RC1. So just change your connection string to add it back (add MultipleActiveResultSets=True to connection string)."

Is it good practice to have one shared SqlConnection object in a .NET application to use for all of your database connections or should you have a separate one each time you access the database?

I currently have a shared one and seem to be running into issues all of a sudden. It seems like I can't use a shared one when I need to use SQL authentication mode instead of Windows authentication mode. I just tried for the first time using SQL authentication and it gave me this error when I tried using the same connection a second time:

There is already an open DataReader associated with this Command which must be closed first.

Shared SqlConnection

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云AI代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

功能1上新10个字符

功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符。

功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符。

功能5描述100个字符功能5描述100个字符功能5描述100个字符功能5描述100个字符功能5描述100个字符功能5描述100个字符

功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符

功能4上新

文章&问答评论现已支持表情

全新交互，全新视觉，新增快捷键、悬浮工具栏、高亮块等功能并同时优化现有功能，全面提升创作效率和体验

社区富文本编辑器全新改版！诚邀体验～ 

精选全网热门MCP server，让你的AI更好用 🚀

💥开发者 MCP广场重磅上线！

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

在.NET应用程序中有一个共享SqlConnection对象用于所有数据库连接是一种好的做法，还是应该在每次访问数据库时都有一个单独的对象？我目前有一个共享的，似乎突然遇到了问题。当我需要使用SQL身份验证模式而不是Windows身份验证模式时，似乎不能使用共享模式。我刚刚第一次尝试使用SQL身份验证，当我第二次尝试使用相同的连接时，它给出了这个错误：已有一个打开的DataReader与此命令关联

问共享SqlConnection
EN

回答 6

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问共享SqlConnectionEN

回答 6

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问共享SqlConnection
EN