Tutorial: MSSQL- Windows Form Development
Multi-User Application With Custom Record Lockout #1
[Database Configuration, Database Authentication, Generic Database Utilities,
Etc.]
Dr. Thomas E. Hicks
Computer Science Department
Trinity University
The purpose of this series of tutorials is to show how to
configure a Windows Application Form, using Visual Studio, to process a
multi-form, multi-user network application. This tutorial will briefly review
many of the concepts demonstrated in some of the other tutorials.
Prerequisite Condition: each of our major database tables must have a unique,
auto-incremented, integer field called ID.
This portion of the tutorial will examine and configure database connections, Database Authentication, and a variety of Generic Database utilities.
Part I-A: Add The Database To Your MSSQL Server
Part I-B: Examine Your Database Layouts
Part I-C:
Configure The Microsoft SQL Server Accounts & PermissionsPart I-D:
Download Visual Studio Library-Net-Multi-UserPart I-E:
LibraryApp Form ClassesPart I-F: Connect To The Database With Windows Authentication
Part I-G:
Connect To The Database With SQLAuthenticationPart I-H: Generic Table Function Utilities - FillDataTable, TableLength, ReadRecord, DisplayRecord
Part I-I:
Utilities Specific To Users - Non Generic - FillFormVariables
If You Have Difficulty With This Tutorial, You Might Find These Help
Prepare You For This Tutorial
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 can work through this series of tutorials with us. I encourage you to download and install the Microsoft SQL Server LibraryApp database. You may install this database by either (a) upsizing the database from Access or (2) restoring LibraryApp.ldf and LibraryApp.mdf.
2003-Access-LibraryApp.zip 2007-Access-LibraryApp.zip
- A tutorial with directions:
Tutorial: MSSQL- Import To MS-SQL-Server From 2003 Access Database
Tutorial: MSSQL- Import To MS-SQL-Server From 2007 Access Database
2005-SQL-Server-LlibraryApp-Restore.zip
- A tutorial with directions:
Tutrial: MSSQL
Import-Export-Backup-MSSQL-Database
1] The Books table is described below.
2] The Genres table is described below.
3] The Majors table is described below.
4] The TransactDetails table is described below.
5] The Transactions table is described below.
6] The Users table is described below.
7] Note that each of our major database tables has a unique, auto-incremented, integer field called ID.
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] Although you can certainly do your own application, it would be a lot faster if you downloaded and extracted the following files into directory C:\Library-Net-Multi-User-1
2005-Library-Net-Multi-User-1.zip
2008-Library-Net-Multi-User-1.zip
2] When you extract the files, you should get the following:
3]
For the sake of compatibility, I made both a Visual Studio 2005 Project and a
Visual Studio 2008 Project available; all of the code in these tutorials will
work with both Visual Studio 2005 and Visual Studio 2008.
4] I have opened the application with Visual Studio 2008 below.
1] Descriptions of the form classes, that are included in the project download, may be found below. These descriptions might suffice for those who have not read the other tutorials; they will serve as a review for those who have.
1] Execute the program. In addition to those tutorials, above, that demonstrate the use of Visual Studio controls, the menustrip notions, introduced in the tutorial below have been used throughout the application.
Tutorial:MSSQL-Windows-Forms-MenuStrip-Apps
2] MainMenu.cs is a MDI container that will house all of the other forms associated with this Library Application.
3] Execute the program. Select/Push the Exit choice on the menustrip; it should terminate the execution. Try it! The Exit menustrip choice, in the code below, executes the command Application.Exit( );
4] The MainMenu.cs code is listed below. As you can see from the code, the LoginForm, the UserForm, and the BookForm are created in MainMenu. The CreateParams code removes the close button from the form.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
namespace LibraryApp
{
public partial class MainMenu : Form
{
Login LoginForm;
Books BookForm;
Users UserForm;
//================================================================================//
// Constructors //
//================================================================================//
// Purpose: Blank Constructor. //
// //
// Written By : Dr. Thomas E. Hicks Environment : .NET 2005/8 //
// Date : xx/xx/xxxx Language : C# //
//================================================================================//
public MainMenu()
{
InitializeComponent();
}
//================================================================================//
// Form Load Events //
//================================================================================//
// Purpose: Do all that is necessary to load and initialize this basic form. //
// //
// Written By : Dr. Thomas E. Hicks Environment : .NET 2005/8 //
// Date : 3/1/2008 Language : C# //
//================================================================================//
private void MainMenu_Load(object sender, EventArgs e)
{
LoginForm = new Login(this);
LoginForm.MdiParent = this;
BookForm = new Books(this);
BookForm.MdiParent = this;
UserForm = new Users (this);
UserForm.MdiParent = this;
}
//================================================================================//
// 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 2008 //
// 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;
}
}
private void Login_Click(object sender, EventArgs e)
{
LoginForm.Show();
}
private void Exit_Click(object sender, EventArgs e)
{
Application.Exit();
}
private void ViewBooks_Click(object sender, EventArgs e)
{
BookForm.Show();
}
private void ViewUsers_Click(object sender, EventArgs e)
{
UserForm.Show();
}
}
}
1] Execute the program. When you select Login, from the MenuStrip, the application will show the LoginForm. Try it! It should look something like the following:
2] Execute the program and launch the LoginForm. Select/Push the Cancel button on the form; it should hide the LoginForm. Try it! The Cancel button, in the code below, executes the command this.Hide( );
3] The Login.cs code is listed below. The CreateParams code removes the close button from the form.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
namespace LibraryApp
{
public partial class Login : Form
{
MainMenu pf;
//================================================================================//
// Constructors //
//================================================================================//
// Purpose: Blank Constructor and one which accepts information from the Parent //
// MainMenu. //
// //
// Written By : Dr. Thomas E. Hicks Environment : .NET 2005/8 //
// Date : xx/xx/xxxx Language : C# //
//================================================================================//
public Login()
{
InitializeComponent();
}
public Login(MainMenu Parent)
{
InitializeComponent();
pf = new MainMenu();
pf = Parent;
}
//================================================================================//
// 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;
}
}
private void btnCancel_Click(object sender, EventArgs e)
{
this.Hide();
}
}
}
1] Execute the program. When you select Users->View Users, from the MenuStrip, the application will show the UserForm. Try it! It should look something like the following:
2] Note that the listbox, called lbtrace, is there for diagnostic and testing displays. If you close the right portion of the window in Visual Studio, lbtrace is hidden and out of the way for the final application. Should we make changes to the application, we can simply drag the form wider and examine our testing output once more.
3] The application was recompiled and executed below; note that lbtrace is hidden and out of the way.
4] Execute the program and launch the UserForm. Select/Push the Close menustrip choice on the UserForm; it should hide the LoginForm. Try it! The Close menustrip choice, in the code below, executes the command this.Hide( );
5] If you do not understand the use of constructor Users(MainMenu Parent) to pass information between the parent form and the child form, I recommend the following tutorial.
6] The Users.cs code is listed below. The CreateParams code removes the close button from the form.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
namespace LibraryApp
{
public partial class Users : Form
{
MainMenu pf;
//================================================================================//
// Constructors //
//================================================================================//
// Purpose: Blank Constructor and one which accepts information from the Parent //
// MainMenu. //
// //
// Written By : Dr. Thomas E. Hicks Environment : .NET 2005/8 //
// Date : xx/xx/xxxx Language : C# //
//================================================================================//
public Users()
{
InitializeComponent();
}
public Users(MainMenu Parent)
{
InitializeComponent();
pf = new MainMenu();
pf = Parent;
}
//================================================================================//
// Form Load Events //
//================================================================================//
// Purpose: Do all that is necessary to load and initialize this basic form. //
// //
// Written By : Dr. Thomas E. Hicks Environment : .NET 2005/8 //
// Date : 3/1/2008 Language : C# //
//================================================================================//
private void Users_Load(object sender, EventArgs e)
{
}
//================================================================================//
// 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/8 //
// 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;
}
}
private void CloseWindow_Click(object sender, EventArgs e)
{
this.Hide();
}
}
}
1] Execute the program. When you select Books->View Books, from the MenuStrip, the application will show the BookForm. Try it! It should look something like the following:
2] As was the case with the UserForm, there is a listbox, called lbtrace, that is there for diagnostic and testing displays. If you close the right portion of the window in Visual Studio, lbtrace is hidden and out of the way for the final application. Should we make changes to the application, we can simply drag the form wider and examine our testing output once more.
3] The application was recompiled and executed below; note that lbtrace is hidden and out of the way.
4] Execute the program and launch the BookForm. Select/Push the Close menustrip choice on the BookForm; it should hide the LoginForm. Try it! The Close menustrip choice, in the code below, executes the command this.Hide( );
5] If you do not understand the use of constructor Books(MainMenu Parent) to pass information between the parent form and the child form, I recommend the following tutorial.
6] The Books.cs code is listed below. The CreateParams code
removes the close button from the form.using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
namespace LibraryApp
{
public partial class Books : Form
{
MainMenu pf;
//================================================================================//
// Constructors //
//================================================================================//
// Purpose: Blank Constructor and one which accepts information from the Parent //
// MainMenu. //
// //
// Written By : Dr. Thomas E. Hicks Environment : .NET 2005/8 //
// Date : xx/xx/xxxx Language : C# //
//================================================================================//
public Books()
{
InitializeComponent();
}
public Books(MainMenu Parent)
{
InitializeComponent();
pf = new MainMenu();
pf = Parent;
}
//================================================================================//
// Form Load Events //
//================================================================================//
// Purpose: Do all that is necessary to load and initialize this basic form. //
// //
// Written By : Dr. Thomas E. Hicks Environment : .NET 2005/8 //
// Date : 3/1/2008 Language : C# //
//================================================================================//
private void Books_Load(object sender, EventArgs e)
{
}
//================================================================================//
// 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/8 //
// 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;
}
}
private void CloseWindow_Click(object sender, EventArgs e)
{
this.Hide();
}
}
}
1] The RecLock form is not yet created or launched; we shall do that later. It will look something like the following:
2] Execute the program and launch the LoginForm. Select/Push the Cancel button on the form; it should hide the LoginForm. Try it! The Cancel button, in the code below, executes the command this.Hide( );
3] The Login.cs code is listed below. The CreateParams code removes the close button from the form.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
namespace LibraryApp
{
public partial class RecLock: Form
{
//================================================================================//
// Constructor //
//================================================================================//
// Purpose: Blank Constructor //
// //
// Written By : Dr. Thomas E. Hicks Environment : .NET 2005/8 //
// Date : xx/xx/xxxx Language : C# //
//================================================================================//
public RecLock()
{
InitializeComponent();
}
//================================================================================//
// 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;
}
}
//================================================================================//
// Form Load Events //
//================================================================================//
// Purpose: Do all that is necessary to load and initialize this basic form. //
// //
// Written By : Dr. Thomas E. Hicks Environment : .NET 2005/8 //
// Date : 3/1/2008 Language : C# //
//================================================================================//
private void RecLock_Load(object sender, EventArgs e)
{
}
private void btnCancel_Click(object sender, EventArgs e)
{
this.Close();
}
}
}
1] Throughout this tutorial, I am going to emphasize software testing and modularization. I would like to create a series of functions that can be used for multiple forms on many databases when possible.
2] Microsoft SQL Server offers two authentication techniques; they are Windows Authentication and SQL Server Authentication. We can use Windows Authentication when our application is running on the SQL Server computer. We can use SQL Server Authentication when our application is running on a computer, other than the SQL Server computer. We use SQL Server Authentication for multi-user applications running on a variety of computers.
3] We are going to discuss and write code in a specific format and then move it to a more general format that can be modularized and re-used.
1] One of the easier ways to identify the name of your SQL Server database engine is to start Microsoft SQL Server Management Studio Express or Micr.osoft SQL Server Management Studio. The server name is automatically identified; the name of my SQL server instance is Net1\SQLExpress. (See Below!)
2] This name is also available once you start Microsoft SQL Server Management Studio Express. Note the name of your SQL Server database engine! (See Below!)
1] We are going to use SQL in a number of our forms. Take a moment and include the System.Data.SqlClient Namespace at the top of the Users.cs, Login.cs, Books.cs, RecLock.cs, and MainMenu.cs.
1] Open the User.cs form. Paste the block of code below into Users.cs. Compile and Execute. the program should run!
//================================================================================// // TestingMaster // //================================================================================// // Purpose: Testing Master which evokes TestModule1, TestModule2, ... // // // // Written By : Dr. Thomas E. Hicks Environment : .NET 2005/8 // // Date : xx/xx/xxxx Language : C# // //================================================================================// public void TestingMaster() { int UserClassDiagnosticLevel = 1; if (UserClassDiagnosticLevel == 1) TestModule1(); if (UserClassDiagnosticLevel == 2) TestModule2(); if (UserClassDiagnosticLevel == 3) TestModule3(); if (UserClassDiagnosticLevel == 4) TestModule4(); if (UserClassDiagnosticLevel == 5) TestModule5(); if (UserClassDiagnosticLevel == 6) TestModule6(); if (UserClassDiagnosticLevel == 7) TestModule7(); if (UserClassDiagnosticLevel == 8) TestModule8(); if (UserClassDiagnosticLevel == 9) TestModule9(); if (UserClassDiagnosticLevel == 10) TestModule10(); if (UserClassDiagnosticLevel == 11) TestModule11(); } //================================================================================// // TestModule1 // //================================================================================// public void TestModule1() { MessageBox.Show("Testing TestModule1"); } //================================================================================// // TestModule2 // //================================================================================// public void TestModule2() { MessageBox.Show("Testing TestModule2"); } //================================================================================// // TestModule3 // //================================================================================// public void TestModule3() { MessageBox.Show("Testing TestModule3"); } //================================================================================// // TestModule4 // //================================================================================// public void TestModule4() { MessageBox.Show("Testing TestModule4"); } //================================================================================// // TestModule5 // //================================================================================// public void TestModule5() { MessageBox.Show("Testing TestModule5"); } //================================================================================// // TestModule6 // //================================================================================// public void TestModule6() { MessageBox.Show("Testing TestModule6"); } //================================================================================// // TestModule7 // //================================================================================// public void TestModule7() { MessageBox.Show("Testing TestModule7"); } //================================================================================// // TestModule8 // //================================================================================// public void TestModule8() { MessageBox.Show("Testing TestModule8"); } //================================================================================// // TestModule9 // //================================================================================// public void TestModule9() { MessageBox.Show("Testing TestModule9"); }
//================================================================================//
// TestModule10 //
//================================================================================//
public void TestModule10()
{
MessageBox.Show("Testing TestModule10");
}
//================================================================================//
// TestModule11 //
//================================================================================//
public void TestModule11()
{
MessageBox.Show("Testing TestModule11");
}
1] Open the User.cs form. Using the mouse, double-click on the Testing button to create the on_click event for that button. (See Below!)
2] Include a function call to TestingMaster. (See Below!)
3] Execute the Users Test Module. By executing the test module, I mean (a) run the application, (b) start the Users form, and (c) Push/Select Testing.. It should launch the dialog box. (See Below!)
1] Since Windows Authentication is the easiest to connect, we shall demonstrate that first. Replace the test code in TestModule1 with the following; always include the documentation. Change the Server Manager Name to yours! Read through the explanation below as you type in each line; if there are lines of code you do not understand, do a google search for clarification. Adjust your datasource as is appropriate.
2] 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!)
MSSQL-28.gifString 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!)
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!
11] Do not proceed until you get this working. Double-check the configuration
above if problems. You must be running this program on the computer which
contains the SQL Server.SqlConnection Conn = new SqlConnection(ConnectionString);
5] Create 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.
String Query = "Select * From Users where ID = 1";
SqlDataAdapter da = new SqlDataAdapter(Query, Conn);
DataTable dt = new DataTable();
da.Fill(dt);
lbTrace.Items.Add("First = " + dt.Rows[0]["First"].ToString());
lbTrace.Items.Add("Last = " + dt.Rows[0]["Last"].ToString());
lbTrace.Items.Add("ID = " + dt.Rows[0]["ID"].ToString());
10] Execute the Users Test Module. Your trace output should be as following: (See Below!)
1] If you have not already, you are going to eventually make some mistakes. Change the UserClassDiagnosticLevel to 2. (See Below!)
2] Copy the code from TestModule1 to TestModule4 and make the following changes. I want you to intentionally provide a non-existent SQL Server Manager. I have changed mine from Net1 to Net11. I would like you to do likewise! Adjust your datasource to something that is not appropriate.
3] Execute the Users Test Module. If you execute the program in the debug mode you get the following error message and that may indeed be helpful. The delay is awful because it is trying, over and over, to find the server; it eventually times out!
3] If you execute the program without the debug mode you get the following error message and that is generally less helpful. The delay is still awful because it is trying, over and over, to find the server; it eventually times out!
4] We could have problems with the connection because:
5] Change your Testmodule2 so that it traps the error. This is certainly not the best of all testing, but far better than none. We do not want the system to have a hard crash. The code below has a wrong datasource, a wrong database, and a wrong query. It still takes a few minutes to time out, but other components of the system can still be used --> try Books or Login!
6] Execute the Users Test Module. You should get the following output!
1] Change the UserClassDiagnosticLevel to 3. (See Below!)
2] We might very well want to create one connection and then use it for all of the processing on our form. This connection, called Conn, needs to be available to all functions on the form. Add SqlConnection Conn to those functions available to the the Users class.
3] Write function WindowsAuthenticationConnection which accept the SQLServer and the Database as arguments and then connects Conn to the database.
4] Copy the code from TestModule2 to TestModule3 and make the following changes. Adjust your datasource as is appropriate.
5] Execute the Users Test Module. You should get the following output!
1] We can use the WindowsAuthenticationConnection function, as is, in Books.cs and forms in other databases.
1] We will need to use SQLAuthenticationConnection for this multi-user application.
1] Change the UserClassDiagnosticLevel to 4. (See Below!)
2] Copy the code from TestModule1 to TestModule4 and make the following changes. Adjust your datasource as is appropriate. This uses the student account created earlier in this tutorial.
3] Execute the Users Test Module. You should get the following output!
1] Change the UserClassDiagnosticLevel to 5. (See Below!)
2] Once again we are going to use the SqlConnection Conn object defined earlier.
3] Write function SQLAuthenticationConnection which accept the SQLServer, the Database, the UserName, and the Password as arguments and then connects Conn to the database.
3] Copy the code from TestModule3 to TestModule5 and make the following changes.
4] Execute the Users Test Module. You should get the following output!
1] The functions in this section will be usable with other tables and databases.
2] See
FillDataTable, TableLength, ReadRecord, and DisplayRecord below.1] By passing a query to an open connection, we can fill a data table with the results of that query.
2] This process of filling the data table may be successful or unsuccessful. We will begin with successful queries and move toward those that are not successful.
3] Make datatable dt available to all of the functions in the Users class; we are going to use this datatable to store the current display record. Declare it at the top with pf and Conn. (See Below!)
4] Change the UserClassDiagnosticLevel to 6. (See Below!)
5] Write function FillDataTable1 which accept a query string and a data table as arguments; after the dataset is filled with the query, the function will explicitly return true.
6] Copy the code from TestModule5 to TestModule6 and make the following changes.
6] Execute the Users Test Module. You should get the following output!
1] Change the UserClassDiagnosticLevel to 7. (See Below!)
2] Write function FillDataTable which accept a query string and a data table as arguments. The function will attempt to fill the datatable with the query; if the query successfully places at least one record in the datatable, then true is returned; otherwise false.
3] Copy the code from TestModule6 to TestModule7 and make the following changes.
4] This should take a while to executed because the server connection must time out. Execute the Users Test Module. You should get the following output! The idea is to make this much more bullet-proof in robustness.
5] I would recommend verifying the return each and every time you call FillDataTable!
1] Change the UserClassDiagnosticLevel to 8. (See Below!)
2] 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.
3] Copy the code from TestModule7 to TestModule8 and make the following changes.
4] Execute the Users Test Module. You should get the following output!
5] Open Microsoft SQL Server Management Studio Express and view the Users; you can see that we do have 56 users.
6] Open Microsoft SQL Server Management Studio Express and view the Books; you can see that we do have 71 books.
7] Open Microsoft SQL Server Management Studio Express and view the Majors; you can see that we do have 23 majors.
8] Open Microsoft SQL Server Management Studio Express and check your data files to confirm that TableLength is working properly.
1] We are going to use LogRecNo to represent the current logical record displayed by our form. We are going to use NoLogRecs to represent the number of records that are currently in the potential selection set. This selection set will vary in accordance with the order and filters associated with the application.
2] Add LogRecNo, NoLogRecs, and LogRecID to Users; they should be available to all of the functions of the Users class. (See Below!)
3] Alter your Users_Load event to authenticate with the database. We shall initialize NoLogRecs and LogRecNo. (See Below!)
4] Execute the Users Test Module. It should still run.
1] Change the UserClassDiagnosticLevel to 9. (See Below!)
2] Add generic Function ReadIDRecord to your Users.cs form.
3] Copy the code from TestModule8 to TestModule9 and make the following changes.
4] Execute the Users Test Module. You should get the following output!
1] Change the UserClassDiagnosticLevel to 10. (See Below!)
2] Add generic Function DisplayRecord to your Users.cs form. We are generally going to have only one record in our DataTable dt; function DisplayRecord shall be used to display the data associated with the datatable. It is generic and will work for other tables as well.
3] Copy the code from TestModule9 to TestModule10 and make the following changes.
4] Execute the Users Test Module. You should get the following output!
1] Each and every database table has a Logical Order and a Physical Order. The Physical Order is the order in which the records in the table have been added; if the table has an auto incremented ID field, the Physical Order will also be in order by ID.
2] We often wish to view records in Logical Order. The Order By component of the Query will define the Logical Order.
3] Filters are often used to select a subset of the original data.
4] Although we are trying to write as many generic utilities as possible, the task of placing the the data from the datatable into the fields on the form can not be easily generalized.
1] Suppose we do the Query :
Select * From Users. The records will be displayed in Physical Order. The users has an auto incremented ID field that helps to confirm this in the snapshot below; note that this primary key helps to confirm the order. If one were to cycle through the records in which the ID = 1, ID =2, ID =3, etc <== this would be referred to as Physical Order.
2] Suppose we do the Query : Select * From Users Order By FullName. The records will be displayed in Logical Order. Note that this order is different than the Physical Order above. If one were to cycle through the records in which the ID = 8, ID = 9, ID = 11, etc but the critical issue here is that cycling through the records would provide Agoston, Akers, Aldana, etc. (because of the Order By) <== this would be referred to as logical order.
3] Most applications display data in some type of Logical Order. It is critical that our Next and Previous buttons cycle through our records in Logical Order.
1] Change the UserClassDiagnosticLevel to 11. (See Below!)
2] Add non-generic 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.
3] Copy the code from TestModule10 to TestModule11 and make the following changes.
4] Execute the Users Test Module. Before you push the Testing button you should get the following output!
5] After you push the Testing button you should get the following output!
