在.Net C#或VB.Net中与SQL Server的全局打开连接 [英] Global Open Connection to SQL Server in .Net C# or VB.Net

问题描述

在我的项目中，我需要从SQL Server提取数据，并将连接字符串放置在web.config中，以便可以对其进行修改和加密...但是现在我想使用

In my project i need my data to be fetched from SQL Server and i had placed my Connection string in web.config, so that it can be modified and encrypted... But now i want to open the sql connection using

SqlConnection conn = new SqlConnection(connectionstring);
conn.open;

当我打开此conn.open时，应该从我的所有Webform或Winforms中调用它，这样就不必一次又一次地编写conn.open了.我可以在想要的任何地方使用此conn，例如SqlCommand cmd = new SqlCommand("select * from xyz",conn);

到现在为止，我在项目中使用了单独的类，但是为此我也必须在每个页面中声明该类，并且我不想用作静态或共享变量...是否使conn成为共享或静态变量? />
指导我，还有其他方法可以执行此操作....

when i open this conn.open it should be called from all my webforms or winforms so that there is no need to write conn.open again and again. i can used this conn anywhere i want like SqlCommand cmd = new SqlCommand("select * from xyz",conn);

Upto now i used a seperate class in my project but for that too i have to declare the class in every page and i dont want to used as a static or shared variable... Is making conn as shared or static in good?

Guide me, is there any other way of doing this....

推荐答案

例如:
我有一个存在多个项目的解决方案.我有1个名为DataAccess的项目.

要创建SqlCommand，请使用:

as an example:
I have a solution that exists of several projects. I have 1 project called DataAccess.

To create the SqlCommand I use:

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Collections;

namespace DataAccess
{
    public class SQLCommands
    {
        public static String DefaultConnectionName()
        {
            return System.Web.Configuration.WebConfigurationManager.ConnectionStrings[0].Name;
        }
        public static SqlCommand OpenCommand(String StoredProcedure, List<sqlparameter> sqlParameters, String ConnectionName)
        {
            ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings[ConnectionName ?? DefaultConnectionName()];
            if (settings == null) throw new Exception("No connectionstring found");
            SqlCommand cmd = new SqlCommand(StoredProcedure, new SqlConnection(settings.ConnectionString));
            cmd.CommandType = CommandType.StoredProcedure;
            if (null != sqlParameters)
            {
                CheckParameters(sqlParameters);
                cmd.Parameters.AddRange(sqlParameters.ToArray());
            }
            cmd.CommandTimeout = 60; // 1 minute
            cmd.UpdatedRowSource = UpdateRowSource.None;
            cmd.Connection.Open();
            return cmd;
        }
        private static void CheckParameters(List<sqlparameter> sqlParameters)
        {
            foreach (SqlParameter parm in sqlParameters)
            {
                if (null == parm.Value)
                    parm.Value = DBNull.Value;
            }
        }
        public static void CloseCommand(SqlCommand sqlCommand)
        {
            if (null != sqlCommand && sqlCommand.Connection.State == ConnectionState.Open)
                sqlCommand.Connection.Close();
        }
    }
}
</sqlparameter></sqlparameter>

做ExecuteNonQuery我有另一个使用上面的类的类.

to do a ExecuteNonQuery I have another class that uses the above class.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
namespace DataAccess
{
    public class ToDataBase
    {
        /// <summary>
        /// Shoot the DATA
        /// </summary>
        /// <param name="StoredProcedure"></param>
        /// <param name="parms">List<sqlparameter></sqlparameter></param>
        /// <param name="ConnectionName"></param>
        public static Int32 InsertData(String StoredProcedure, List<sqlparameter> parms, String ConnectionName)
        {
            SqlCommand myCommand = null;
            Int32 affectedRows = 0;
            try
            {
                myCommand = SQLCommands.OpenCommand(StoredProcedure, parms, ConnectionName);
                affectedRows = myCommand.ExecuteNonQuery();
            }
            catch (Exception err)
            {
                // do something with the error
                string error = err.ToString();
            }
            finally
            {
                SQLCommands.CloseCommand(myCommand);
            }
            return affectedRows;
        }
    }
}
</sqlparameter>

希望这会给您带来解决方案的线索

I hope this will give you a lead to your solution

已阅读这些内容

有关连接方法的常规讨论 [ MSDN ADO.NET最佳实践 [

Have a read of these

General Discussion on connection practises[^]

MSDN ADO.NET Best Practises[^]

Solution 1 gives a good solution to your problem also, but the links should be a good background reading for you

这篇关于在.Net C#或VB.Net中与SQL Server的全局打开连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！