KnowHow of SqlDataSource with co

Know-How of SqlDataSource with code

Step : 1 Open Visual Studio 2008, and make sure that Solution explorer and Server explorer are visible as shown below. ( Below is done with Professional Visual Studio 2008), later recreated with Visual Studio Express 2008 ( http://manas6/aspnet.35/mm.SQL1B/ )

As you see you will have a series of database, some are configured as dabase.dbo (database owner, you need some permissions from the server before you can use those) and mdf1 indicates that you created a database Visitior.mdf with VS-2008 in the recently and you can use it via it's representation Visitior.mdf1.

Now Look at another IDE, where I created a test database, so answer is clear, if you see "mdf" know that you are using the original database; mdf1, you are using a representation and depending upon your database security, the main database can be changed by the users after handling "a conflict if any"

Now create a new website

Under App_Data we are going to add a database

Now look at the server explorer

Let us take pause from Visual Studio and open SQL Server Express

Select a path, at this point I am selecting SQLDemo1.mdf

Now let us give a quick demo, showing how to create a table with query, if know query fundamentals, it is as easy as typing letters not worrying about grammars

CREATE TABLE demo1
(id int NOT NULL,
FirstName VARCHAR(50) not null)

DROP TABLE demo1;
CREATE TABLE demo1
(id int NOT NULL,
FirstName VARCHAR(50) not null);
INSERT INTO demo1
(id, FirstName) VALUES (1, 'Peter');
INSERT INTO demo1
(id, FirstName) VALUES (2, 'Brian');
INSERT INTO demo1
(id, FirstName) VALUES (3, 'So On');

Saved this query as

Even you could have used visual guide from the table ( I am returning to SQL Server after a long time (SQL 7.0) , I was wondering Go or use ";" breaker.

USE [SQLDEMO1.MDF]
GO
/****** Object: Table [dbo].[demo1] Script Date: 11/06/2008 12:38:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[demo1](
[id] [int] NOT NULL,
[FirstName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Back On Track, when we are back to our Project, you will notice that the database updated by adding table and values to the coulmns.

Add two SqlDataSource Controls

Configure data source

The database, options will include your current database "SQLDemo1"

The connection string will look like this, the string shows that you are connected to the database and its physical location, where you used integrated security (most like windows authentication")

Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\SQLDemo1.mdf;Integrated Security=True;User Instance=True

Now the above connection string, will allow to reach the table where "DataSource" will hold the information about the data you would be using with "Bound Controls provided by Visual Studio". At the next screen, you may rename it that suits us the most.

Click Next on the above to select the table, and also choose the order by

SELECT * FROM [demo1] ORDER BY [id], [FirstName]

Click Next on the screen shown below.

And you confirm that your data source is good.

What is did and where this string is stored? In Web.config file.

<connectionStrings>
<add name="SQLDemo1" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\SQLDemo1.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>

Subsequent Connection and data sources

Here what choices you have

Use the same database connection or new a new connection

Option : Same Connection to Database

you are changing your slections.

What if you want different connection string: Usually I chose to do so in my first time approach, and I did not apprehend the complete privileges (Comparing MySQl or Oracle) , since I was not shown any choices to access my data as Read or Editable mode. Note the database is empty and connection is still good, as declared in Web.config file.

If you try to do this, the database in not accessible.

Where is Initial Catalog? it is with your local SqlDatasource1/

Data Source=MANAS6\SQLEXPRESS;Integrated Security=True

Sample Code:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>mm.SQL1 : Using SqlDataSource control</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:PlayerConnectionString %>"
SelectCommand="SELECT * FROM [PlayerInfo]"></asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="ID" HeaderText="ID" SortExpression="ID" />
<asp:BoundField DataField="FirstName" HeaderText="FirstName"
SortExpression="FirstName" />
<asp:BoundField DataField="LastName" HeaderText="LastName"
SortExpression="LastName" />
<asp:BoundField DataField="Position" HeaderText="Position"
SortExpression="Position" />
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>

Connection string in Web.config ( for SQL Server Express 2008)

<connectionStrings>
<add name="PlayerConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Players1B.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>