调用带有asp.net存储过程

问题描述

如果我在我的web.config文件中定义的连接字符串，我怎么从创建到SQL数据库的连接 C＃ code（不好意思忘了指定），然后调用存储过程。那么我想以某种方式为我的数据源的GridView最终使用这些数据。

If I have a connection string defined in my web.config file, how do I create a connection to the SQL db from C# code (sorry forgot to specify) and then call a stored procedure. I would then like to eventually use this data in some way as my DataSource for a GridView.

下面是连接字符串是如何在web.config中定义的：

Here is how the connection string is defined in the web.config:

<connectionStrings>
 <add name="db.Name" connectionString="Data Source=db;Initial Catalog=dbCat;User ID=userId;Password=userPass;" providerName="System.Data.SqlClient" />
 </connectionStrings>

数据库服务器是一个Microsoft SQL Server。

The db server is a Microsoft SQL server.

下面就是我一直在寻找：

Here is what I was looking for:

ConnectionStringSettings conSet = ConfigurationManager.ConnectionStrings["db.Name"];
SqlConnection con = new SqlConnection(conSet.ConnectionString);

在code到得到的数据是相当琐碎。我更感兴趣的是在web.config文件中从变量的connectionString访问它。

The code to get the data is fairly trivial. I was more interested in accessing it from a connectionString variable in the web.config file.

推荐答案

如果它像这样一个资源文件：

If it's a resource file like so:

私有静态只读字符串CONNSTRING = Resource1.connString;

在哪里CONNSTRING是关键的名称。如果是的web.config 文件

Where connString is the name of the key. If it is a web.config file

东西像这样：

私有静态只读字符串CONNSTRING = System.Configuration.ConfigurationManager.AppSettings [创建失败]; 其中conn是在Web配置文件中定义

private static readonly string connString = System.Configuration.ConfigurationManager.AppSettings["strConn"]; where conn is defined in your web config file.

<add key="strConn" value="User ID=test;Password=test;Initial Catalog=TestDB;Data Source=NameOfServer;"/>

然后调用存储过程的：

Then call the sproc:

  //connString = the string of our database app found in the resource file
                using (SqlConnection con = new SqlConnection(connString))
                {
                    using (SqlCommand cmd = new SqlCommand("EMPDLL_selClientByClientID", con))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@ClientID", SqlDbType.VarChar).Value = cID;
                        con.Open();

                        using (SqlDataReader reader = cmd.ExecuteReader())
                        {
                            if (reader.HasRows)
                            {
                                if (reader.Read())
                                {
                                       //more code
                                }
                             }
                        }
                     }
                  }

这就是如果你在C＃编码，VB.net它同样处理只是有点更wordier :)，这里是一个小样本：

That's if you are coding in C#, VB.net its the same deal just a bit more wordier :), here's a small sample:

 Public Sub DeleteEmployee(ByVal lVID As Long)
        Dim conMyData As SqlConnection
        Dim cmdDelete As SqlCommand

        Try
            conMyData = New SqlConnection(connString)
            cmdDelete = New SqlCommand("delEmployee", conMyData)

            With cmdDelete
                .CommandType = CommandType.StoredProcedure
                'add the parameters
                .Parameters.Add("@LoginID", SqlDbType.BigInt).Value = lVID    'the request
                conMyData.Open()    'open a connection
                .ExecuteNonQuery()  'execute it
            End With

        Catch ex As Exception
            Throw ex
        Finally
            cmdDelete = Nothing
            conMyData.Close()
            conMyData = Nothing
        End Try
    End Sub

当然，你应该使用使用语句而不是的try / catch /终于，以确保您清理正在使用的资源。

Of course you should use a using statement instead of try/catch/finally to ensure you clean up your resources that are being used.

这篇关于调用带有asp.net存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！