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.
|