Hi, Dear's here we learn how to create Database Layer for Accounting Project in Visual Studio using C#, so now for creating database layer you have have c# basic(learn c# basic ). Ilyasoft software company provide full project step by step training on our YouTube Channel ilyasoft software company so now subscribe, share and like.
In this tutorial we will cover all the below mention topics:
i) How to Create Database Layer Class in visual studio?
ii) Why we are using System.Data and System.Data.SqlClient data link library in C#?
iii) How to Create Static Sql Connection to access in overall the application?
iv) How to Create Static Method for Insert Data in Sql Server Database table?
v) How to Create Static Method for update data in sql server database table?
vi) How to Create Static Method for Delete data for sql server base database table?
vii) How to Create Static Method to Select Data for sql server database table?
viii) How to Implement database layer in our project with example?
So if you want all question answers watch the video, and for source code scroll down.
Source Code Database Layer in C# / C-Sharp :
using System.Data;
using System.Data.SqlClient;
namespace POSAccounts
{
public class DatabaseLayer
{
public static SqlConnection conn; // Create Sql Connection Static Object
public static SqlConnection ConOpen()
{
if (conn == null)
{
conn = new SqlConnection(@"Data Source=.\SqlExpress;Initial Catalog=ProjectBaseAccountingMgtDb;Integrated Security=True");
// Set Connection String( Connection String the path where database is alocated)
}
if (conn.State != ConnectionState.Open) // Check Sql Connection State is open or not if not then open
{
conn.Open(); // Open Sql Connection( To Perform CRUD(Create,Retrive,Update,Delete) Operation)
}
return conn; // Return Sql Connection
}
// Create Method For Insert Data in Tables
public static bool Insert(string query)
{
try
{
// Here we are using Sql Command Second Overload constructor
SqlCommand cmd = new SqlCommand(query, ConOpen());
int noofrecordeffect = cmd.ExecuteNonQuery();
if (noofrecordeffect > 0)
{
return true;
}
else {
return false;
}
}
catch
{
return false;
}
}
// Create Method For Update Data in Tables
public static bool Update(string query)
{
try
{
// Here we are using Sql Command Second Overload constructor
SqlCommand cmd = new SqlCommand(query, ConOpen());
int noofrecordeffect = cmd.ExecuteNonQuery();
if (noofrecordeffect > 0)
{
return true;
}
else
{
return false;
}
}
catch
{
return false;
}
}
// Create Method For Delete Data From Table
public static bool Delete(string query)
{
try
{
// Here we are using Sql Command Second Overload constructor
SqlCommand cmd = new SqlCommand(query, ConOpen());
int noofrecordeffect = cmd.ExecuteNonQuery();
if (noofrecordeffect > 0)
{
return true;
}
else
{
return false;
}
}
catch
{
return false;
}
}
// Create Method to Retrive Data From Tables
public static DataTable Select(string query)
{
DataTable dt;
try
{
dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(query, ConOpen());
da.Fill(dt);
if (dt.Rows.Count > 0)
{
return dt;
}
return null;
}
catch
{
return null;
}
}
}
}
How to use Database layer in our project:
So in this example, we have category form, show below in figure:
Source Code Category Form :
using System;
using System.Data;
using System.Drawing;
using System.Runtime.InteropServices;
using System.Windows.Forms;
namespace POSAccounts.Forms.StockForms
{
public partial class FrmCategory : Form
{
public FrmCategory()
{
InitializeComponent();
PanelBar.MouseDown += PanelBar_MouseDown;
}
private const int HT_CAPTION = 0x2; // what does this means
private const int WM_NCLBUTTONDOWN = 0x00A1; // what does this means
[DllImport("user32", CharSet = CharSet.Auto)]// what does this means
private static extern bool ReleaseCapture();
[DllImport("user32", CharSet = CharSet.Auto)]// what does this means
private static extern int SendMessage(
IntPtr hwnd,
int wMsg,
int wParam,
int lParam);
private void PanelBar_MouseDown(object sender, MouseEventArgs e)
{
base.OnMouseDown(e);
if (e.Button == MouseButtons.Left)
{
Rectangle rct = DisplayRectangle;
if (rct.Contains(e.Location))
{
ReleaseCapture();
SendMessage(this.Handle, WM_NCLBUTTONDOWN, HT_CAPTION, 0);
}
}
}
private void label2_Click(object sender, EventArgs e)
{
}
private void btnClose_Click(object sender, EventArgs e)
{
this.Close(); // using to close current form
}
private void btnMaximize_Click(object sender, EventArgs e)
{
if (this.WindowState == FormWindowState.Normal)
{
this.WindowState = FormWindowState.Maximized;
}
else
{
this.WindowState = FormWindowState.Normal;
}
}
private void btnMinimize_Click(object sender, EventArgs e)
{
if (this.WindowState == FormWindowState.Minimized)
{
this.WindowState = FormWindowState.Maximized;
}
else
{
this.WindowState = FormWindowState.Minimized;
}
}
// Retrive Data From Category Table using Select Method of Database layer
private void FillGrid(string searchvalue)
{
try
{
string query = "Select CategoryID [ID], CategoryName [Category] FROM CategoryTable";
if (!string.IsNullOrEmpty(searchvalue))
{
query = "Select CategoryID [ID], CategoryName [Category] FROM CategoryTable Where CategoryName like '%" + searchvalue.Trim() + "%'";
}
DataTable dt = DatabaseLayer.Select(query);
dgv.DataSource = dt;
if (dt != null)
{
if (dt.Rows.Count > 0)
{
dgv.Columns[0].Width = 80; // CategoryID
dgv.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; // CategoryName
}
}
}
catch
{
dgv.DataSource = null;
}
}
// Valition Method for More Details Watch Vedio
private void EnableComponents()
{
btnCancel.Visible = true;
btnUpdate.Visible = true;
btnSave.Visible = false;
btnClear.Visible = false;
dgv.Enabled = false;
txtSearch.Enabled = false;
}
// Valition Method for More Details Watch Vedio
private void ResetComponents()
{
btnCancel.Visible = false;
btnUpdate.Visible = false;
btnSave.Visible = true;
btnClear.Visible = true;
dgv.Enabled = true;
txtSearch.Enabled = true;
FillGrid(string.Empty);
}
// Clear Form inpurt Controls
private void btnClear_Click(object sender, EventArgs e)
{
txtCategoryName.Clear();
}
// Filter Categories list
private void txtSearch_TextChanged(object sender, EventArgs e)
{
FillGrid(txtSearch.Text.Trim());
}
// Getting Data on Form load
private void FrmCategory_Load(object sender, EventArgs e)
{
FillGrid(string.Empty);
}
// It's Button Save Click Event, using this button we are storing data in category table
private void btnSave_Click(object sender, EventArgs e)
{
try
{
ep.Clear();
// First Check Category Textbox, if it's is empty then show message to user, plese enter category name
if (txtCategoryName.Text.Trim().Length == 0)
{
ep.SetError(txtCategoryName, "Please Enter Category Title!");
txtCategoryName.Focus();
return;
}
// Check Category is already exist in table or not
DataTable existdt = DatabaseLayer.Select("Select * From CategoryTable Where CategoryName = '" + txtCategoryName.Text.Trim() + "'");
if (existdt != null)
{
if (existdt.Rows.Count > 0)
{
ep.SetError(txtCategoryName, "Already Exist!");
txtCategoryName.Focus();
txtCategoryName.SelectAll();
return;
}
}
// Now use database layer insert method to insert database in Category Table.
string insertquery = string.Format("Insert into CategoryTable (CategoryName) values('{0}')", txtCategoryName.Text.Trim());
bool result = DatabaseLayer.Insert(insertquery);
if (result == true)
{
MessageBox.Show("Registered Successfully!");
FillGrid(string.Empty);
txtCategoryName.Clear();
}
}
catch
{
// incase of any issue is occure show this message
MessageBox.Show("Some Unexptected issue is occure plz try agian!");
}
}
// Reset form , for more details watch vedio
private void btnCancel_Click(object sender, EventArgs e)
{
ResetComponents();
}
// It's Button Update Click Event, using this button we are updating data in category table
private void btnUpdate_Click(object sender, EventArgs e)
{
try
{
ep.Clear();
// First Check Category Textbox, if it's is empty then show message to user, plese enter category name
if (txtCategoryName.Text.Trim().Length == 0)
{
ep.SetError(txtCategoryName, "Please Enter Category Title!");
txtCategoryName.Focus();
return;
}
// Check Category is already exist in table or not
DataTable existdt = DatabaseLayer.Select("Select * From CategoryTable Where CategoryName = '" + txtCategoryName.Text.Trim() + "' AND CategoryID != '"+dgv.Rows[dgv.SelectedRows[0].Index].Cells[0].Value+"'");
if (existdt != null)
{
if (existdt.Rows.Count > 0)
{
ep.SetError(txtCategoryName, "Already Exist!");
txtCategoryName.Focus();
txtCategoryName.SelectAll();
return;
}
}
// Now use database layer update method to update database in Category Table.
string updatequery = string.Format("Update CategoryTable set CategoryName = '{0}' where CategoryID = '{1}'", txtCategoryName.Text.Trim(), dgv.Rows[dgv.SelectedRows[0].Index].Cells[0].Value);
bool result = DatabaseLayer.Update(updatequery);
if (result == true)
{
MessageBox.Show("Updated Successfully!");
txtCategoryName.Clear();
ResetComponents();
}
}
catch
{
// incase of any issue is occure show this message
MessageBox.Show("Some Unexptected issue is occure plz try agian!");
}
}
// it's context menu strip edit tool click event, using to select record to edit
private void editToolStripMenuItem_Click(object sender, EventArgs e)
{
if (dgv != null)
{
if (dgv.Rows.Count > 0)
{
if (dgv.SelectedRows.Count == 1)
{
if (MessageBox.Show("Are you sure you want to update selected Category?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
txtCategoryName.Text = dgv.Rows[dgv.SelectedRows[0].Index].Cells[1].Value.ToString();
EnableComponents();
}
}
else
{
MessageBox.Show("Please Select One Record!");
}
}
}
}
}
}
Comments
Post a Comment