Read data from database
Code
public ActionResult Index()
{
List<CustomerModel> customers = new List<CustomerModel>();
string connstr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (MySqlConnection con = new MySqlConnection(connstr))
{
string query = "SELECT id,fullname, username FROM tbUser";
using (MySqlCommand cmd = new MySqlCommand(query))
{
cmd.Connection = con;
con.Open();
using (MySqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
customers.Add(new CustomerModel
{
id = Convert.ToInt32(sdr["id"]),
fullname = sdr["fullname"].ToString(),
username = sdr["username"].ToString()
});
}
}
con.Close();
}
}
return View(customers);
}
Picture
Example 2
public ActionResult Index()
{
List<Customer> customers = new List<Customer>();
string connstr = ConfigurationManager.ConnectionStrings["conndbstr"].ConnectionString;
using (SqlConnection con = new SqlConnection(connstr))
{
string query = "SELECT CusID,FullName,NickName,Company,Phone,Fax,Mobile,Email,Address,TaxReg,Branch,Remark,Status FROM Customer";
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Connection = con;
con.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
customers.Add(new Customer
{
CusID = sdr["CusID"].ToString(),
FullName = sdr["FullName"].ToString(),
NickName = sdr["NickName"].ToString(),
Company = sdr["Company"].ToString(),
Phone = sdr["Phone"].ToString(),
Fax = sdr["Fax"].ToString(),
Mobile = sdr["Mobile"].ToString(),
Email = sdr["Email"].ToString(),
Address = sdr["Address"].ToString()
});
}
}
con.Close();
}
}
return View(customers);
}
Picture
Insert to database
Code
public ActionResult Create(CustomerModel users)
{
//List<CustomerModel> users = new List<CustomerModel>();
string connstr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (MySqlConnection con = new MySqlConnection(connstr))
{
string query = "INSERT INTO tbUser(fullname,username,password,Role) VALUES(@fullname,@username,@password,@Role)";
using (MySqlCommand cmd = new MySqlCommand(query))
{
bool status = false;
if (ModelState.IsValid)
{
cmd.Connection = con;
con.Open();
cmd.Parameters.AddWithValue("@fullname", users.fullname);
cmd.Parameters.AddWithValue("@username", users.username);
cmd.Parameters.AddWithValue("@password", EncryptString.Encrypt.EncryptText(users.password));
cmd.Parameters.AddWithValue("@Role", users.Role);
users.id = Convert.ToInt32(cmd.ExecuteNonQuery());
con.Close();
status = true;
}
}
}
return View(users);
}
Picture
Delete from database
Code
public ActionResult DeleteConfirmed(string id)
{
List<CustomerModel> users = new List<CustomerModel>();
string connstr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (MySqlConnection con = new MySqlConnection(connstr))
{
using (MySqlCommand cmd = new MySqlCommand("DELETE FROM tbUser WHERE id = @id"))
{
using (MySqlDataAdapter sda = new MySqlDataAdapter())
{
bool status = false;
cmd.Parameters.AddWithValue("@id", id);
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
status = true;
}
}
}
//tbInvoice tbInvoice = db.tbInvoice.Find(id);
//db.tbInvoice.Remove(tbInvoice);
//db.SaveChanges();
return RedirectToAction("Index");
}
Picture
