Tutorial: MSSQL- Library App Form Control
If You Have Difficulty With This Tutorial, You Might Find These Help
Prepare You For This Tutorial
Without GUI Connections
Dr. Thomas E. Hicks
Computer Science Department
Trinity University
I Recommend That My Students Complete The Tutorials In This Order!
Tutorial: MSSQL-Forms - Windows Application & Basic Form Properties
Tutorial: MSSQL-Forms - Windows Application - Labels & Textboxes
Tutorial: MSSQL-Forms - Windows Application - Buttons
Tutorial: MSSQL-Connection\MSSQL-Connections
Tutorial: MSSQL-Forms - Windows Application - Menu - Multiple Forms
Tutorial: MSSQL-Visual Studio MenuStrip Application With Multiple Windows
1] You might want to download and add the following database on the server; call the database LibraryApp. You can use import from the Microsoft Access database
Tutorial: MSSQL-Server-From-2003-Access-DB\MSSQL-Server-From-2003-Access-DB
Tutorial: MS-SQL-Server-From-2007-Access-DB\MS-SQL-Server-From-2007-Access-DB
or you might want to try to import the LibraryApp database directly into Microsoft SQL Server using Microsoft SQL Server Management Studio Express.
Tutrial: MSSQL Import-Export-Backup-MSSQL-Database
1] Our database is shown, below, via Microsoft SQL Server Management Studio Express. Note that our server, to which we shall connect, is NET1\SQLEXPRESS (See Below!)
2] There are a number of good ways to connect. We are going to use two connection string options. See the link below for an Internet link that most of us should keep bookmarked.
3] Since we shall connect with both Windows and SQL server authentication, it is important that you configure your SQL Server to authenticate both ways. An easy way to do this is to use Microsoft SQL Server Management Studio Express (Right mouse click on Net1\Express and select properties-select Security page). (See Below!)
4] Use Microsoft SQL Server Management Studio Express to create a user, called student whose password is student. (See Below!)
5] Double-click on the student account; select User Mappings. Use Microsoft SQL Server Management Studio Express to enable the student user to use the LibraryApp database. (See Below!)
6] Continue to use Microsoft SQL Server Management Studio Express. Right-mouse click on the LibraryApp database and select Properties. Select the Permissions Page. Add the student user if necessary. The student user certainly does not need all of the permissions, but for the sake of simplicity (and because I don't want this to be a tutorial on SQL Server), grant the student user all permissions. (See Below!)
7] SQL user student
should now be able to use password student to authenticate with the LibraryApp
database throughout this tutorial.
1] My students have been asked to complete some basic forms to support this tutorial. Firs is LibraryApp.cs, which uses a MenuStrip to control the main application. The lab description may be seen below. LibraryApp.cs, is to look something like the following:
MSSQL-Library-App\MSSQL-Library-App-1 Lab
2] They were to create a basic Administrative login form called
Login.cs. It is to look something like the following:
3] They
were to also create a non-functional ViewUser vorm called Users.cs that
looks something like the following: [We realize that the MajorID and
Administrator should eventually be combo boxes and that the password might
eventually be replaced by #'s. To get rid of the close box, add the following function right below the
function public
Users()
private const int CS_NOCLOSE = 0x200;
protected override CreateParams CreateParams
{
get
{
CreateParams cp = base.CreateParams;
cp.ClassStyle = cp.ClassStyle | CS_NOCLOSE;
return cp;
}
}
4] They were supposed to place all of the fields, from the Users table, in the first tab frame of the Users form.
5] Button Close should hide the Users Form. Their Users form was to include all of the buttons seen on the form above and all of the combo boxes seen on that form.
6] A collection of images was made available to the students. They included: Top
,
Bottom 
, Next
,
and Previous 
.
Top
is to navigate to the first logical record.
Bottom
Next
is to navigate to the next logical record - wrap around if necessary.
Previous
is to navigate to the previous logical record - wrap around if
necessary.
7] Below you can see, in turquoise, the names I have associated with the various TextBoxes, lLabels, and ComboBoxes on my Users.cs form.
1] The purpose of this tutorial is to connect to our database, without guis, and add functionality to many of the buttons on the Users.cs form.
1] You must include the System.Data.SqlClient Namespace at the top of the Users.cs form.
1] If you are working on your SQL Server, using Windows Authentication will probably be the easiest.
2] Using the mouse, double-click someplace on your users form that has no objects; this will create a User_Load function that will be fired each time our form is loaded. We are going to place our temporary testing code in that function.
3] In accordance with ConnectionStrings.com, let us define our Connection String to load the Library database on Net1\SQLExpress. Note the need to use the \\ escape sequence in the connection string. Create a string object, called ConnectionString, and fill it in accordance with your SQL Server Name. Add the line below to your form and test it; your form should load, but there will be no evidence that the database stuff is working yet. (See Below!)
String ConnectionString = "Data Source=net1\\SQLExpress;Integrated Security=True;" +
"Initial Catalog=LibraryApp";
4] Create a connection object, called Conn. Add the line below to
your form and test it; your form should load, but there will be no evidence that
the database stuff is working yet. (See Below!)
5] Create a string object, called Query. We could read all of the
User records into a record set and cycle through them. If we had thousands of
records, this would take both time and memory. If this were a single user
application with a small number of records, this might be ok.
We are designing an application strategy that will be extremely fast for small
and large data sets. We are going to read only a single record at a time. We are
about to design a record to read the Crisanto record (ID = 1) from our
Users table. Add the line below to your form and test it; your form should load,
but there will be no evidence that the database stuff is working yet. (See
Below!)
6] We have established a connection and created a query command. We are now
going to create a data adapter object, called da.
The SqlDataAdapter holds the SQL commands
and connection object for reading and writing data. We are going to initialize
it by passing our SQL Query and our Connection object. The code above creates a new SqlDataAdapter, da. The SQL select
statement specifies what data will be read into a DataSet. The connection
object, Conn, has already been instantiated, but not opened. As you
can see in the diagram above, It is the SqlDataAdapter's responsibility to open
and close the connection when Fill and Update method are called. Add the line below to your form and test it; your form should load, but there
will be no evidence that the database stuff is working yet.
7] The results of our query command are going to be returned to the data
table in our application. We are now going to create a data table object, called
dt. Add the line below to your form and test it; your form should load,
but there will be no evidence that the database stuff is working yet. (See
Below!)
8] Once we have created the data table, we can use the data adapter to
fill it with data in accordance with our diagram above. Add the line below to
your form and test it; your form should load, but there will be no evidence that
the database stuff is working yet. (See Below!)
9] We now have data and can display it! Add the line below to your form
and test it! 10] User_Load can be seen below!SqlConnection Conn = new SqlConnection(ConnectionString);
String Query = "Select * From Users where ID = 1";
SqlDataAdapter da = new SqlDataAdapter(Query, Conn);
DataTable dt = new DataTable();
da.Fill(dt);
MessageBox.Show(dt.Rows[0]["First"].ToString());
Your form should load and the message box should display the following: (See Below!)
1] In accordance with ConnectionStrings.com, let us define our Connection String
to load the Library database on Net1\SQLExpress for user student whose
password is student. Note the need to use the \\ escape sequence in
the connection string. Create a string object, called ConnectionString,
and fill it in accordance with your SQL Server Name. Add the line below to
your form and test it; your form should load, but there will be no evidence that
the database stuff is working yet. (See Below!)String ConnectionString = "Data Source=NET1\\SQLEXPRESS;Initial Catalog=LibraryApp;" +
"Persist Security Info=True;User ID=student; Pwd=student";
2] Your form should once again load and the message box should display the following: (See Below!)
3] Your Users.cs form should work equally well. The compelte code is shown below!
1] As we begin to modularize our code, we will find that we are going to use the Conn object and the dt object in a number of modules. Rather than create multiple connections, or pass it, let us make them available to the entire form. Declare Conn and dt as shown below. Add the other variables as well; we shall use them later.
public partial class Users : Form
{
long RecNo, NoRecords;
SqlConnection Conn;
DataTable dt;
2] A number of the functions in this application can be re-used in multiple applications if generalized a bit. Include the code for functions WindowsAuthenticationConnection and SQLAuthenticationConnection immediately below the declarations for Conn and dt (above)..
3] Test function WindowsAuthenticationConnection with the code below. Substitute your SQLServer!
private void Users_Load(object sender, EventArgs e)
{
WindowsAuthenticationConnection("NET1\\SQLEXPRESS", "LibraryApp");
String Query = "Select * From Users where ID = 1";
SqlDataAdapter da = new SqlDataAdapter(Query, Conn);
DataTable dt = new DataTable();
da.Fill(dt);
MessageBox.Show(dt.Rows[0]["First"].ToString());
}
3] Test function SQLAuthenticationConnection with the code below. Substitute your SQLServer!
private void Users_Load(object sender, EventArgs e)
{
SQLAuthenticationConnection("NET1\\SQLEXPRESS", "LibraryApp", "student", "student");
String Query = "Select * From Users where ID = 1";
SqlDataAdapter da = new SqlDataAdapter(Query, Conn);
DataTable dt = new DataTable();
da.Fill(dt);
MessageBox.Show(dt.Rows[0]["First"].ToString());
}
1] Using your mouse, drag a ListBox from the ToolBox off to the side of your Users form. Using a MessageBox to display very much is a hassle. Name the ListBox lbTrace.
1] We are generally going to have only one record in our DataTable dt; it will be located at Function DisplayRecord shall be used to display the data table field name and content of the the first, and only, record in our DataTable. It is generic and will work for other tables as well. Add the following utility function to your application.
2] Test Function DisplayRecord with the following:
private void Users_Load(object sender, EventArgs e)
{
SQLAuthenticationConnection("NET1\\SQLEXPRESS", "LibraryApp", "student", "student");
String Query = "Select * From Users where ID = 1";
SqlDataAdapter da = new SqlDataAdapter(Query, Conn);
dt = new DataTable();
da.Fill(dt);
RecNo = 1;
NoRecords = 56;
DisplayRecord("Contents Of Record (ID=1)");
}
3] The output in txtTrace should be:
4] As we move to the top, next, previous, and bottom records, it is essential that we be able to display the DataTable information.
1] Add generic Function TableLength to your Users.cs form. If we are going to create a button to go to the Next Record, it will be essential that we know how many records are in the table. Note that this number might continually change in a multi-user (concurrent) database application.
2] Test Function TableLength with the following:
private void Users_Load(object sender, EventArgs e)
{
SQLAuthenticationConnection("NET1\\SQLEXPRESS", "LibraryApp", "student", "student");
String Query = "Select * From Users where ID = 1";
SqlDataAdapter da = new SqlDataAdapter(Query, Conn);
dt = new DataTable();
da.Fill(dt);
RecNo = 1;
NoRecords = TableLength("Users");
DisplayRecord("Contents Of Record (ID=1)");
}
3] The output in txtTrace is once again as follows:
4] Note that the value in the CNoRecords variable was calculated this time; in the previous section, it was simply assigned..
1] Add Function FillFormVariables to your Users.cs form. The responsibility of FillFormVariables is to fill the User Form variables with the data from record 0 in the DataTable Record. This function is non-generic, but there will be a similar function on other forms, such as Books.cs.
2] Test Function FillFormVariables with the following:
private void Users_Load(object sender, EventArgs e)
{
TableName = "Users";
PrimaryKey = "ID";
OrderBy = "FullName";
WindowsAuthenticationConnection("NET1\\SQLEXPRESS", "LibraryApp");
RecNo = 1;
NoRecords = TableLength(TableName);
dt = FillDataTable("Select * From Users where ID = 2");
DisplayRecord("Contents Of Record (ID=2)");
FillFormVariables();
}
3] The output should be as follows:
1] Add Function FillDataTable to your Users.cs form. The responsibility of FillFormVariables explicitly return a DataTable that has been filled with the passed Query.
2] Test Function FillDataTable with the following:
private void Users_Load(object sender, EventArgs e)
{
TableName = "Users";
PrimaryKey = "ID";
OrderBy = "FullName";
WindowsAuthenticationConnection("NET1\\SQLEXPRESS", "LibraryApp");
RecNo = 1;
NoRecords = TableLength(TableName);
dt = FillDataTable("Select * From Users where ID = 2");
FillFormVariables();
}
3] The output should be as follows:
1] In accordance with our other tutorials, the btnClose should be as follows:
1] In accordance with our other tutorials, the ViewUser MenuStrip control seen below
should open your User form with the Show method.
2] Should you choose to close our exiting user form by clicking the X in the top right corner
you would get an error message next time you tried to open it from the main Application form. Even though I have included this code in previous tutorials, I am going to include the code it agian below. Add the code to remove the close functionality from your User form.
//================================================================================//
// CreateParams //
//================================================================================//
// Purpose: Block of code to disable the close box on a form and yet control //
// miniize and maximize functionality. //
// //
// Written By : Dr. Thomas E. Hicks Environment : .NET 2005 //
// Date : 3/1/2008 Language : C# //
//================================================================================//
private const int CS_NOCLOSE = 0x200;
protected override CreateParams CreateParams
{
get
{
CreateParams cp = base.CreateParams;
cp.ClassStyle = cp.ClassStyle | CS_NOCLOSE;
return cp;
}
}
3] I would also add this code to the LibraryApp.cs form.
1] Add Function DisplayFormVariables to your Users.cs form. This function will send a copy of all Form Variables to txtTrace. It should prove to be a valuable diagnostic routine when we begin to have problems with our navigation functions later in this tutorial.
//================================================================================//
// DisplayFormVariables //
//================================================================================//
// Purpose: Display the Form Variables in the listbox called lbTrace. //
// //
// Written By : Dr. Thomas E. Hicks Environment : .NET 2005 //
// Date : 3/1/2008 Language : C# //
//================================================================================//
public void DisplayFormVariables()
{
lbTrace.Items.Add("-----------------------------------------------------");
lbTrace.Items.Add(" Form Variables ");
lbTrace.Items.Add("-----------------------------------------------------");
lbTrace.Items.Add("TableName.........= " + TableName.ToString());
lbTrace.Items.Add("PrimaryKey........= " + PrimaryKey.ToString());
lbTrace.Items.Add("OrderBy...........= " + OrderBy.ToString());
lbTrace.Items.Add("NoRecords.........= " + NoRecords.ToString());
lbTrace.Items.Add("RecNo.............= " + RecNo.ToString());
lbTrace.Items.Add("OrderBy...........= " + OrderBy.ToString());
lbTrace.Items.Add("txtFirst..........= " + txtFirst.Text);
lbTrace.Items.Add("txtMI.............= " + txtMI.Text);
lbTrace.Items.Add("txtLast...........= " + txtLast.Text);
lbTrace.Items.Add("txtMajorID........= " + txtMajorID.Text);
lbTrace.Items.Add("txtAdministrator..= " + txtAdministrator.Text);
lbTrace.Items.Add("txtUserName.......= " + txtUserName.Text);
lbTrace.Items.Add("txtPassword...... = " + txtFirst.Text);
lbTrace.Items.Add("dt->[ID]..........=" + dt.Rows[0]["ID"].ToString());
lbTrace.Items.Add("dt->[Deleted].....=" + dt.Rows[0]["Deleted"].ToString());
lbTrace.Items.Add("dt->[FullName]....=" + dt.Rows[0]["FullName"].ToString());
lbTrace.Items.Add("-----------------------------------------------------");
}
2] Test Function DisplayFormVariables with the following:
private void Users_Load(object sender, EventArgs e)
{
TableName = "Users";
PrimaryKey = "ID";
OrderBy = "FullName";
WindowsAuthenticationConnection("NET1\\SQLEXPRESS", "LibraryApp");
RecNo = 1;
NoRecords = TableLength(TableName);
dt = FillDataTable("Select * From Users where ID = 2");
FillFormVariables();
DisplayFormVariables();
}
3] The output should be as follows:
1] Using your mouse, drag a ToolTip from the ToolBox to your User.cs form. Name the ToolTip toolTip1.
2] Add function LoadToolTips to your User.cs form.
public void LoadToolTips()
{
toolTip1.SetToolTip(btnA, "Go To The First User Whose Last Name Starts With An 'A'");
toolTip1.SetToolTip(btnB, "Go To The First User Whose Last Name Starts With An 'B'");
toolTip1.SetToolTip(btnC, "Go To The First User Whose Last Name Starts With An 'C'");
toolTip1.SetToolTip(btnD, "Go To The First User Whose Last Name Starts With An 'D'");
toolTip1.SetToolTip(btnE, "Go To The First User Whose Last Name Starts With An 'E'");
toolTip1.SetToolTip(btnF, "Go To The First User Whose Last Name Starts With An 'F'");
toolTip1.SetToolTip(btnG, "Go To The First User Whose Last Name Starts With An 'G'");
toolTip1.SetToolTip(btnH, "Go To The First User Whose Last Name Starts With An 'H'");
toolTip1.SetToolTip(btnI, "Go To The First User Whose Last Name Starts With An 'I'");
toolTip1.SetToolTip(btnJ, "Go To The First User Whose Last Name Starts With An 'J'");
toolTip1.SetToolTip(btnK, "Go To The First User Whose Last Name Starts With An 'K'");
toolTip1.SetToolTip(btnL, "Go To The First User Whose Last Name Starts With An 'L'");
toolTip1.SetToolTip(btnM, "Go To The First User Whose Last Name Starts With An 'M'");
toolTip1.SetToolTip(btnN, "Go To The First User Whose Last Name Starts With An 'N'");
toolTip1.SetToolTip(btnO, "Go To The First User Whose Last Name Starts With An 'O'");
toolTip1.SetToolTip(btnP, "Go To The First User Whose Last Name Starts With An 'P'");
toolTip1.SetToolTip(btnQ, "Go To The First User Whose Last Name Starts With An 'Q'");
toolTip1.SetToolTip(btnR, "Go To The First User Whose Last Name Starts With An 'R'");
toolTip1.SetToolTip(btnS, "Go To The First User Whose Last Name Starts With An 'S'");
toolTip1.SetToolTip(btnT, "Go To The First User Whose Last Name Starts With An 'T'");
toolTip1.SetToolTip(btnU, "Go To The First User Whose Last Name Starts With An 'U'");
toolTip1.SetToolTip(btnV, "Go To The First User Whose Last Name Starts With An 'V'");
toolTip1.SetToolTip(btnW, "Go To The First User Whose Last Name Starts With An 'W'");
toolTip1.SetToolTip(btnX, "Go To The First User Whose Last Name Starts With An 'X'");
toolTip1.SetToolTip(btnY, "Go To The First User Whose Last Name Starts With An 'Y'");
toolTip1.SetToolTip(btnZ, "Go To The First User Whose Last Name Starts With An 'Z'");
toolTip1.SetToolTip(btnSearch, "Launch Search User Dialog!");
toolTip1.SetToolTip(btnAdd, "Add A New User!");
toolTip1.SetToolTip(btnDelete, "Delete This User!");
toolTip1.SetToolTip(btnUnDelete, "Undelete Users!");
toolTip1.SetToolTip(btnEdit, "Edit This User!");
toolTip1.SetToolTip(btnPrint, "User Reports & PrintOut Options!");
toolTip1.SetToolTip(btnNextRight, "Go To The LoadNextRecord User!");
toolTip1.SetToolTip(btnPreviousRight, "Go To The LoadPreviousRecord User!");
toolTip1.SetToolTip(btnTopRight, "Go To The First User!");
toolTip1.SetToolTip(btnBottomRight, "Go To The Last User!");
toolTip1.SetToolTip(btnNextLeft, "Go To The LoadNextRecord User!");
toolTip1.SetToolTip(btnPreviousLeft, "Go To The LoadPreviousRecord User!");
toolTip1.SetToolTip(btnTopLeft, "Go To The First User!");
toolTip1.SetToolTip(btnBottomLeft, "Go To The Last User!");
toolTip1.SetToolTip(btnClose, "Close This Form!");
}
3] Test Function LoadToolTips with the following:
private void Users_Load(object sender, EventArgs e)
{
TableName = "Users";
PrimaryKey = "ID";
OrderBy = "FullName";
WindowsAuthenticationConnection("NET1\\SQLEXPRESS", "LibraryApp");
RecNo = 1;
NoRecords = TableLength(TableName);
dt = FillDataTable("Select * From Users where ID = 2");
FillFormVariables();
DisplayFormVariables();
LoadToolTips();
}
//==========================================================================
//================================================================================//
// LoadNextRecord //
//================================================================================//
// Purpose: Load the next logical record into the datatable. If that record //
// deleted, proceed to the Next until you find one that is has not //
// been deleted. //
// //
// Limitation : Infinite loop is caused when all records are deleted. //
// //
// Written By : Dr. Thomas E. Hicks Environment : .NET 2005 //
// Date : 3/1/2008 Language : C# //
//================================================================================//
public void LoadNextRecord()
{
//Not Done Yet
FillFormVariables();
}
//================================================================================//
// LoadPreviousRecord //
//================================================================================//
// Purpose: Load the previous logical record into the datatable. If that record //
// deleted, proceed to the Previous until you find one that is has not //
// been deleted. //
// //
// Limitation : Infinite loop is caused when all records are deleted. //
// //
// Written By : Dr. Thomas E. Hicks Environment : .NET 2005 //
// Date : 3/1/2008 Language : C# //
//================================================================================//
public void LoadPreviousRecord()
{
//Not Done Yet
FillFormVariables();
}
//================================================================================//
// LoadFirstRecord //
//================================================================================//
// Purpose: Load the first logical record into the datatable. If that record //
// deleted, proceed to the Next until you find one that is has not //
// been deleted. //
// //
// Limitation : Infinite loop is caused when all records are deleted. //
// //
// Written By : Dr. Thomas E. Hicks Environment : .NET 2005 //
// Date : 3/1/2008 Language : C# //
//================================================================================//
public void LoadFirstRecord()
{
//Not Done Yet
FillFormVariables();
}
//================================================================================//
// LoadLastRecord //
//================================================================================//
// Purpose: Load the last logical record into the datatable. If that record //
// deleted, proceed to the Previous until you find one that is has not //
// been deleted. //
// //
// Limitation : Infinite loop is caused when all records are deleted. //
// //
// Written By : Dr. Thomas E. Hicks Environment : .NET 2005 //
// Date : 3/1/2008 Language : C# //
//================================================================================//
public void LoadLastRecord()
{
//Not Done Yet
FillFormVariables();
}
//================================================================================//
// LoadFirstLetterRecord //
//================================================================================//
// Purpose: Load the first logical record whose Field begins with the Letter //
// into the datatable. If that record deleted, proceed to the Previous //
// until you find one that is has not been deleted. //
// //
// Limitation : Infinite loop is caused when all records are deleted. //
// //
// Written By : Dr. Thomas E. Hicks Environment : .NET 2005 //
// Date : 3/1/2008 Language : C# //
//================================================================================//
public void LoadFirstLetterRecord(String FieldName, String Letter)
{
//Not Done Yet
FillFormVariables();
}
//================================================================================//
// FillTableWithRecord //
//================================================================================//
// Purpose: Fill the table with the specified record number. //
// //
// Written By : Dr. Thomas E. Hicks Environment : .NET 2005 //
// Date : 3/1/2008 Language : C# //
//================================================================================//
public void FillTableWithRecord(long RecNo)
{
String Query = " SELECT TOP 1 * " +
" FROM " + TableName +
" WHERE [" + PrimaryKey + "] NOT IN " +
" (SELECT TOP " + RecNo.ToString() + " [" + PrimaryKey + "] " +
" FROM " + TableName +
" ORDER BY " + OrderBy + ") " +
" ORDER BY " + OrderBy;
dt = FillDataTable(Query);
}
//=======================================================================================
1] Change the two functions below. Figure out what they do before class on Tuesday!
public Users()
{
InitializeComponent();
TableName = "Users";
PrimaryKey = "ID";
OrderBy = "FullName";
WindowsAuthenticationConnection("NET1\\SQLEXPRESS", "LibraryApp");
RecNo = 1;
NoRecords = TableLength(TableName);
LoadToolTips();
}
private void Users_Load(object sender, EventArgs e)
{
String Query = " SELECT TOP 1 * " +
" FROM Users " +
" WHERE [ID] NOT IN " +
" (SELECT TOP 29 [id] " +
" FROM [Users] " +
" ORDER BY fullName) " +
" ORDER BY fullname ";
dt = FillDataTable(Query);
FillFormVariables();
}