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文件中定义了连接字符串,如何从C#代码创建与SQL数据库的连接(抱歉忘记指定),然后调用存储过程。然后我想最终以某种方式使用这些数据作为GridView的DataSource。
Here is how the connection string is defined in the web.config:
以下是在web.config中定义连接字符串的方法:
<connectionStrings>
<add name="db.Name" connectionString="Data Source=db;Initial Catalog=dbCat;User ID=userId;Password=userPass;" providerName="System.Data.SqlClient" />
</connectionStrings>
The db server is a Microsoft SQL server.
数据库服务器是Microsoft SQL服务器。
Here is what I was looking for:
这是我在寻找的东西:
ConnectionStringSettings conSet = ConfigurationManager.ConnectionStrings["db.Name"];
SqlConnection con = new SqlConnection(conSet.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.
获取数据的代码相当简单。我更感兴趣的是从web.config文件中的connectionString变量访问它。
2 个解决方案
#1
6
If it's a resource file like so:
如果是这样的资源文件:
private static readonly string connString = Resource1.connString;
private static readonly string connString = Resource1.connString;
Where connString is the name of the key. If it is a web.config
file
其中connString是密钥的名称。如果是web.config文件
Something like so:
像这样的东西:
private static readonly string connString = System.Configuration.ConfigurationManager.AppSettings["strConn"];
where conn is defined in your web config file.
private static readonly string connString = System.Configuration.ConfigurationManager.AppSettings [“strConn”];其中conn在您的Web配置文件中定义。
<add key="strConn" value="User ID=test;Password=test;Initial Catalog=TestDB;Data Source=NameOfServer;"/>
Then call the sproc:
然后调用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
}
}
}
}
}
That's if you are coding in C#, VB.net its the same deal just a bit more wordier :), here's a small sample:
那就是如果你在C#中编码,那么VB.net的同一个交易只是更加冗长:),这是一个小样本:
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
Of course you should use a using
statement instead of try/catch/finally
to ensure you clean up your resources that are being used.
当然,您应该使用using语句而不是try / catch / finally来确保清理正在使用的资源。
#2
4
Something like this...
这样的东西......
using (var con = new SqlConnection(_connectionString))
{
using (var cmd = new SqlCommand(_storedProcedureName, con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@pMyParamater", myParamaterValue);
con.Open();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
// do something with the row
}
}
}
}
This is all pretty simple stuff to be honest, you should be able to find everything you need from the ADO.NET documentation
说实话,这一切都非常简单,您应该能够从ADO.NET文档中找到所需的一切
#1
6
If it's a resource file like so:
如果是这样的资源文件:
private static readonly string connString = Resource1.connString;
private static readonly string connString = Resource1.connString;
Where connString is the name of the key. If it is a web.config
file
其中connString是密钥的名称。如果是web.config文件
Something like so:
像这样的东西:
private static readonly string connString = System.Configuration.ConfigurationManager.AppSettings["strConn"];
where conn is defined in your web config file.
private static readonly string connString = System.Configuration.ConfigurationManager.AppSettings [“strConn”];其中conn在您的Web配置文件中定义。
<add key="strConn" value="User ID=test;Password=test;Initial Catalog=TestDB;Data Source=NameOfServer;"/>
Then call the sproc:
然后调用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
}
}
}
}
}
That's if you are coding in C#, VB.net its the same deal just a bit more wordier :), here's a small sample:
那就是如果你在C#中编码,那么VB.net的同一个交易只是更加冗长:),这是一个小样本:
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
Of course you should use a using
statement instead of try/catch/finally
to ensure you clean up your resources that are being used.
当然,您应该使用using语句而不是try / catch / finally来确保清理正在使用的资源。
#2
4
Something like this...
这样的东西......
using (var con = new SqlConnection(_connectionString))
{
using (var cmd = new SqlCommand(_storedProcedureName, con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@pMyParamater", myParamaterValue);
con.Open();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
// do something with the row
}
}
}
}
This is all pretty simple stuff to be honest, you should be able to find everything you need from the ADO.NET documentation
说实话,这一切都非常简单,您应该能够从ADO.NET文档中找到所需的一切