////////////Database///////////////
create database test ---- This is the creation of database name
use test ---- Use the created database
create table customer( ---- Create a table
cid int primary key,
fname varchar(100),
lname varchar(100),
address varchar(100),
phone int,
)
/////////////////////////////////////////////////////////////
Simply copy the above queries and open the MS-SQL and paste it. finally execute the queries.
Next step is to create a interface. It's not a difficult step to create the interface. in this case you need to create a interface like below one. but with the other projects it's your responsibility to decide what are the items that needed for the interface and how is the it's look.
Open the Microsoft Visual Studio >>>> File >>>> New >>>> Project
It will show the New Project window like below one
Select the Visual C# from the tabs and select the Windows Forms Applications. Give the Name for the application and set the location where you need to save your project and click ok. Then it will create the new project with one interface.
There is a one form all ready in the project. Lets see how to create a new Form.
Right Click on the Project name on the Solution Explorer and select Add >>>> Windows Form >>> Select Window Form and Give a name for the Form. Finally click add button.
In this case i am not going to use any other new form and i am using the default one. Now you have to create a Interface looks like a below one (Download the sample interface from here)
In the next step lets see how to take the Data Source path. Data Source Path is the path we used in the app.config file to implement the connection between application and the sql server. to do that Go to view >>> Server Explorer and in the Server Explorer right click on the Data Connections and click one the Add Connections. Choose the Server name of the Sql Server and select your database from the below drop down box in the below image and click ok.
After that right click on the database added to the Data Connections tab in the server explorer and go to properties. there you will see point called Connection String, copy that Connection String Path.
Next we need to use below code in the app.config file(Application configuration file). Application configuration file contain settings specific to an application. This file contains configuration settings that the common language runtime reads (such as assembly binding policy, remoting objects, and so on), and settings that the application can read.
Delete the "Use Your Data Source Path" In the below code and copy your Data Source path to that point. refer the below image.
<connectionStrings>
<add name="ConString" connectionString="Data Source= Use Your Data Source Path"
providerName="System.Data.SqlClient" />
</connectionStrings>
Now all the basic things are done.
Lets create the DBManager class. Go to Solution Explorer and right click on the your project(SimpleDbApplication) >> Add >> New Item>> Select the C# class and gives a name for it as a DBManager and add the class. Right click on the references on the solution explorer and select Add References. There you will see a List of configuration items. from this list select the "System.Configuration" and click ok. After that open the DBManager class and use below headers in the DBManager class.
using System.Configuration;
using System.Data.SqlClient;
The DBManager class is already created as a normal class(Private Class) so that we can't access the methods that are in the DBManager class from the other class. Because of that we need to Change it as a Public one like below code
Already In this Form
class DBManager
{
}
Change it's looks like a below one
public class DBManager
{
}
After that use the below code inside the DBManager
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
public static SqlConnection NewCon;
public static string ConStr = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
public static SqlConnection GetConnection()
{
NewCon = new SqlConnection(ConStr);
return NewCon;
}
The DBManager Class should be like below one
/////////////////////////////////////DBManager Class////////////////////////////////////////////////////////////
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace SimpleDbApplication
{
public class DBManager
{
public static SqlConnection NewCon;
public static string ConStr = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
public static SqlConnection GetConnection()
{
NewCon = new SqlConnection(ConStr);
return NewCon;
}
}
}
Ok now it's finished. but there is a DBAccess class to create. Take a another class and name it as a DBAccess. In the DBAccess Class change it as a below one
////////////////////////DBAccess////////////////////////////////////////////////////////////////////////
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace SimpleDbApplication
{
public class DBAccess
{
SqlConnection conn;
public DBAccess() {
conn = DBManager.GetConnection();
}
}
}
In this application we need to load our all the customer details to the Data Grid View when form is loading other wise anyone can't see the customer informations in the Grid View. To do this we need to add below code for the DBAccess Class.
public DataSet getCustomerDetails()
{
if (conn.State.ToString() == "Closed")
{
conn.Open();
}
SqlCommand NewCmd = new SqlCommand();
NewCmd.Connection = conn;
NewCmd.CommandType = CommandType.Text;
NewCmd.CommandText = "SELECT * FROM CUSTOMER";
SqlDataAdapter da = new SqlDataAdapter(NewCmd);
DataSet ds = new DataSet();
da.Fill(ds, "Customer");
conn.Close();
return ds;
}
And also we need to create a another methods in the DBAccess class to Add, Update, Delete details. To do that you need to use below codes.
////////////////////////////////////////////Add Customer///////////////////////////////////////////////////////////
public bool addCustomer(string cid, string fname, string lname, string address, int phone)
{
bool status = false;
if (conn.State.ToString() == "Closed")
{
conn.Close();
}
SqlCommand NewCmd = new SqlCommand();
NewCmd.Connection = conn;
NewCmd.CommandType = CommandType.Text;
NewCmd.CommandText = "INSERT INTO customer VALUES('"+cid+"','"+fname+"','"+ lname +"','"+address+"','"+phone+"')";
NewCmd.ExecuteNonQuery();
conn.Close();
}
/////////////////////////////////////////////////////Update Customer//////////////////////////////////////////////////////////////
public bool updateCustomer(string cid,string cid2,string fname, string lname, string address, int phone)
{
bool status = false;
if (conn.State.ToString() == "Closed")
{
conn.Open();
}
SqlCommand NewCmd = new SqlCommand();
NewCmd.Connection = conn;
NewCmd.CommandType = CommandType.Text;
NewCmd.CommandText = "UPDATE customer SET cid = '" + cid + "',fname = '" + fname + "',lname = '" + lname + "',address = '" + address + "',phone = '" + phone + "' where cid = '"+cid2+"'";
NewCmd.ExecuteNonQuery();
conn.Close();
return status;
}
/////////////////////////////////////////////////////////////////Delete Customer//////////////////////////////////////////////////////
public bool deteleCustomer(string cid)
{
bool status = false;
if (conn.State.ToString() == "Closed")
{
conn.Open();
}
SqlCommand NewCmd = new SqlCommand();
NewCmd.Connection = conn;
NewCmd.CommandType = CommandType.Text;
NewCmd.CommandText = "DELETE FROM customer WHERE cid = '"+cid+"'";
NewCmd.ExecuteNonQuery();
return status;
}
The DBAccess class should be like a below one after you added all above codes to the DBAccess class
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace SimpleDbApplication
{
public class DBAccess
{
SqlConnection conn;
public DBAccess()
{
conn = DBManager.GetConnection();
}
//////////////////////////////////////////////Get Customer Details////////////////////////////////////////
public DataSet getCustomerDetails()
{
if (conn.State.ToString() == "Closed")
{
conn.Open();
}
SqlCommand NewCmd = new SqlCommand();
NewCmd.Connection = conn;
NewCmd.CommandType = CommandType.Text;
NewCmd.CommandText = "SELECT * FROM CUSTOMER";
SqlDataAdapter da = new SqlDataAdapter(NewCmd);
DataSet ds = new DataSet();
da.Fill(ds, "Customer");
conn.Close();
return ds;
}
////////////////////////////////////////////Add Customer///////////////////////////////////////////////////////////
public bool addCustomer(string cid, string fname, string lname, string address, int phone)
{
bool status = true;
if (conn.State.ToString() == "Closed")
{
conn.Open();
}
SqlCommand NewCmd = new SqlCommand();
NewCmd.Connection = conn;
NewCmd.CommandType = CommandType.Text;
NewCmd.CommandText = "INSERT INTO customer VALUES('" + cid + "','" + fname + "','" + lname + "','" + address + "','" + phone + "')";
NewCmd.ExecuteNonQuery();
conn.Close();
return status;
}
/////////////////////////////////////////////////////Update Customer//////////////////////////////////////////////////////////////
public bool updateCustomer(string cid, string cid2, string fname, string lname, string address, int phone)
{
bool status = true;
if (conn.State.ToString() == "Closed")
{
conn.Open();
}
SqlCommand NewCmd = new SqlCommand();
NewCmd.Connection = conn;
NewCmd.CommandType = CommandType.Text;
NewCmd.CommandText = "UPDATE customer SET cid = '" + cid + "',fname = '" + fname + "',lname = '" + lname + "',address = '" + address + "',phone = '" + phone + "' where cid = '" + cid2 + "'";
NewCmd.ExecuteNonQuery();
conn.Close();
return status;
}
/////////////////////////////////////////////////////////////////Delete Customer//////////////////////////////////////////////////////
public bool deteleCustomer(string cid)
{
bool status = true;
if (conn.State.ToString() == "Closed")
{
conn.Open();
}
SqlCommand NewCmd = new SqlCommand();
NewCmd.Connection = conn;
NewCmd.CommandType = CommandType.Text;
NewCmd.CommandText = "DELETE FROM customer WHERE cid = '" + cid + "'";
NewCmd.ExecuteNonQuery();
return status;
}
}
}
Code Lines for the User Interface(Form)
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace SimpleDbApplication
{
public partial class Form1 : Form
{
private static string cid;
DBAccess dba = new DBAccess();
public Form1()
{
InitializeComponent();
fillGridView();
}
private void Form1_Load(object sender, EventArgs e)
{
}
void fillGridView() // Fill the grid view
{
DataSet ds = dba.getCustomerDetails();
dgvShowCustomer.DataSource = ds.Tables["Customer"].DefaultView;
}
// Add Customers to the system
private void btnInsert_Click(object sender, EventArgs e)
{
dba.addCustomer(txtCid.Text,txtFname.Text,txtLname.Text,txtAddress.Text,Int32.Parse(txtPhone.Text));
fillGridView();
}
private void dgvShowCustomer_RowHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
{
cid = txtCid.Text = dgvShowCustomer.CurrentRow.Cells["column1"].Value.ToString();
txtCid.Text = dgvShowCustomer.CurrentRow.Cells["column1"].Value.ToString();
txtFname.Text = dgvShowCustomer.CurrentRow.Cells["column3"].Value.ToString();
txtLname.Text = dgvShowCustomer.CurrentRow.Cells["column4"].Value.ToString();
txtAddress.Text = dgvShowCustomer.CurrentRow.Cells["column5"].Value.ToString();
txtPhone.Text = dgvShowCustomer.CurrentRow.Cells["column6"].Value.ToString();
}
// update customers from the system
private void btnUpdate_Click(object sender, EventArgs e)
{
dba.updateCustomer(txtCid.Text,cid,txtFname.Text,txtLname.Text,txtAddress.Text,Int32.Parse(txtPhone.Text));
fillGridView();
}
// delete customers from the system
private void btnDelete_Click(object sender, EventArgs e)
{
if (MessageBox.Show("Are you sure you want to delete this customer", "Delete", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning) == DialogResult.OK)
{
dba.deteleCustomer(cid);
fillGridView();
}
}
}
}
Download the full Project from here here
0 comments:
Post a Comment