Monday, December 6, 2010

Query Tool with Asp .Net



using System.Data;

using System.Data.SqlClient;

using System.Configuration;


public partial class _Default : System.Web.UI.Page

{

SqlConnection cnn, cnn2;

SqlCommand cmd;

SqlDataReader dr;


protected void Page_Load(object sender, EventArgs e)

{

cnn = new SqlConnection("Integrated Security=sspi;Initial Catalog=master");

cnn.Open();

cmd = new SqlCommand("sp_helpDb", cnn);

dr = cmd.ExecuteReader();

TreeNode tn;

if (!IsPostBack)

{

while (dr.Read())

{

ddlDb.Items.Add(dr["name"].ToString());

tn = new TreeNode(dr["name"].ToString());

TreeView1.Nodes[0].ChildNodes.Add(tn);

}

dr.Close();

}

}


protected void btnExecute_Click(object sender, EventArgs e)

{

cnn2 = new SqlConnection(@"integrated Security=sspi;Initial Catalog=" + Session["Db"]);

cnn2.Open();


cmd = new SqlCommand(txtQuery.Text, cnn2);

dr = cmd.ExecuteReader();

GridView1.DataSource = dr;

DataBind();

dr.Close();

}


protected void ddlDb_SelectedIndexChanged(object sender, EventArgs e)

{

Session["Db"] = ddlDb.Text;

}

}




<%@ 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></title>
<style type="text/css">
.style1
{
width: 100%;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>

<table class="style1">
<tr>
<td colspan="2">
<br />
Select Database
<asp:DropDownList ID="ddlDb" runat="server" AutoPostBack="True" Height="21px"
onselectedindexchanged="ddlDb_SelectedIndexChanged" Width="122px">
</asp:DropDownList>
&nbsp;&nbsp;&nbsp;
<asp:Button ID="btnExecute" runat="server" onclick="btnExecute_Click"
Text="Execute" />
<br />
</td>
</tr>
<tr>
<td>
<asp:TreeView ID="TreeView1" runat="server">
<Nodes>
<asp:TreeNode Text="Database" Value="Database"></asp:TreeNode>
</Nodes>
</asp:TreeView>
</td>
<td>
<table class="style1">
<tr>
<td>
<asp:TextBox ID="txtQuery" runat="server" Height="87px" TextMode="MultiLine"
Width="370px"></asp:TextBox>
</td>
</tr>
<tr>
<td>
<asp:TextBox ID="txtResult" runat="server" Height="16px" TextMode="MultiLine"
Visible="False" Width="370px"></asp:TextBox>
<br />
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
ASP.NET 4 Unleashed</td>
</tr>
</table>
</td>
</tr>
</table>

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

Sunday, December 5, 2010

Entity Framework and Stored Procedures

Entity Framework also allows you to work with Stored Procedures.
I have created a stored procedure for extracting the Contact information based on GroupId.

create procedure spGetContactsByGroup (@pGroupId int)
Entity Framework 4.0 Recipes: A Problem-Solution Approach (Expert's Voice in .NET)as
select * from Contacts where GroupId = @pGroupId
go

spGetContactsByGroup 2

While running the Entity Data Model Wizard I am selecting the Stored Procedure spGetContactsByName.






















But when you look at ContactManagementModel in the Model Browser you won't see any indication of stored procedure.  For that you have to explicitly create a complex type.



















Navigate to the ContactManagementModel.Store and then to Stored Procedures section there you will find spGetContactByGroup stored procedure. Right click the stored procedure and select Add Function Import.

























In the Add Function Import dialog select Select Complex type from Return a Collection of section. Click on Get Column Information Button and then Create Complex Type Button.



Now you can see that the stored procedure is appearing as a Complex Type within the ContactManagementModel.


Now you can use the stored procedure in code.

using ContactManagementModel;public partial class _Default : System.Web.UI.Page
{ ContactManagementEntities context = new ContactManagementEntities();
protected void Page_Load(object sender, EventArgs e)

{
if (!IsPostBack)
{

ddlGroups.DataSource = context.ContactGroups.ToList();
ddlGroups.DataTextField = "GroupName";

    ddlGroups.DataValueField = "GroupId";
DataBind();
}
}
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)

{ int intGroupId = Int32.Parse(ddlGroups.SelectedValue.ToString());

var Contact = from p in context.spGetContactsByGroup(intGroupId)
select new { p.ContactName, p.Email };

GridView1.DataSource = Contact.ToList();
DataBind();

}



Top

Saturday, December 4, 2010

Entity Framework Model First

With Entity Framework it is possible to develop a model first and then derive at a database.

In the Entity Data Mode Wizard select Empty Model.





This action will instantiate the Entity Model Designer with its associated toolbox which contains options for creating Entity, Association and Inheritance.

For creating an Entity double click on Entity from the toolbox.

We are in the process of creating a many to many relationship. I am going to take Marriage Hobbyist as the sample. Here I am going to track the marriages of Marriage Hobbyists like Elizabeth Taylor, Richard Burton, Tom Cruise, Sasi Taroor, etc. I am creating two Entities Gentleman and Lady. The association here is Many to Many.  Here a Entity called Elizabeth Taylor have multiple marriages with Richard Burton, Conrad Hilton, etc. And Richard Burton in turn have multiple marriages with Elizabeth Taylor, etc.






















..




















..




















By default the association is One to Many Instead I am selecting *(Many) from the Multiplicity attribute of Gentleman Entity.




















In effect a  Many to Many Association between Gentleman and Lady as shown in the diagram.
























You are expected to create a database as shown below. Once you have done that you can select Generate Database from Model by right clicking on the Model Designer surface.




















..




















You can see the database Script generated for you.




















Here is the output of running the script and opening the table in Database Diagram. Here you can see Gentleman, Lady as well as the junction table GentlemanLady.






















Now you can use the Asp .Net Dynamic Data Project for easily creating a Project for MarriageHobbyists.

Asp .Net Dynamic Data




Asp .Net Dynamic Data is a technology used for dynamically generating CRUD forms. It is based on either Entity Framework or Linq to Sql.

Here I am demonstrating Ado .Net Dynamic Data with Entity Framework.

Start a New Web Site in Asp .Net and select Asp .Net Dynamic Entities Web Site.




Add and Ado .Net Entity Data Model as described in the beginning of the blog.



Open Global.asax and navigate to RegisterRoutes event. Uncomment the DefaultModel.RegisterContext and inside the typeof type the name of the Ado .Net Entities you have created and set ScaffoldAllTables attribute to true as shown below.







DefaultModel.RegisterContext(typeof(ContactManagementModel.ContactManagementEntities),
new ContextConfiguration() { ScaffoldAllTables = true });



Wednesday, December 1, 2010

User Group Meeting - 11th Dec 2010 - Kochi

Venue: Mastermind Computer Institute, 2nd Floor,
New Kochappilly Building,
Professional WCF 4: Windows Communication Foundation with .NET 4 (Wrox Programmer to Programmer)Near Federal Bank,
Civil Line Road,
Chembumukku,Cochin-682030

Agenda                                                                           

09:30 - 09:40 Community updates
09:40 - 10:30 Introduction to WCF
10:30 - 11:15 Lambda - It's uses and abuses
11:15 - 11:30 Tea Break (15 min)
11:30 - 12:15 Introducing Razor – A new view engine for ASP.NET
12.15 - 01:00 Memory and Resource Leaks in .NET application

Click Here to Register

Further enquiries : 9745800512