LINQ DataSource Insert, Update and Delete : ASP.NET 4.0

Its been some time I didn’t write any thing on the blog, I was busy with my projects, so now thought of writing some thing on LINQ Datasource in ASP.NET .NET 4.0 (also part of .NET v3.5).

In this example I am trying to display list of products in a GridView and Insert, Update and Delete products using LINQ Datasource, so lets start building the application.

Create a new Web Application Project in Visual Studio and add one GridView and LINQ DataSource to Default.aspx, before we configure LINQ data source we need a LINQ to SQL Class so add a new file to project and select LINQ to SQL class file.

Once this file is added project we need to select data from data base, for this open the server explorer and Drag and drop Products, Supplier and Category Tables to this file, please refer below screen shot.

Now we have successfully created LINQ SQL class with our required entities, to reflect these changes to project we need build project once here.

Now open Default.aspx and select LINQDataSource1 controls and click ‘>’ symbol to configure the datasource, once you click on that symbol you will be asked to select the Entity class, as we have already created our LINQ to SQL class you can select that from the combo box

Click on Next to select the actual entity that is products, here we need the option to Select, Update and delete so click on Advance button and select those options.

 

Once selected click on Ok, and Finish, now our LINQDatasource is bound with data and ready to bind to GridView, so select the GridView and click ‘>’ symbol to configure datasource. Select DataSource as LINQDataSource1 and check the options Enable Editing and Enable Deleting.

 

Now we are ready with our Display products, Edit Products and Delete products you can run the project and check these are working fine.

 

Now we will add a new product for that we need a separate web form where we will add new product, for this add new Web for and name it as AddProduct.aspx, now add 6 TextBoxs, 2 DropDownLists, 2 Buttons and one CheckBox control to this page as show in the below Screen shot, also add 3 LINQDataSource objects to form.

Here we need 3 LINQDataSource controls because we want to show Categories and Suppliers in combo boxes while creating the product so that user will select the values, so select LinqDataSource1 control and configure select SQL to LINQ class entity and select Suppliers as Table and select only SupplierId and CompanyName fields.

Similarly select LinqDataSource2 and configure and select Categories Table and select only CategoryId and CategoryName fields from this.

 

These two linq datasources are for displaying the data now we will configure LinqDataSource3 which is for inserting the Product details, so select the LinqDataSource3 and configure select Product table and all fields and select Advanced button and select the option LINQ to perform automatic insert

Now bind the Category and Supplier data to 2 DropDownLists using LinqDataSource1 and LinqDataSource2.

Similarly for Categories DropDownList, now we have to insert the data into LinqDataSource3 once user enters data and clicks Save button, so we need to write following code in the Save button’s click event

In the above code I am creating a ListDictionary object and adding all the values with the Column names and passing that object to Insert method of LinqDatasource after inserting I am redirecting to Default.aspx page.

So to launch Add Product page we need to add a link on the Default.aspx page and on clicking link redirect to AddProduct.aspx page.

Now we are ready with our code let build, run and test.

Hope this helps

Tags : LINQDataSource Insert, Update and Delete, ASP.NET 4.0, GridView, LINQ

Storing Files In Database Using ASP.NET

In this example I am storing files like word document, text file and excel files into database and retrieving back from database and saving back into a folder.

So lets start creating a Table in SQL Server database in which we are going store our uploaded files, in this table I am creating following columns.

1. DocumentID int primary key Identity yes
2. DocumentName varchar(100) name of the document with extension
3. Document varbinary(MAX) binary column for saving file in Binary format.

Open SQL Server Management Studio and open Northwind database and run following script for creating Table

Now we are ready with our Database creation so lets create a web application

Open Visual Studio and create a Web Application and in Default.aspx add following controls.

1. One upload control
2. One Button for uploading
3. One DropDownList for displaying List of files which are in database already
4. One Button for opening selected document.

Please refer following screen shot for design.

In Page_Load event we have to retrieve all documents which are already stored in database before so I have written a method called FillDropDown to fill the DropDownList

Once user selected a file to upload and click on Upload button we have to store that file in database in Binary format so I am using InputStream of uploaded file and reading into byte array and connection to database and storing the data.

Once user selects one uploaded file click on open I am retrieving the binary data of that file from database and using FileStream object I am saving the a folder, I am created a Folder called Documents in the solution itself and storing files in that folder, if a file already present in the folder it will be over written. please add following code in open button click event.

 

Now we are ready with our code just build, run and test.

 

Tags : Storing files into database, ASP.NET, C#, Web Application, FileStream, InputStream.

Show Long Text Item in ComboBox

Many times in our windows form we need to show data in Combo Box and items in combo box are have same length, so we face a problem that some item in the combo box is not shown fully, and increasing width of then combo might not always the good idea.

To avoid above mentioned problem we have a solution, we can increase the height of the combo box item at run time based on the length so the item can be read fully, so lets start building the example.

Open Visual Studio and Create a Windows Form Application and Add One label and one combo box and and some items in the combo box as shown in the below screen shot.

Now we are ready with design part, so lets start writing some code to increase the height of the combo box, for this we need to make use of the MeasureItem event of Combo box, in this item we can find out what is the length of each item and based on the length we can set the height, once you we set the item length we need to add the item in Combo Box using DrawItem event.

Normally the combo box draw style is OwnerDrawFixed so we cannot change any item at run time if it is OwnerDrawFixed so we need to change Combo Box’s DrawStyle to OwnerDrawVariable so that run time changes are allowed.

Please add following code in Form_Load, in below code I am setting DrawStyle and assigning MeasureIteam and DrawItem events to Combo Box

We need add following event handler for handling MeasureItem and DrawItem events.

Now we are ready with our code so lets build, run and test.

Tags: Showing full item text in Combo Box, .NET, C#, Windows Application

Client Side AJAX in ASP.NET 4.0

AJAX is most used technology these days, this avoids page reloads and user will not get frustrated, however it still requires a post back to server so ASP.NET 4.0 introduced ClientSide AJAX which is achieved by using some jQuery and server side service

Here I am going to build a sample using Northwind database Categories and Products, on selecting a category all products under that category will be listed, to list the products I am using a HTML Table and Templates.

So lets start, open Visual Studio 2010 and create a Web Application and name it as ClientSideAjax. To retrive the data from database I am using 2 DataSources those are

1) SQLDataSource1 : Just drag and drop a SQLDataSource object from tool box and select connection and connect to Northwind database’s Categories table.

2) ProductsCategories.edmx: This datasource is EntityDataModel, just right click on project in Solution Explorer and add a new file select EntityDataModel and name it as ProductsCategories.edmx, just follow the steps and connect to Northwind Database and select Product and Categories tables and finish.

Now we have created required datasources for our project, so let start designing the web form, we need to add one Dropdownlist for listing all the categories and one HTML table for listing all the products under selected category, set the DataSource1 to DropDownlist and set DataTextField = CategoryName and DataValueField = CategoryID

We will talk about {{ ProductName }} template in details below.

So now lets write a service from where we will get the all the products under a category, for that I added a WebService page Northwind.asmx(create a folder called Services and Northwind service under that folder) and a web method to get products based on category, please find the asmx page in code below I am creating a public web method getProducts, in this method I am reading the data from ProductsCategories EntityDataModel and returning products.

Now we are ready with our server side code and let start write some client side JQuery to read the data from web service and display using JQuery templates. so we need add some Microsoft AJAX JavaScript files to our project just create a folder in our project called MicorsoftAjax and add following files

  You can download these JavaScript files from http://aspnet.codeplex.com

Add reference in the Default.aspx page to all js file and Northwind service, also create a style temple which is used to hide the jQuery template initially

So now we will write a PageLoad method in JQuery to get the products, now we will add following method to Default.aspx

Now we are ready with our code lets build, run and test

Hope this helps.

Tags: Client Side AJAX in ASP.NET 4.0, jQuery, Visual Studio 2010, C# .NET

QueryExtender Web Control : ASP.NET 4.0

Query extender is a new control introduced in ASP.NET 4.0, this control is used to filter and sort data by using different expressions, it has Search Expression, OrderByExpression, RangeExpressions etc.

When you have data in your Datasource and you want to filter at the application end so you always go for this control.

As of now this control is used with LINQDataSource other DataSource controls are not support with Query Extender in Visual Studio 2010 ASP.NET 4.0 beta 1.

I am building a example how we can filter data using QueryExtender, as it is part of ASP.NET 4.0 here I am using Visual Studio 2010 beta 1 so lets start.

Create a web application and Name it as QueryExtender, now we will add a LINQ To SQL Class file to our project by right clicking the project in Solution Explorer selecting Data and Select LINQ to SQL Class file and name it as LINQProducts.dbml, this is the LINQ DataSource for LINQDataSource.

Once you add this file to our project you can see LINQProducts.dbml file and you will observer 2 parts in the file, now you need to drag and drop your table from Server explorer to right side part, here I am selecting Northwind database's Products table and dropping.

To effect the changes we need to build our project here once.

Now we have created LINQ source for LINQDataSource control, Now open the design view of Default.aspx page and Add LINQDataSource Control from the Toolbox.

Once you have added the LINQDataSource control select the control and click on '>' symbol and click on Configure DataSource, now you will see a wizard for configuring the datasource, select the 'Show only DataContext objects' check box and choose the LINQProductsDataContext from Context combo box, click on next.

Now choose required fields to display, here I am selecting ProductId, ProductName, UnitPrice, UnitsInStock fields to display and click finish.

Now we have creaetd LINQDataSource object and bind the data with Products table to display, now drag and drop GridView control from tool box and add to Default.aspx, and '>' and click on Configure DataSource and select LINQDataSouce1 as datasource and Save.

To check whether data is getting display or not just run the project and see the output .

By this point we have displayed the data using LINQDataSource control to filter the data displayed we will use QueryExtender control, before creating the QueryExtender we need to add a reference in Web.Config to make use of its Expressions, so please add following in Pages -> Control section in Web.Config

Note : In above line you need to replace the PublicKeyToken value with other controls PublicKeyToken.
Once you have done this we are now ready to add QueryExtender to our project just add following code in our Default.aspx page, before adding we need to add some control to our form for search purpose, so add one label and textbox and a button to Default.aspx as shown in the screen shot Design.jpg

Now add QueryExtender control to Default.aspx

In the above lines I am creating a QueryExtender and assigning its taget control as our LINQDataSource1, for filtering I am getting the value from a text box, you can also get the result in order by using OrderByExpression.

Now we are ready with our code so let build, run and test

Hope this helps.

EntityDataSource Control ASP.NET

ASP.NET 3.5 sp1 introduces a data source control called EntityDataSource using this control we can bind data to different Data Binding controls.

Now lets start building an example application which gets the data from Northwind database employee table.

Open Visual Studio 2008/2010 and create a Web Application.

Now right click on Solution explorer and add new item and select ADO.NET Entity Data Model and name it as Employees, we will fill EntityDataSource object using this model only.

Select the option generate from database and click next

Now click on the New Connection button and give required details and create a connection to Northwind Database and click next

Once you have connected to database it list all the table procedures etc, here I would like to display data of Employee Table so I selected Employee table and click on finish.

Now we are ready with our model once you have done this, in you project you will Employees.edmx file

to use our model we need to build our project and check whether there are any error or not, just build the project once.

Our model is ready now, so lets bind the data to a EntityDataSource control for that we need to select a EntityDataSource control from ToolBox and add to Default.aspx, and also add one GridView control to Default.aspx

To bind the model to our EntityDatasource control just select the control and click on > symbol and select configure.

Select Named Connection combo box and select our EntityModel, click on next

Select EntitySet name as employees, you will see only one employee table because while creating model we have selected only one table if select more those tables also will be listed, one you select employees all the columns of that table will be listed below select which are appropriate to you here I am selected EmpId, first name and last name and finsh

Now we are created EntityDataSource lets bind the data to grid view for that select the grid view and click on ‘>’ symbol and select EntityDataSource1 as data source.

Now we are ready with our code just build, run and test.

tags: Using EntityDataSource Control, EntityDataModel, ASP.NET 3.5, ADO.NET

Bing : A decision making engine

Bing is new search engine from Microsoft; actually they say it is not search engine but decision making engine. Microsoft tries to live up to Google search.

Bing is good in finding deals, so try it yourself www.bing.com.

List all the Logical Drive in the Computer : Windows Application, C#

Using System.Management name space we can read the all operating system details and we query System_Win32 database in the following example I am trying to read all the logical drives in the computer and list them in a ListBox and select a drive and you can browse all the files in the drive.

So lets start creating the example, open visual studio and create a Windows Forms Application.

Add one list box and 2 buttons to the Form, you can below screen shot for the design

Now we need to add the reference for the System.Management.dll to project so that we can make use of some class using which we can read all logical drives. Once you add the reference you need to import the System.Management namespace, you can refer below screen shot.

Now we are ready with Design part and reference part so let start putting code to retrieve drive, for that please double click on Get All Drives button and add following code, in the following code I am call a method GetAllDrives in which I am creating object for ManagementObjectSearcher and searching all the drives using “SELECT Caption, DeviceID FROM Win32_DiskDrive” query, please refer below screen shot for code

Now we are done with searching and listing all the drives in the local computer now we will add the code for opening the selected drive in listbox, please refer the below screen shot.

We are now ready with our code just build, run and test.

Hope this helps.

Tags: Read all Logical Drives in Local Computer, C#, Windows Application, .NET