Can anyone help me out please? I'm confused.
I want to set up my connection string so I can just call it from my Web.Config file.
I need a way to call it from my code, please make a little example. :(
I also need help on setting up the Web.Config file.
I don't know what properties to use. Here's a screenshot of what my credentials are. I have no password set up for Windows. I'm really lost here.
This question is related to
asp.net
sql
sql-server
sql-server-2008
web-config
The answer is
Here's a great overview on MSDN that covers how to do this.
In your web.config, add a connection string entry:
<connectionStrings>
<add
name="MyConnectionString"
connectionString="Data Source=sergio-desktop\sqlexpress;Initial
Catalog=MyDatabase;User ID=userName;Password=password"
providerName="System.Data.SqlClient"
/>
</connectionStrings>
Let's break down the component parts here:
Data Source is your server. In your case, a named SQL instance on sergio-desktop.
Initial Catalog is the default database queries should be executed against. For normal uses, this will be the database name.
For the authentication, we have a few options.
User ID and Password means using SQL credentials, not Windows, but still very simple - just go into your Security section of your SQL Server and create a new Login. Give it a username and password, and give it rights to your database. All the basic dialogs are very self-explanatory.
You can also use integrated security, which means your .NET application will try to connect to SQL using the credentials of the worker process. Check here for more info on that.
Finally, in code, you can get to your connection string by using:
ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
public partial class form_city : System.Web.UI.Page
{
connection con = new connection();
DataTable dtable;
string status = "";
protected void Page_Load(object sender, EventArgs e)
{
TextBoxWatermarkExtender1.WatermarkText = "Enter State Name !";
if (!IsPostBack)
{
status = "Active";
fillgrid();
Session.Add("ope", "Listing");
}
}
protected void fillgrid()
{
//Session.Add("ope", "Listing");
string query = "select *";
query += "from State_Detail where Status='" + status + "'";
dtable = con.sqlSelect(query);
grdList.DataSource = dtable;
grdList.DataBind();
lbtnBack.Visible = false;
}
protected void grdList_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
grdList.PageIndex = e.NewPageIndex;
string operation = Session["ope"].ToString();
if (operation == "ViewLog")
status = "Inactive";
else if (operation == "Listing")
status = "Active";
fillgrid();
}
public string GetImage(string status)
{
if (status == "Active")
return "~/images/green_acti.png";
else
return "~/images/red_acti.png";
}
protected void grdList_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
string st = "Inactive";
int State_Id = Convert.ToInt32(grdList.DataKeys[e.RowIndex].Values[0]);
string query = "update State_Detail set Status='" + st + "'";
query += " where State_Id=" + State_Id;
con.sqlInsUpdDel(query);
status = "Active";
fillgrid();
}
protected void grdList_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName.Equals("Select"))
{
string query = "select * ";
query += "from State_Detail where State_Id=" + e.CommandArgument;
dtable = con.sqlSelect(query);
grdList.DataSource = dtable;
grdList.DataBind();
lbtnBack.Visible = true;
}
}
protected void ibtnSearch_Click(object sender, ImageClickEventArgs e)
{
Session.Add("ope", "Listing");
if (txtDepId.Text != "")
{
string query = "select * from State_Detail where State_Name like '" + txtDepId.Text + "%'";
dtable = con.sqlSelect(query);
grdList.DataSource = dtable;
grdList.DataBind();
txtDepId.Text = "";
}
}
protected void grdList_RowEditing(object sender, GridViewEditEventArgs e)
{
int State_Id = Convert.ToInt32(grdList.DataKeys[e.NewEditIndex].Values[0]);
Session.Add("ope", "Edit");
Session.Add("State_Id", State_Id);
Response.Redirect("form_state.aspx");
}
protected void grdList_Sorting(object sender, GridViewSortEventArgs e)
{
string operation = Session["ope"].ToString();
if (operation == "ViewLog")
status = "Inactive";
else if (operation == "Listing")
status = "Active";
string query = "select * from State_Detail";
query += " where Status='" + status + "'";
dtable = con.sqlSelect(query);
DataView dview = new DataView(dtable);
dview.Sort = e.SortExpression + " asc";
grdList.DataSource = dview;
grdList.DataBind();
}
}
<asp:Image ID="imgGreenAct" ImageUrl='<%# GetImage(Convert.ToString(DataBinder.Eval(Container.DataItem, "Status")))%>' AlternateText='<%# Bind("Status") %>' runat="server" />
Add this to your web config and change the catalog name which is your database name:
<connectionStrings>
<add name="MyConnectionString" connectionString="Data Source=SERGIO-DESKTOP\SQLEXPRESS;Initial Catalog=YourDatabaseName;Integrated Security=True;"/></connectionStrings>
Reference System.Configuration assembly in your project.
Here is how you retrieve connection string from the config file:
System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
http://www.connectionstrings.com is a site where you can find a lot of connection strings. All that you need to do is copy-paste and modify it to suit your needs. It is sure to have all the connection strings for all of your needs.
If you use the Connect to Database under tools in Visual Studio, you will be able to add the name of the Server and database and test the connection. Upon success you can copy the string from the bottom of the dialog.
Your best bet, starting fresh like you are, is to go grab the enterprise library. They have a configuration tool you can use to wire everything up for you nicely.
They also have a data access application block which is very useful and documentation filled with good samples.
Web.config file
<connectionStrings>
<add name="MyConnectionString" connectionString="Data Source=SERGIO-DESKTOP\SQLEXPRESS; Initial Catalog=YourDatabaseName;Integrated Security=True;"/>
</connectionStrings>
.cs file
System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
If you are using SQL Express (which you are), then your login credentials are .\SQLEXPRESS
Here is the connectionString in the web config file which you can add:
<connectionStrings>
<add connectionString="Server=localhost\SQLEXPRESS;Database=yourDBName;Initial Catalog= yourDBName;Integrated Security=true" name="nametoCallBy" providerName="System.Data.SqlClient"/>
</connectionStrings>
Place is just above the system.web tag.
Then you can call it by:
connString = ConfigurationManager.ConnectionStrings["nametoCallBy"].ConnectionString;
Source: Stackoverflow.com