By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,190 Members | 801 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,190 IT Pros & Developers. It's quick & easy.

How to insert, update and delete values using Datagridview?

P: 7
Hi Friends, The below code is a sample code for insert, update and delete using datagrid but i need to convert the entire code for datagridview.. Plsss help me..




Expand|Select|Wrap|Line Numbers
  1. using System;
  2. using System.Drawing;
  3. using System.Collections;
  4. using System.ComponentModel;
  5. using System.Windows.Forms;
  6. using System.Data;
  7. using System.Data.OleDb;
  8. using System.IO;
  9.  
  10.  
  11. namespace UsingDataGridForBeginners
  12. {
  13.     /// <summary>
  14.     /// Summary description for Form1.
  15.     /// </summary>
  16.     public class clsDataGridForm : System.Windows.Forms.Form
  17.     {
  18.       private System.Windows.Forms.DataGrid dataGrid1;
  19.       private System.Windows.Forms.Button btExit;
  20.       private System.Windows.Forms.Button btInsertnew;
  21.       private System.Windows.Forms.Button btUpdate;
  22.       private System.Windows.Forms.Button btDelete;
  23.       private System.Windows.Forms.Button btRefresh;
  24.       private System.Windows.Forms.Button btCopyToXml;
  25.       private System.Data.OleDb.OleDbCommand oleDbSelectCommand1;
  26.       private System.Data.OleDb.OleDbCommand oleDbInsertCommand1;
  27.       private System.Data.OleDb.OleDbCommand oleDbUpdateCommand1;
  28.       private System.Data.OleDb.OleDbCommand oleDbDeleteCommand1;
  29.       private System.Data.OleDb.OleDbConnection oleDbConnection1;
  30.       private System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter1;
  31.       private UsingDataGridForBeginners.DataSet1 dataSet11;
  32.       private int iRowIndex=0;
  33.       private DataTable dtbl=new DataTable();
  34.       private DataTable tbl=new DataTable("MyContactsTable");
  35.       private System.Windows.Forms.Button btSearch;
  36.       private System.Windows.Forms.Button btFromXML;
  37.  
  38.  
  39.         /// <summary>
  40.         /// Required designer variable.
  41.         /// </summary>
  42.         private System.ComponentModel.Container components = null;
  43.  
  44.         public clsDataGridForm()
  45.         {
  46.             //
  47.             // Required for Windows Form Designer support
  48.             //
  49.             InitializeComponent();
  50.  
  51.             //
  52.             // TODO: Add any constructor code after InitializeComponent call
  53.             //
  54.         }
  55.  
  56.         /// <summary>
  57.         /// Clean up any resources being used.
  58.         /// </summary>
  59.         protected override void Dispose( bool disposing )
  60.         {
  61.             if( disposing )
  62.             {
  63.                 if (components != null) 
  64.                 {
  65.                     components.Dispose();   
  66.                 }
  67.             }
  68.             base.Dispose( disposing );
  69.         }
  70.  
  71.         #region Windows Form Designer generated code
  72.         /// <summary>
  73.         /// Required method for Designer support - do not modify
  74.         /// the contents of this method with the code editor.
  75.         /// </summary>
  76.         private void InitializeComponent()
  77.         {
  78.           this.dataGrid1 = new System.Windows.Forms.DataGrid();
  79.           this.dataSet11 = new UsingDataGridForBeginners.DataSet1();
  80.           this.btInsertnew = new System.Windows.Forms.Button();
  81.           this.btUpdate = new System.Windows.Forms.Button();
  82.           this.btDelete = new System.Windows.Forms.Button();
  83.           this.btFromXML = new System.Windows.Forms.Button();
  84.           this.btRefresh = new System.Windows.Forms.Button();
  85.           this.btCopyToXml = new System.Windows.Forms.Button();
  86.           this.btExit = new System.Windows.Forms.Button();
  87.           this.oleDbSelectCommand1 = new System.Data.OleDb.OleDbCommand();
  88.           this.oleDbConnection1 = new System.Data.OleDb.OleDbConnection();
  89.           this.oleDbInsertCommand1 = new System.Data.OleDb.OleDbCommand();
  90.           this.oleDbUpdateCommand1 = new System.Data.OleDb.OleDbCommand();
  91.           this.oleDbDeleteCommand1 = new System.Data.OleDb.OleDbCommand();
  92.           this.oleDbDataAdapter1 = new System.Data.OleDb.OleDbDataAdapter();
  93.           this.btSearch = new System.Windows.Forms.Button();
  94.           ((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
  95.           ((System.ComponentModel.ISupportInitialize)(this.dataSet11)).BeginInit();
  96.           this.SuspendLayout();
  97.           // 
  98.           // dataGrid1
  99.           // 
  100.           this.dataGrid1.AllowSorting = false;
  101.           this.dataGrid1.AlternatingBackColor = System.Drawing.Color.FromArgb(((System.Byte)(255)), ((System.Byte)(255)), ((System.Byte)(192)));
  102.           this.dataGrid1.BackgroundColor = System.Drawing.SystemColors.Control;
  103.           this.dataGrid1.CaptionBackColor = System.Drawing.Color.FromArgb(((System.Byte)(0)), ((System.Byte)(0)), ((System.Byte)(64)));
  104.           this.dataGrid1.CaptionText = "My Contacts Table";
  105.           this.dataGrid1.DataMember = "";
  106.           this.dataGrid1.DataSource = this.dataSet11.MyContactsTable;
  107.           this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
  108.           this.dataGrid1.Location = new System.Drawing.Point(0, 0);
  109.           this.dataGrid1.Name = "dataGrid1";
  110.           this.dataGrid1.SelectionBackColor = System.Drawing.Color.Blue;
  111.           this.dataGrid1.Size = new System.Drawing.Size(656, 232);
  112.           this.dataGrid1.TabIndex = 0;
  113.           this.dataGrid1.Click += new System.EventHandler(this.dataGrid1_Click);
  114.           // 
  115.           // dataSet11
  116.           // 
  117.           this.dataSet11.DataSetName = "DataSet1";
  118.           this.dataSet11.Locale = new System.Globalization.CultureInfo("en-GB");
  119.           // 
  120.           // btInsertnew
  121.           // 
  122.           this.btInsertnew.BackColor = System.Drawing.SystemColors.Control;
  123.           this.btInsertnew.FlatStyle = System.Windows.Forms.FlatStyle.System;
  124.           this.btInsertnew.Location = new System.Drawing.Point(8, 248);
  125.           this.btInsertnew.Name = "btInsertnew";
  126.           this.btInsertnew.Size = new System.Drawing.Size(72, 24);
  127.           this.btInsertnew.TabIndex = 1;
  128.           this.btInsertnew.Text = "Insert";
  129.           this.btInsertnew.Click += new System.EventHandler(this.fnAllButtonsClicks);
  130.           // 
  131.           // btUpdate
  132.           // 
  133.           this.btUpdate.BackColor = System.Drawing.SystemColors.Control;
  134.           this.btUpdate.FlatStyle = System.Windows.Forms.FlatStyle.System;
  135.           this.btUpdate.Location = new System.Drawing.Point(88, 248);
  136.           this.btUpdate.Name = "btUpdate";
  137.           this.btUpdate.Size = new System.Drawing.Size(80, 24);
  138.           this.btUpdate.TabIndex = 1;
  139.           this.btUpdate.Text = "Save/Update";
  140.           this.btUpdate.Click += new System.EventHandler(this.fnAllButtonsClicks);
  141.           // 
  142.           // btDelete
  143.           // 
  144.           this.btDelete.BackColor = System.Drawing.SystemColors.Control;
  145.           this.btDelete.FlatStyle = System.Windows.Forms.FlatStyle.System;
  146.           this.btDelete.Location = new System.Drawing.Point(176, 248);
  147.           this.btDelete.Name = "btDelete";
  148.           this.btDelete.Size = new System.Drawing.Size(72, 24);
  149.           this.btDelete.TabIndex = 1;
  150.           this.btDelete.Text = "Delete";
  151.           this.btDelete.Click += new System.EventHandler(this.fnAllButtonsClicks);
  152.           // 
  153.           // btFromXML
  154.           // 
  155.           this.btFromXML.BackColor = System.Drawing.SystemColors.Control;
  156.           this.btFromXML.FlatStyle = System.Windows.Forms.FlatStyle.System;
  157.           this.btFromXML.Location = new System.Drawing.Point(256, 248);
  158.           this.btFromXML.Name = "btFromXML";
  159.           this.btFromXML.Size = new System.Drawing.Size(80, 24);
  160.           this.btFromXML.TabIndex = 1;
  161.           this.btFromXML.Text = "ReadFromXML";
  162.           this.btFromXML.Click += new System.EventHandler(this.fnAllButtonsClicks);
  163.           // 
  164.           // btRefresh
  165.           // 
  166.           this.btRefresh.BackColor = System.Drawing.SystemColors.Control;
  167.           this.btRefresh.FlatStyle = System.Windows.Forms.FlatStyle.System;
  168.           this.btRefresh.Location = new System.Drawing.Point(344, 248);
  169.           this.btRefresh.Name = "btRefresh";
  170.           this.btRefresh.Size = new System.Drawing.Size(75, 24);
  171.           this.btRefresh.TabIndex = 1;
  172.           this.btRefresh.Text = "Refresh";
  173.           this.btRefresh.Click += new System.EventHandler(this.fnAllButtonsClicks);
  174.           // 
  175.           // btCopyToXml
  176.           // 
  177.           this.btCopyToXml.BackColor = System.Drawing.SystemColors.Control;
  178.           this.btCopyToXml.FlatStyle = System.Windows.Forms.FlatStyle.System;
  179.           this.btCopyToXml.Location = new System.Drawing.Point(432, 248);
  180.           this.btCopyToXml.Name = "btCopyToXml";
  181.           this.btCopyToXml.Size = new System.Drawing.Size(80, 24);
  182.           this.btCopyToXml.TabIndex = 1;
  183.           this.btCopyToXml.Text = "CopyToXML";
  184.           this.btCopyToXml.Click += new System.EventHandler(this.fnAllButtonsClicks);
  185.           // 
  186.           // btExit
  187.           // 
  188.           this.btExit.BackColor = System.Drawing.Color.Red;
  189.           this.btExit.FlatStyle = System.Windows.Forms.FlatStyle.System;
  190.           this.btExit.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
  191.           this.btExit.Location = new System.Drawing.Point(616, 248);
  192.           this.btExit.Name = "btExit";
  193.           this.btExit.Size = new System.Drawing.Size(32, 24);
  194.           this.btExit.TabIndex = 1;
  195.           this.btExit.Text = "Exit";
  196.           this.btExit.Click += new System.EventHandler(this.fnAllButtonsClicks);
  197.           // 
  198.           // oleDbSelectCommand1
  199.           // 
  200.           this.oleDbSelectCommand1.CommandText = "SELECT Address, City, Country, Email, FirstName, LastName, Message, Phone FROM My" +
  201.              "ContactsTable";
  202.           this.oleDbSelectCommand1.Connection = this.oleDbConnection1;
  203.           // 
  204.           // oleDbConnection1
  205.           // 
  206.           this.oleDbConnection1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\\\\..\\\\MyContactsDB.mdb";
  207.           // 
  208.           // oleDbInsertCommand1
  209.           // 
  210.           this.oleDbInsertCommand1.CommandText = "INSERT INTO MyContactsTable(Address, City, Country, Email, FirstName, LastName, M" +
  211.              "essage, Phone) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
  212.           this.oleDbInsertCommand1.Connection = this.oleDbConnection1;
  213.           this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Address", System.Data.OleDb.OleDbType.VarWChar, 50, "Address"));
  214.           this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("City", System.Data.OleDb.OleDbType.VarWChar, 50, "City"));
  215.           this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Country", System.Data.OleDb.OleDbType.VarWChar, 50, "Country"));
  216.           this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Email", System.Data.OleDb.OleDbType.VarWChar, 50, "Email"));
  217.           this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("FirstName", System.Data.OleDb.OleDbType.VarWChar, 50, "FirstName"));
  218.           this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("LastName", System.Data.OleDb.OleDbType.VarWChar, 50, "LastName"));
  219.           this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Message", System.Data.OleDb.OleDbType.VarWChar, 50, "Message"));
  220.           this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Phone", System.Data.OleDb.OleDbType.VarWChar, 50, "Phone"));
  221.           // 
  222.           // oleDbUpdateCommand1
  223.           // 
  224.           this.oleDbUpdateCommand1.CommandText = @"UPDATE MyContactsTable SET Address = ?, City = ?, Country = ?, Email = ?, FirstName = ?, LastName = ?, Message = ?, Phone = ? WHERE (LastName = ?) AND (Address = ? OR ? IS NULL AND Address IS NULL) AND (City = ? OR ? IS NULL AND City IS NULL) AND (Country = ? OR ? IS NULL AND Country IS NULL) AND (Email = ? OR ? IS NULL AND Email IS NULL) AND (FirstName = ? OR ? IS NULL AND FirstName IS NULL) AND (Message = ? OR ? IS NULL AND Message IS NULL) AND (Phone = ? OR ? IS NULL AND Phone IS NULL)";
  225.           this.oleDbUpdateCommand1.Connection = this.oleDbConnection1;
  226.           this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Address", System.Data.OleDb.OleDbType.VarWChar, 50, "Address"));
  227.           this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("City", System.Data.OleDb.OleDbType.VarWChar, 50, "City"));
  228.           this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Country", System.Data.OleDb.OleDbType.VarWChar, 50, "Country"));
  229.           this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Email", System.Data.OleDb.OleDbType.VarWChar, 50, "Email"));
  230.           this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("FirstName", System.Data.OleDb.OleDbType.VarWChar, 50, "FirstName"));
  231.           this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("LastName", System.Data.OleDb.OleDbType.VarWChar, 50, "LastName"));
  232.           this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Message", System.Data.OleDb.OleDbType.VarWChar, 50, "Message"));
  233.           this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Phone", System.Data.OleDb.OleDbType.VarWChar, 50, "Phone"));
  234.           this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_LastName", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "LastName", System.Data.DataRowVersion.Original, null));
  235.           this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Address", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Address", System.Data.DataRowVersion.Original, null));
  236.           this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Address1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Address", System.Data.DataRowVersion.Original, null));
  237.           this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_City", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "City", System.Data.DataRowVersion.Original, null));
  238.           this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_City1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "City", System.Data.DataRowVersion.Original, null));
  239.           this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Country", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Country", System.Data.DataRowVersion.Original, null));
  240.           this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Country1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Country", System.Data.DataRowVersion.Original, null));
  241.           this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Email", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Email", System.Data.DataRowVersion.Original, null));
  242.           this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Email1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Email", System.Data.DataRowVersion.Original, null));
  243.           this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_FirstName", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "FirstName", System.Data.DataRowVersion.Original, null));
  244.           this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_FirstName1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "FirstName", System.Data.DataRowVersion.Original, null));
  245.           this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Message", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Message", System.Data.DataRowVersion.Original, null));
  246.           this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Message1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Message", System.Data.DataRowVersion.Original, null));
  247.           this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Phone", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Phone", System.Data.DataRowVersion.Original, null));
  248.           this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Phone1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Phone", System.Data.DataRowVersion.Original, null));
  249.           // 
  250.           // oleDbDeleteCommand1
  251.           // 
  252.           this.oleDbDeleteCommand1.CommandText = @"DELETE FROM MyContactsTable WHERE (LastName = ?) AND (Address = ? OR ? IS NULL AND Address IS NULL) AND (City = ? OR ? IS NULL AND City IS NULL) AND (Country = ? OR ? IS NULL AND Country IS NULL) AND (Email = ? OR ? IS NULL AND Email IS NULL) AND (FirstName = ? OR ? IS NULL AND FirstName IS NULL) AND (Message = ? OR ? IS NULL AND Message IS NULL) AND (Phone = ? OR ? IS NULL AND Phone IS NULL)";
  253.           this.oleDbDeleteCommand1.Connection = this.oleDbConnection1;
  254.           this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_LastName", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "LastName", System.Data.DataRowVersion.Original, null));
  255.           this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Address", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Address", System.Data.DataRowVersion.Original, null));
  256.           this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Address1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Address", System.Data.DataRowVersion.Original, null));
  257.           this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_City", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "City", System.Data.DataRowVersion.Original, null));
  258.           this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_City1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "City", System.Data.DataRowVersion.Original, null));
  259.           this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Country", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Country", System.Data.DataRowVersion.Original, null));
  260.           this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Country1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Country", System.Data.DataRowVersion.Original, null));
  261.           this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Email", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Email", System.Data.DataRowVersion.Original, null));
  262.           this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Email1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Email", System.Data.DataRowVersion.Original, null));
  263.           this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_FirstName", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "FirstName", System.Data.DataRowVersion.Original, null));
  264.           this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_FirstName1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "FirstName", System.Data.DataRowVersion.Original, null));
  265.           this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Message", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Message", System.Data.DataRowVersion.Original, null));
  266.           this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Message1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Message", System.Data.DataRowVersion.Original, null));
  267.           this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Phone", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Phone", System.Data.DataRowVersion.Original, null));
  268.           this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Phone1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Phone", System.Data.DataRowVersion.Original, null));
  269.           // 
  270.           // oleDbDataAdapter1
  271.           // 
  272.           this.oleDbDataAdapter1.DeleteCommand = this.oleDbDeleteCommand1;
  273.           this.oleDbDataAdapter1.InsertCommand = this.oleDbInsertCommand1;
  274.           this.oleDbDataAdapter1.SelectCommand = this.oleDbSelectCommand1;
  275.           this.oleDbDataAdapter1.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {
  276.                                                                                                                                   new System.Data.Common.DataTableMapping("Table", "MyContactsTable", new System.Data.Common.DataColumnMapping[] {
  277.                                                                                                                                                                                                                                                                                          new System.Data.Common.DataColumnMapping("Address", "Address"),
  278.                                                                                                                                                                                                                                                                                          new System.Data.Common.DataColumnMapping("City", "City"),
  279.                                                                                                                                                                                                                                                                                          new System.Data.Common.DataColumnMapping("Country", "Country"),
  280.                                                                                                                                                                                                                                                                                          new System.Data.Common.DataColumnMapping("Email", "Email"),
  281.                                                                                                                                                                                                                                                                                          new System.Data.Common.DataColumnMapping("FirstName", "FirstName"),
  282.                                                                                                                                                                                                                                                                                          new System.Data.Common.DataColumnMapping("LastName", "LastName"),
  283.                                                                                                                                                                                                                                                                                          new System.Data.Common.DataColumnMapping("Message", "Message"),
  284.                                                                                                                                                                                                                                                                                          new System.Data.Common.DataColumnMapping("Phone", "Phone")})});
  285.           this.oleDbDataAdapter1.UpdateCommand = this.oleDbUpdateCommand1;
  286.           // 
  287.           // btSearch
  288.           // 
  289.           this.btSearch.BackColor = System.Drawing.SystemColors.Control;
  290.           this.btSearch.FlatStyle = System.Windows.Forms.FlatStyle.System;
  291.           this.btSearch.Location = new System.Drawing.Point(520, 248);
  292.           this.btSearch.Name = "btSearch";
  293.           this.btSearch.Size = new System.Drawing.Size(88, 24);
  294.           this.btSearch.TabIndex = 1;
  295.           this.btSearch.Text = "WriteToTextFile";
  296.           this.btSearch.Click += new System.EventHandler(this.fnAllButtonsClicks);
  297.           // 
  298.           // clsDataGridForm
  299.           // 
  300.           this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
  301.           this.BackColor = System.Drawing.Color.Olive;
  302.           this.ClientSize = new System.Drawing.Size(654, 289);
  303.           this.Controls.Add(this.btInsertnew);
  304.           this.Controls.Add(this.dataGrid1);
  305.           this.Controls.Add(this.btUpdate);
  306.           this.Controls.Add(this.btDelete);
  307.           this.Controls.Add(this.btFromXML);
  308.           this.Controls.Add(this.btRefresh);
  309.           this.Controls.Add(this.btCopyToXml);
  310.           this.Controls.Add(this.btExit);
  311.           this.Controls.Add(this.btSearch);
  312.           this.FormBorderStyle = System.Windows.Forms.FormBorderStyle.Fixed3D;
  313.           this.MaximizeBox = false;
  314.           this.Name = "clsDataGridForm";
  315.           this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
  316.           this.Text = "DataGrid Form";
  317.           this.Load += new System.EventHandler(this.clsDataGridForm_Load);
  318.           ((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
  319.           ((System.ComponentModel.ISupportInitialize)(this.dataSet11)).EndInit();
  320.           this.ResumeLayout(false);
  321.  
  322.         }
  323.         #endregion
  324.  
  325.         /// <summary>
  326.         /// The main entry point for the application.
  327.         /// </summary>
  328.         [STAThread]
  329.         static void Main() 
  330.         {
  331.           Application.EnableVisualStyles();
  332.           Application.Run(new clsDataGridForm());
  333.         }
  334.  
  335.       //--------------------------------------------------------------------
  336.       // when the application first time runs
  337.       private void clsDataGridForm_Load(object sender, System.EventArgs e)
  338.       {
  339.          try
  340.          {
  341.             //connection was ok.display data in Datagrid
  342.             fnRefresh();
  343.          }
  344.          catch(Exception ex) 
  345.          {                 
  346.             MessageBox.Show("Connection failed..."+ex.Message);
  347.             Application.Exit(); //end the program
  348.          }            
  349.       }
  350.  
  351.       //--------------------------------------------------------------------------
  352.       // All the buttons-clicks captured here 
  353.       private void fnAllButtonsClicks(object sender, System.EventArgs e)
  354.       {
  355.           Button bt=(Button)sender; //get which button clicked
  356.           switch (bt.Text) //button-text
  357.           {
  358.  
  359.              case "Insert"      : fnInsertNew();
  360.                                      break;
  361.  
  362.              case "Save/Update"      
  363.                                   : fnSaveUpdate();                                     
  364.                                      break;    
  365.  
  366.              case "Delete"      : fnDelete();                                    
  367.                                      break;    
  368.  
  369.              case "ReadFromXML"      
  370.                                   : fnDataReadingFromXMLFile("C:\\MyXMLdata.xml");                                    
  371.                                      break;
  372.  
  373.              case "Refresh"  : fnRefresh();
  374.                                      fnEnableDisableAllButtons(true);                             
  375.                                      break;
  376.  
  377.              case "CopyToXML": fnCopyToXMLandTextFile();                                     
  378.                                      break;                                                                                                              
  379.  
  380.              case "WriteToTextFile"   
  381.                                   : fnWriteToTextFile();
  382.                                      break;
  383.  
  384.              case "Exit"      : Application.Exit();                 
  385.                                      break;                            
  386.  
  387.           }//switch
  388.       }
  389.  
  390.       //--------------------------------------------------------------------------
  391.       //
  392.       private void fnInsertNew()
  393.       {
  394.          //keep in mind the previous clicked row to unselect
  395.          int iPrevRowindex=this.iRowIndex;
  396.          MessageBox.Show("Enter the new record at the end of the DataGrid and click 'Save/Update'-button", "Stop");          
  397.          this.btInsertnew.Enabled=false;
  398.          //get how many records in the table
  399.          this.iRowIndex=this.dataSet11.Tables["MyContactsTable"].Rows.Count;
  400.           //select the last row
  401.          this.dataGrid1.Select(this.iRowIndex);
  402.          //unselect the previous row
  403.          this.dataGrid1.UnSelect(iPrevRowindex);
  404.       }
  405.  
  406.       //--------------------------------------------------------------------------
  407.       //Update or save if made any changes otherwise either no changes or error
  408.       private void fnSaveUpdate()
  409.       {    
  410.           try 
  411.           {
  412.              //put the modified DataSet into a new DataSet(myChangedDataset)          
  413.              DataSet myChangedDataset= this.dataSet11.GetChanges();
  414.              if (myChangedDataset != null)
  415.              {
  416.                 //get how many rows changed
  417.                 int modifiedRows = this.oleDbDataAdapter1.Update(myChangedDataset);
  418.                 MessageBox.Show("Database has been updated successfully: " + modifiedRows + " Modified row(s) ", "Success");
  419.                 this.dataSet11.AcceptChanges(); //accept the all changes
  420.                 fnRefresh();
  421.              }
  422.              else //no changes
  423.              {
  424.                 MessageBox.Show("Nothing to save", "No changes");
  425.              }//if-else          
  426.           }
  427.           catch(Exception ex)
  428.           {
  429.              //if something somehow went wrong
  430.              MessageBox.Show("An error occurred updating the database: " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
  431.              this.dataSet11.RejectChanges(); //cancel the changes
  432.           }//try-catch 
  433.           fnEnableDisableAllButtons(true);    
  434.         }
  435.  
  436.       //--------------------------------------------------------------------------
  437.       //
  438.       private void fnDelete()
  439.       {         
  440.           //ask user if wanting to delete
  441.           DialogResult dr=MessageBox.Show("Are you sure you want to delete this row ? ", "Confirm deleting", MessageBoxButtons.YesNo, MessageBoxIcon.Question);    
  442.           if (dr ==DialogResult.Yes) //if 
  443.           {    
  444.              //user clicked the "Delete" button
  445.              DataTable tbl=new DataTable("MyContactsTable");
  446.              tbl=this.dataSet11.Tables[0];
  447.              int i=this.iRowIndex;//get the index of the row you clicked
  448.              tbl.Rows[i].Delete(); //delete the row
  449.              this.oleDbDataAdapter1.Update(tbl); //update the table
  450.              this.fnRefresh(); //refresh the table                      
  451.           }//if          
  452.       }
  453.  
  454.       //--------------------------------------------------------------------------
  455.       //
  456.       private void fnDataReadingFromXMLFile(string filename)
  457.       {
  458.           //check if the file exists
  459.           if ( File.Exists(filename))
  460.           {
  461.              //clear the DataSet contents
  462.              this.dataSet11.Clear(); 
  463.              MessageBox.Show("Data reading from "+filename+" -file");
  464.              this.dataSet11.ReadXml(filename);      
  465.           } //if
  466.           else {             
  467.              MessageBox.Show(filename+" does NOT exist; Please click first the button 'CopyToXML' ", "File Error", 
  468.                                          MessageBoxButtons.OK, MessageBoxIcon.Error );
  469.           }//else
  470.       }
  471.  
  472.       //---------------------------------------------------------------------------
  473.       // clear DataSet-contents, refresh the DataSet and display data in Datgrid
  474.       private void fnRefresh()
  475.       {
  476.          this.dataSet11.Clear();
  477.          this.oleDbDataAdapter1.Fill(this.dataSet11,"MyContactsTable");
  478.          this.dataGrid1.DataSource=this.dataSet11.Tables["MyContactsTable"].DefaultView;
  479.       }
  480.  
  481.       //----------------------------------------------------------------------------
  482.       // it writes the current data of the DataSet to 3 files on C:\
  483.       //1.Text file: MyXMLdataText.txt
  484.       //2.XML file: XMLdata.xml
  485.       //3.schema file: MyXMLschema.xsd
  486.       private void fnCopyToXMLandTextFile()
  487.       {     
  488.          if (this.dataSet11 == null) 
  489.          { 
  490.             return; 
  491.          }        
  492.          // Writes the current data for the DataSet to the specified files.
  493.          this.dataSet11.WriteXml("C:\\MyXMLText.txt");
  494.          this.dataSet11.WriteXml("C:\\MyXMLdata.xml", XmlWriteMode.WriteSchema);
  495.          this.dataSet11.WriteXmlSchema("C:\\MyXMLschema.xsd");
  496.          string s="The current data of the DataSet coppied on C:\\ as: \n";
  497.          s +="* Text file: MyXMLdataText.txt\n";
  498.          s +="* XML file: XMLdata.xml\n";
  499.          s +="* Schema file: MyXMLschema.xsd";
  500.          MessageBox.Show(s, "Copy to XML/Text/Schema file");
  501.       }                  
  502.  
  503.  
  504.       private void fnWriteToTextFile()
  505.       {              
  506.           OleDbDataReader reader;
  507.           OleDbCommand cmd=new OleDbCommand();                    
  508.           this.oleDbConnection1.Open();    //open the connection          
  509.           cmd.CommandText="SELECT Address, City, Country, Email, FirstName, LastName, Message, Phone FROM MyContactsTable";                
  510.           cmd.Connection=this.oleDbConnection1;
  511.           reader=cmd.ExecuteReader();
  512.          //the "using" statement causes the close method to be called internally.
  513.          //if "using" not used, use "writer.Close()" in try-catch-finally explicitly
  514.           using (StreamWriter writer = new StreamWriter("C:\\MyTextFile.txt",false)) 
  515.           { //false means: textfile is overwritten
  516.              try
  517.              {
  518.                 while (reader.Read())
  519.                 {
  520.                   writer.Write(reader["LastName"]);
  521.                   writer.Write("#");
  522.                   writer.Write(reader["FirstName"]);
  523.                   writer.Write("#");
  524.                   writer.Write(reader["Address"]);
  525.                   writer.Write("#");
  526.                   writer.Write(reader["City"]);
  527.                   writer.Write("#");
  528.                   writer.Write(reader["Country"]);
  529.                   writer.Write("#");
  530.                   writer.Write(reader["Email"]);
  531.                   writer.Write("#");
  532.                   writer.Write(reader["Message"]);
  533.                   writer.Write("#");
  534.                   writer.Write(reader["Phone"]);
  535.                   writer.WriteLine(); //next new line            
  536.                 }//while 
  537.              }
  538.              catch (Exception excp)
  539.              {
  540.                 MessageBox.Show(excp.Message);
  541.              }finally {
  542.                 reader.Close(); //close the reader
  543.              }//try-catch
  544.           }//using        
  545.          MessageBox.Show("Data of table writtten into C:\\MyTextFile.txt file","Writing completed");
  546.  
  547.       }
  548.  
  549.  
  550.       //--------------------------------------------------------------------------
  551.       //get the row index on the DataGrid if clicked a row
  552.       private void dataGrid1_Click(object sender, System.EventArgs e)
  553.       {         
  554.          this.iRowIndex =this.dataGrid1.CurrentRowIndex;    
  555.          this.btInsertnew.Enabled=true;
  556.          fnShowCurrentRecord(this.iRowIndex);         
  557.       }
  558.  
  559.       //-------------------------------------------------------------------------
  560.       //shows the current record number
  561.       private void fnShowCurrentRecord(int pos) 
  562.       {        
  563.          this.dataGrid1.CaptionText=" Record:   "+((pos)+1);
  564.       }
  565.  
  566.       //-------------------------------------------------------------------------
  567.       //- This is the way to enable or disable ALL the buttons on the Form
  568.       //- if flag true all buttons enabled, if false buttons disabled
  569.       public void fnEnableDisableAllButtons(bool flag) 
  570.       {
  571.          string str;
  572.          foreach(Control ctrl in this.Controls) 
  573.          {
  574.             str = Convert   .ToString(ctrl.GetType());
  575.             if(str == "System.Windows.Forms.Button") 
  576.             {
  577.               ctrl.Enabled = flag;
  578.             }//if
  579.          }//foreach
  580.       }
  581.  
  582.  
  583.     }//class
  584. }//namespace
  585.  
Aug 9 '10 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.