Database Systems & Programming Chapter 4 – Introducing

Database Systems & Programming Chapter 4 - Introducing Windows Applications and ADO.NET P.Uruthiran Lecturer

Overview UNIVOTEC Windows provides graphical items, such as menus, text boxes, and radio buttons, so that you can build a visual interface that is easy to use. You can create Windows applications that use ADO.NET Windows applications are simple to learn and use because people have become accustomed to interacting with machines in a visual manner. The ubiquitous Microsoft Word and Excel are just two examples of how successful Windows applications can be because of the way they combine power and ease of use. Database Programming B.Tech (ICT) P.Uruthiran

Overview UNIVOTEC The idea of using graphical user interfaces (GUIs) and a mouse to interact with a computer is not unique to Windows. In fact, these concepts were originally developed back in the early 1970s by engineers at Xerox Corporation's Palo Alto Research Center (PARC) in California, and one of the first computers to use a GUI and a mouse was the Alto. Unfortunately, the Alto was expensive, and it wasn't until Apple Computer launched the Macintosh in 1984 that the GUI became popular. Later, Microsoft developed the Windows operating system that built on the ideas made popular by Apple. Database Programming B.Tech (ICT) P.Uruthiran

Part II – Developing a simple windows application Chapter 5 Database Programming B.Tech (ICT) P.Uruthiran

Developing a simple windows application You'll see how to create a simple Windows application using VS .NET. This application will consist of a single form that contains a label and a button When you click the button, the text for the label will change You'll also see how to compile and run the example application. Database Programming B.Tech (ICT) P.Uruthiran UNIVOTEC

Developing a simple windows application Open visual C# New project Windows Forms application Press OK Open tool box Database Programming B.Tech (ICT) P.Uruthiran UNIVOTEC

UNIVOTEC Database Programming B.Tech (ICT) P.Uruthiran

Working with the Toolbox You add controls to your form by selecting the control from the Toolbox and dragging the control to your form. You can also click and drag, or double-click on the control to drop a new one of that type onto your form. As you can see in the figure shown earlier, the Toolbox is to the left of the blank form. Note If you don't see the Toolbox, you can display it by selecting View Other windows Toolbox, or by pressing Ctrl W,X on your keyboard. Database Programming B.Tech (ICT) P.Uruthiran UNIVOTEC

Working with the Toolbox UNIVOTEC You can see that the available items in the Toolbox are categorized into groups with names such as Data and XML Schema. The Toolbox will show only categories that are relevant to the type of application you are developing. Database Programming B.Tech (ICT) P.Uruthiran

Working with the properties window UNIVOTEC The Properties window contains aspects of a control that you can set. For example, you can set the background color of your form using the BackColor property. Some other properties of the form control include ForeColor (the foreground color) and BackgroundImage (an image displayed in the background). Different types of controls have different types of properties. If the properties windows is not available goto View Other windows Properties Window You set the property by clicking the area to the right of the property name. Go ahead and click to the right of the BackColor property to view some of the colors to which you can set this property. Database Programming B.Tech (ICT) P.Uruthiran

Adding a Label and a Button Control UNIVOTEC To add a label and a button control to your form select the appropriate control from the Toolbox and drag it to your form. For example, to add a label to your form, you select the label control from the Toolbox. Once you've dragged a label to your form, you can resize it by using the mouse or setting the Size property in the Properties window. You can also click on the label in the Toolbox and drag it on your form. Make your label big enough so that that it stretches across the length of your form. Next, add a button control below your label Database Programming B.Tech (ICT) P.Uruthiran

Adding a Label and a Button Control UNIVOTEC Next, you'll change some of the properties for your label and button. You do this using the Properties window. Set the Name property of your label to myLabel. Set the Name and Text properties for your button to myButton and Press Me!, respectively. Also, set the Text property of your form to myForm Database Programming B.Tech (ICT) P.Uruthiran

Adding a Label and a Button Control UNIVOTEC Next, you'll add a line of code to the myButton Click() method. This method is executed when myButton is clicked in your running form. The statement you'll add to myButton Click() will set the Text property of myLabel to a string. To add the code, double-click myButton and enter the following code in the myButton Click() method: You pressed the button Thank you Database Programming B.Tech (ICT) P.Uruthiran

UNIVOTEC Database Programming B.Tech (ICT) P.Uruthiran

Adding a Label and a Button Control You've now finished your form. Build your project by selecting Debug Build Solution To run your form, select Debug Start Debugging Debug Stop debugging Database Programming B.Tech (ICT) P.Uruthiran UNIVOTEC

Examining the code behind the form using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; private void label1 Click(object sender, EventArgs e) { UNIVOTEC } private void Form1 Load(object sender, EventArgs e) { } namespace private void WindowsFormsApplication1 myButton Click(object sender, { EventArgs e) public partial class Form1 : { Form myLabel.Text "You have { pressed the button \n" public Form1() "Thank you"; { } InitializeComponent(); } } Database Programming B.Tech } (ICT) P.Uruthiran

Examining the code behind the form As you can see, the Form1 class is derived from the System.Windows.Forms.Form class. The Form class represents a Windows form The System.Windows.Forms namespace contains the various classes for creating Windows applications. Most of the classes in this namespace are derived from the System.Windows.Forms.Control class; this class provides the basic functionality for the controls you can place on a form. Database Programming B.Tech (ICT) P.Uruthiran UNIVOTEC

Examining the code behind the form The Form1 class declares two private objects named myLabel and myButton, which are the label and button controls you added to your form earlier. Because the myLabel and myButton objects are private, this means that they are accessible only in the Form1 class. Database Programming B.Tech (ICT) P.Uruthiran UNIVOTEC

Access modifiers UNIVOTEC Access modifiers enable you to specify the degree to which a class member is available outside the class. You can also use an access modifier to specify the degree to which the class itself is available. Table shows the access modifiers in decreasing order of availability: public is the most accessible, and private the least Database Programming B.Tech (ICT) P.Uruthiran

Access modifiers UNIVOTEC ACCESS MODIFIER ACCESSIBILITY public Member accessible without restriction. protected internal Member accessible only within the class, a derived class, or class in the same program (or assembly). internal Member accessible only within the class or class in the same program (or assembly). protected Member accessible only within the class or derived classes. private Member accessible only within the class. This is The Form1 class constructor calls the InitializeComponent() method. the default. This method adds myLabel and myButton to the form and sets the properties for those objects. These properties include the Location (the position in the form), Name, Size, TabIndex (the order in which the control is accessed using the Tab key), and Text. Database Programming B.Tech (ICT) P.Uruthiran

The main() method using using using using System; System.Collections.Generic; System.Linq; System.Windows.Forms; namespace WindowsFormsApplication1 { static class Program { /// summary /// The main entry point for the application. /// /summary [STAThread] static void Main() { Application.EnableVisualStyles(); Application.SetCompatibleTextRenderingDefault(false); Application.Run(new Form1()); } } } Database Programming B.Tech (ICT) P.Uruthiran UNIVOTEC

The main() method UNIVOTEC The myButton Click() method is the method you modified earlier that sets the Text property of myLabel to a string. When myButton is clicked, the myButton Click() method is called and the text in myLabel is changed; you saw this when you ran your form earlier. Database Programming B.Tech (ICT) P.Uruthiran

Working with the Solution Explorer You can use the VS .NET Solution Explorer to view the items in your project, such as the namespace for your project. Of course, a project may contain more than one namespace. To view the Solution Explorer, you select View Other Windows Solution Explorer. Database Programming B.Tech (ICT) P.Uruthiran UNIVOTEC

Working with the Solution Explorer UNIVOTEC You can use Solution Explorer to view the following items in a project's namespace: References References include other namespaces and classes to which your form's code refers. You can employ the using statement to reference other namespaces and classes. Assembly File An assembly file contains the metadata for your application's assembly. An assembly is collection of code for your application. Code Files A code file is a program source file, such as the code for a form Database Programming B.Tech (ICT) P.Uruthiran

Working with the Solution Explorer UNIVOTEC As you can see, you can expand or collapse the items shown in the Solution Explorer by clicking the plus or minus icon, respectively. You can also display the properties for an item in Solution Explorer: When you have the Properties window displayed, selecting an item in Solution Explorer will also display the properties for that item. For example, the properties for the MyWindowsApplication project are displayed; you can see that the project file is MyWindowsApplication.csproj. Database Programming B.Tech (ICT) P.Uruthiran

Working with the Class View UNIVOTEC You can see the Class View by pressing Ctrl Shift C on your keyboard. You can view the classes, methods, and objects You can also view the properties for a selected item in the Properties window Database Programming B.Tech (ICT) P.Uruthiran

Using Windows Controls CONTROL Label LinkLabel UNIVOTEC DESCRIPTION Displays text. You set the text that you want to display using the Text property. Similar to a label, except it displays hyperlinks. You set the hyperlink that you want to display using the Text property. You set the navigation via the LinkClicked event. Button A clickable button. The Text property determines the text shown on the button. TextBox A box containing text that the user of your form may edit at runtime. The Text property contains the text contained in the TextBox. MainMenu A menu you can add to a form. CheckBox A check box contains a Boolean true/false value that is set to true by the user if they check the box. The Checked property indicates the Boolean value. RadioButton A radio button contains a Boolean true/false value that is set to true by the user if they click the button. The Checked property indicates the Boolean value. GroupBox A group box allows you to group related controls together. For example, you can group related radio buttons together. Most importantly, it allows you to treat multiple controls as a group. PictureBox A picture box displays an image that you set using the Image property. Panel A container for other controls such as radio buttons or group boxes. DataGrid A grid containing data retrieved from a data source, such as a database. You set the data source using the DataSource property. ListBox A list of options. You set the list of options using the Add() method of the Items collection property. CheckedListBo Similar to a list box except that a check mark is placed to the left of each item in the list. The x check mark allows the user to select the items via a check box, as opposed to multiselecting with the Shift and/or Ctrl keys. ComboBox Combines an editable field with a list box. Database Programming B.Tech (ICT) P.Uruthiran

Using a DataGrid Control to Access a Database Chapter 5 Database Programming B.Tech (ICT) P.Uruthiran

Saving data to SQL server using C# and ADO.NET Objects used SqlConnectionString SqlDataAdapter Text Boxes Button Click Event Start SQL server management studio Login Select databases Right click New Database Create the database named ‘Personal’ Database Programming B.Tech (ICT) P.Uruthiran UNIVOTEC

Saving data to SQL server using C# and ADO.NET USE Personal GO CREATE TABLE Contacts( ID INT IDENTITY NOT NULL, FIRSTNAME VARCHAR(100), LASTNAME VARCHAR(100) ); SELECT * FROM Contacts; Database Programming B.Tech (ICT) P.Uruthiran UNIVOTEC

Saving data to SQL server using C# and ADO.NET Open Microsoft Visual C# New project Windows Forms Application Use the following objects on windows form UNIVOTEC Two labels Two textboxes One button Create the form Give appropriate names for labels, text boxes, and button Provide the necessary text for the labels and button Database Programming B.Tech (ICT) P.Uruthiran

Saving data to SQL server using C# and ADO.NET Database Programming B.Tech (ICT) P.Uruthiran UNIVOTEC

Saving data to SQL server using C# and ADO.NET UNIVOTEC Test database connectivity with form using the button click event Database Programming B.Tech (ICT) P.Uruthiran

using using using using using using using using using System; System.Collections.Generic; System.ComponentModel; System.Data; System.Drawing; System.Linq; System.Text; System.Windows.Forms; System.Data.SqlClient; namespace WindowsFormsApplication1 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } UNIVOTEC private void btnAddRecord Click(object sender, EventArgs e) { SqlConnection cs new SqlConnection("server localhost; database Personal;uid sa;pwd a dmin123"); cs.Open(); MessageBox.Show(cs.State.ToStrin g()); cs.Close(); } } } Database Programming B.Tech (ICT) P.Uruthiran

namespace WindowsFormsApplication1 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } UNIVOTEC private void btnAddRecord Click(object sender, EventArgs e) { SqlConnection cs new SqlConnection("server localhost;database Personal;uid sa;pwd admin123"); SqlDataAdapter da new SqlDataAdapter(); da.InsertCommand new SqlCommand("INSERT INTO Contacts VALUES(@FIRSTNAME, @LASTNAME)",cs); da.InsertCommand.Parameters.Add("@FIRSTNAME", SqlDbType.VarChar).Value txtFirstName.Text; da.InsertCommand.Parameters.Add("@LASTNAME", SqlDbType.VarChar).Value txtLastName.Text; cs.Open(); da.InsertCommand.ExecuteNonQuery(); cs.Close(); } }} Database Programming B.Tech (ICT) P.Uruthiran

Saving data to SQL server using C# and ADO.NET Database Programming B.Tech (ICT) P.Uruthiran UNIVOTEC

Saving data to SQL server using C# and ADO.NET Database Programming B.Tech (ICT) P.Uruthiran UNIVOTEC

Retrieving data from SQL server using C# and ADO.NET Objects used UNIVOTEC SqlConnectionString SqlDataAdapter DataSet Add another button named ‘Display’ and Datagrid called ‘dg’ Rename the form as Main Form Add a new Dataset to the form (Dataset is the holding place in memory) Database Programming B.Tech (ICT) P.Uruthiran

Retrieving data from SQL server using C# and ADO.NET Database Programming B.Tech (ICT) P.Uruthiran UNIVOTEC

Retrieving data from SQL server using C# and ADO.NET Database Programming B.Tech (ICT) P.Uruthiran UNIVOTEC

namespace WindowsFormsApplication1 { public partial class Form1 : Form { DataSet ds new DataSet(); SqlConnection cs new SqlConnection("server localhost;database Personal;uid sa;pwd admin123"); SqlDataAdapter da new SqlDataAdapter(); public Form1() { InitializeComponent(); } private void btnAddRecord Click(object sender, EventArgs e) { da.InsertCommand new SqlCommand("INSERT INTO Contacts VALUES(@FIRSTNAME,@LASTNAME)",cs); da.InsertCommand.Parameters.Add("@FIRSTNAME", SqlDbType.VarChar).Value txtFirstName.Text; da.InsertCommand.Parameters.Add("@LASTNAME", SqlDbType.VarChar).Value txtLastName.Text; cs.Open(); da.InsertCommand.ExecuteNonQuery(); cs.Close(); } private void btnDisplay Click(object sender, EventArgs e) { da.SelectCommand new SqlCommand("SELECT * FROM Contacts", cs); Database Programming B.Tech da.Fill(ds); (ICT) P.Uruthiran dg.DataSource ds.Tables[0];

Retrieving data from SQL server using C# and ADO.NET If display is pressed twice the output will look as follows: Database Programming B.Tech (ICT) P.Uruthiran UNIVOTEC

namespace WindowsFormsApplication1 { public partial class Form1 : Form { DataSet ds new DataSet(); SqlConnection cs new SqlConnection("server localhost;database Personal;uid sa;pwd admin123"); SqlDataAdapter da new SqlDataAdapter(); public Form1() { InitializeComponent(); } private void btnAddRecord Click(object sender, EventArgs e) { da.InsertCommand new SqlCommand("INSERT INTO Contacts VALUES(@FIRSTNAME,@LASTNAME)",cs); da.InsertCommand.Parameters.Add("@FIRSTNAME", SqlDbType.VarChar).Value txtFirstName.Text; da.InsertCommand.Parameters.Add("@LASTNAME", SqlDbType.VarChar).Value txtLastName.Text; cs.Open(); da.InsertCommand.ExecuteNonQuery(); cs.Close(); } private void btnDisplay Click(object sender, EventArgs e) { da.SelectCommand new SqlCommand("SELECT * FROM Contacts", cs); ds.Clear(); da.Fill(ds); Database Programming B.Tech dg.DataSource ds.Tables[0]; (ICT) P.Uruthiran }

Using the binding source to navigate through the dataset with C# Helps to navigate through the records using text boxes. Add a binding source object BindingSource TblContactsBS new BindingSource(); Attach a source with the binding object Use the Display button UNIVOTEC TblContactsBS.DataSource ds.Tables[0]; Bind the text boxes to the binding source txtFirstName.DataBindings.Add(new Binding("Text",TblContactsBS,"FIRSTNAME")); txtLastName.DataBindings.Add(new Binding("Text",TblContactsBS,"LASTNAME")); Database Programming B.Tech (ICT) P.Uruthiran

Using the binding source to navigate through the dataset with C# Run the application Press display button You can now observe that the first record has been bound to first record (see arrow sign) Database Programming B.Tech (ICT) P.Uruthiran UNIVOTEC

Using the binding source to navigate through the dataset with C# UNIVOTEC Lets see how to navigate between the records Add four buttons First, Previous, Next, Last Add the given code See how the records change in the text boxes However, the navigation cannot be seen in this data grid Database Programming B.Tech (ICT) P.Uruthiran

Using the binding source to navigate through the dataset with C# Database Programming B.Tech (ICT) P.Uruthiran UNIVOTEC

namespace WindowsFormsApplication1 { public partial class Form1 : Form { DataSet ds new DataSet(); SqlConnection cs new SqlConnection("server localhost;database Personal;uid sa;pwd admin123"); SqlDataAdapter da new SqlDataAdapter(); BindingSource TblContactsBS new BindingSource(); public Form1() { InitializeComponent(); } private void btnAddRecord Click(object sender, EventArgs e) { da.InsertCommand new SqlCommand("INSERT INTO Contacts VALUES(@FIRSTNAME,@LASTNAME)",cs); da.InsertCommand.Parameters.Add("@FIRSTNAME", SqlDbType.VarChar).Value txtFirstName.Text; da.InsertCommand.Parameters.Add("@LASTNAME", SqlDbType.VarChar).Value txtLastName.Text; cs.Open(); da.InsertCommand.ExecuteNonQuery(); cs.Close(); } Database Programming B.Tech (ICT) P.Uruthiran

private void btnDisplay Click(object sender, EventArgs e) { da.SelectCommand new SqlCommand("SELECT * FROM Contacts", cs); ds.Clear(); da.Fill(ds); dg.DataSource ds.Tables[0]; TblContactsBS.DataSource ds.Tables[0]; txtFirstName.DataBindings.Add(new Binding("Text",TblContactsBS,"FIRSTNAME")); txtLastName.DataBindings.Add(new Binding("Text",TblContactsBS,"LASTNAME")); } private void btnFirst Click(object sender, EventArgs e) { TblContactsBS.MoveFirst(); } private void btnPrevious Click(object sender, EventArgs e) { TblContactsBS.MovePrevious(); } private void btnLast Click(object sender, EventArgs e) { TblContactsBS.MoveLast(); } private void btnNext Click(object sender, EventArgs e) { TblContactsBS.MoveNext(); } } } Database Programming B.Tech (ICT) P.Uruthiran

Using the binding source to navigate through the dataset with C# UNIVOTEC To include the navigation in the data grid the method dg.update() should be included in the private void btnFirst Click(object sender, EventArgs e) code { TblContactsBS.MoveFirst(); dgupdate(); } private void btnPrevious Click(object sender, EventArgs e) { TblContactsBS.MovePrevious(); dgupdate(); } private void btnLast Click(object sender, EventArgs e) { TblContactsBS.MoveLast(); dgupdate(); } private void btnNext Click(object sender, EventArgs e) { TblContactsBS.MoveNext(); dgupdate(); } private void dgupdate() { dg.ClearSelection(); Database Programming dg.Rows[TblContactsBS.Position].Selected true;B.Tech (ICT) } P.Uruthiran

namespace WindowsFormsApplication1 { public partial class Form1 : Form { DataSet ds new DataSet(); SqlConnection cs new SqlConnection("server localhost;database Personal;uid sa;pwd admin123"); SqlDataAdapter da new SqlDataAdapter(); BindingSource TblContactsBS new BindingSource(); public Form1() { InitializeComponent(); } private void btnAddRecord Click(object sender, EventArgs e) { da.InsertCommand new SqlCommand("INSERT INTO Contacts VALUES(@FIRSTNAME,@LASTNAME)",cs); da.InsertCommand.Parameters.Add("@FIRSTNAME", SqlDbType.VarChar).Value txtFirstName.Text; da.InsertCommand.Parameters.Add("@LASTNAME", SqlDbType.VarChar).Value txtLastName.Text; cs.Open(); da.InsertCommand.ExecuteNonQuery(); cs.Close(); Database Programming B.Tech } (ICT) P.Uruthiran

private void btnDisplay Click(object sender, EventArgs e) { da.SelectCommand new SqlCommand("SELECT * FROM Contacts", cs); ds.Clear(); da.Fill(ds); dg.DataSource ds.Tables[0]; TblContactsBS.DataSource ds.Tables[0]; txtFirstName.DataBindings.Add(new Binding("Text",TblContactsBS,"FIRSTNAME")); txtLastName.DataBindings.Add(new Binding("Text",TblContactsBS,"LASTNAME")); } private void btnFirst Click(object sender, EventArgs e) { TblContactsBS.MoveFirst(); dgupdate(); } private void btnPrevious Click(object sender, EventArgs e) { Database Programming B.Tech TblContactsBS.MovePrevious(); (ICT) P.Uruthiran

private void btnLast Click(object sender, EventArgs e) { TblContactsBS.MoveLast(); dgupdate(); } private void btnNext Click(object sender, EventArgs e) { TblContactsBS.MoveNext(); dgupdate(); } private void dgupdate() { dg.ClearSelection(); dg.Rows[TblContactsBS.Position].Selected true; } } } Database Programming B.Tech (ICT) P.Uruthiran

Using the binding source to navigate through the dataset with C# Adding an additional label to update the navigation progress Delete the contents of the label Include the record() method Database Programming B.Tech (ICT) P.Uruthiran UNIVOTEC

private void btnDisplay Click(object sender, EventArgs e) { da.SelectCommand new SqlCommand("SELECT * FROM Contacts", cs); ds.Clear(); da.Fill(ds); dg.DataSource ds.Tables[0]; TblContactsBS.DataSource ds.Tables[0]; txtFirstName.DataBindings.Add(new Binding("Text",TblContactsBS,"FIRSTNAME")); txtLastName.DataBindings.Add(new Binding("Text",TblContactsBS,"LASTNAME")); records(); } private void btnFirst Click(object sender, EventArgs e) { TblContactsBS.MoveFirst(); dgupdate(); records(); } private void btnPrevious Click(object sender, EventArgs e) { TblContactsBS.MovePrevious(); dgupdate(); Database Programming B.Tech records(); (ICT) P.Uruthiran

private void btnLast Click(object sender, EventArgs e) { TblContactsBS.MoveLast(); dgupdate(); records(); } private void btnNext Click(object sender, EventArgs e) { TblContactsBS.MoveNext(); dgupdate(); records(); } private void dgupdate() { dg.ClearSelection(); dg.Rows[TblContactsBS.Position].Selected true; records(); } private void records() { label1.Text "Record " TblContactsBS.Position " of " (TblContactsBS.Count - 1); } Database Programming B.Tech (ICT) P.Uruthiran

Updating SQL using SqlDataAdapter Update Command with C# Add a new button called Update Click on Update button and add the following code private void btnUpdate Click(object sender, EventArgs e) { da.UpdateCommand new SqlCommand("UPDATE Contacts SET FIRSTNAME @FIRSTNAME, LASTNAME @LASTNAME WHERE ID @ID",cs); da.UpdateCommand.Parameters.Add("@FIRSTNAME", SqlDbType.VarChar).Value txtFirstName.Text; da.UpdateCommand.Parameters.Add("@LASTNAME", SqlDbType.VarChar).Value txtLastName.Text; da.UpdateCommand.Parameters.Add("@ID", SqlDbType.Int).Value ds.Tables[0].Rows[TblContactsBS.Position][0]; cs.Open(); da.UpdateCommand.ExecuteNonQuery(); cs.Close(); } Database Programming B.Tech (ICT) P.Uruthiran UNIVOTEC

namespace WindowsFormsApplication1 { public partial class Form1 : Form { DataSet ds new DataSet(); SqlConnection cs new SqlConnection("server localhost;database Personal;uid sa;pwd admin123"); SqlDataAdapter da new SqlDataAdapter(); BindingSource TblContactsBS new BindingSource(); public Form1() { InitializeComponent(); } private void btnAddRecord Click(object sender, EventArgs e) { da.InsertCommand new SqlCommand("INSERT INTO Contacts VALUES(@FIRSTNAME,@LASTNAME)",cs); da.InsertCommand.Parameters.Add("@FIRSTNAME", SqlDbType.VarChar).Value txtFirstName.Text; da.InsertCommand.Parameters.Add("@LASTNAME", SqlDbType.VarChar).Value txtLastName.Text; cs.Open(); da.InsertCommand.ExecuteNonQuery(); cs.Close(); Database Programming B.Tech } (ICT) P.Uruthiran

private void btnDisplay Click(object sender, EventArgs e) { da.SelectCommand new SqlCommand("SELECT * FROM Contacts", cs); ds.Clear(); da.Fill(ds); dg.DataSource ds.Tables[0]; TblContactsBS.DataSource ds.Tables[0]; txtFirstName.DataBindings.Add(new Binding("Text",TblContactsBS,"FIRSTNAME")); txtLastName.DataBindings.Add(new Binding("Text",TblContactsBS,"LASTNAME")); records(); } private void btnFirst Click(object sender, EventArgs e) { TblContactsBS.MoveFirst(); dgupdate(); records(); } private void btnPrevious Click(object sender, EventArgs e) { TblContactsBS.MovePrevious(); dgupdate(); Database Programming B.Tech records(); (ICT) P.Uruthiran

private void btnLast Click(object sender, EventArgs e) { TblContactsBS.MoveLast(); dgupdate(); records(); } private void btnNext Click(object sender, EventArgs e) { TblContactsBS.MoveNext(); dgupdate(); records(); } private void dgupdate() { dg.ClearSelection(); dg.Rows[TblContactsBS.Position].Selected true; records(); } Database Programming B.Tech (ICT) P.Uruthiran

private void records() { label1.Text "Record " TblContactsBS.Position " of " (TblContactsBS.Count - 1); } private void btnUpdate Click(object sender, EventArgs e) { int x; da.UpdateCommand new SqlCommand("UPDATE Contacts SET FIRSTNAME @FIRSTNAME, LASTNAME @LASTNAME WHERE ID @ID",cs); da.UpdateCommand.Parameters.Add("@FIRSTNAME", SqlDbType.VarChar).Value txtFirstName.Text; da.UpdateCommand.Parameters.Add("@LASTNAME", SqlDbType.VarChar).Value txtLastName.Text; da.UpdateCommand.Parameters.Add("@ID", SqlDbType.Int).Value ds.Tables[0].Rows[TblContactsBS.Position] [0]; cs.Open(); x da.UpdateCommand.ExecuteNonQuery(); cs.Close(); if (x 1) MessageBox.Show("Record has been updated "); } } } Database Programming B.Tech (ICT) P.Uruthiran

Deleting records from SQL using the SqlDataAdapter DeleteCommand with C# Add a new button to the form called “Delete” Database Programming B.Tech (ICT) P.Uruthiran UNIVOTEC

namespace WindowsFormsApplication1 { public partial class Form1 : Form { DataSet ds new DataSet(); SqlConnection cs new SqlConnection("server localhost;database Personal;uid sa;pwd admin123"); SqlDataAdapter da new SqlDataAdapter(); BindingSource TblContactsBS new BindingSource(); public Form1() { InitializeComponent(); } private void btnAddRecord Click(object sender, EventArgs e) { da.InsertCommand new SqlCommand("INSERT INTO Contacts VALUES(@FIRSTNAME,@LASTNAME)",cs); da.InsertCommand.Parameters.Add("@FIRSTNAME", SqlDbType.VarChar).Value txtFirstName.Text; da.InsertCommand.Parameters.Add("@LASTNAME", SqlDbType.VarChar).Value txtLastName.Text; cs.Open(); da.InsertCommand.ExecuteNonQuery(); cs.Close(); Database Programming B.Tech (ICT) P.Uruthiran

private void Form1 Load(object sender, EventArgs e) { } private void btnDisplay Click(object sender, EventArgs e) { da.SelectCommand new SqlCommand("SELECT * FROM Contacts", cs); ds.Clear(); da.Fill(ds); dg.DataSource ds.Tables[0]; TblContactsBS.DataSource ds.Tables[0]; txtFirstName.DataBindings.Add(new Binding("Text",TblContactsBS,"FIRSTNAME")); txtLastName.DataBindings.Add(new Binding("Text",TblContactsBS,"LASTNAME")); records(); } private void btnFirst Click(object sender, EventArgs e) { TblContactsBS.MoveFirst(); dgupdate(); records(); } Database Programming B.Tech (ICT) P.Uruthiran

private void btnPrevious Click(object sender, EventArgs e) { TblContactsBS.MovePrevious(); dgupdate(); records(); } private void btnLast Click(object sender, EventArgs e) { TblContactsBS.MoveLast(); dgupdate(); records(); } private void btnNext Click(object sender, EventArgs e) { TblContactsBS.MoveNext(); dgupdate(); records(); } private void dgupdate() { dg.ClearSelection(); dg.Rows[TblContactsBS.Position].Selected true; records(); Database Programming B.Tech } (ICT) P.Uruthiran

private void records() { label1.Text "Record " TblContactsBS.Position " of " (TblContactsBS.Count - 1); } private void btnUpdate Click(object sender, EventArgs e) { int x; da.UpdateCommand new SqlCommand("UPDATE Contacts SET FIRSTNAME @FIRSTNAME, LASTNAME @LASTNAME WHERE ID @ID",cs); da.UpdateCommand.Parameters.Add("@FIRSTNAME", SqlDbType.VarChar).Value txtFirstName.Text; da.UpdateCommand.Parameters.Add("@LASTNAME", SqlDbType.VarChar).Value txtLastName.Text; da.UpdateCommand.Parameters.Add("@ID", SqlDbType.Int).Value ds.Tables[0].Rows[TblContactsBS.Position][0]; cs.Open(); x da.UpdateCommand.ExecuteNonQuery(); cs.Close(); if (x 1) MessageBox.Show("Record has been updated "); } Database Programming B.Tech (ICT) P.Uruthiran

private void btnDelete Click(object sender, EventArgs e) { DialogResult dr; dr MessageBox.Show("Are you sure\nThere is no undo after delete ", "Confirm Deletion ", MessageBoxButtons.YesNo); if (dr DialogResult.Yes) { da.DeleteCommand new SqlCommand("DELETE FROM Contacts WHERE ID @ID", cs); da.DeleteCommand.Parameters.Add("@ID", SqlDbType.Int).Value ds.Tables[0].Rows[TblContactsBS.Position][0]; cs.Open(); da.DeleteCommand.ExecuteNonQuery(); cs.Close(); ds.Clear(); da.Fill(ds); } else { MessageBox.Show("Deletion Cancelled"); } } } } Database Programming B.Tech (ICT) P.Uruthiran