The UpdateProduct Method (C#)
public class ProductDAL
{
...
public static void UpdateProduct(int original_ProductID,
string productName, decimal unitPrice, int unitsInStock)
{
// Updates the Products table
string sql = "UPDATE Products SET ProductName = " +
"@ProductName, UnitPrice = @UnitPrice, UnitsInStock = " +
"@UnitsInStock WHERE ProductID = @ProductID";
using (SqlConnection myConnection =
new
SqlConnection(ConfigurationManager.ConnectionStrings["NWConnectionString"].ConnectionString))
{
SqlCommand myCommand = new SqlCommand(sql, myConnection);
myCommand.Parameters.Add(new SqlParameter("@ProductName",
productName));
myCommand.Parameters.Add(new SqlParameter("@UnitPrice",
unitPrice));
myCommand.Parameters.Add(new SqlParameter("@UnitsInStock",
unitsInStock));
myCommand.Parameters.Add(new SqlParameter("@ProductID",
original_ProductID));
myConnection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();
}
}
}
----------------------------------------------------------------------------------------------------
public void DAL_UpdateStudentsTable(DataTable table)
{
using (SqlConnection sqlConn = new SqlConnection(connString))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = @"UPDATE Students SET " +
"StudentID = @id, " +
"FirstName = @first, " +
"LastName = @last, " +
"Birthday = @birthday, " +
"PersonalNo = @personal " +
"WHERE StudentID = @oldId";
cmd.Parameters.Add("@id", SqlDbType.Int, 5, "StudentID");
cmd.Parameters.Add("@first", SqlDbType.VarChar, 50, "FirstName");
cmd.Parameters.Add("@last", SqlDbType.VarChar, 50, "LastName");
cmd.Parameters.Add("@birthday", SqlDbType.DateTime, 1, "Birthday");
cmd.Parameters.Add("@personal", SqlDbType.VarChar, 50, "PersonalNo");
SqlParameter param = cmd.Parameters.Add("@oldId", SqlDbType.Int, 5, "StudentID");
param.SourceVersion = DataRowVersion.Original;
cmd.Connection = sqlConn;
using (SqlDataAdapter da = new SqlDataAdapter())
{
da.UpdateCommand = cmd;
da.Update(table);
}
}
}
}
----------------------------------------------------------------------------------------------
A sample code
using System;
using System.Collections.Generic;
using System.Collections;
using System.Text;
using KMS.DAO;
using KMS.Database_Connection;
using System.Data.Odbc;
using KMS.ENTITY;
using KMS.CommanUtility;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
namespace KMS.DAO
{
public class AHBDAO
{
Services servi = new Services();
/*Insert a row into database*/
public void Insert(AHBEntity obj)
{
int rwaffect;
try
{
String insertStr = "INSERT INTO tbluser (" +
"fullname,pkuserid,name,ITZone,ministry,offices,Address,Email" +
") VALUES ('" + obj.Name + "'," + obj.Pkuserid + ",'" + obj.Name + "','" + obj.Zone + "'," + obj.Ministry + ",'" + obj.Offices + "','" + obj.Address1 + "','" + obj.Email1 + "')";
rwaffect = servi.ExecuteQuery(insertStr);
servi.CloseResource();
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}
/*Delete a row from database*/
public void delete(AHBEntity del)
{
try
{
int rwaffect;
String deleteStr = "DELETE FROM tbluser WHERE pkid="+del.Pkuserid+" ";
rwaffect = servi.ExecuteQuery(deleteStr);
servi.CloseResource();
}
catch
{
}
}
/*Set current properties from database*/
//public AHBEntity select(AHBEntity s)
//{
// // AHBEntity AHBobj = null;
// //// String selectStr = "SELECT u.pkuserid,u.name, u.ITZone, u.ministry, " +
// // "u.Offices, u.Address, u.Email,d.designation,d.phone,d.phone2 FROM tbluser u,mstbldesignation d" +
// // " WHERE pkuserid=" + s.Pkuserid + " and u.pkuserid="+s.Pkid+"";
// // OdbcDataReader result;
// // result = servi.ExecuteReader(selectStr);
// // if (result.HasRows)
// // {
// // AHBobj = new AHBEntity();
// // //iteminpanelobj = new TbliteminpanelEntity();
// // AHBobj.Pkuserid = (result.IsDBNull(0) ? "" : Convert.ToString(result.GetDecimal(0)));
// // AHBobj.Name = (result.IsDBNull(1) ? "" : result.GetString(1));
// // AHBobj.Zone = (result.IsDBNull(2) ? "" :Convert.ToString(result.GetInt32(2)));
// // AHBobj.Ministry = (result.IsDBNull(3) ? "" : Convert.ToString(result.GetInt32(3)));
// // AHBobj.Offices = (result.IsDBNull(4) ? "" : result.GetString(4));
// // AHBobj.Address1 = (result.IsDBNull(5) ? "" : result.GetString(5));
// // AHBobj.Email1 = (result.IsDBNull(6) ? "" : result.GetString(6));
// // AHBobj.Designation = (result.IsDBNull(7) ? "" : result.GetString(7));
// // AHBobj.Phone = (result.IsDBNull(8) ? "" : result.GetString(8));
// // AHBobj.Phone2 = (result.IsDBNull(9) ? "" : result.GetString(9));
// // }
// // servi.CloseResource();
// // return AHBobj;
//}
/*Update database*/
public int update(AHBEntity obj)
{
int rawAffected;
String updateStr = "UPDATE tbluser SET " +
"pkuserid=?,name=?,ITzone=?,ministry=?,offices=?,Address=?,Email=? WHERE pkuserid=? ";
rawAffected = servi.ExecuteQuery(updateStr);
servi.CloseResource();
return rawAffected;
}
/*Auto-Increment Id*/
public int selectMaxPkId()
{
int maxpkid = 0;
String selectStmt = "select max(pkuserid) from tbluser";
OdbcDataReader result;
result = servi.ExecuteReader(selectStmt);
if (result.HasRows)
{
maxpkid = result.GetInt32(0);
maxpkid++;
}
return maxpkid;
}
/*List value*/
public ArrayList listAHB()
{
OdbcDataReader result;
ArrayList listAHB = new ArrayList();
AHBEntity AHBobj;
String selectSt = "SELECT u.pkuserid,u.name, u.ITZone, u.ministry, u.Offices, u.Address, u.Email,d.designation,d.phone,d.phone2 FROM tbluser u,mstbldesignation d WHERE u.pkuserid = d.pkid";
result = servi.ExecuteReader(selectSt);
if (result.HasRows)
{
while (result.Read())
{
AHBobj = new AHBEntity();
AHBobj.Pkuserid = (result.IsDBNull(0) ? "" : Convert.ToString(result.GetDecimal(0)));
AHBobj.Name = (result.IsDBNull(1) ? "" : result.GetString(1));
AHBobj.Zone = (result.IsDBNull(2) ? "" : Convert.ToString(result.GetInt32(2)));
AHBobj.Ministry = (result.IsDBNull(3) ? "" : Convert.ToString(result.GetInt32(3)));
AHBobj.Offices = (result.IsDBNull(4) ? "" : result.GetString(4));
AHBobj.Address1 = (result.IsDBNull(5) ? "" : result.GetString(5));
AHBobj.Email1 = (result.IsDBNull(6) ? "" : result.GetString(6));
AHBobj.Designation = (result.IsDBNull(7) ? "" : result.GetString(7));
AHBobj.Phone = (result.IsDBNull(8) ? "" : result.GetString(8));
AHBobj.Phone2 = (result.IsDBNull(9) ? "" : result.GetString(9));
listAHB.Add(AHBobj);
}
}
servi.CloseResource();
return listAHB;
}
}
}
--------------------------------------------------
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
FillGridView();
}
}
/// <summary>
/// Fill record in gridview
/// </summary>
public void FillGridView()
{
try
{
string cnString = ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString;
SqlConnection con = new SqlConnection(cnString);
GlobalClass.adap = new SqlDataAdapter("select * from gridvew", con);
SqlCommandBuilder bui = new SqlCommandBuilder(GlobalClass.adap);
GlobalClass.dt = new DataTable();
GlobalClass.adap.Fill(GlobalClass.dt);
GridView1.DataSource = GlobalClass.dt;
GridView1.DataBind();
}
catch
{
}
}
protected void editRecord(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
FillGridView();
}
protected void cancelRecord(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
FillGridView();
}
/// <summary>
/// New Row Add
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void AddNewRecord(object sender, EventArgs e)
{
try
{
if (GlobalClass.dt.Rows.Count > 0)
{
GridView1.EditIndex = -1;
GridView1.ShowFooter = true;
FillGridView();
}
else
{
GridView1.ShowFooter = true;
DataRow dr = GlobalClass.dt.NewRow();
dr[1] = "0";
dr[2] = 0;
dr[3] = 0;
dr[4] = "0";
dr[5] = "0";
GlobalClass.dt.Rows.Add(dr);
GridView1.DataSource = GlobalClass.dt;
GridView1.DataBind();
GridView1.Rows[0].Visible = false;
}
}
catch
{
}
}
/// <summary>
/// New Record Cancel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void AddNewCancel(object sender, EventArgs e)
{
GridView1.ShowFooter = false;
FillGridView();
}
/// <summary>
/// Insert New Record
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void InsertNewRecord(object sender, EventArgs e)
{
try
{
string strName = GlobalClass.dt.Rows[0]["name"].ToString();
if (strName == "0")
{
GlobalClass.dt.Rows[0].Delete();
GlobalClass.adap.Update(GlobalClass.dt);
}
TextBox txtName = GridView1.FooterRow.FindControl("txtNewName") as TextBox;
TextBox txtAge = GridView1.FooterRow.FindControl("txtNewAge") as TextBox;
TextBox txtSalary = GridView1.FooterRow.FindControl("txtNewSalary") as TextBox;
TextBox txtCountry = GridView1.FooterRow.FindControl("txtNewCountry") as TextBox;
TextBox txtCity = GridView1.FooterRow.FindControl("txtNewCity") as TextBox;
DataRow dr = GlobalClass.dt.NewRow();
dr["name"] = txtName.Text.Trim();
dr["age"] = txtAge.Text.Trim();
dr["salary"] = txtSalary.Text.Trim();
dr["country"] = txtCountry.Text.Trim();
dr["city"] = txtCity.Text.Trim();
GlobalClass.dt.Rows.Add(dr);
GlobalClass.adap.Update(GlobalClass.dt);
GridView1.ShowFooter = false;
FillGridView();
}
catch
{
}
}
/// <summary>
/// Record Updation
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void updateRecord(object sender, GridViewUpdateEventArgs e)
{
try
{
TextBox txtName = GridView1.Rows[e.RowIndex].FindControl("txtName") as TextBox;
TextBox txtAge = GridView1.Rows[e.RowIndex].FindControl("txtAge") as TextBox;
TextBox txtSalary = GridView1.Rows[e.RowIndex].FindControl("txtSalary") as TextBox;
TextBox txtCountry = GridView1.Rows[e.RowIndex].FindControl("txtCountry") as TextBox;
TextBox txtCity = GridView1.Rows[e.RowIndex].FindControl("txtCity") as TextBox;
GlobalClass.dt.Rows[GridView1.Rows[e.RowIndex].RowIndex]["name"] = txtName.Text.Trim();
GlobalClass.dt.Rows[GridView1.Rows[e.RowIndex].RowIndex]["age"] = Convert.ToInt32(txtAge.Text.Trim());
GlobalClass.dt.Rows[GridView1.Rows[e.RowIndex].RowIndex]["salary"] = Convert.ToInt32(txtSalary.Text.Trim());
GlobalClass.dt.Rows[GridView1.Rows[e.RowIndex].RowIndex]["country"] = txtCountry.Text.Trim();
GlobalClass.dt.Rows[GridView1.Rows[e.RowIndex].RowIndex]["city"] = txtCity.Text.Trim();
GlobalClass.adap.Update(GlobalClass.dt);
GridView1.EditIndex = -1;
FillGridView();
}
catch
{
}
}
/// <summary>
/// Record Deletion
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void RowDeleting(object sender, GridViewDeleteEventArgs e)
{
try
{
GlobalClass.dt.Rows[GridView1.Rows[e.RowIndex].RowIndex].Delete();
GlobalClass.adap.Update(GlobalClass.dt);
FillGridView();
}
catch
{
}
}
}
-----------------------------------------------------------------------------------------
No comments:
Post a Comment