Thursday 12.8.11

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class DataRelations : System.Web.UI.Page
{
private readonly string connectionString = @"Data Source=Alfred-PC\SQLExpress;" + "Initial Catalog=AdventureWorksLT2008R2; Integrated Security=SSPI";

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
DataSet ds = CreateOrderDataSet();
OrderGridView.DataSource = ds.Tables["Orders"];
OrderGridView.DataBind();

UpdateDetailsGrid();
}
}

protected void OrderGridView_SelectedIndexChanged(object sender, EventArgs e)
{
UpdateDetailsGrid();
}

private DataSet CreateOrderDataSet()
{
//create a database connection
SqlConnection connection = new SqlConnection(connectionString);

//create a DataAdapter for the SalesORderHeader GridView
SqlDataAdapter OrdersAdapter = CreateAdapterForOrders(connection);

//create the dataset and use the data adapter to fill it
DataSet dataSet = new DataSet();

try
{
connection.Open();
OrdersAdapter.Fill(dataSet);
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
return dataSet;
}

private void UpdateDetailsGrid()
{
int index = OrderGridView.SelectedIndex;
if (index != -1)
{
//get the order id from the data grid
DataKey key = OrderGridView.DataKeys[index];
int orderID = (int)key.Value;

//DataSet ds = CreateOrderDetailsDataSet(orderID);

OrderDetailsGridView.DataSource = ds;
OrderDetailsGridView.DataBind();
OrderDetailsPanel.Visible = true;
}
else
{
OrderDetailsPanel.Visible = false;
}
}
//the SqlCommand's CommandText property is set to the name fo the stored procedure

private SqlDataAdapter CreateAdapterForOrders(SqlConnection connection)
{
//set the command to use the spOrders sproc
SqlCommand cmd = new SqlCommand("spOrders", connection);
cmd.CommandType = CommandType.StoredProcedure;

//set the adapter to use sproc as command
SqlDataAdapter OrdersAdapter = new SqlDataAdapter(cmd);
OrdersAdapter.TableMappings.Add("Table", "Orders");
return OrdersAdapter;
}

private SqlDataAdapter CreateAdapterForOrderDetails(SqlConnection connection, int orderId)
{
//set the command to use the spOrderDetails sproc and parameter
SqlCommand cmd = new SqlCommand("spOrderDetails", connection);
cmd.CommandType = CommandType.StoredProcedure;

SqlParameter orderIdParameter = cmd.Parameters.AddWithValue("@OrderId", orderId);
orderIdParameter.Direction = ParameterDirection.Input;
orderIdParameter.DbType = DbType.Int32;

//set adapter to use sproc as command
SqlDataAdapter OrderDetailsAdapter = new SqlDataAdapter(cmd);
OrderDetailsAdapter.TableMappings.Add("Table", "OrderDetails");
return OrderDetailsAdapter;

}
}

---

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class UpdatingDBDirectly : System.Web.UI.Page
{

private readonly string connectionString = @"Data Source=Alfred-PC\SQLExpress;" + "Initial Catalog=AdventureWorksLT2008R2; Integrated Security=True;";

protected void Page_Load(object sender, EventArgs e)
{
PopulateCategoryList();
PopulateGrid();
}

private void PopulateCategoryList()
{
//create connection to AdventureWOrksLT
SqlConnection connection = new SqlConnection(connectionString);

//create SqlCommand
StringBuilder cmdString = new StringBuilder();
cmdString.Append("SELECT DISTINCT ProductCategoryID, Name ");
cmdString.Append("FROM SalesLT.ProductCategory ");
cmdString.Append("WHERE (ParentProductCategoryID IS NULL) ");
cmdString.Append("ORDER BY ProductCategoryID");
SqlCommand cmd = new SqlCommand(cmdString.ToString(), connection);

try
{
connection.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

while (dr.Read())
{
ddlParentCategory.Items.Add(new ListItem(dr["Name"].ToString(), dr["ProductCategoryID"].ToString()));
}

dr.Close();
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}

private void PopulateGrid()
{
//create connection to Advneture works LT
SqlConnection connection = new SqlConnection(connectionString);

//create SqlCommand string
StringBuilder cmdString = new StringBuilder();
cmdString.Append("SELECT child.ProductCategoryId, ");
cmdString.Append("Child.Name AS 'Category', ");
cmdString.Append("child.ParentProductCategoryID, ");
cmdString.Append("parent.Name As 'ParentCategory' ");
cmdString.Append("FROM SalesLT.ProductCategory AS child ");
cmdString.Append("INNER JOIN SalesLT.ProductCategory AS parent ON ");
cmdString.Append("child.ParentProductCategoryID = parent.ProductCategoryID");

SqlCommand cmd = new SqlCommand(cmdString.ToString(), connection);

try
{
connection.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
CategoryGridView.DataSource = dr;
CategoryGridView.DataBind();

dr.Close();
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}

//gridviews selectedindexchanged event handlr
//uses gridviews cells collection for the selected row to discover teh values to use.
protected void CategoryGridView_SelectedIndexChanged(object sender, EventArgs e)
{
int selectedIndex = CategoryGridView.SelectedIndex;

if (selectedIndex != -1)
{
TableCellCollection selectedValues = CategoryGridView.Rows[selectedIndex].Cells;

//have to know the order of these cells in the grid
hdnCategoryID.Value = selectedValues[1].Text;
txtName.Text = selectedValues[2].Text;
ddlParentCategory.SelectedValue = selectedValues[3].Text;
}
}

protected void btnAdd_Click(object sender, EventArgs e)
{
StringBuilder insertCommand = new StringBuilder();
insertCommand.Append("insert int SalesLT.ProductCategory ");
insertCommand.Append("([ParentProductCategoryID], [Name]) ");
insertCommand.AppendFormat("values ('{0}', '{1}')", ddlParentCategory.SelectedValue, txtName.Text);
UpdateDB(insertCommand.ToString());
PopulateGrid();
}

protected void btnEdit_Click(object sender, EventArgs e)
{
StringBuilder updateCommand = new StringBuilder("Update SalesLT.ProductCategory SET ");
updateCommand.AppendFormat("Name='{0}', ", txtName.Text);
updateCommand.AppendFormat("ParentProductCategoryID='{1}' ", ddlParentCategory.SelectedValue);
updateCommand.AppendFormat("where ProductCategoryID='{2}'", hdnCategoryID.Value);
UpdateDB(updateCommand.ToString());
PopulateGrid();
}

protected void btnDelete_Click(object sender, EventArgs e)
{
string deleteCommand = string.Format("delete from SalesLT.ProductCategory where ProductCategoryID = '{0}'", hdnCategoryID.Value);
UpdateDB(deleteCommand);
PopulateGrid();
}

private void UpdateDB(string cmdString)
{
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand(cmdString, connection);
try
{
connection.Open();
command.ExecuteNonQuery();
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
}