Pritpal's Web Blog

2009-12-08T05:03:00.000-08:00

How to convert Microsoft Access Database into SQL SERVER Database.

In order to convert a Microsoft Access Database into SQL SERVER Database, we have the following two options :

Option A

The first option involves creating a database in the SQL SERVER yourself first i.e. create all the tables with the contained columns and their data types in the SQL SERVER yourself. After creating the database, we will populate the database with the data from the MS-Access file.
The option consists of the following steps :

Step 1. Create a link server between the SQL SERVER and MS Access Database file.
Step 2. Access or get data from the MS Access file and insert into the SQL SERVER Database.

The option A has the following code :

- Code to create a link server between the SQL SERVER and MS Access Database file is :-

EXEC sp_addlinkedserver
@server = '<Link Server Name>',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = '<FullPath of MS Access file>'

After creating a link server between the SQL SERVER and MS Access file, we can now access the data from the MS Access file using the link server and can directly insert the accessed data into the SQL SERVER tables.
Let us say, we have a table tbemployee in the SQL SERVER and tbemp in MS Access file. We want to populate the tbemployee table with the data from the tbemp table.

- Code for the above is :-

insert into tbemployee select * from [Link Server Name]...[tbemp]

Note :- Create a Linked Server once and then fetch the records using the same link server as you wish.
Note :- Three dots (...) is must and [tbemp] is a table from Access file.

Option B

The second option involves creating a link between the SQL SERVER and MS Access file and then creating tables in the SQL SERVER at the RUN-TIME after reading data from the MS Access file.
The option B has the following code :

- Code to create a link server between the SQL SERVER and MS Access Database file is :-

Please refer to the above piece of code to create a link server.

After creating a link server between the SQL SERVER and MS Access file, we will be executing SQL's two stored procedures to get information about the tables from the MS Access file, columns and their datatypes as well.

The stored procedure to get information about all the tables in the MS Access file :

exec sp_tables_ex 'Link Server Name'

The stored procedure to get information about all the tables, columns and their data types as well in the MS Access file :

exec sp_columns_ex 'Link Server Name'

After getting this information from the MS Access file, we can create tables with the specified columns and their data types at the run time after creating a query and executing it.

Note :- There are some data types which are available in MS Access but not in SQL SERVER like byte,short etc. Do make a check to these data types.

And thats all.

Wish you Happy Programming.

Pritpal Singh

2009-09-16T04:44:00.000-07:00

Read & Write to XML file with AJAX

In this post, I am trying to explain the following :

Create a XML file.
Write to XML file.
Read from XML file.

The post will first explain to create a XML document and then adding the elements or nodes to the XML file. Then the XML document will be saved to XML file. The post also includes explanation for creating a file as well as deleting a file.

After successfully creating the XML file, we will enter data into the XML file or append data to a XML file. The data that is to be append to the XML file will be entered from the User Interface by the user through different controls on the web page. There after entering data into the XML file, we will read the XML file by using the ReadXML() method of the dataset. We will be using a GridView Control to represent the data stored into the XML file.

The post also makes use of AJAX controls i.e. ScriptManager and two UpdatePanels. The controls used to enter data into the XML file are in first UpdatePanel and the controls used to read data from the XML file are in the second UpdatePanel.

The code for the Default.aspx code as follows :

<div>
    <asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>
    <span style="font-size:x-large; color:Red">Write and Read from XML</span>

    <asp:UpdatePanel ID="UpdatePanel1" runat="server" UpdateMode="Conditional">
    <ContentTemplate>
    <b>Name :</b> <asp:TextBox ID="txt_Name" runat="server"></asp:TextBox><br />
    <b>Author :</b> <asp:TextBox ID="txt_Author" runat="server"></asp:TextBox><br />
    <b>Publisher :</b> <asp:TextBox ID="txt_Publisher" runat="server"></asp:TextBox><br />
    <asp:Button ID="Submit" runat="server" Text="Write to XML" OnClick="Submit_Click" /><br />
    <asp:Label ID="lbl_message" runat="server" Text="" ForeColor="Navy"></asp:Label>
    </table>
    </ContentTemplate>
    </asp:UpdatePanel>
    <br />
    <asp:UpdatePanel ID="UpdatePanel2" runat="server" UpdateMode="Conditional">
    <ContentTemplate>
    <asp:Button ID="xmlRead" runat="server" Text="Read XML" OnClick="xmlRead_Click" /><br />
    <asp:GridView ID="GridView1" runat="server"></asp:GridView>
    </ContentTemplate>
    </asp:UpdatePanel>
    </div>

The code for the Default.aspx.cs file as follows :

using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Xml;
using System.IO;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (Page.IsPostBack == false)
        {
            //==== Create an XML Document.
            XmlDocument xmlDoc = new XmlDocument();

            //==== XML Document Declaration.
            XmlDeclaration dec = xmlDoc.CreateXmlDeclaration("1.0", "utf-8", "");
            xmlDoc.AppendChild(dec);

            //==== Root Element.
            XmlElement Books = xmlDoc.CreateElement("Books");

            //==== Element Book.
            XmlElement Book = xmlDoc.CreateElement("Book");

            //==== Element for Name of the book.
            XmlElement Name = xmlDoc.CreateElement("Name");
            Name.InnerText = "ASP.Net";
            Book.AppendChild(Name);

            //==== Element for Author of the book.
            XmlElement Author = xmlDoc.CreateElement("Author");
            Author.InnerText = "Pritpal Singh";
            Book.AppendChild(Author);

            //==== Element for Publisher of the book.
            XmlElement Publisher = xmlDoc.CreateElement("Publisher");
            Publisher.InnerText = "Geeni Publishers";
            Book.AppendChild(Publisher);

            Books.AppendChild(Book);
            xmlDoc.AppendChild(Books);

            //==== Create a file to save the XML Document.
            if (File.Exists(@"C:\Practice.xml"))
           {

                //==== Delete the file, if already exists.
                File.Delete(@"C:\Practice.xml");
            }

            xmlDoc.Save(@"C:\Practice.xml");
        }
    }
    protected void Submit_Click(object sender, EventArgs e)
    {
        //==== Read the XML file.
        XmlDocument xmlDoc = new XmlDocument();
        xmlDoc.Load(@"C:\Practice.xml");

        //==== get the Root Element or Node.
        //XmlElement rootElement = xmlDoc.DocumentElement;

        //==== Element Book.
        XmlElement Book = xmlDoc.CreateElement("Book");

        //==== Element for Name of the book.
        XmlElement Name = xmlDoc.CreateElement("Name");
        Name.InnerText = txt_Name.Text;
        Book.AppendChild(Name);

        //==== Element for Author of the book.
        XmlElement Author = xmlDoc.CreateElement("Author");
        Author.InnerText = txt_Author.Text;
        Book.AppendChild(Author);

        //==== Element for Publisher of the book.
        XmlElement Publisher = xmlDoc.CreateElement("Publisher");
        Publisher.InnerText = txt_Publisher.Text;
        Book.AppendChild(Publisher);

        //==== Append the New node to the Root Element.
        xmlDoc.DocumentElement.AppendChild(Book);

        //==== Save the XML file.
        xmlDoc.Save(@"C:\Practice.xml");

        //==== Clear the Text Boxes.
        txt_Name.Text = "";
        txt_Author.Text = "";
        txt_Publisher.Text = "";
        lbl_message.Text = "XML write operation successful.";

    }
    protected void xmlRead_Click(object sender, EventArgs e)
    {
        //==== Create a Dataset.
        DataSet ds = new DataSet();

        //==== Call method to read data from dataset.
        ds.ReadXml(@"C:\Practice.xml");

        //==== Set the dataset.
        GridView1.DataSource = ds;
        GridView1.DataBind();
    }
}

And that's all.
Wish you Happy Programming.

Pritpal Singh

2009-09-09T05:16:00.000-07:00

Use LINQ to perform Edit/Update, Delete, Insert operations on SQL table.

In this post, I am trying to explain the following :
How to :

1. Retrieve data from SQL table and represent in the GridView Control using LINQ.
2. Edit or update the records in the GridView using LINQ.
3. Delete records from GridView using LINQ.
4. Insert records into SQL table using LINQ.

For this purpose, lets create a SQL table named tbemployee.

The table tbemployee is having the following fields and their data types :

    Name of the field      Data Type

   ID int
   Name                         nvarchar(50)
   Designation               nvarchar(50)
   Salary                        int

The ID field is assigned as the Primary Key of the table.
Create a new website. Add a LINQ to SQL Classes class file and name it as grid.dbml . Click Yes
to add the grid.dbml class file into the App_Code directory of the website.

From the Solution Explorer, open the grid.dbml class file. From the Server Explorer, drag and drop the tbemployee table into the grid.dbml file.

The design of the Default.aspx page would look somewhat similar to the following :

The code for the Default.aspx class would be as follows :

<span style="font-size:x-large; color:Red">Insert, Delete, Update using LINQ </span>

</td></tr>

<asp:TextBox ID="txtid" runat="server"></asp:TextBox></td></tr>

<asp:TextBox ID="txtname" runat="server"></asp:TextBox></td></tr>

<tr><td width="50%" align="right"><b>Designation :</b></td>

<asp:TextBox ID="txtdesi" runat="server"></asp:TextBox></td></tr>

<tr><td width="50%" align="right"><b>Salary :</b></td>

<asp:TextBox ID="txtsala" runat="server"></asp:TextBox></td></tr>

<asp:Button ID="Button1" runat="server" Text="Add Record" onclick="Button1_Click" />

</td></tr>

</table>

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"

onrowcancelingedit="GridView1_RowCancelingEdit"

onrowdeleting="GridView1_RowDeleting" onrowediting="GridView1_RowEditing"

onrowupdating="GridView1_RowUpdating" BackColor="White"

BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px" CellPadding="4"

ForeColor="Black" GridLines="Vertical" Width="80%">

<asp:TemplateField HeaderText="ID">

<asp:Label ID="lbl_IDI" runat="server" Text='<%# Eval("ID")%>'></asp:Label>

</ItemTemplate>

<asp:Label ID="lbl_IDE" runat="server" Text='<%# Eval("ID")%>'></asp:Label>

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Name">

<asp:Label ID="lbl_NameI" runat="server" Text='<%# Eval("Name")%>'></asp:Label>

</ItemTemplate>

<asp:TextBox ID="lbl_NameE" runat="server" Text='<%# Eval("Name")%>'></asp:TextBox>

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Designation">

<asp:Label ID="lbl_DesignationI" runat="server" Text='<%# Eval("Designation")%>'></asp:Label>

</ItemTemplate>

<asp:TextBox ID="lbl_DesignationE" runat="server" Text='<%# Eval("Designation")%>'></asp:TextBox>

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Salary">

<asp:Label ID="lbl_SalaryI" runat="server" Text='<%# Eval("Salary")%>'></asp:Label>

</ItemTemplate>

<asp:TextBox ID="lbl_SalaryE" runat="server" Text='<%# Eval("Salary")%>'></asp:TextBox>

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField>

<asp:LinkButton ID="link1" runat="server" Text="Edit" CommandName="edit"></asp:LinkButton>

</ItemTemplate>

<asp:LinkButton ID="link3" runat="server" Text="Update" CommandName="update"></asp:LinkButton>

<asp:LinkButton ID="link4" runat="server" Text="Cancel" CommandName="cancel"></asp:LinkButton>

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField>

<asp:LinkButton ID="link2" runat="server" Text="Delete" CommandName="delete"></asp:LinkButton>

</ItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

</td></tr>

</table>

The code for the Default.aspx.cs file would be as follows :

using System;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

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

{

protected void Page_Load(object sender, EventArgs e)

{

if (Page.IsPostBack == false)

{

//---- Call function to bind the GridView Control.

gridbind();

}

//---- Function to bind the GridView Control.

private void gridbind()

{

gridDataContext db = new gridDataContext();

var q = from p in db.tbemployees select p;

GridView1.DataSource = q;

GridView1.DataBind();

}

//---- GridView Events.

//---- Code for the RowEditing Event of the GridView Control.

protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)

{

GridView1.EditIndex = e.NewEditIndex;

gridbind();

}

//---- GridView Events.

//---- Code for the RowCancelingEdit Event of the GridView Control.

protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)

{

GridView1.EditIndex = -1;

gridbind();

}

//---- GridView Events.

//---- Code for the RowUpdating Event of the GridView Control.

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)

{

gridDataContext db = new gridDataContext();

//---- Declare variables.

Int32 id = 0;

Int32 salary = 0;

String name = String.Empty;

String designation = String.Empty;

//---- Get values for variables.

id = Convert.ToInt32(((Label)(GridView1.Rows[e.RowIndex].FindControl("lbl_IDE"))).Text);

name = ((TextBox)(GridView1.Rows[e.RowIndex].FindControl("lbl_NameE"))).Text;

designation = ((TextBox)(GridView1.Rows[e.RowIndex].FindControl("lbl_DesignationE"))).Text;

salary = Convert.ToInt32(((TextBox)(GridView1.Rows[e.RowIndex].FindControl("lbl_SalaryE"))).Text);

if (id != 0)

{

//---- Create an object of the tbemployee class.

tbemployee tb = (from p in db.tbemployees where p.ID == id select p).Single();

//---- Assign values to different properties.

tb.ID = id;

tb.Name = name;

tb.Designation = designation;

tb.Salary = salary;

db.SubmitChanges();

GridView1.EditIndex = -1;

gridbind();

}

//---- GridView Events.

//---- Code for the RowDeleting Event of the GridView Control.

protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)

{

gridDataContext db = new gridDataContext();

Int32 id = 0;

id = Convert.ToInt32(((Label)(GridView1.Rows[e.RowIndex].FindControl("lbl_IDI"))).Text);

if (id != 0)

{

tbemployee tb = (from p in db.tbemployees where p.ID == id select p).Single();

db.tbemployees.DeleteOnSubmit(tb);

db.SubmitChanges();

gridbind();

}

//---- Code to insert record into the SQL table.

protected void Button1_Click(object sender, EventArgs e)

{

gridDataContext db = new gridDataContext();

//---- Create an object of the tbemployee class.

tbemployee tb = new tbemployee();

//---- Assign values to different properties.

tb.ID = Convert.ToInt32(txtid.Text);

tb.Name = txtname.Text;

tb.Designation = txtdesi.Text;

tb.Salary = Convert.ToInt32(txtsala.Text);

db.tbemployees.InsertOnSubmit(tb);

db.SubmitChanges();

gridbind();

//---- Clear the Text Boxes after inserting record.

txtid.Text = "";

txtname.Text = "";

txtdesi.Text = "";

txtsala.Text = "";

}

So that's all.

Wish you Happy Programming.

Pritpal Singh

2009-08-17T05:31:00.000-07:00

How to bind a GridView control to a temporary table in ASP.Net
In this post, the author is trying to bind a GridView Control to a temporary table using ASP.Net with C#.Net. First of all, we will create a temporary table named 'tb'. Next we will add columns or fields to the temporary table by specifying the name of the field or column with their datatype. After successfully creating a datatable and the fields, next we will create a row 'tr' for the table. We need to specify value for each column of the row and finally add the row 'tr' to the table 'tb'.

Now we will create a Dataset 'ds' and will add the datatable 'tb' to the dataset. Then we can specify the dataset 'ds' as DataSource Property of the GridView Control and call the

Databind Method to finally bind the GridView Control to the dataset.

Namespaces required :

using System;
using System.Data;

So here is the code for .apsx.cs file :

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
//==== Create a temporary table.
DataTable tb=new DataTable();

//==== Add Columns or fields to the table.
tb.Columns.Add("Name", Type.GetType("System.String"));
tb.Columns.Add("DOB", Type.GetType("System.String"));
tb.Columns.Add("Gender", Type.GetType("System.String"));
tb.Columns.Add("Race", Type.GetType("System.String"));
tb.Columns.Add("Email", Type.GetType("System.String"));

//==== Create a DataRow for the table.
DataRow tr = tb.NewRow();

//==== Enter values for each column of the datarow.
tr["Name"] = "Pritpal Singh";
tr["DOB"] = "20 May 1985";
tr["Gender"] = "Male";
tr["Race"] = "Asian";
tr["Email"] = "pritpal@palinfocom.net";

//==== Add datarow to the table.
tb.Rows.Add(tr);

//==== Create a dataset.
DataSet ds = new DataSet();

//==== Add datatable to the dataset.
ds.Tables.Add(tb);

//==== Bind the GridView Control to the dataset.
GridView1.DataSource=ds;
GridView1.DataBind();
}
}

Add the following controls in .apsx file :
- GridView Control with ID GridView1

    <asp:GridView ID="GridView1" runat="server" BackColor="White" BorderColor="#DEDFDE"
            BorderStyle="None" BorderWidth="1px" CellPadding="4" ForeColor="Black"
            GridLines="Vertical">
        <FooterStyle BackColor="#CCCC99" />
        <RowStyle BackColor="#F7F7DE" />
        <PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />
        <SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />
        <HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />
        <AlternatingRowStyle BackColor="White" />
    </asp:GridView>

So that's all.
Wish you Happy Coding.

Pritpal Singh

2009-07-27T21:44:00.000-07:00

How to Open a PDF File in ASP.Net

To open a PDF file through code with ASP.Net , a class named WebClient Class in the System.Net namespace is required. To dynamically specify the path of the PDF file - we will be using the FileUpload Control. Further after selecting the file to open, we will make a check to selected file extension to make sure that the file selected is a PDF file using System.IO.Path.GetExtension() Method.

Just declare an object of the WebClient Class. Create a string variable to store the complete path of the PDF file.
The different Properties and Methods used in the code are explained below :
- The DownloadData Method of the WebClient Class downloads the resource from the specified path or URI as a System.Byte array.
- The ContentType property gets or sets the HTTP MIME type of the Output stream.
- The AppendHeader Method adds an HTTP Header to the Output stream.
- The BinaryWrite Method writes a string of binary characters to the HTTP Output stream.

So here is the code for .apsx.cs file :

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Net;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

protected void OpenPDF_Click(object sender, EventArgs e)
{
string _path = string.Empty;
if (FileUpload1.HasFile)
{
//----- get the extension of the file.
System.String _fileExtension = System.IO.Path.GetExtension(FileUpload1.FileName);

//----- check the extension of the file.
if (_fileExtension.ToUpper() == ".PDF")
{
_path = FileUpload1.PostedFile.FileName;

WebClient client = new WebClient();
Byte[] buffer = client.DownloadData(_path);

if (buffer != null)
{
Response.ContentType = "application/pdf";
Response.AppendHeader("content-length", buffer.Length.ToString());
Response.BinaryWrite(buffer);
}
}
else
{
//---- Selected file is not a PDF file.
Response.Write("The " + FileUpload1.FileName + " file does not appear to be a valid PDF file.");
}

}
else
{
//---- No file selected to open.
Response.Write("Please select a PDF file to open.");
}
}

Add the following controls in .apsx file :
- FileUpload Control with ID FileUpload1
- Button Control with ID OpenPDF.

So thats all.

Wish you Happy Coding.
-- Pritpal Singh

2009-07-09T22:02:00.000-07:00

How to dynamically add connection string to web.config file.

It is now possible to add a dynamically build connection string to the configuration files especially to the web.config file during the run-time by using the WebConfigurationManager class. WebConfigurationManager class simply provides access to the configuration files for web applications and belongs to the System.Web.Configuration namespace.

WebConfigurationManager class provides write option on the configuration files and make changes in any level of configuration settings with the open methods OpenWebConfiguration, OpenMachineConfiguration, OpenMappedWebConfiguration and OpenMappedMachineConfiguration. These open methods enables .Microsoft Framework programmers to write or alter any settings in the .config files.

We will use SqlConnectionStringBuilder class to build the connection string.
It is further important to note that we will use WebConfigurationManager class, OpenWebConfiguration method, call the GetSection method , then call the ConnectionStrings.Add method and last Save() method to reflect the changes.

So here is the code :

Dim strbuilder As SqlConnectionStringBuilder = New SqlConnectionStringBuilder()

'---- Provide the required parameters

strbuilder.DataSource = "your server name, port number(optional)"
strbuilder.UserID = "your username"
strbuilder.Password = "your password"
strbuilder.InitialCatalog = "your database name"

Dim webconfiguration As System.Configuration.Configuration = WebConfigurationManager.OpenWebConfiguration(Request.ApplicationPath)

Dim connections As ConnectionStringsSection =
DirectCast(webconfiguration.GetSection("connectionStrings"), ConnectionStringsSection)

'---- Check if the connectionstring of the same name exists
If connections.ConnectionStrings("your connection string name") Is Nothing Then
connections.ConnectionStrings.Add
(New ConnectionStringSettings("your connection string name", strbuilder.ConnectionString))
End If

'---- Save the webconfig file.
webconfiguration.Save()

So thats all.

Wish you Happy Coding.
-- Pritpal Singh