سلام. من از این روش استفاده می کنم. یک لایه برای اتصال به پایگاه داده که فقط یک کلاس Connection داره. و یک لایه برای ارسال دستورات SQL و سایر دستورات مورد نیاز برنامه که از طریق لایه ی اتصال به پایگاه داده وصل می شه، در لایه ی اتصال کدهای دیگر نمی نویسم چرا که می توان فقط با تغییر StringConnection در سایر پروژه ها هم استفاده کرد.
کد لایه ی اتصال:
using System;using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
namespace Data_Access
{
public class Connection
{
SqlConnection con;
SqlCommand cmd;
SqlDataAdapter sda;
DataTable dt;
public Connection()
{
con = new SqlConnection();
cmd = new SqlCommand();
sda = new SqlDataAdapter();
dt = new DataTable();
}
public void link()
{
con.ConnectionString= "Data Source=.;Initial Catalog=Library_mgr;Integrated Security=True";
con.Open();
}
public void unlink()
{
con.Close();
}
public void command_line(string query)
{
cmd.CommandText = query;
cmd.Connection = con;
cmd.ExecuteNonQuery();
}
public DataTable select_command(string query)
{
cmd.CommandText = query;
cmd.Connection = con;
sda = new SqlDataAdapter(cmd);
sda.Fill(dt);
return dt;
}
}
//end of class.
}
در لایه ی دیگر یا businessLayer :
using System;using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Data_Access;
namespace business_Logic.bl_users
{
public class bl_users: Connection
{
public string un, pass, email,job,status, dt;
public void add_users()
{
base.link();
string query = "insert into tbl_users(un,pass,email,job,status)values(N'{0}',N '{1}',N'{2}',N'{3}',N'{4}')";
query = string.Format(query, un, pass, email, job, status);
base.command_line(query);
base.unlink();
}
//end of adding users.
public void update_users()
{
base.link();
string query = "update tbl_users set pass=N'{0}' , email=N'{1}' , job=N'{2}' , status=N'{3}' where un=N'{4}'";
query = string.Format(query, pass, email,job, status, un);
base.select_command(query);
base.unlink();
}
//end of updating users.
public void delete_users()
{
base.link();
string query = "delete tbl_users where un=N'{0}'";
query = string.Format(query, un);
base.command_line(query);
base.unlink();
}
//end of deleting users.
public DataTable select_users()
{
base.link();
string query = "select un,pass,job,email,status,dt from tbl_users";
DataTable dt = base.select_command(query);
base.unlink();
return dt;
}
//end of selecting users.
public DataTable check_existing_users()
{
base.link();
string query = "select un from tbl_users where un=N'{0}'";
query = string.Format(query, un);
DataTable dt = base.select_command(query);
base.unlink();
return dt;
}
//end of checking existing users.
public DataTable select_user_by_user()
{
base.link();
string query = "select un,pass,job,email,status,dt from tbl_users where un like N'{0}%'";
query = string.Format(query, un);
DataTable dt = base.select_command(query);
base.unlink();
return dt;
}
//end of selecting user by user name.
public DataTable select_user_by_job()
{
base.link();
string query = "select un,pass,job,email,status,dt from tbl_users where job=N'{0}'";
query = string.Format(query, job);
DataTable dt = select_command(query);
base.unlink();
return dt;
}
//end of selecting user by job style.
public DataTable select_user_by_status()
{
base.link();
string query = "select un,pass,job,email,status from tbl_users where status=N'{0}'";
query = string.Format(query, status);
DataTable dt = select_command(query);
base.unlink();
return dt;
}
// selecting users using status if acctive or not.
public DataTable select_user_by_email()
{
base.link();
string query = "select un,pass,job,email,status,dt from tbl_users where email like N'{0%}'";
query = string.Format(query, email);
DataTable dt = base.select_command(query);
base.unlink();
return dt;
}
//loading user by email like.
public DataTable select_users_names()
{
base.link();
string query = "select un from tbl_users";
DataTable dt = base.select_command(query);
base.unlink();
return dt;
}
//end of selecting.
public DataTable select_user_by_username()
{
base.link();
string query = "select un,pass,job,email,status from tbl_users where un=N'{0}'";
query = string.Format(query, un);
DataTable dt = base.select_command(query);
base.unlink();
return dt;
}
//end of selecting user by usernames.
}
//end of class.
}
امید است که کارامد باشد برای شما.
موفق باشید.
