I would like to ask your help about saving in ado.net. I was able to update it only on the display but when I check the database, it is not updated. I paste below my code and hopefully you can help me.
Expand|Select|Wrap|Line Numbers
- using System;
- using System.Collections.Generic;
- using System.Text;
- using System.Data;
- using System.Data.SqlClient;
- namespace ADOTest5
- {
- class Connect
- {
- //declare member var
- private SqlConnection _con;
- private SqlDataAdapter _da;
- private DataSet _ds;
- private DataTable _dt;
- private string con_string;
- private string com_string;
- //private System.Windows.Forms.ListBox listBox;
- public Connect()
- {
- con_string = global::ADOTest5.Properties.Settings.Default.MyDatabaseConnectionString;
- com_string = "SELECT * FROM dbo.Customers";
- //this.listBox = new System.Windows.Forms.ListBox();
- }
- public bool GetConnected()
- {
- try
- {
- _con = new SqlConnection(con_string);
- _da = new SqlDataAdapter(com_string, con_string);
- _con.Open();
- //populate dataset
- //_ds = new DataSet();
- //_da.Fill(_ds, "Customers");
- InitializeCommands();
- }
- catch(Exception ex)
- {
- Console.WriteLine("Connection Error" + ex.Message);
- _con = null;
- return false;
- }
- return true;
- }
- public void GetClosed()
- {
- if (_con != null)
- {
- _con.Close();
- _con = null;
- }
- }
- //fill the listbox
- public void PopulateListBox(Object obj)
- {
- //populate dataset
- _ds = new DataSet();
- _da.Fill(_ds, "Customers");
- System.Windows.Forms.ListBox listBox =
- (System.Windows.Forms.ListBox)obj;
- listBox.Items.Clear();
- _dt = _ds.Tables["Customers"];
- //Loop through the DataSet and add each row
- // to the listbox
- foreach (DataRow drow in _dt.Rows)
- {
- listBox.Items.Add(drow["Id"] + ", " + drow["Lname"] + ", " + drow["Fname"]);
- }
- //Prepare Commands
- //InitializeCommands();
- }
- //Bind parameters to each column;params means variable # of param
- public void AddParams(SqlCommand cmd, params string[] cols)
- {
- //add each parameter
- cmd.Parameters.Add("@Id", SqlDbType.Int, 0, "Id");
- cmd.Parameters.Add("@Fname", SqlDbType.NVarChar, 9, "Fname");
- cmd.Parameters.Add("@Lname", SqlDbType.NVarChar, 9, "Lname");
- //foreach (String column in cols)
- //{
- // cmd.Parameters.Add("@", SqlDbType.NVarChar, 0, column);
- //}
- }
- public void InitializeCommands()
- {
- //prepare UpdateCommand
- _da.UpdateCommand = _con.CreateCommand();
- _da.UpdateCommand.CommandText =
- "UPDATE [dbo].[Customers] SET [Fname] = @Fname, [Lname] = @Lname" +
- " WHERE ([Id] = @Id)";
- AddParams(_da.UpdateCommand, "Fname", "Lname");
- }
- // Fill the firsname/lastname textbox
- public void FillInfo(Object obj1, Object obj2, int index)
- {
- // cast to textbox; for fname
- System.Windows.Forms.TextBox Tbox1 =
- (System.Windows.Forms.TextBox) obj1;
- //for last name
- System.Windows.Forms.TextBox Tbox2 =
- (System.Windows.Forms.TextBox)obj2;
- Tbox1.Text = _dt.Rows[index]["Fname"].ToString();
- Tbox2.Text = _dt.Rows[index]["Lname"].ToString();
- }
- public void SaveChanges(Object obj1, Object obj2, int index)
- {
- // cast to textbox; for fname
- System.Windows.Forms.TextBox Tbox1 =
- (System.Windows.Forms.TextBox) obj1;
- //for last name
- System.Windows.Forms.TextBox Tbox2 =
- (System.Windows.Forms.TextBox)obj2;
- DataRow row = _dt.Rows[index];
- row.BeginEdit();
- row["Fname"] = Tbox1.Text;
- row["Lname"] = Tbox2.Text;
- row.EndEdit();
- _da.Update(_ds, "Customers");
- _ds.AcceptChanges();
- }
- }
- }
- using System;
- using System.Collections.Generic;
- using System.ComponentModel;
- using System.Data;
- using System.Drawing;
- using System.Text;
- using System.Windows.Forms;
- namespace ADOTest5
- {
- public partial class Form1 : Form
- {
- Connect c;
- int index = 0;
- public Form1()
- {
- InitializeComponent();
- c = new Connect();
- }
- private void LoadButton_Click(object sender, EventArgs e)
- {
- //Connect c = new Connect();
- if (c.GetConnected())
- {
- c.PopulateListBox(this.listBox1);
- c.GetClosed();
- }
- }
- private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
- {
- //display the index of selected items
- //this.FirstNameTbox.Text = this.listBox1.SelectedIndex.ToString();
- //int index = this.listBox1.SelectedIndex;
- index = this.listBox1.SelectedIndex;
- c.FillInfo(this.FirstNameTbox, this.LastNameTbox, index);
- }
- private void SaveButton_Click(object sender, EventArgs e)
- {
- if (c.GetConnected())
- {
- c.SaveChanges(this.FirstNameTbox, this.LastNameTbox, index);
- c.PopulateListBox(this.listBox1);
- }
- c.GetClosed();
- //c.SaveChanges(this.FirstNameTbox, this.LastNameTbox, index);
- //c.PopulateListBox(this.listBox1);
- }
- }
- }