SQL Database and Visual Studio I

SQL Database and Visual Studio I2008 DE

A basic Know How.

To know how to create database using DataConntection from Server Explorer or to create a Data connection, visit this LINK

Step: 1. To begin let us open Visual IDE . Below, I am showing with an option I chose, not showing any start page that appears as default.

If you choose different be my guest and pick that suits you most.

Step: 2 To begin with Data Base SqlServer 2005 Express, a free source Data Base, Select Server Explorer. Before writing this document, I created Players and NewPro1 Databases, and you are viewing those below the root level of database connection. Question is Why and What dbo or mdb1?

dbo : data base object or data base owner ? = Data Base Owner ! sorry object oriented guys.

No complain? If you are using Server Help, it is huge, good and bad; good for whom who knows the territory meaning not useful to open to all, other meaning you buy the software and a person from the provider, no matter how good you are. The facts are hidden or not easily viewable.

It is bad for whom, expert or no expert, coming from MySQL or Oracle, C++, C# or Java programmers; you look at the documents as a GPS; will crack your patience. It would be much easier to guess my Pet's name, than where to tap in. Some lollipop steps are good enough to learn click buttons, worth of trying those, but do you have to wait months to get correct information, when your hours are purchased by your clients?

First My secretes are out

Here is the view from

where you need to add this users right and wrongs, therefore Visual Studio 2008 is half Filled Glass, consolations to Visual Web Express developers,

But any way this test db needs more manipulation before it can be accessed from VS-2008. So be a "Data Base Owner First" or rent a file.

Step: Now let us create a database and see it is accepted by SQL Server

Step: 1. Now create a website ( http://manas6/aspnet.35/mm.SQLTest/ )

Step: Create database from Solution Explorer, Add new Item will allow to pick SQL Server Database, and name this database as TestSql

Save Table

Since you have no content, you may get this message that no rows affected.

If you refresh the table, you will note that "Table-1 is added " , YOU MAY DELETE TABLE1 using left pane of
SELECT *
FROM tab1 LEFT OUTER JOIN tab2 ON tab1.c3 = tab2.c3
    RIGHT OUTER JOIN tab3 LEFT OUTER JOIN tab4
        ON tab3.c1 = tab4.c1
        ON tab2.c3 = tab4.c3
Yes will delete the table from the database. You are dbo of Table1, so be it.

Now add data in table Memberinfo, we will talk about Normalization in brief,

Primary Keys are not copied into the other table

Step: Connection Exposes the table to Visual Studio IDE, if you look at the Server Management Console, your database is not not included in the list..

On this screen Pick the database

Test Query

Step: Now let us look, the visible efffects of the above connection configuration changes

web.config

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

Step:

Step: Add another datasource

Now if you configure you will not see the mdf file, rather will see connection string to the server, that connects to mdf file.

Note : Now Read carefully, if you choose to go with New Connection (as shown below), you will be allowed to connect with the databases that you have created with SSMS (SQL Server Management Studio), make sense that at smaller scale one "connection string " will direct IDE to fetch a database, then some web control will be engaged to retrieve values from the tables in the database. I got a feeling that SQL Express will allow one connection string from the one database created from Visual Studio 2008.

Therefore, Cancel out from the above phase and be back to the screen showing below, we are going the use the same connection string to the database and pick different table.

Click to Finish and get back to Visual Studio 2008.

Step: Let us look at the two SqlDataSources

Note the codes

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:TestSqlConnectionString %>"
SelectCommand="SELECT [ID] FROM [MemberID]"></asp:SqlDataSource>

<asp:SqlDataSource ID="SqlDataSource2" runat="server"
ConnectionString="<%$ ConnectionStrings:TestSqlConnectionString %>"
SelectCommand="SELECT * FROM [MemberInfo]"></asp:SqlDataSource>

Step: Now add two controls, one DropDwon List and one DetailsView as shown below

Step: Configuring DropdownList

Click OK

The code snippet of DropDownList control, AutoPostBack="True" must set in order to get the control to be acting as a source to other controls like DetailView.

<asp:DropDownList ID="DropDownList1" runat="server" Height="24px" Width="177px"
DataSourceID="SqlDataSource1" DataTextField="ID" DataValueField="ID" AutoPostBack="True">
</asp:DropDownList>

Step: Now let us configure Details View

It will show all the columns and you need to configure the datasource, meaning how you like to present you data, as this control allows table format

Let us tune up this control to view as details to the dropdown control, click on Configure DataSource,

Final Tune of the code.

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:TestSqlConnectionString %>"
SelectCommand="SELECT * FROM [MemberID]"></asp:SqlDataSource>

<asp:SqlDataSource ID="SqlDataSource2" runat="server"
ConnectionString="<%$ ConnectionStrings:TestSqlConnectionString %>"
SelectCommand="SELECT * FROM [MemberInfo] WHERE ([ID] = @ID)">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="ID"
PropertyName="SelectedValue" Type="String" />
</SelectParameters>
</asp:SqlDataSource>

Step: Now adjust the connection SqlDataSource2 to "DataSet" that will hold the data like a table.

Click OK on the above screen and Next (below). Selection parameter generated is shown below

SELECT * FROM [MemberInfo] WHERE ([ID] = @ID)

If you click on Test Query, it will show the format.

Step: Run time

Step: Finale: The complete 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.SQLTest : Connection String All About</title>
<style type="text/css"">
#div1
{
position:absolute; top:100px; width: 600px; height:300px; background-color:#FFFFCC;left:20px;
}
form
{
background-color: Gray;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:TestSqlConnectionString %>"
SelectCommand="SELECT * FROM [MemberID]"></asp:SqlDataSource>

<asp:SqlDataSource ID="SqlDataSource2" runat="server"
ConnectionString="<%$ ConnectionStrings:TestSqlConnectionString %>"
SelectCommand="SELECT * FROM [MemberInfo] WHERE ([ID] = @ID)">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="ID"
PropertyName="SelectedValue" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
</div>
<div id="div1">
<asp:DropDownList ID="DropDownList1" runat="server" Height="24px" Width="177px"
DataSourceID="SqlDataSource1" DataTextField="ID" DataValueField="ID" AutoPostBack="True">
</asp:DropDownList>

<asp:DetailsView ID="DetailsView1" runat="server"
AutoGenerateRows="False" DataKeyNames="ID" DataSourceID="SqlDataSource2"
Height="25px" Width="288px">
<Fields>
<asp:BoundField DataField="ID" HeaderText="ID" ReadOnly="True"
SortExpression="ID" />
<asp:BoundField DataField="FirstName" HeaderText="FirstName"
SortExpression="FirstName" />
<asp:BoundField DataField="LastName" HeaderText="LastName"
SortExpression="LastName" />
<asp:BoundField DataField="Citizen" HeaderText="Citizen"
SortExpression="Citizen" />
<asp:BoundField DataField="Notes" HeaderText="Notes" SortExpression="Notes" />
</Fields>
</asp:DetailsView>

</div>
</form>
</body>
</html>

Step: Code in Web.config to configure the connection string

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