using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace preApp
{
public class Register1
{
public int id { get; set; }
public string name { get; set; }
public string adress { get; set; }
public decimal phon { get; set; }
public string email { get; set; }
public decimal pin { get; set; }
public string password { set; get; }
public string rol { set; get; }
}
public class DataAccessLayer
{
public static List<Register1> getData()
{
List<Register1> register = new List<Register1>();
string connstr = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
using (SqlConnection con = new SqlConnection(connstr))
{
SqlCommand cmd = new SqlCommand("select * from dbo.Register", con);
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.HasRows)
{
Register1 reg = new Register1();
reg.id = Convert.ToInt16(dr["id"]);
reg.name = dr["name"].ToString();
reg.adress = dr["adress"].ToString();
reg.phon = Convert.ToDecimal(dr["phon"]);
reg.email = dr["email"].ToString();
reg.pin = Convert.ToDecimal(dr["pin"]);
reg.password = dr["password"].ToString();
reg.rol = dr["rol"].ToString();
register.Add(reg);
}
}
return register;
}
public static void delData(int id)
{
string cs = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand("delete from Register where id= @id", con);
SqlParameter parm = new SqlParameter("@id", id);
cmd.Parameters.Add(parm);
con.Open();
cmd.ExecuteNonQuery();
}
}
public static int updateData(int id, string name, string adress, decimal phon, string email, decimal pin, string password, string rol)
{
string cs = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
using (SqlConnection con = new SqlConnection(cs))
{
string updateQuery = "update Register set name=@name, adress=@adress, phon=@phon, email=@email, pin=@pin, password=@password, rol=@rol where id= @id";
SqlCommand cmd = new SqlCommand(updateQuery, con);
SqlParameter parmId = new SqlParameter("@id", id);
cmd.Parameters.Add(parmId);
SqlParameter parmName = new SqlParameter("@name", name);
cmd.Parameters.Add(parmName);
SqlParameter parmAdress = new SqlParameter("@adress", adress);
cmd.Parameters.Add(parmAdress);
SqlParameter parmPhon = new SqlParameter("@phon", phon);
cmd.Parameters.Add(parmPhon);
SqlParameter parmEmail = new SqlParameter("@email", email);
cmd.Parameters.Add(parmEmail);
SqlParameter parmPin = new SqlParameter("@pin", pin);
cmd.Parameters.Add(parmPin);
SqlParameter parmPassword = new SqlParameter("@password", password);
cmd.Parameters.Add(parmPassword);
SqlParameter parmRol = new SqlParameter("@rol", rol);
cmd.Parameters.Add(parmRol);
con.Open();
return cmd.ExecuteNonQuery();
}
}
public static int insertData(string name, string adress, decimal phon, string email, decimal pin, string password, string rol)
{
string cs = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
using (SqlConnection con = new SqlConnection(cs))
{
string insertQuery = "insert into Register(name, adress, phon, email, pin, password, role) values(@name, @adress, @phon, @email, @pin, @password, @rol)";
SqlCommand cmd = new SqlCommand(insertQuery, con);
SqlParameter parmName = new SqlParameter("@name", name);
cmd.Parameters.Add(parmName);
SqlParameter parmAdress = new SqlParameter("@adress", adress);
cmd.Parameters.Add(parmAdress);
SqlParameter parmPhon = new SqlParameter("@phon", phon);
cmd.Parameters.Add(parmPhon);
SqlParameter parmEmail = new SqlParameter("@email", email);
cmd.Parameters.Add(parmEmail);
SqlParameter parmPin = new SqlParameter("@pin", pin);
cmd.Parameters.Add(parmPin);
SqlParameter parmPassword = new SqlParameter("@password", password);
cmd.Parameters.Add(parmPassword);
SqlParameter parmRol = new SqlParameter("@rol", rol);
cmd.Parameters.Add(parmRol);
con.Open();
return cmd.ExecuteNonQuery();
}
}
}
}