Explanation:
Some Days ago a User at ASP .NET Saintcorp ask
a question a http://forums.asp.net/thread/1507419.aspx .
Here is the exact answer to that.
I thought it would be better to post the reply at Dotnet-Friends
so other Friends can aslo access it.
Here we will explain how to create a DataTable from Two different Tables of MS SQL
DataBase. We will generate our requierd result by menipulating the Datasources and then we
will caret a new Table.
Let us first see the DataBase given Tables. Our tables names are "Module" and "Reservation".
Here we are posting the Create Table queries . We are posting the complete queries so you can also practice with it.
CREATE TABLE [dbo].[MODULE](
[Id] [int] IDENTITY(1,1)
NOT NULL,
[Doc] [int] NULL,
[Module] [int] NULL,
[Hour] [nvarchar] (50)
COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[Day] [int] NULL,
CONSTRAINT [PK_MODULE]
PRIMARY KEY CLUSTERED
(
[Id] ASC
) WITH
(PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF)
ON [PRIMARY]
) ON
[PRIMARY]
Our Next Table;
CREATE TABLE [dbo].[RESERVATION](
[RId] [int] IDENTITY(1,1)
NOT NULL,
[Date] [nvarchar] (50)
COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[Hour] [nvarchar] (50)
COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Doc] [int] NULL,
CONSTRAINT [PK_RESERVATION]
PRIMARY
KEY CLUSTERED
(
[RId] ASC
) WITH
(PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF)
ON [PRIMARY]
) ON
[PRIMARY]
You noticed that our "Hour" and "Date" Fields are string typed that is just for the
sake of simplicity. Now see the Gridview in .aspx Page;
< asp:GridView ID="GridView1"
runat="server" AllowPaging="True" AutoGenerateColumns="true">
</asp:GridView>
you can see its just a simple gridview with AutoGenerateColumns="true". Now, Let us see how does "code
behind" look like;
Test tst = new Test();
GridView1.DataSource = tst.MakeDataTable(1, "25/12/2006");
GridView1.DataBind();
Here is the "Test" Class;
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public
classTest
{
private string
myConnStr=
ConfigurationManager
.ConnectionStrings["PersonalLocal"
].ConnectionString;
public DataTable MakeDataTable(int doc, string
thisDate)
{
using (SqlConnection connection =
new SqlConnection(myConnStr))
{
string Query =
"SELECT * FROM [MODULE] WHERE Doc=@Doc";
SqlCommand command =
new SqlCommand(Query, connection);
command.CommandType = CommandType.Text;
command.Parameters.Add( "@Doc",
SqlDbType.Int).Value = doc;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
DataTable dt =
new DataTable("Result"); // Here is DataTable which will be later
dt.Columns.Add( "Hour",
typeof(string)); // filled with its
Data
dt.Columns.Add( "Sate",
typeof(string));
while (reader.Read())
{
if (IsReserved(doc, reader["Hour"].ToString(), thisDate))
{
dt.Rows.Add(new
Object[] { reader["Hour"].ToString(), "Reserved" });
}
else
{
dt.Rows.Add( new
Object[] { reader["Hour"].ToString(), "Available"});
}
}
dt.AcceptChanges();
return dt;
}
}
public
bool IsReserved(int thisdoc, string hour, string Date)
{
string Query =
@"SELECT * FROM [RESERVATION] WHERE Doc=@Doc AND Date=@Date
AND [Hour]=@Hour";
using (SqlConnection connection = new SqlConnection(myConnStr))
{
SqlCommand command =
new SqlCommand(Query, connection);
command.CommandType = CommandType.Text;
command.Parameters.Add( "@Doc",
SqlDbType.Int).Value = thisdoc;
command.Parameters.Add( "@Date", SqlDbType.NVarChar).Value
= Date;
command.Parameters.Add( "@Hour", SqlDbType.NVarChar).Value
= hour;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
bool isthere = reader.HasRows;
return isthere;
}
}
}
Thats all. I hope it help. If there are still some questions or requierd an Explanation,
We will be happy to answer.
|