jump to navigation

Create an Asp.Net web user control with C# October 28, 2009

Posted by fofo in asp.net, C#, Visual Studio 2005, Visual Studio 2008.
Tags: , ,
4 comments

User controls are reusable controls that can be defined once and used whenever we need them, in any of the .aspx pages of our application. We do have skins,themes and css to give a standard look to our website. User controls help to achieve that as well because since we define them once and use many times, thus making sure we do not have same controls on pages looking differently. When we make changes to a user control all these changes will be reflected to all instances of the control.

I will try to highlight the following in this post

  1. What is a user control and why we need it
  2. How to add Content to a User Control
  3. How to add a User Control to a Web Page
  4. How to define properties in a User Control
  5. How to handle events in a User Control
  6. How to raise events in a User Control
  7. How to access the contents of a User Control

We will have to create a simple user control step by step.Imagine you have many pages in your asp.net website. Let’s say that you are developing a fully functional e-commerce site. You will find that you need to collect user data, e.g address information (shipping address info,billing address info,registration customer address info). In this example we will use a user control to collect customer address data.

We just need Visual Studio 2005/2008 or VS 2010.

1) Launch Visual Studio and create a new asp.net website

2)  Save this website in you local file system and give it a name. Choose C# as your development language for this website

3)  Add new folder to your website and name it UserControl

4) Right-click on this folder and add a new item. From all the items available select a Web User control. Also choose C# as the devlopment language and tick the option Place code in a seperate file.Name the control Address.ascx

5) Switch to the Source view of the Address.ascx control.Have a look at the first line.

<%@ Control Language=”C#” AutoEventWireup=”true” CodeFile=”Address.ascx.cs” Inherits=”UserControls_Items_Address” %>

Notice that even though this directive looks a lot like a page directive it starts with  @ Control

6)  Open the Address.ascx.cs file.Your newly create class does not inherit from theSystem.Web.UI.Page but from

public partial class UserControl_Address : System.Web.UI.UserControl

The classes System.Web.UI.Page and System.Web.UI.UserControl have lots in common since they both inherit from another class,TemplateControl class

For more information click here

7)  Let’s work on our new user control. Insert a table in your “Address.ascx” page. This table should have 4 rows and 2 columns.

8)  Add 4 label web server controls on the first 4 rows of the first column. Set their IDproperty as you like(AddressLabel1,AddressLabel2,AddressLabel3,PostCodeLabel). Set their text property like  this

  • Address1
  • Address2
  • Address3
  • PostCode

9) Add 4 textbox web server controls on the 4 rows of the second column.Set their IDproperty like this

txtaddr1,txtaddr2,txtaddr3,txtpostcode

10)  In order to add this newly created web user control to the Default.aspx page just drag and drop it from the Solution Explorer onto the .aspx page

11) Look in the source view of the Default.aspx page and notice this line

<%@ Register src=”UserControl/Address.ascx” tagname=”Address” tagprefix=”uc1″ %>

This is how the user control is registered with the .aspx page. You will also see this

<uc1:Address ID=”Address1″ runat=”server” />

Change the ID property to Shipping_Address

12) We can add new server controls to our user control. If we wanted to have a label control as a header we must select the Address.ascx and just above the table to insert a new label.Name this label headingLabel.

One can set the Text property of this new header label control and make it apparent to the end use that we talk about Shipping Address. But as I mentioned before we need to use this user control in many places in our website. So we do not want to have a fixed Textproperty but one we can set its value accordingly.

Select the Default.aspx page and in the

<uc1:Address ID=”Shipping_Address”  runat=”server”/> section you will see thatheadingLabel is not exposed as a property. It cannot be accessed from our page.

So we must add a new public property on this UserControl_Address class.This is the same with every other normal class.

So in the Address.ascx.cs file type

public string Header
{
set { headingLabel.Text = value; }
}

13) Now you can go back to the Default.aspx page (Source View) and add theHeader=Shipping Address <uc1:Address ID=”Shipping_Address”  runat=”server” Header=”Shipping Address”>

14) It is very easy to handle events in a user control.Add a button to the user control. This means that you go to the Address.ascx file and drop a button under the table. Set the IDproperty to be txtNext and the Text property to be Next. Add another .aspx page to your website and call it Checkout.aspx. Double click on the button and you have the empty event handling routine.

Type the following

protected void Button1_Click(object sender, EventArgs e)
{
Response.Redirect(“~/Checkout.aspx”);
}

You see how easy it is to handle events in a user control.

14 ) Let’s see now, how we can create an event in the user control. First we need to define an event.

When we define an event we must define the signature of the event handler method. We do that by choosing a delegate type.We also must give a name to our event. We will call itConfirmed event.

Select the Address.ascx.cs and type

public event EndEventHandler Confirmed

So we know now that our user control class will raise an event and we must write some code to actually cause this event to be raised.

So in our Button_Click event handler, we type

if (Confirmed!=null) Confirmed(this, new EventArgs());

When the user clicks the button, the Confirmed event will be raised.

Select the Default.aspx page and in the Source View locate the

<uc1:Address ID=”Shipping_Address”  runat=”server” Header=”Shipping Address”/>

and change it to

<uc1:Address ID=”Shipping_Address” onConfirmed=”ShippingAdress_Confirmed”  runat=”server” Header=”Shipping Address”/>

We just added the event handler in the user control.We do that by entering the name of the event and the name of the event handling routine we will add shortly.

In order to add the ShippingAdress_Confirmed event handling routine we go back to theDefault.aspx.cs file and type

protected void ShippingAdress_Confirmed(object sender, EventArgs e)
{

}

Inside this routine add this line of code:

Response.Write(“The event has been handled”);

Run the application and you will see the event raised and handled (the text “The event has been handled” will be printed in the Default.aspx page)

For more information on events have a look here .

15) Let’s assume that we need 2 instances of our user contol in the Default.aspx page (e.g one for shipping address and one for billing address).Select the Default.aspx and add a new user control. Go to the Source View and add a Header property e.g Billing Address. Also set the ID property to “Billing_Address“. You should have something like this

<uc1:Address ID=”Billing_Address” runat=”server” Header=”Billing Address” />.

Run the application and see the 2 user controls.

We need to copy all the fields entered in our Shipping address to the Billing address fields.

So we must access the text properties of the Shipping_Address user control and copy them to the Billing_Address user control.

You will see that it is impossible to access these textbox values directly  fromShippingAdress_Confirmed event handler in the Default.aspx.cs class, because these text properties are protected and thus invisible to the event handling routine.

So we need to create some public properties in our user control class.

Select the Address.asx.cs file and type

public string Address1
{
get {return txtaddr1.Text; }
set {txtaddr1.Text=value;}
}
public string Address2
{
get { return txtaddr2.Text; }
set { txtaddr2.Text = value; }
}
public string Address3
{
get { return txtaddr3.Text; }
set { txtaddr3.Text = value; }
}
public string PostCode
{
get { return txtpostcode.Text; }
set { txtpostcode.Text = value; }
}

16) Select the Default.aspx.cs file and inside the ShippingAddress_Confirmed event handling routine, comment out the ( Response.Write(“The event has been handled”);) and type
Billing_Address.Address1 = Shipping_Address.Address1;
Billing_Address.Address2 = Shipping_Address.Address2;
Billing_Address.Address3 = Shipping_Address.Address3;
Billing_Address.PostCode = Shipping_Address.PostCode;

Run your application. Type some address data into the first user control and then click the button.You will see the entered data copied in the second user control.

Hope it helps. If you need the source code just email me.

Add to FacebookAdd to NewsvineAdd to DiggAdd to Del.icio.usAdd to StumbleuponAdd to RedditAdd to BlinklistAdd to TwitterAdd to TechnoratiAdd to Furl

Using the ASP.Net Calendar Control September 21, 2009

Posted by fofo in asp.net, C#, Visual Studio 2005, Visual Studio 2008.
Tags: , , , ,
1 comment so far

I have been using my blog to talk primarily about new things and enhancements in the .Net Framework. I have been talking about C# new features,VB new features, LINQ,Entity framework and the new Visual Studio 2010. The other day during a seminar I was asked about a very important ASP.NET 2.0 web server control, the calendar control. So I thought to write a post and look into this very useful control, presenting its basic functionality but also looking into the more advanced functionality it has.

So what is the calendar control.

A simple but powerful definition could be:

It allows the users to select a date or a range of dates withing an application

I will be using VS 2008 and C# to create a simple asp.net application.

1) Launch Visual Studio 2005 or 2008.

2) Create a new website and name it with a meaningful name, e.g CalendarSite

3) Drag and drop a Calendar control from the toolbox into the default.aspx page.Leave the default name

4) Let’s examine some of the most used properties.

SelectedDate: By setting this property to a value, you have this value-date selected in the calendar control. Set the value to a date and run your application

VisibleDate:Whenever we want to use a different month or year, when the control initially loads, we can set the VisibleDate property to a date

If you want to select any particular day just click on the hyperlink on the bottom of each day.

5) But what if we wanted to select all of the days of a particular week or all the days of a particulat month.

Then we need to look into another useful property of the Calendar control, the SelectionMode. For selecting all the days of the week, you can set its value to DayWeek.

For selecting all the days in a month, you can set its value to DayWeekMonth.

Run your application and you will see more selectors (arrows) in the calendar control. By clicking on them you will be able to select a whole week or month.

Some other very important properties are:

  • DayNameFormat , which allow us to change the number of letters the header day text contains
  • FirstDayOfWeek, which allow us to set the first day of the week to Monday if we want to

We have so many options to style the control by setting the values of properties like BorderColor,BordeWidth,BorderStyle,BackColor to the values we want. We can also set the values of various font properties. We can always use the smart tag(little arrow on the top right hand corner) to use one of the many predefined formatting styles for our calendar control.

Now that I have covered some of the basic stuff, I would like to mention

  • Calendar click events
  • Control Calendar cell’s rendering

6) Every time the user selects a date in you calendar control, you capture this selection by the SelectionChanged event.

Add a literal control to the default.aspx page. Name it SelectedDatesLiteral

Just double click on a day in the calendar control. In the event handling routine, type the following

foreach (DateTime selectedDate in Calendar1.SelectedDates)
{
SelectedDatesLiteral.Text = string.Format(“You selected: {0:d}<br/>{1}”, selectedDate, SelectedDatesLiteral.Text);
}

Here, I just loop through the selected dates and append their values to a literal control.Run your application and see for yourself.

7) If we want to add content in a day in the calendar, we must use  the DayRender event. This event is invoked once for every day in the calendar. The table cell and the date are passed to the event handler and can be used to render custom content or look up data in a database etc. We will pull event data from an xml file.Add an xml file to your project and call it events.xml. Place this file in the App_Data special folder. The contents of this file could be something like this

<?xml version=”1.0″ encoding=”utf-8″ ?>
<Events>
<Event Month=”1″ Day=”1″>Happy New Year.</Event>
<Event Month=”2″ Day=”29″>Happy leap day.</Event>
<Event Month=”12″ Day=”25″><![CDATA[Merry Christmas.]]></Event>
<Event Month=”12″ Day=”7″><![CDATA[Bob's Birthday!]]></Event>
<Event Month=”12″ Day=”28″><![CDATA[16th anniverary]]></Event>
</Events>

Add a XMLDataSource control in to your default.aspx page and configure it so it reads the contents of your xml file.

Then in the DayRender Event type the following

protected void Calendar1_DayRender(object sender, DayRenderEventArgs e)
{
string xpath = string.Format(“//Event[@Month={0} and @Day={1}]/text()”, e.Day.Date.Month, e.Day.Date.Day);
XmlDocument doc = XmlDataSource1.GetXmlDocument();

StringBuilder content = new StringBuilder(e.Day.DayNumberText);
foreach (XmlNode node in doc.SelectNodes(xpath))
{
content.Append(“<br/>”);
content.Append(node.Value);
}

e.Cell.Text = content.ToString();
}

Make sure you have added these namespaces in the Default.aspx.cs file

using System.Xml;
using System.Text;

I am using an XPath to filter out only the nodes in our xml document that have content.

Then I am opening the xml document and then I am building a string with values that will be displayed in the calendar cell for the current day.

Then I loop through all the items that were returned back from the XML document and append those values together in order to create a string of all of events that happend in this specific day.

Run your application and see the content that is passed from the xml file to the specific dates, e.g Christmas day.

If you need the full code, just email me.

Hope it helps!!!

Add to FacebookAdd to NewsvineAdd to DiggAdd to Del.icio.usAdd to StumbleuponAdd to RedditAdd to BlinklistAdd to TwitterAdd to TechnoratiAdd to Furl

Create an XML file using LINQ and import XML data into an Excel spreadsheet March 31, 2009

Posted by fofo in LINQ, MS Office, VB 9.0, Visual Studio 2008.
Tags: , , , , ,
6 comments

Hey all,

You must think that this post title is a rather long one. Yes it is, but the concepts are rather easy and I have been posting lots of stuff regarding LINQ to SQL,VB.Net, XML Literals, e.t.c.

A friend of mine had a project which required to get some data from an SQL Server database table, create an XML document from the relational data and import it to an excel file. Another thing to bear in mind here is that we needed to create the excel file programmatically.

I tried to help him out and then thought that it might be a good idea to write a post about the solution I proposed.

So I will try to show all these, by (a hands-on example) building a windows form application using VB 2008(9.0).

I am going to use Visual Studio 2008 professional but Visual studio 2008 Express edition will do just fine.You can download Visual Studio 2008 from here .

Obviously for this example we will need a database. I will use the Pubs database. This is a well known database and many people are familiar with its schema.You can download the Pubs database from this link. If you need some help on how to install the database have a look here .

I have installed SQL Server 2008 Standard edition in my PC. SQL Server 2008/2005 Express editions will work just fine.You can download SQL Server 2008 Express edition from this link. I have attached the Pubs database in my local instance of the SQL Server.

In order to follow along you must have all the above running smoothly in your pc.It will be great for you in order to better understand what I am about to show you, If you are familiar with LINQ to SQL and XML Literals.

If you want to read another post of mine regarding XML Literals click here .

If you want to read another post of mine regarding LINQ to SQL click here

So let’s start…

1) Lauch Visual Studio 2008

2) Create a new project. From the available templates choose “Windows form Application”. Make sure VB is selected as your language of choice for this project.

3) Give your project a suitable name, e.g “sqltoxmlandimporttoexcel”

4) We will create the excel file programmatically first.In the form (Form1) just drag and drop a button on it. Name it “btnCreateExcel”.

5) Go to your c:\ drive and create a folder.Name this folder as you want. I have named this new folder after my name, thus nikos. So I have a path of “c:\nikos”. In this folder I will place the excel file that I will create programmatically.

6) Double click on the button. In the event handling routine that is created by VS 2008 for us, paste the code below

Dim xlApp As excel.Application

Dim xlWorkBook As excel.Workbook

Dim xlWorkSheet As excel.Worksheet

xlApp = New excel.ApplicationClass

xlWorkBook = xlApp.Workbooks.Add

xlWorkSheet = xlWorkBook.Sheets(“sheet1″)

xlWorkSheet.Cells(1, 1) = “XML Code starts here”

xlWorkSheet.SaveAs(“C:\nikos\mydataxml.xls”)

xlWorkBook.Close()

xlApp.Quit()

xlApp = Nothing

MsgBox(“Your Excel file has been created successfully”)

As you see I have called my excel workfile, “mydataxml.xls

7) You must do 2 two things in order for this code to run. First you must add a reference to the Microsoft Excel Interop Library which can be found in the COM components tab.

  • Right-click on your Project and select “Add reference” and then from the “Add reference” window, Select the COM tab. Scroll down till you find the Microsoft Excel 11.0 Object Library and click OK.

Have a look at the picture below.

excel-com-library-new

  • Add this line of code just above the “Public Class Form 1″

Imports excel = Microsoft.Office.Interop.Excel

Public Class Form1

8) Run your application by hitting F5. Click on the button and then check on your specified folder to see if the excel file has been created.

9) Now we need to query our database table. We are going to use this by using LINQ to SQL. Go to your Solutions Explorer, select your project and then right click and choose “Add new item” and choose LINQ to SQL classes. Name the .dbml file as Pubs.dbml. If you open the Pubs.dbml file, you will see the O-R designer surface.From your Server Explorer window, click to the Connect to Database button. Select the Sql Server instance name and select the Pubs database and hit the OK button.See the picture below:

add-connection-new

10) After establishing the connection with the Pubs database, expand the Tables node and drag – drop the Authors table in the O-R designer surface. An “Author” data class is automatically created for us.

11) Place another button on your form. Name the button “CreateXML”. Double click on the button. In the event handling routine type the code below. I am trying to get all the rows of the authors table and save them in an .xml file.

Dim mydb As New PubsDataContext

Dim myauthors = <authors>

<%= From author In mydb.authors Select _
<author>
<lname>

<%= author.au_lname %>
</lname>
<fname>

<%= author.au_fname %>
</fname>

<phone>

<%= author.phone %>
</phone>
<address>

<%= author.address %>
</address>
<city>

<%= author.city %>
</city>
<state>

<%= author.state %>
</state>

<zip>

<%= author.zip %>
</zip>

<contract>

<%= author.contract %>
</contract>

</author> %>

</authors>

myauthors.Save(“c:\nikos\myxmldata.xml”)

12) As you see what I am doing is pretty simple. I type XML statements that form the structure of my XML document using LINQ to SQL expressions to query the relational data.If you run your application and click the “CreateXML” button, you will have in your specified folder (c:\nikos\ , in my case) a document called “myxmldata.xml”. Browse to the specified folder and select the “myxmldata.xml”, right-click on this file and Open it with Internet Explorer. You will see all 23 rows of data that resides in the Authors table to be represented as nodes in our newly created xml file.

13) So now we are on the final part of our solution. Yes you guessed it!!! We will place another button on the form. We will call it “SaveXMLtoExcel”. By clicking on this button, all the data from the xml file should be placed in the excel file we created in the first steps of this example.

14) Double click on the “SaveXMLtoExcel” button and in the event handling routine ,and paste the code below. Do no forget to include the Imports System.Xml namespace at the beginning of your Form1.vb file.

In this routine I get an instance of the existing excel file and by using the XMLReader class and the ReadXml to read the xml data into a dataset.Then I use the dataset to populate the rows and columns of the specific worksheet.

Dim xlApp As excel.Application
Dim xlWorkBook As excel.Workbook
Dim xlWorkSheet As excel.Worksheet

Dim ds As New DataSet
Dim xmlFile As XmlReader
Dim i, j As Integer

xlApp = New excel.ApplicationClass
xlWorkBook = xlApp.Workbooks.Open(“c:\nikos\mydataxml.xls”)
xlWorkSheet = xlWorkBook.Sheets(“sheet1″)

xmlFile = XmlReader.Create(“c:\nikos\myxmldata.xml”, New XmlReaderSettings())
ds.ReadXml(xmlFile)

For i = 0 To ds.Tables(0).Rows.Count – 1
For j = 0 To ds.Tables(0).Columns.Count – 1
xlWorkSheet.Cells(i + 1, j + 1) = _
ds.Tables(0).Rows(i).Item(j)
Next
Next

xlWorkSheet.SaveAs(“C:\nikos\mydataxml.xls”)
xlWorkBook.Close()
xlApp.Quit()
xlWorkBook = Nothing
xlApp = Nothing

GC.Collect()

15) Run your application. Hit the “SaveXMLtoExcel” button. Open the excel file and hopefully you will see 23 rows of data that represent authors data

I am sure you can realise that you could combine the whole functionality in one button or routine, but I broke the code into various parts so it is easier to read. I think we have covered lots of things in this post. We talked about XML,XML literals,LINQ to SQL and how to create excel file from XML data.

Hope it helps!!!

Happy reading!!!

Add to FacebookAdd to NewsvineAdd to DiggAdd to Del.icio.usAdd to StumbleuponAdd to RedditAdd to BlinklistAdd to Ma.gnoliaAdd to TechnoratiAdd to Furl

Create a simple data-centric WPF application March 28, 2009

Posted by fofo in VB 9.0, Visual Studio 2008, WPF, XAML.
Tags: , , ,
8 comments

I have been asked from some people to write a simple post regarding the younger and more flashy brother of  window forms. WPF is about 1 years old now and gaining ground on developers for building next generation window applications. I urge people who are new to WPF to familiarise themselves a little bit with WPF by clicking here .

In this post I am going to do just that through a very practical hands-on example. I am going to use Visual Studio and VB 2008 to build a simple data entry form.

I am going to use the Northwind database as our data store for this example.

Click here to download the installation scripts. Fire SQL Server 2005/2008 Express Edition or SQL Server Standand edition 2005/2008 and run the scripts. All the tables and their related data is now available for us to use.

1) Launch Visual Studio 2008 professional or express edition

2) Create a new project by choosing from the available templates WPF application (choose VB as your language of development)

3) Name your application myfirstWPFapp.

4) Click the Data from the menu and then click Show Data Sources.Click on the Add New Data Source link.

5) Select Database from the Dialog window and click Next .

6) Create a new connection string by clicking the New Connection. In the new window that appears choose the SQL Server name to connect to. This is of course the server that you installed the Northwind database. In my case it is FOFO-PC\SQLEXPRESS and then select the database from the dropdown list (Northwind of course). Test the connection and click OK to close the window.

7) CLick Next on the data source configuration wizard and save the connection string as NorthwindConnectionString. Click Next.

8) In the next step of the wizard from all the database objects expand the table node and select Customers. Name the dataset CustomersDataset. Click Finish.

9) In the Window1.xaml just drag and drop 4 button(on the right hand corner) controls from the Toolbox on your Wpf form. We want to add, delete,save and cancel(cancel the data that we are currently inserting) data from our Customers table. Name the buttons accordingly by changing their Name property for each one of them. I named them for example btnSave,btnCancel, e.t.c.Give them an appropriate label by changing  the Content property for each one of them.Change from the properties window some properties e.g background,foreground colors. Align the buttons and make them the same width. This is a good practice.These changes will be saved in the XAML code that you can see in the lower half of the window. We will not focus too much on the design issues in this post.

10) Drag and drop 7 label controls on the WPF form. Name them e.g lblCustomerID,lblCompanyname lblName,lblCity,lblCountry,lblRegion,lblPhone. Give them an appropriate text by changing  the Content property for each one of them.Change from the properties window some properties e.g background,foreground colors. Align them and make them the same width by setting the width property for each one of them to “100”.

11) Drag and drop 5 textboxes on the Wpf form.Name them e.g txtCustomerID, txtCompanyName,txtName,txtCity,txtCountry,txtRegion,txtPhone. Change from the properties window some properties e.g background,foreground colors. Align them and make them the same width by setting the width property for each one of them. Align the label controls and the textbox controls using the visual alignment controls. Resize the formif you want.

12) We have not finished yet. We must provide some sort of navigation controls to our application. In WPF applications we do not have ready controls(navigators) like we did with normal window forms. Drag and drop 4 button(on the left hand corner) controls from the Toolbox on your Wpf form. We want to allow users to navigate to the first,last,previous and next record from our Customers table. Name the buttons accordingly by changing their Name property for each one of them. I named them for example btnfirst,btnlast, e.t.c.Give them an appropriate label by changing  the Content property for each one of them.Change from the properties window some properties e.g background,foreground colors if you want. Align the buttons and make them the same width.If you have followed along every step you must have something similar with this picture below

wpf-new-1

13) That seemed like a lot of work… Ok, so now we are ready to finally do our databinding tasks. We obviously need to populate the textbox controls with data from our Customer database table through our CustomersDataSet object and the Customers Data Table object.If you have done databinding using window forms , you may think that we simply drag and drop the fields from the Data Sources window on the wpf form. We can not do that. But we can databind the textboxes to their respective values from the database from XAML code. We do that by telling each textbox Text property that is bound to the relevant field in the table. In the XAML pane we locate the first textbox, and we bind it to the CustomerID field using this code(i just typed the code in brown color):

<TextBox Grid.Column=”1″ Grid.Row=”1″ Height=”23″ Margin=”61,13,103,0″ Name=”txtCustomerID” VerticalAlignment=”Top” Text=”{Binding Path=CustomerID}” />

You do that for the remaining 6 textbox controls.

14) Now we need to load the data in our form. We must write some code in our code behind file in the appropriate event. This is the Window_Loaded event handler routine. We must create some variables for our dataset,tableadapter and tableadaptermanager. You can do this by writing something like that

Dim customerdataset As New CustomersDataSet

Dim customeradapter As New CustomersDataSetTableAdapters.CustomersTableAdapter

Dim customermanager As New CustomersDataSetTableAdapters.TableAdapterManager

Now inside the event handling routine you have

Private Sub myfirstWPFAPP_Loaded(ByVal sender As Object, ByVal e As    System.Windows.RoutedEventArgs) Handles Me.Loaded

Me.customeradapter.Fill(Me.customerdataset.Customers)

Me.DataContext = Me.customerdataset.Customers

End Sub

15) If you hit F5 and run your application you will see the first row(only the fields we specified, not all of them) of the Customers Table loaded into the 7 textboxes. That is great!!! But we do not need just the data for one record, we need all the records. For this we need to write some code in our navigation controls. In order to do that we need an object to navigate the data collection. This object is the CollectionView object.

We create a variable of that data type e.g

Dim myview As CollectionView

and inside the form Loaded Event(myfirstWPFAPP_Loaded)we type the following line to give a value to our object

Me.myview = CollectionViewSource.GetDefaultView(Me.customerdataset.Customers)

16) Double Click the button that you named “First”. An event handling routine is created. Add this line of code

Me.myview.MoveCurrentToFirst()

So the full code is:

Private Sub btnfirst_Click(ByVal sender As System.Object, ByVal e As  System.Windows.RoutedEventArgs) Handles btnfirst.Click

Me.myview.MoveCurrentToFirst()

End Sub

17) Double Click the button that you named “Previous”. An event handling routine is created. Add these three lines of code inside it. In this case we check with a very simple If statenement if we are on the first record. If not we can go to the previous one.

If Me.myview.CurrentPosition > 0 Then

Me.myview.MoveCurrentToPrevious()

End If

18) Double Click the button that you named “Next”. An event handling routine is created. Add these three lines of code inside it. Here we check if we are in the last row of our table. If not , we move to the next record.

If Me.myview.CurrentPosition < Me.myview.Count – 1 Then

Me.myview.MoveCurrentToNext()

End If

19) Double Click the button that you named “Last”. An event handling routine is created. Add this line of code inside it.

Me.myview.MoveCurrentToLast()

20) Run your application by hitting F5. Make sure that the navigation buttons operate as desired.

21) Yes it is now time to write some more code so our ,cancel,save,delete,add buttons to be operational.Double Click on the “Delete” button on your form. An event handling routine is created. Add these 4 lines of code inside it.

If Me.myview.CurrentPosition > -1 Then
Dim row = CType(Me.myview.CurrentItem, System.Data.DataRowView).Row
row.Delete()

End If

We check to see that we are in a record and that our table is not empty. Then we must get the row that is currently in view and then we simply delete it. I want to highlight something here. This Customers table is related via a foreign key constraint to the Orders table which is another table of the Northwind database. If you go to delete one record from the Customers table that has placed an order, then you will receive an error. In order to test the “Delete” button just add a brand new customer using the “Add” button first.

22) Double Click on the “Add” button on your form. An event handling routine is created. Add these 3 lines of code inside it.

Dim row = Me.customerdataset.Customers.NewCustomersRow

‘ because these fields do not allow nulls we specify some values

row.CustomerID = “[mid]“
row.CompanyName = “[my company name]“

Me.customerdataset.Customers.AddCustomersRow(row)

Me.myview.MoveCurrentToLast()

23) Double Click on the “Cancel” button on your form. An event handling routine is created. Add this line of code inside it.

Me.customerdataset.RejectChanges()

24) Double Click on the “Save” button on your form. An event handling routine is created. Add these 7 lines of code inside it.

Try
If Me.customerdataset.HasChanges Then

Me.customeradapter.Update(Me.customerdataset)

End If

Catch ex As Exception
MsgBox(ex.Message)

End Try

25)  Run your application. Add a new customer by clicking the “Add” Button and filling in the data and click the “Save” button. A new customer is in the database. Then click the “Delete” Button while the newly created customer row is active. Click the “Save’ button again.The customer is deleted. Check these changes in your actual database.

Hope it helps. If you need the source code email me at nkantzelis@q-training.gr

Happy browsing!!!!

Add to FacebookAdd to NewsvineAdd to DiggAdd to Del.icio.usAdd to StumbleuponAdd to RedditAdd to BlinklistAdd to Ma.gnoliaAdd to TechnoratiAdd to Furl

LINQ TO SQL and Stored procedures December 13, 2008

Posted by fofo in c# 3.0, LINQ, SQL Server 2008, Visual Studio 2008.
Tags: , , ,
8 comments

In this post, I will go back to the issue of LINQ. I am going to show with an example how to use LINQ and stored procedures to insert,update and delete records from a particular table.

I will use visual studio 2008 to create an asp.net application (c# version) that demonstrates how to use stored procs with LINQ.

If you want to see how LINQ treats stored procedures , have a look here

I am going to use the Pubs datatabase and in particular the Authors table.

I am pretty sure, because I have used the Pubs database in almost all my examples that you have installed it and attached it in your local instance of Sql Server.

You can use Visual Studio 2008 express edition and Sql Server express edition for this example.

We often need to use stored procs to talk with our database because there have many benefits

for example

  • They allow modular programming.
  • They allow faster execution.
  • They can reduce network traffic.
  • They can be used as a security mechanism.

First we need to create the 3 stored procedures that will insert,update and delete records from the Authors table

This is not a post on how to create stored procedures, so i am just going to paste here the complete stored procs.

  • DeleteAuthor

USE [pubs]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[DeleteAuthor]

@AuthorID nvarchar(20)

AS
BEGIN

DELETE FROM authors
WHERE au_id = @AuthorID

END
GO

  • UpdateAuthor

USE [pubs]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[UpdateAuthor]

@authorID varchar(11),
@lname nvarchar(50),
@fname nvarchar(50),
@phone char(12),
@address nvarchar(40),
@city nvarchar(40),
@state char(2),
@zip char(5),
@contract bit

AS
BEGIN

UPDATE authors
SET

au_lname=@lname,
au_fname=@fname,
phone=@phone,
address=@address,
city=@city,
state=@state,
zip=@zip,
contract=@contract
WHERE au_id  = @authorID

END
GO

  • InsertAuthor

USE [pubs]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[InsertAuthor]
@id varchar(11),
@lName nvarchar(50),
@fname nvarchar(50),
@phone char(12),
@address nvarchar(40),
@city nvarchar(40),
@state char(2),
@zip char(5),
@contract bit

AS
BEGIN

INSERT INTO pubs.dbo.authors(
au_id,
au_lname,
au_fname,
phone,
address,
city,
state,
zip,
contract)
VALUES (
@id,
@lname,
@fname,
@phone,
@address,
@city,
@state,
@zip,
@contract)

END

GO

1) Launch Visual Studio 2008

2) Create an ASP.net web application. Use C# a your language of development

3) Name your project – solution as you want.

4) Open the Server Explorer and connect to the Pubs database.

5) Add a new item in your project, a Linq to SQL classes, a .dbml file. name it authors.dbml

6) Drag and drop from the Server explorer window the authors table into the designer area (on the .dbml file)

7) Right-Click on the designer area and show the “Show methods Pane”

8) Drag and drop the stored procedures from the Server explorer to the designer area

9) Select the author entity from the deisgner area and select the Properties window. In the Default methods we need to assign the correct stored procs and not to leave the default option which is “use Runtime”. So please assign the different methods to their respective stored procs. Have a look at the picture below

sql-linq

10) We will use these stored procs that are methods now as far as LINQ is concerned to update,insert and delete records from the database.

11) Add a button in the Default.aspx file. Name it “Update”.Double click on this button. In the event handling routine type:

using (authorsDataContext authdata = new authorsDataContext())
{
var author = (from a in authdata.authors
where a.au_id == “238-95-7766″
select a).Single();
Response.Write(author.au_fname);
author.au_fname = “nikolaos”;

authdata.SubmitChanges();

}

12) Add another button in the default.aspx page. Name it “After Update”.In this routine we try to see the updated value in the database.Double click on this button. In the event handling routine type:

using (authorsDataContext authdata = new authorsDataContext())
{
var author = (from a in authdata.authors
where a.au_id == “238-95-7766″
select a).Single();
Response.Write(author.au_fname);

}
12) Add another button in the default.aspx page and name it “Insert”.Double click on this button. In the event handling routine type:

using (authorsDataContext authdata = new authorsDataContext())
{

authdata.InsertAuthor(“216-49-8915″, “Jones”, “Michael”, “432423424”, “james street 123″, “New york”, “NY”, “94618”, true);

authdata.SubmitChanges();
}

13) Add another button in the default.aspx page and name it “Delete”. Double click on this button. In the event handling routine type:

using (authorsDataContext authdata = new authorsDataContext())
{
var author = (from a in authdata.authors
where a.au_id == “216-49-8915″
select a).Single();
authdata.DeleteAuthor(author.au_id);
authdata.SubmitChanges();
}

if you named you .dbml file “authors”, then there is a file “authors.designer.cs”. Inside there you will find this code

private void Insertauthor(author obj)
{
this.InsertAuthor(default(string), default(string), default(string), obj.phone, obj.address, obj.city, obj.state, obj.zip, ((System.Nullable<bool>)(obj.contract)));
}
private void Updateauthor(author obj)
{
this.UpdateAuthor(obj.au_id, obj.au_lname, obj.au_fname, obj.phone, obj.address, obj.city, obj.state, obj.zip, ((System.Nullable<bool>)(obj.contract)));
}
private void Deleteauthor(author obj)
{
this.DeleteAuthor(default(string));
}

The methods above, are our stored procedures , which are called whenever we insert,update,delete

Hope it helps!!!

If you need the source code just email me.

Add to FacebookAdd to NewsvineAdd to DiggAdd to Del.icio.usAdd to StumbleuponAdd to RedditAdd to BlinklistAdd to Ma.gnoliaAdd to TechnoratiAdd to Furl

Follow

Get every new post delivered to your Inbox.

Join 1,792 other followers