C# database programming pdf

We hope these C projects would be very useful for any C project submission. Our website Freeprojectz. Subscribe our YouTube channel for latest project videos and tutorials Click Here. When a column allows null values, that column is defined as null; otherwise it is defined as not-null. A not-null column in a row must always have value stored in it.

If you tried to add a row but didn't supply a value to a not-null column, then the database would display an error and wouldn't add your new row. Indexes When looking for a particular topic in a book, you can either scan the whole book looking for your topic, or you can use the book's index to find the exact location of the topic directly.

An index for a database table is similar in concept to a book index, except that database indexes are used to find specific rows in a table. The downside of indexes is that when a row is added to the table, additional time is required to update the index for the new row. Generally, you should only create an index on a column when you find that you are retrieving a small number of rows from a table containing many rows. A good rule of thumb is that an index is useful when you expect any single query to retrieve 10 percent or less of the total rows in a table.

This means that the candidate column for an index should be used to store a wide range of values. A good candidate for indexing would be a column containing a unique number for each record, while a poor candidate for indexing would be a column that contains only a small range of numeric codes such as 1, 2, 3, or 4.

This consideration applies to all database types, not just numbers. Note SQL Server automatically creates an index for the primary key column of a table. Normally, the DBA is responsible for creating indexes, but as an application developer, you probably know more about your application than the DBA and will be able to recommend which columns are good candidates for indexing.

You'll learn how to add an index to a table in the "Creating an Index" section later. Column Types Each column in a table has a specific database type. This type is similar to the type of a variable in C , except that a database type applies to the kind of value you can store in a table column. You can only define one timestamp column in a table. Okay, enough theory! The Customers Table The Customers table contains rows that store the details of a company that might place orders with the Northwind Company.

As mentioned earlier, the primary key for the Customers table is the CustomerID column. If you tried to add a row with a primary key already used by a row, then the database would reject your new row. You'll learn more about viewing rows from tables later in the "Building Queries" section. Definition of the Customers Table Table 2. This table shows the column name, database type, length, and whether the column allows null values. The Orders Table The Orders table contains rows that store the orders placed by customer.

You can now see how foreign keys relate information. In this example, the Orders table is the child table, and the Customers table is the parent table. You can think of the foreign key as a pointer from the Orders table to the Customers table. In Figure 2. You'll learn about the Products table next. The Categories table contains the various categories of products. The Suppliers table contains the suppliers of products to the Northwind Company. In this section, you'll learn how to build and run a query to view the orders placed by the customer with a CustomerID of ALFKI, along with the order details and products for the order with an OrderID of This opens the query builder, as shown in Figure 2.

As you can see, the Customers table is initially shown in the Diagram Pane. The pane below is called the Grid Pane, and it shows the details for the columns and rows to be retrieved from the tables. This is initially empty because no query has yet been run. Use the following steps to build the query: 1. This stops all columns from being retrieved from the Customers table. Click the right mouse button in the Diagram Pane, and select Add Table.

Add the Orders and Order Details tables so that you can query these tables. You can also click the Add table button on the toolbar to add tables. You'll notice that after you add the tables, they appear in the Diagram Pane along with lines that connect the parent and child tables through the foreign key.

Select the CustomerID and CompanyName columns from the Customers table by selecting the check boxes to the left of the column names in the Diagram Pane.

This causes the query to retrieve only the rows from the Orders table where the OrderID column is equal to Run the query by clicking the Run button on the toolbar.

In the next section, you'll learn how to create a table using Enterprise Manager. In this section, you'll add a table to the Northwind database to store the details of a person. This table will be called Persons, and will contain the columns shown in Table 2. You'll then see the table designer. Add the columns as shown in Table 2. For example, the int type always uses 4 bytes of storage space, so you can't change the length of an int column from 4. Similarly, the datetime type always uses 8 bytes of storage space.

You can change the length of nchar and nvarchar columns because those types are designed to store variable-length data. Click the Save button on the toolbar to save the table. The Columns Tab In the area beneath the grid, you'll notice a tab named Columns. The Columns tab contains additional information about the currently selected column in the grid, and Figure 2.

As you change your selected column, the information in the Columns tab will change. You can enter an optional description for a column in the Description field of the Columns tab. The Default Value field allows you to supply an initial value when a new row is added to the table; you can of course supply your own value to a column that will override the default value.

The Precision field shows the maximum number of digits that may be used to store a number, including those that might be stored to the right of a decimal point. The Scale field shows the maximum number of digits to the right of a decimal point. For example, the precision and scale of an int column are 10 and 0, meaning that an int column can store up to 10 digits, with no digits to the right of a decimal point-no digits to the right because an int is an integral number.

The precision and scale for a money column are 19 and 4, meaning that a money column can store up to 19 digits, with up to four of those digits to the right of a decimal point. The Identity field allows you specify whether SQL Server should automatically assign a value to a field. If you set the Identity field to Yes, then you can also specify values for the Identity Seed and Identity Increment fields.

You use the Identity Seed field to set the initial value for the column, and you use the Identity Increment field to specify the increment for value. For example, if you set the Identity Seed to 1 and the Identity Increment to 1, then the first value for the column would be 1, the next would be 2, and so on.

The ProductID column of the Products table is an example of a column that uses an identity to set its value. You can then use the output from this function as the Default Value for your uniqueidentifier column.

You'll learn more about SQL Server functions in the next chapter. The Formula field allows you to set a formula that is used to assign a value to a column. You might need to set this when working with foreign languages. To do this, click on the first row in the grid containing the PersonID column, and click the Set primary key button on the toolbar.

Once you've done this, you'll see a small key icon to the left of PersonID. Setting the Permissions To set the permissions for your table, click the Show permissions button on the toolbar of the table designer.

These permissions allow public users to retrieve, add, modify, and remove rows from the Persons table. Creating the Relationship You'll be creating a relationship between your Persons table and the Customers table.

To view the relationships screen, click the Manage Relationships button on the toolbar of the table designer. Click New to start creating the relationship. Pick the Customers table as the primary key table and pick the CustomerID column from this table. Make sure Persons is selected as the foreign key table, and pick the EmployerID column from this table. Enforce relationship for replication Replication allows you to copy information to a different database.

When you enable Enforce relationship for replication, your constraint is applied to the foreign key table when that table is copied to a different database during replication. It also prevents a row in the primary key table from being deleted when there is a matching row in your foreign key table.

Cascade Update Related Fields This causes SQL Server to automatically update the foreign key values of your relationship when the primary key value is modified. Cascade Delete Related Fields This causes SQL Server to automatically remove rows from the foreign key table whenever the referenced row in the primary key table is removed. Click Close to continue. Creating an Index An index allows the database to quickly locate a row when you request retrieval of that row based on a particular column value.

In this section, you'll create an index on the LastName column of your Persons table. Click New to start creating a new index. A filegroup is made up of one or more physical files on a computer's hard disk. SQL Server uses filegroups to store the actual information that makes up a database.

You indicate whether you are creating a unique constraint or index by selecting either the Constraint or Index radio button. Ignore duplicate key If you create a unique index, you can select this option to ignore duplicate key values. Fill factor Unless you are an advanced SQL Server user, you should leave the fill factor in the default setting. The smallest unit of storage in a SQL Server database is a page, which can hold up to 8, bytes of data.

The data for tables and indexes are stored in pages. You can specify how full each index page can be by setting the fill factor. For example, if you set the fill factor to 60 percent, then the page will contain up to 60 percent data and 40 percent empty space.

The amount of empty space on an index page is important because when an index page fills up, SQL Server must split the page to make room for new index data. By reducing the fill factor, therefore, you can increase the performance of your database because SQL Server won't have to split pages so often. Reducing the fill factor, however, also causes the index to take up more hard disk space because there will be more empty space in each page.

If you don't specify a fill factor, then the database's default fill factor is used. This informs SQL Server that it is to use the same percentage you specified in the fill factor field as the space to leave open on each leaf node of the binary tree that makes up the index.

A clustered index is one that contains the actual table rows, rather than pointers to the table rows. Clustered indexes allow faster retrieval of rows, but require more time when inserting new rows. Do not automatically recompute statistics You typically shouldn't use this option as it might reduce performance. When you create an index, SQL Server automatically stores statistical information regarding the distribution of values in your indexed columns.

SQL Server uses these statistics to estimate the cost of using the index for a query. You use the Do Not Automatically Recompute Statistics option to indicate that SQL Server should use previously created statistics, which means that the statistics are not necessarily up to date and could reduce performance.

Creating a Constraint A constraint allows you to define a limit on the value that may be stored in a column. In this section, you'll be creating a constraint on the DateOfBirth column of your Persons table. This constraint will ensure that you can place only dates between January 1, , and December 31, , in the DateOfBirth column.

To view the constraints for your Persons table, click the Manage Constraints button on the toolbar of the table designer. Click New to start creating a new constraint. You must define the label before issuing the GOTO to that label. Before I show you the details of the GOTO statement, you should be aware that its use is considered poor programming practice, and you should avoid it if at all possible. It is usually possible to structure code so that you don't need to use the GOTO statement. Having said that, I've included it in this chapter for completeness.

As mentioned, the GOTO statement requires that you create a label in your program. You do this by placing an identifier containing the label name in your code, followed by a colon :. Any statements that follow your return are not executed. You'll see an example of that later in the "Introducing Stored Procedures" section. You'll typically want to do this if an error occurs in one of your stored procedures, which you'll see how to use later in the section "Creating Stored Procedures.

The description is a message that cannot exceed characters. The severity is the degree of the error and must be between 0 and 18 18 is the most severe error. The state is an arbitrary value that must be between 1 and , and represents information about the invocation state of the error. This might not always be appropriate. For example, you might want to take some action based on the column values retrieved for a particular row.

To do this, you can use a cursor to process rows retrieved from the database one row at a time. You follow these steps when using a cursor: 1. Open your cursor. Fetch the rows from your cursor. Close your cursor. You'll learn the details of these steps in the following sections. For example, you'll want to use an int variable to store the value from an int column, and so on. Notice that each of the 5 rows from the Products table is returned as XML. In this section, you'll learn how to use the ExecuteNonQuery method to execute commands that modify information in the database.

Table 8. The int value returned is the number of database rows affected by the command, if any. ExecuteNonQuery ; The ExecuteNonQuery method returns an int value that indicates the number of rows affected by the command. In this example, the value returned is the number of rows added to the Customers table, which is 1 since one row was added by the INSERT statement.

Listing 8. This program features a procedure named DisplayRow that retrieves and displays the details of a specified row from the Customers table. ExecuteReader ; while mySqlDataReader. CreateCommand ; mySqlCommand. ExecuteNonQuery ; Console. WriteLine "mySqlCommand. The transaction is then committed or rolled back as one unit. For example, in the case of a banking transaction, you might want to withdraw money from one account and deposit it into another.

You would then commit both of these changes as one unit, or if there's a problem, roll back both changes. You'll be introduced to using transactions in ADO. I'll show you how to use an object of the SqlTransaction class in this section.

Create a SqlTransaction object and start the transaction by calling the BeginTransaction method of the SqlConnection object. Set the Transaction property for the SqlCommand object to the SqlTransaction object created in step 1.

This statement adds a row to the Orders table. Commit the transaction using the Commit method of the SqlTransaction object. WriteLine "Committing transaction" ; mySqlTransaction. Commit ; mySqlConnection. By default, transactions are rolled back. Always use the Commit or Rollback methods to explicitly indicate whether you want to commit or roll back your transactions.

For example, in Listing 8. Fortunately, you can use parameters to solve this problem. Parameters allow you specify different column values when running your program.

To execute a command containing parameters, you use the following high-level steps: 1. Create a Command object containing a SQL statement with parameter placeholders. These placeholders mark the position where a parameter will be supplied. Add parameters to the Command object. Set the parameters to specified values. Execute the command. Let's take a look at the details of the four steps when using parameters with SQL Server. A placeholder marks the position where a value will be supplied later.

The syntax you use for the placeholders depends on the database you are using. The column values for this row will be specified using parameters. The type for the column in the database. SqlDbType enumeration. Binary An array of bytes with a maximum length of 8, Bit An unsigned numeric value that can be 0, 1, or a null reference.

Char A string of non-Unicode characters with a maximum length of 8, This is accurate to 3. Float A bit floating-point number between Image An array of bytes with a maximum length of - 1 2,,, Int A bit signed integer between -2,,, and - 1 2,,, Money A currency value between ,,,, NChar A string of Unicode characters with a maximum length of 4, Ntext A string of Unicode characters with a maximum length of - 1 1,,, NVarChar A string of Unicode characters with a maximum length of 4, Real A bit floating-point number between This is accurate to 1 minute.

SmallMoney A currency value between , Text A string of non-Unicode characters with a maximum length of - 1 2,,, Timestamp A date and time in the format yyyymmddhhmmss. TinyInt An 8-bit unsigned integer between 0 and 28 - 1 UniqueIdentifier A bit integer value 16 bytes that that is unique across all computers and networks. VarBinary An array of bytes with a maximum length of 8, VarChar A string of non-Unicode characters with a maximum length of 4, Variant A data type that can contain numbers, strings, bytes, or dates.

The maximum length of the parameter value. You specify this parameter only when using variable length types, for example, Char and VarChar. Earlier in step 1, the CommandText property for mySqlCommand had three placeholders and was set as follows: mySqlCommand. NChar, 5 ; mySqlCommand. NVarChar, 40 ; mySqlCommand. This requires some explanation. A SqlCommand object stores parameters using a SqlParameterCollection object, which is a collection of SqlParameter objects a SqlParameter object contains the details of a parameter.

Therefore, to add a parameter to mySqlCommand, you call the Add method through its Parameters property. As you can see from the previous code that added the three parameters to mySqlCommand, the CustomerID parameter is defined as an NChar-a string of Unicode characters with a maximum length of 4, A value of 5 is passed as the third parameter to the Add method for CustomerID, meaning that a maximum of five characters may be supplied as the parameter value.

Similarly, the CompanyName and ContactName parameters are defined as an NVarChar-a string of Unicode characters-with a maximum length of 40 and 30 characters respectively, as indicated by the third parameter to the Add method.

You'll see the setting of these parameters to values in the next step. Step 3: Set the Parameters to Specified Values You use the Value property of each parameter to set it to a specified value in your Command object.

These values are substituted for the placeholders in your SQL statement. The following example uses the Value property to set the values of the parameters added in the previous section: mySqlCommand. Parameters[" CustomerID"]. Parameters[" CompanyName"]. Parameters[" ContactName"]. You can also add a parameter and set its value in one step. For example: mySqlCommand. NChar, 5. As you learned in Chapter 2, "Introduction to Databases," a column defined as null can store a null value.

A null value indicates that the column value is unknown. You indicate that a parameter can accept a null value by setting the IsNullable property to true the default is false.

DBNull class. Value; The DBNull. Value property returns a null value. Step 4: Execute the Command To execute the command, you use one of your Command object's execute methods. In Table 8.

There are a couple of ways you can execute a stored procedure depending on whether your procedure returns a result set a result set is one or more rows retrieved from a table by a SELECT statement. You'll learn these two ways to execute a stored procedure next.

Add any required parameters for the procedure call to your Command object, remembering to set the Direction property for any output parameters to ParameterDirection. Execute your Command object using the ExecuteNonQuery method. You use this method because the procedure doesn't return a result set. Read the values of any output parameters.

These examples will show you the possible ways to execute a stored procedure using ADO. NET and read the output parameters. The procedure you saw was named AddProduct , and Listing 8. You saw how to run this script in Chapter 4. If you didn't already run this script when reading Chapter 4, and you want to run the example C program shown later, you'll need to run this script.

Because AddProduct doesn't return a result set, you use the first set of steps outlined earlier. Let's examine the details of these four steps to execute this stored procedure. Step 2: Add Any Required Parameters to the Command Object Your second step is to add any parameters to your Command object, remembering to set the Direction property for any output parameters to ParameterDirection.

In this example, AddProduct expects an output parameter to store the ProductID for the new row, and you therefore need to add an output parameter to your Command object. You do this by setting the Direction property of your parameter to ParameterDirection.

Int ; mySqlCommand. Parameters[" MyProductID"]. NVarChar, Output; mySqlCommand. Value ; mySqlConnection. You'll need to run this script before running the C program. Because AddProduct2 doesn't return a result set of rows, you use the same four steps shown in the previous section to execute the procedure using ADO. But can't decide which way I can achieve this goal in my project. Alternative of Global Variable.

I dig down more and come up with an idea called static. Now big question come up where I put this static string variable in my project so that I can get the value during the application life time. As I told you before I am novice in C development so i chose the shortcut way to do it May be this is not the right way, but It works in this project, Hope someone correct me. I put it in the Program. Now Program class become like this:. Finally I put a message box in my 2nd window form named FrmMain at load event just for testing.

I like to address one thing, in this code i don't use encrypted password, but I strongly suggest you to make your password encrypted. There are lots of scopes to achieve this with different ways. You can work on that or give me suggestion about the miss leading concept Which may be my lack of knowledge about C I use in this project. So that we beginner programmer can get clear idea about this kind of project development.

Here are some key points I like to share with you. Sign in Email. Forgot your password? Search within: Articles Quick Answers Messages. Tagged as Win Stats C Project on Database for Beginners.

Hasan Habib Surzo Rate me:. Please Sign up or sign in to vote. Download source - 1. Here is a basic procedure User runs the software. Software waits for username and password. User enters username and password. Software open database connection and query with the user input.

If Software found the User, it allows User to enter next win form. Software passes username to the next win form. Background I am a beginner C developer. Using the code Create table script: SQL. Copy Code. Hasan Habib Surzo. Software Developer Senior. I am very positive about life and a born explorer. I set my personal goal to be a good human being,. Beroec82 7-Jan Pleby Sep Member Dec Member 6-Nov Member Jun Member Sep Hasan Habib Surzo Apr BillW33 Jan Member 7-Jan Hasan Habib Surzo 8-Jan Paul Conrad 6-Jan Sander Rossel 3-Jan Sander Rossel Apr Visit my blog at Sander's bits - Writing the code you need.

Or read my articles at my CodeProject profile. Simplicity is prerequisite for reliability. We also saw how we read each row of the table and use a messagebox to display the contents of a table to the user. From the above data structure, the user would ideally want to see the TutorialID and Tutorial Name displayed in a textbox.

Secondly, they might want to have some sort of button control which could allow them to go to the next record or to the previous record in the table. The good news is that C can reduce the additional coding effort by allowing binding of controls to data. What this means is that C can automatically populate the value of the textbox as per a particular field of the table.

So, you can have 2 textboxes in a windows form. You can then link one text box to the TutorialID field and another textbox to the TutorialName field. Visual Studio will ensure that it writes the code for you to ensure the linkage works. Then when you run your application, the textbox controls will automatically connect to the database, fetch the data and display it in the textbox controls. In our example, we are going to create 2 textboxes on the windows form. Step 1 Construct the basic form.

In the form drag and drop 2 components- labels and textboxes. Then carry out the following substeps. Step 2 The next step is to add a binding Navigator to the form. The binding Navigator control can automatically navigate through each row of the table. To add the binding navigator, just go to the toolbox and drag it to the form. Step 3 The next step is to add a binding to our database.

The Binding Navigator is used to establish a link from your application to a database. When you perform this step, Visual Studio will automatically add the required code to the application to make sure the application is linked to the database. So to ensure the connection is established between the application and the database, the first step is to create a project data source. The following screen will show up. When you click on the project data source, you will be presented with a wizard; this will allow you to define the database connection.

Step 4 Once you click on the Add Project Data Source link, you will be presented with a wizard which will be used to create a connection to the demotb database. The following steps show in detail what needs to be configured during each step of the wizard.