DataBase Connection MS Access with ASP.NET C#

DataBase Connection MS Access with ASP.NET C#



Source Code Download.Simply Click Download.


Note: If you want Documents of this code then join our Groups

Click to join DotNetBD_VB_CsHARP

----------------------------------------------------------------------------------------------

In this article, we will see how to create a database connection between ASP.NET(C#).We will explore design a website and write server code for this project.

In this articles insert, update, delete and search data from MS access database ASP.NET 2.0 and ASP.NET 3.5 (language C#).we had discussed how to save and retrieve images in the database. In this article, we will build on that concept and use the same technique to display data in a grid view.

We will learn how to insert an data and then display the data on the same page with details view . At the end of this article, you will also learn how to use the database in ASP.NET 2.0 and ASP.NET 3.5 (language C#). If you are unfamiliar with the gridview control, I suggest you to read the gridview control in ASP.NET 3.5 to get an understanding of the same

Let us start off by first creating a sample database and adding a table to it. We will call the database ‘SampleDB’ and the table will be called ‘Info_TBL’. This table will contain some column along with some other columns.

Step 1: Create a new ASP.NET website. In the code-behind, add the following namespace

C#

using System.Data.OleDb;

Step 2: Drag and drop table, label, textbox and button control as much we need. Also drag drop some validation control as we need. The source would look similar to the following:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

And Snap shot of this code is

Connection string is

String ConnectionStringSampleDB = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\\DatabaseDB.mdb;Persist Security Info=True";

Step 3: In the button click event, add the following code:

C#

protected void btnInsert_Click(object sender, EventArgs e)
{

OleDbConnection cn = new OleDbConnection(ConnectionStringSampleDB);//create connection
cn.Open();//Open connection
string strSave = "insert into Info_TBL Values(";
strSave = strSave + "'" + txtName.Text + "'";
strSave = strSave + ",'" + txtEmail.Text + "'";
strSave = strSave + ",'" + txtPhone.Text + "'";
strSave = strSave + ",'" + txtAdd.Text + "'";
strSave = strSave + ",'" + txtJob.Text + "'";
strSave = strSave + ",'" + txtComments.Text + "')";

OleDbCommand cmd = new OleDbCommand(strSave, cn);//create command
cmd.ExecuteNonQuery();//Finally execute
cn.Close();//connection close

lblmgs.Text = "Succesfully Insert Data";
LoadData();
Clear_Data();
}
protected void btnUpdate_Click(object sender, EventArgs e)
{

OleDbConnection cn = new OleDbConnection(ConnectionStringSampleDB);
cn.Open();//Open connection

string strUpdate = "update Info_TBL set Email = '" + txtEmail.Text + "',Phone ='" + txtPhone.Text + " ',Address ='" + txtAdd.Text + "',Job ='" + txtJob.Text + "'where Name ='" + txtName.Text + "'";
OleDbCommand sqlCmd = new OleDbCommand(strUpdate, cn);
sqlCmd.ExecuteNonQuery();

lblmgs.Text = "Update successfully";
cn.Close();
LoadData();

}
protected void btnDelete_Click(object sender, EventArgs e)
{
if (txtName.Text == "")
{
lblmgs.Text = "You need to Put Name!!!";
}
else
{

OleDbConnection cn = new OleDbConnection(ConnectionStringSampleDB);
cn.Open();//Open connection

string strDel = "Delete from Info_TBL where Name='" + txtName.Text + "'";
OleDbCommand oleCmd = new OleDbCommand(strDel, cn);
oleCmd.ExecuteNonQuery();

lblmgs.Text = "Deleted successfully";
cn.Close();
LoadData();
}
}
protected void btnSearch_Click(object sender, EventArgs e)
{

OleDbConnection cn = new OleDbConnection(ConnectionStringSampleDB);
cn.Open();//Open connection
string strserch = "Select * from Info_TBL Where Name='" + txtSearch.Text + "'";

OleDbDataAdapter da = new OleDbDataAdapter(strserch, cn);// create data adapter
DataTable dt = new DataTable();//declare data table
DataSet ds = new DataSet();// declare a data set
da.Fill(ds, "Info_TBL");//
dt = ds.Tables["Info_TBL"];

if (dt.Rows.Count > 0)//search data is present or not
{
txtName.Text = dt.Rows[0]["Name"].ToString();
txtEmail.Text = dt.Rows[0]["Email"].ToString();
txtPhone.Text = dt.Rows[0]["Phone"].ToString();
txtAdd.Text = dt.Rows[0]["Address"].ToString();
txtJob.Text = dt.Rows[0]["Job"].ToString();
txtComments.Text = dt.Rows[0]["Comments"].ToString();

}
else
{
lblmgs.Text = "Data Not Found";
}
cn.Close();
LoadData();

}

In the code above, we are creating a connetion. In this connection four operations are performed.

Step 4: In order to display the data Gridview on the page, first we will add a Gridview then we will create another connections and query String then we will bind the whole data with Gridview.

void LoadData()

{

OleDbConnection cn = new OleDbConnection(ConnectionStringSampleDB);

cn.Open();//Open connection

string strserch = "Select * from Info_TBL";

OleDbDataAdapter da = new OleDbDataAdapter(strserch, cn);

DataSet ds = new DataSet();

da.Fill(ds, "Info_TBL");// load data into adadpter

GridView1.DataSource = ds;

GridView1.DataBind();// finally bind in datagrid

}

void Clear_Data()

{

txtName.Text = string.Empty;

txtEmail.Text = string.Empty;

txtPhone.Text = string.Empty;

txtJob.Text = string.Empty;

txtAdd.Text = string.Empty;

txtComments.Text = string.Empty;

}

Step 5: here we write a Clear_data

All then connections techniques are explain if u want to download source code of this Connection please Go to this link. Source Code