How do I delete a record in the table using ADO.Net dataset?

To delete a record, you first get the DataRow object from the DataTable object. Then you simply call the Delete() method of the data row object to delete a record from the data table.

C# Version
DataTable dt = ds.Tables["Article"];
                          
// Delete
DataRow dr = dt.Rows[3];
dr.Delete();

 

da.Update(ds, "Article");

ds.AcceptChanges();

VB.Net Version
Dim dt As DataTable
dt = ds.Tables("Article")

 

' Delete

Dim dr As DataRow

dr = dt.Rows(3)

dr.Delete()

 

da.Update(ds, "Article")

ds.AcceptChanges()

 

What is the architecture of connected environment of data access in ADO.Net?

The connected environment of data access is the traditional procedure for accessing data programmatically. The differentiating property of the connected data access environment is that here you (the programmer) is required to manage the connection with the database. You can only perform database operations when, there exists an open connection to the database. Hence, before performing any database operation (select, update, insert, delete), the programmer opens a database connection and close the database connection after performing the database operations.

The important objects for working in the connected environment are:

  • Connection (SqlConnection, OleDbConnection, etc): It represents a connection to the database. All the connection objects in the ADO.Net implement the System.Data.IDbConnection interface.
  • Command (SqlCommand, OleDbCommand, etc): It represents the SQL command sent to the database, e.g., SELECT, UPDATE, INSERT, DELETE. All commands in ADO.Net implements IDbCommand interface
  • DataReader (SqlDataReader, OleDbDataReader, etc): It represents a data stream that can be used to read the result of your query returned by the database server. Using this object, you read the individual records and their fields returned as a result of your query to the database server. All the data readers in ADO.Net implement the System.Data.IDataReader interface.

 

How do I read data (or records) from database using data reader?

To read data from the database, you first make a connection object (SqlConnection, etc) and open it.

C# Version
         string connString = "server=FARAZ; database=programmersheaven;" +
                                  "uid=sa; pwd=";
         SqlConnection conn = new SqlConnection(connString);
         conn.Open();
VB.Net Version
        Dim connString As String = "server=siraj; database=programmersheaven;" + _
                                   "uid=sa; pwd="
        Dim conn As New SqlConnection(connString)
           conn.Open()

Then you create a command using this connection and the command text.

C# Version
         string cmdString = "select * from author";
         SqlCommand cmd = new SqlCommand(cmdString, conn);
VB.Net Version
        Dim cmdString As String = "select * from author"
        Dim cmd As New SqlCommand(cmdString, conn)

Then you execute the command with the command object’s ExecuteReader() method. The ExecuteReader method returns the object of type IDataReader

C# Version
         SqlDataReader reader = cmd.ExecuteReader();
VB.Net Version
        Dim reader As SqlDataReader = cmd.ExecuteReader()

Now you read the individual records using this data reader. To advance to the next record, you call its Read() method which returns Boolean to indicate if there exists a next row. If the DataReader’s Read() method returns true then the DataReader acts as a database row (record). Now you can access the fields of this particular row specifying the column names (or integral indexes) in its indexers.

C# Version
         while(reader.Read())
         {
                 txtData.Text += reader["authorId"].ToString();
                 txtData.Text += ", "; 
                 txtData.Text += reader["name"].ToString();
                 txtData.Text += "\r\n";
         }
VB.Net Version
        While reader.Read()
            txtData.Text += reader("authorId").ToString()
            txtData.Text += ", "
            txtData.Text += reader("name").ToString()
            txtData.Text += vbCrLf
        End While

Finally, you need to close the database connection opened before performing the database operation (SELECT, in our case)

C# Version
         conn.Close();
VB.Net Version
        conn.Close()

Let’s look at the complete code now for review

C# Version
         string connString = "server=siraj; database=programmersheaven;" +
                                  "uid=sa; pwd=";
         SqlConnection conn = new SqlConnection(connString);
         string cmdString = "select * from author";
         SqlCommand cmd = new SqlCommand(cmdString, conn);

 

         conn.Open();

         SqlDataReader reader = cmd.ExecuteReader();

         while(reader.Read())

         {

                 txtData.Text += reader["authorId"].ToString();

                 txtData.Text += ", ";

                 txtData.Text += reader["name"].ToString();

                 txtData.Text += "\r\n";

         }

         conn.Close();

VB.Net Version
        Dim connString As String = "server=siraj; database=programmersheaven;" + _
                                   "uid=sa; pwd="
        Dim conn As New SqlConnection(connString)
        Dim cmdString As String = "select * from author"
        Dim cmd As New SqlCommand(cmdString, conn)

 

        conn.Open()

        Dim reader As SqlDataReader = cmd.ExecuteReader()

 

        While reader.Read()

            txtData.Text += reader("authorId").ToString()

            txtData.Text += ", "

            txtData.Text += reader("name").ToString()

            txtData.Text += vbCrLf

        End While

 

        conn.Close()

 

How do I insert records using data reader?

The procedure for updating records using INSERT commands is very similar to the one we presented in the previous example (of SELECT) except that here the command does not return anything and thus the method to call on the SqlCommand object is called ExecuteNonQuery().

C# Version
         string connString = "server=FARAZ; database=programmersheaven;" +
                                     "uid=sa; pwd=";
         SqlConnection conn = new SqlConnection(connString);
                          
         // INSERT Query
         string cmdString ="INSERT INTO Author " +
                                   "(authorId, name) " +
                                   "VALUES(3, 'Anders Hejlsberg')";
                          
         SqlCommand cmd = new SqlCommand(cmdString, conn);

 

         conn.Open();

         cmd.ExecuteNonQuery();

         conn.Close();

VB.Net Version
        Dim connString As String = "server=FARAZ; database=programmersheaven;" + _
                                   "uid=sa; pwd="
        Dim conn As New SqlConnection(connString)

 

        ' INSERT Query

        Dim cmdString As String = "INSERT INTO Author " + _

                                  "(authorId, name) " + _

                                  "VALUES(3, 'Anders Hejlsberg')"

 

        Dim cmd As New SqlCommand(cmdString, conn)

 

        conn.Open()

        cmd.ExecuteNonQuery()

        conn.Close()

 

How do I update records using data reader?

The procedure for updating records using INSERT commands is very similar to the one we presented in the previous example (of SELECT) except that here the command does not return anything and thus the method to call on the SqlCommand object is called ExecuteNonQuery().

C# Version
         string connString = "server=FARAZ; database=programmersheaven;" +
                                     "uid=sa; pwd=";
         SqlConnection conn = new SqlConnection(connString);
                          
         // UPDATE Query
         string cmdString =  "UPDATE Author " +
                                   "SET name = 'Grady Booch' " + 
                                   "WHERE authorId = 3";

 

         SqlCommand cmd = new SqlCommand(cmdString, conn);

 

         conn.Open();

         cmd.ExecuteNonQuery();

         conn.Close();

VB.Net Version
        Dim connString As String = "server=FARAZ; database=programmersheaven;" + _
                                   "uid=sa; pwd="
        Dim conn As New SqlConnection(connString)

 

        ' UPDATE Query

        Dim cmdString As String =  "UPDATE Author " + _

                                   "SET name = 'Grady Booch' " + _

                                   "WHERE authorId = 3"

 

        Dim cmd As New SqlCommand(cmdString, conn)

 

        conn.Open()

        cmd.ExecuteNonQuery()

        conn.Close()

 

How do I delete records using data reader?

The procedure for updating records using INSERT commands is very similar to the one we presented in the previous example (of SELECT) except that here the command does not return anything and thus the method to call on the SqlCommand object is called ExecuteNonQuery().

C# Version
         string connString = "server=FARAZ; database=programmersheaven;" +
                                     "uid=sa; pwd=";
         SqlConnection conn = new SqlConnection(connString);
                          
         // DELETE Query
         string cmdString =  "DELETE FROM Author " + 
                                   "WHERE authorId = 3";

 

         SqlCommand cmd = new SqlCommand(cmdString, conn);

 

         conn.Open();

         cmd.ExecuteNonQuery();

         conn.Close();

VB.Net Version
        Dim connString As String = "server=FARAZ; database=programmersheaven;" + _
                                   "uid=sa; pwd="
        Dim conn As New SqlConnection(connString)

 

        ' DELETE Query

        Dim cmdString As String =  "DELETE FROM Author " +

                                   "WHERE authorId = 3"

 

        Dim cmd As New SqlCommand(cmdString, conn)

 

        conn.Open()

        cmd.ExecuteNonQuery()

        conn.Close()

 

How do I write common code for different dot net framework data providers?

The System.Data namespace contains the interfaces implemented by different dot net framework data providers, such as:

  • IDbConnection implemented by SqlConnection, OracleConnection, OleDbConnection, OdbcConnection classes represents a connection with the database server
  • IDbCommand implemented by SqlCommand, OracleCommand, OleDbCommand, OdbcCommand classes represents an SQL command passed to the database server
  • IDbDataAdapter implemented by SqlDataAdapter, OracleDataAdapter, OleDbDataAdapter, OdbcDataAdapter classes represents a data adapter used to fill the data set in the disconnected environment of the ADO.Net
  • IDataReader implemented by SqlDataReader, OracleDataReader, OleDbDataReader, OdbcDataReader classes represents a data reader used to read records from the database server, analogous to read only, forward only cursor
  • IDbTransaction implemented by SqlTransaction, OracleTransaction, OleDbTransaction, OdbcTransaction classes represents a transaction established with the database server

We strongly recommend the readers to use the references of these interface type to perform the database operations wherever possible. Using these, you can write a code that is data provider independent. Consider a data access module which is supplied the database connection and which performs the database operations using this connection. This module does not know which data provider the connection belongs and uses the interface approach. Following code demonstrate this data access module

C# Version
internal class DataAccessModule
{
         private IDbConnection conn;
         private IDbCommand cmd;

 

         private const string GetValueCmdText ="SELECT value FROM MyTable WHERE name = '";

                

         public DataAccessModule(IDbConnection conn)

         {

                 this.conn = conn;

                 cmd = conn.CreateCommand();

                 conn.Open();

         }

 

         public string GetValue(string name)

         {

           cmd.CommandText = GetValueCmdText + name + "'";

                 IDataReader reader = cmd.ExecuteReader();

                 if(reader.Read())

                 {

                          return reader["value"].ToString();

                 }

                 else

                 {

                          return null;

                 }

         }

 

         // more functions...

}

VB.Net Version
Friend Class DataAccessModule

 

    Private conn As IDbConnection

    Private cmd As IDbCommand

 

    Private Const GetValueCmdText As String ="SELECT value FROM MyTable WHERE name = '"

 

    Public Sub New(ByVal conn As IDbConnection)

        Me.conn = conn

        cmd = conn.CreateCommand()

        conn.Open()

    End Sub

 

    Public Function GetValue(ByVal name As String) As String

        cmd.CommandText = GetValueCmdText + name + "'"

        Dim reader As IDataReader = cmd.ExecuteReader()

        If (reader.Read()) Then

            Return reader("value").ToString()

        Else

            Return Nothing

        End If

    End Function

 

    ' More Functions....

 

End Class

 

 

What is a stored procedure?

A stored procedure is a precompiled executable object that contains one or more SQL statements. A stored procedure may be written to accept inputs and return output.

What is the advantage of using stored procedure over the SQL queries?

Writing the SQL statements inside our code is usually not a good idea. In this way you expose your database schema (design) in the code which may be changed. Hence most of the time programmers use stored procedures instead of plain SQL statements. A stored procedure is a precompiled executable object that contains one or more SQL statements. Hence you can replace your complex SQL statements with a single stored procedure. Since, stored procedures are precompiled objects they execute faster at the database server. Most of the time, stored procedures contain more than one command; in this case, the time to pass the individual commands to the database server from the program is saved. The database is issued just one command (to execute the stored procedure) and the DB server executes all the commands and returns the result in the end. Hence, the overall interaction time with the DB server reduces in a great deal. This can result in a huge optimization in case where the DB server is accessed via a slow network.

How do stored procedure look like, can you provide some sample stored procedures?

Here we are presenting a brief review of four basic type of stored procedure for SELECT, INSERT, UPDATE and DELETE operations. In SQL Server, you can create and add stored procedures to your database using the SQL Server Enterprise Manager.

UPDATE Stored Procedure

A simple stored procedure to update a record is

CREATE PROCEDURE UpdateProc (
         @artId as int, 
         @title as varchar(100), 
         @topic as varchar(100),
         @authorId as int,
         @lines as int,
         @dateOfPublishing as datetime)
AS
         UPDATE Article SET
                 title=@title, topic=@topic, authorId=@authorId,
                 lines=@lines, dateOfPublishing=@dateOfPublishing 
         WHERE artId=@artId
GO

The name of stored procedure is UpdateProc and it has the input parameters for each of the fields of our Article table. The query to be executed when the stored procedure is run updates the record with the supplied primary key (@artId) using the supplied parameters. It is very similar to the code we have written to initialize command in the previous example and we hope you don’t have any problem in understanding this even you are not familiar with stored procedure.

INSERT Stored Procedure

A simple stored procedure to insert a record is

CREATE PROCEDURE InsertProc (
         @artId as int, 
         @title as varchar(100), 
         @topic as varchar(100),
         @authorId as int,
         @lines as int,
         @dateOfPublishing as datetime)
AS
         INSERT INTO article (artId, title, topic, authorId, lines, dateOfPublishing) 
         VALUES(@artId, @title, @topic, @authorId, @lines, @dateOfPublishing)
GO

The stored procedure above is named InsertProc and is very similar to the UpdateProc except that here we are using the INSERT SQL statement instead of the UPDATE command.

DELETE Stored Procedure

A simple stored procedure to delete a record is

CREATE PROCEDURE DeleteProc (@artId as int)
AS
         DELETE FROM article WHERE artId = @artId
GO

Here we have used only one parameter as to delete a record you only need its primary key value.

SELECT Stored Procedure

A simple stored procedure to delete a record is

CREATE PROCEDURE SelectProc 
AS
         SELECT * FROM Article
GO

This probably is the simplest of all. It does not take any parameter and only selects all the records from the Article table.

All the four stored procedures presented above are kept extremely simple so that the reader does not find any difficulty in understanding the use of stored procedure in his C#/VB.Net code. The real world stored procedures are much more complex and off course useful than these!

How do I call a stored procedure from my application using ADO.Net?

Using stored procedures with ADO.Net in C# is extremely simple, especially when we have developed the application with SQL commands. All we need is:

  • Create a command object (SqlCommand, etc) and specify the stored procedure name
  • Set the CommandType property of the command object to the CommandType.StoredProcedure enumeration value. This tells the runtime that the command used here is a stored procedure.

That’s it! The sample code to use with data adapter is:

C# Version
         // Preparing Insert SQL Command
         SqlCommand insertCommand = new SqlCommand("InsertProc", conn);
         insertCommand.CommandType = CommandType.StoredProcedure;
         dataAdapter.InsertCommand = insertCommand;
         insertCommand.UpdatedRowSource = UpdateRowSource.None;
         ...
VB.Net Version
        ' Preparing Insert SQL Command
        Dim insertCommand = New SqlCommand("InsertProc", conn)
        insertCommand.CommandType = CommandType.StoredProcedure
        dataAdapter.InsertCommand = insertCommand
        insertCommand.UpdatedRowSource = UpdateRowSource.None

 

 

How do I make my first application to call stored procedure using ADO.Net?

Please download the attached source code for the sample application using the stored procedures to access the data with ADO.Net in VB and C#.

What are the important points when developing a data access app with ADO.Net?

  • Always try to use the base interfaces for connection, command, data reader and other objects.
  • Always try to use the SqlClient, SqlServerCe and OracleClient to connect with the Sql Server, Sql Server CE and Oracle Database servers as they are specialized and optimized for the specific database servers.
  • Still remember to reference the data provider specific objects (SqlConnection, OracleCommand) to reference with the base interface (IDbConnection, IDbCommand)
C# Version
         IDbConnection conn = new SqlConnection();
         ...
         IDbCommand cmd = new OracleCommand();
VB.Net Version
         Dim conn As IDbConnection 
         conn = New SqlConnection();
         ...
         Dim cmd As IDbCommand 
         cmd = new OracleCommand();
  • Do not write the connection string in your code as it may change. Either write it in a text file or an xml file and read it on the application startup. For security purposes, you may also write the encrypted connection string in the text/xml file
  • Try to use the stored procedures wherever possible especially when you are to write a series of queries whose individual results are not required to be used in the code in between this series of queries.
  • Do not use the complex queries in the source code. If the query is getting complex, try to make the views inside the database server and use the views instead.
  • Practice using the transactions when it makes sense, especially with error handling codes
  • Put special consideration in the error handling code. The database operation may fail due to various reasons such as invalid connection string, invalid table/field name in the query, database server failure, connection failure, too many connections on the server or the server busy, invalid query, etc You need to consider all these while writing the code for error handling.
  • Using Visual Studio.Net’s debugger is a very good and useful practice to find the possible errors. Remember, Ado.Net exception messages are not much useful (or quite vague) in general for debugging; hence the use of watch and quick watch debugger windows is extremely useful and helpful in when debugging the code.
  • When using dataset and disconnected architecture, we don’t update the data source (by calling DataAdapter’s Update() method and DataSet’s AcceptChanges() method) for each update. Instead we make the changes local and update all these changes later as a batch. This provides optimized use of network bandwidth. BUT, this off course is not a better option when multiple users are updating the same database. When changes are not to be done locally and need to be reflected at database server at the same time, it is preferred to use the connected oriented environment for all the changes (UPDATE, INSERT and DELETE) by calling the ExecuteNonQuery() method of your command (SqlCommand or OleDbCommand) object.
  • Disconnected data access is suited most to read only services. In common practice clients are often interested in reading and displaying data. In this type of situation, the disconnected data access excels as it fetches the whole data in a single go and store it in the local buffer (dataset). This local storage of data eliminates the need of staying connecting to the database and fetching single record at a time. On the down side, disconnected data access architecture is not designed to be used in the networked environment where multiple users are updating data simultaneously and each of them needs to be aware of current state of database at any time (e.g., Airline Reservation System).

What is data grid and what is the use of it?

Data Grid is the standard control for viewing data in .Net environment. A data grid control is represented in .Net by System.Windows.Forms.DataGrid class. The data grid control can display the data from a number of data sources e.g. data table, dataset, data view and array.

How can I make my first application with DataGrid using the data from ADO.Net?

Let’s create a simple application first that loads a table data from database server to the data grid control. First of all, add a data grid control and a button to your form from Visual Studio toolbox. We have set the Name property of data grid to ‘dgDetails’ and its CaptionText property to ‘ProgrammersHeaven Database’. The name of button is ‘btnLoadData’. The event handler for button is:

C# Version
private void btnLoadData_Click(object sender, System.EventArgs e)
{
         string connectionString = "server=FARAZ; database=programmersheaven;" +
                                              "uid=sa; pwd=;";
         SqlConnection conn = new SqlConnection(connectionString);
         string cmdString = "SELECT * FROM article";
         SqlDataAdapter dataAdapter = new SqlDataAdapter(cmdString, conn);
         DataSet ds = new DataSet();
         dataAdapter.Fill(ds, "article");

 

         dgDetails.SetDataBinding(ds, "article");

}

VB.Net Version
    Private Sub btnLoadData_Click(ByVal sender As System.Object, _
                ByVal e As System.EventArgs) Handles btnLoadData.Click

 

        Dim connectionString As String = "server=P-III; database=programmersheaven;" + _

                                         "uid=sa; pwd=;"

        Dim conn As New SqlConnection(connectionString)

        Dim cmdString As String = "SELECT * FROM article"

        Dim dataAdapter As New SqlDataAdapter(cmdString, conn)

        Dim ds As New DataSet()

        dataAdapter.Fill(ds, "article")

 

        dgDetails.SetDataBinding(ds, "article")

 

    End Sub

Here we first created data adapter and filled the data set using it as we used to do in other applications. The only new thing is the binding of “article” table to the data grid control which is done by calling the SetDataBinding() method of the DataGrid class. The first parameter of this method is the dataset while the second parameter is the name of table in the dataset.

C# Version
         dgDetails.SetDataBinding(ds, "article");
VB.Net Version
         dgDetails.SetDataBinding(ds, "article")

When you execute this program and select the Load button you will see the output presented in the previous figure.

How can I make my data grid to view data from multiple related tables?

Let’s see how we can use Data Grid control to show multiple related tables. When two tables are related, one is called the parent table while the other is called the child table. The child table contains the primary key of parent table as a foreign key. For example in our ProgrammersHeaven database, table Author is the parent table of the Article table as the Article table contains ‘AuthorId’ as foreign key which is a primary key in the Author table.

In this example, we will use data grid to show the related records from article and author table. In order to specify the relationship between the two tables we need to use the DataRelation class as:

C# Version
         dgDetails.SetDataBinding(ds, "article")
         DataRelation relation = new DataRelation("ArtAuth", 
                          ds.Tables["author"].Columns["authorId"], 
                          ds.Tables["article"].Columns["authorId"] 
                          );
VB.Net Version
Dim relation As New DataRelation("ArtAuth", _
    ds.Tables("author").Columns("authorId"), _
    ds.Tables("article").Columns("authorId") _
    )

Here the first argument of DataRelation constructor is the name for the new relation while second and third arguments are the columns of the tables which will be used to relate the two tables. After creating this relationship we need to add it to the Relations collection of the dataset.

C# Version
         ds.Relations.Add(relation);
VB.Net Version
         ds.Relations.Add(relation)

Hence the modified code for the Load Data button is:

C# Version
private void btnLoadData_Click(object sender, System.EventArgs e)
{
         string connectionString = "server=P-III; database=programmersheaven;" +
                                              "uid=sa; pwd=;";
         SqlConnection conn = new SqlConnection(connectionString);

 

         string cmdString = "SELECT * FROM article";

         SqlDataAdapter dataAdapter = new SqlDataAdapter(cmdString, conn);

         DataSet ds = new DataSet();

         dataAdapter.Fill(ds, "article");

 

         cmdString = "SELECT * FROM author";

         dataAdapter = new SqlDataAdapter(cmdString, conn);

         dataAdapter.Fill(ds, "author");

 

         DataRelation relation = new DataRelation("ArtAuth",

                          ds.Tables["author"].Columns["authorId"],

                          ds.Tables["article"].Columns["authorId"]

                          );

         ds.Relations.Add(relation);

 

         DataView dv = new DataView(ds.Tables["author"]);

         dgDetails.DataSource = dv;

}

VB.Net Version
    Private Sub btnLoadData_Click(ByVal sender As System.Object, _
                ByVal e As System.EventArgs) Handles btnLoadData.Click

 

        Dim connectionString As String = "server=P-III; database=programmersheaven;" + _

                                         "uid=sa; pwd=;"

        Dim conn As New SqlConnection(connectionString)

        Dim cmdString As String = "SELECT * FROM article"

        Dim dataAdapter As New SqlDataAdapter(cmdString, conn)

        Dim ds As New DataSet()

        dataAdapter.Fill(ds, "article")

 

        cmdString = "SELECT * FROM author"

        dataAdapter = New SqlDataAdapter(cmdString, conn)

        dataAdapter.Fill(ds, "author")

 

        Dim relation As New DataRelation("ArtAuth", _

                    ds.Tables("author").Columns("authorId"), _

                    ds.Tables("article").Columns("authorId") _

                    )

        ds.Relations.Add(relation)

 

 

        Dim dv As New DataView(ds.Tables("author"))

        dgDetails.DataSource = dv

    End Sub

In the above code we first filled the dataset with the two tables, defined the relationship between them and then added it to the dataset. In the last two lines, we created an instance of DataView class by supplying the parent table in its constructor call and then set the DataSource property of data grid to this data view.

When we compile and execute this application, the data grid will show the records of parent table with ‘+’ button on the left of each record

When you press the ‘+’ button on the left of the record, it will expand to show the name of relationship as a link

Now when you click the relation name, the data grid will show all the related records in the child table

Still you can see the parent record at the top of all the rows of the child table. You can go back to the parent table using the back arrow button (ç) at the title bar of the data grid.

What are the issues related to the deployment of data access application?

Some of the basic issues related to the deployment of the data access applications are:

  • Can we suppose the required database exists at the target location?
  • If the database does exist then how can we get the connection string of it? Should we get it at the installation time? Or at the first run?
  • If the database does not exist then how it can be created? Is it the responsibility of human installing our application? Or the application should create it at the time of installation?
  • If the database is to be created by the application installation, then how does the installation setup know where (on which machine) to create the database? And what user name and password the setup should use to create the database?
  • Once, the database is created or its connection string is found at the setup time, then how to store the connection string for later use (regular execution)?
  • What if the database address (connection string) is changed during the application life cycle then what should be the application behavior? How can the application be aware of the new connection string?
  • If the database schema is hard-coded in the code (like table and field names) and the DB schema is changed (table/field name or data type is changed or new field/table added or some fields/tables deleted) because of any reason, then how to fix this problem?

You can see from the above points that the basic issue is the identification of the address (or connection string) of the database server. The most important point to remember here is that the application must perform in any condition and must not be crashed because of any condition and most of the problems (if not all) should be handled without any change in the code or any update patch installation

How do I solve the deployment issues (mentioned in the previous FAQ)?

Ok, let’s discuss some of the issues and their solution briefly. The specific detail of these solutions is given in the next FAQs.

  • The easiest and very sound solution is to provide the database script to create necessary database and tables along with the installation and ask your user to run the script on the target database server to create the required database. Then ask the user to supply the connection string at the installation setup time.
  • You can create the database with the installation by executing the database creation script with the installation. But before this, you have to ask the user the location (the computer on the network) where the database server exists, and the user name, password to login to the database.
  • The connection string should be stored in a text or binary or xml file. You can also encrypt the connection string before writing it to the file. The application, thus, is required to decrypt and load the connection string in the memory at each startup.
  • The application should load the connection string at each startup and attempt to connect to the database. If the connection attempt fails then it should inform the user that the database is not available and ask the user to setup the database and try again. The application should also allow the user to change the database connection string any time and, it the application logic permits, let the user specify to work without database. If the user specifies a new connection string during the application startup or regular execution, the application should save it for later use.
  • One solution to the schema changed problem is to use views and stored procedure wherever possible. But if this is not done or the change is too big to be catered by the views and/or stored procedure then you can supply a new data access module update (a new DLL may be). For this reason, it is advised to separate the data access code in a separate physical and logical module (or assembly in .Net language) so you can change it without affecting the overall application. But when using this, the interface (the method signatures) should be made that abstract that they does not exactly map to the physical database schema but to the logical schema. Finally, if the database schema change is major (which is not a very good sign for the application overall design) then there is no solution but to change the code and ship the installation again!!!

How do I set the connection string at installation :specific data provider?

Well this is quite tricky and interesting. Connection strings are database dependent and different database servers allow connection strings in different formats.

If you are using the database specific provider classes (like those from System.Data.SqlClient or System.Data.OracleClient) then you can generate the connection string easily by taking the specific inputs from the user. For example, if you are using SQL Server and the classes from the System.Data.SqlClient namespace then we can ask user the SQL Server instance name, the user name, password of if he/she is using the Windows Authentication to log in and the database name.

How do I set the connection string at installation :general data provider?

The problem arises when you are using the general data providers such as classes from System.Data.OleDb and System.Data.Odbc namespaces. You can’t make a general GUI to generate the connection string for all the database servers. Then what to do now? The solution is the DataLink Dialog. You must have seen the dialog which looks like this in many windows applications:

So how you can use this dialog in your program? For this you need add a reference to COM component ‘Microsoft OLE DB Service Component 1.0 Type Library’ which should be available if you have installed Microsoft ActiveX Data Components. Once you have added the component, you can show the Data Link Properties dialog box by making an object of type MSDASC.DataLink class and calling its PromptNew() method.

C# Version
         MSDASC.DataLinks udl = new MSDASC.DataLinksClass();
         udl.PromptNew();
VB.Net Version
      Dim udl As MSDASC.DataLinks
      udl = New MSDASC.DataLinksClass
      udl.PromptNew()

The above code will show the Data Link Properties dialog box. But how can we get the connection string generated by the dialog box? The PromptNew() method returns a connection type object which can be captured in an object of type ADODB.Connection. Hence for this, add a reference to ‘adodb’ .Net assembly in your project, and get the connection string using the ConnectionString property of this object. The following code snippets demonstrate this:

C# Version
         string connStr = "";

 

         MSDASC.DataLinks udl = new MSDASC.DataLinksClass();

         ADODB.Connection conn = (ADODB.Connection) udl.PromptNew();

                         

         if(conn != null)

         {

                 connStr = conn.ConnectionString;

         }

VB.Net Version
      Dim connStr As String

 

      Dim udl As MSDASC.DataLinks

      udl = New MSDASC.DataLinksClass

      Dim conn As ADODB.Connection = udl.PromptNew()

 

      If Not conn Is Nothing Then

          connStr = conn.ConnectionString

      End If

You can use this code in the overrided Install() method of your project’s installer class (the class derived from System.Configuration.Install.Installer class), and add the project output in the ‘Custom Actions’ of the setup project.

How do I supply the connection string during first run or during the regular execution?

Well that should be very simple Just add the Data Link Properties dialog box at the start of the application or during the regular execution of the application whenever you need it. Alternatively, you can also provide your own designed user interface for connection string related properties, if your application supports specific .Net data providers (like System.Data.SqlClient or System.Data.OracleClient)

How do I store / retrieve the connection string in / from a text file?

You can store the connection string in the text file or an xml file and later retrieve it. Let’s see some example code to write a connection string to the text file and read it back

C# Version
         string connStr = "";

 

         // get connection string in the connStr variable

 

         // Write connection string to text file

         StreamWriter sw = new StreamWriter(@"C:\ConnectionString.txt");

         sw.WriteLine(connStr);

 

         // ...

 

         // Read connection string from the text file

         StreamReader sr = new StreamReader(@"C:\ConnectionString.txt");

         connStr = sr.ReadLine();

VB.Net Version
      Dim connStr As String = ""

 

      ' get connection string in the connStr variable

 

      ' Write connection string to text file

      Dim sw As New StreamWriter("C:\ConnectionString.txt")

      sw.WriteLine(connStr)

 

      ' ...

 

      ' Read connection string from the text file

      Dim sr As New StreamReader("C:\ConnectionString.txt")

      connStr = sr.ReadLine()

How do I store / retrieve the connection string in / from an XML file?

Once you get the connection string, you can store the connection string in the text file or an xml file and later retrieve it. Let’s see some example code to write a connection string to an XML file and read it back

C# Version
         string connStr = "";

 

         // get connection string in the connStr variable

 

         // Write connection string to xml file

         XmlDocument xmlDoc = new XmlDocument();

         XmlNode xn = xmlDoc.CreateNode(XmlNodeType.Element, "ConnectionString", "");

         xn.InnerText = connStr;

         xmlDoc.AppendChild(xn);

         xmlDoc.Save(@"C:\ConnectionString.xml");

                

         // ...

 

         // Read connection string from the text file

         XmlDocument xmlDoc = new XmlDocument();

         xmlDoc.Load(@"C:\ConnectionString.xml");

         XmlNode xn = xmlDoc.SelectSingleNode("ConnectionString");

         connStr = xn.InnerText;

VB.Net Version
      Dim connStr As String = ""

 

      ' get connection string in the connStr variable

 

      ' Write connection string to xml file

      Dim xmlDoc As New XmlDocument

      Dim xn As XmlNode

      xn = xmlDoc.CreateNode(XmlNodeType.Element, "ConnectionString", "")

      xn.InnerText = connStr

      xmlDoc.AppendChild(xn)

      xmlDoc.Save("C:\ConnectionString.xml")

 

      ' ...

 

      ' Read connection string from the xml file

      Dim xmlDoc As New XmlDocument

      xmlDoc.Load("C:\ConnectionString.xml")

      Dim xn As XmlNode

      xn = xmlDoc.SelectSingleNode("ConnectionString")

      connStr = xn.InnerText

How do I make my first “Hello, Data Access Application Deployment” setup program?

We will start with the assumption that you have a data access application. Note that we are only providing here the C# example. The VB.Net example is exactly similar. Why we didn’t include the VB.Net example? Because, the intent here is not to explain the code but the procedure of how to create the data accessing application’s deployment project which is independent of the language being used. All the code we will present here has already been presented in the previous FAQs and explained in much detail.

The first step, then, is to add an installer class. The easiest way to do is to right click the project icon in the solution explorer and select ‘Add New Item…’ and in the pop-up window, select Installer class, name it appropriately and select ‘Open’ button to add it. Now right click the newly added installer class and select view code. You will notice that this new class is automatically inherited from the System.Configuration.Install.Installer class

         public class Installer1 : System.Configuration.Install.Installer

The only thing you need to do now in this class is to override the Install() method of the base class, write the code you want to execute when the installation setup is executed. We have written the code to display the Data Link Properties dialog box and save the resulted connection string into an xml file, so later the application can use it.

         public override void Install(IDictionary stateSaver)
         {
                 base.Install (stateSaver);
                 string connStr = "";

 

                 MSDASC.DataLinks udl = new MSDASC.DataLinksClass();

                 ADODB.Connection conn = (ADODB.Connection) udl.PromptNew();

                         

                 if(conn != null)

                 {

                          connStr = conn.ConnectionString;

                 }

 

                 // Write connection string to xml file

                 XmlDocument xmlDoc = new XmlDocument();

                 XmlNode xn = xmlDoc.CreateNode(XmlNodeType.Element, "ConnectionString", "");

                 xn.InnerText = connStr;

                 xmlDoc.AppendChild(xn);

                 xmlDoc.Save(@"C:\ConnectionString.xml");

         }

This is all for the setup, we have added a button to our database deployment application form which displays the connection string in a message box. It retrieves the connection string from the xml file generated by the installation setup program.

         private void btnShowConnStr_Click(object sender, System.EventArgs e)
         {
                 string connStr = "";

 

                 // Read connection string from the xml file

                 XmlDocument xmlDoc = new XmlDocument();

                 xmlDoc.Load(@"C:\ConnectionString.xml");

                 XmlNode xn = xmlDoc.SelectSingleNode("ConnectionString");

                 connStr = xn.InnerText;

                 MessageBox.Show(connStr, "Connection String from XML file");

                         

         }

Now the application is ready. We will start building our setup project. For this, add a new ‘Setup and Deployment’ project to the solution by right clicking the solution and selecting ‘Add New Project…’ in the pop-up window, selecting the ‘Setup Project’ from the ‘Setup and Deployment Projects’ section. Once the setup project is added to the solution, right click it in the solution explorer and select View-->File System. Here right click the application folder icon and select Project Output --> Primary Out put of your data access project. This will automatically add the project dependencies. Now right click the ‘Primary Output of your project’ and create its short cut. Copy this shortcut to the Desktop folder and the User Program Menu and rename shortcut appropriately.

Now to ask the setup project run our custom installation code (we have written in the Installer class in the data access application), we need to add the primary output of our project to the custom actions of the setup project. To do this, right click the setup project in the solution explorer, and select View --> Custom Actions. Here right click ‘Install’ and select ‘Add Custom Action’. In the popup window, select the primary output of the database access application from the application folder and click OK.

This is all we need to do! Rebuild the solution and remove any minor bugs (if they do popup). When your setup project will be built, it will generate a Setup.exe file in its debug (or release folder depending on the project configuration). Run this setup.exe to install this application. The setup will ask you the connection string and save it in the xml file.

After installation is complete, run the application using its desktop icon. When you will click the button on the form, it will read the connection string from the xml file and display it in the message box.

 

 

How to use relation objects in a dataset?

DataSet's that contain multiple DataTable objects can use DataRelation objects to relate one table to another. Adding a DataRelation to a DataSet adds by default a UniqueConstraint to the parent table and a ForeignKeyConstraint to the child table.

The code sample below creates a DataRelation using two DataTable objects in a DataSet. Each DataTable contains a column named CustID which serves as a "relation" between two the DataTable objects. The example adds a single DataRelation to the Relations collection of the DataSet. The first argument in the sample specifies the name of the DataRelation being created. The second argument sets the parent DataColumn and the third argument sets the child DataColumn.

custDS.Relations.Add("CustOrders",
custDS.Tables["Customers"].Columns["CustID"],
custDS.Tables["Orders"].Columns["CustID"]);

OR

private void CreateRelation()
{
DataColumn parentCol;
DataColumn childCol;

 

parentCol = DataSet1.Tables["Customers"].Columns["CustID"];

childCol = DataSet1.Tables["Orders"].Columns["CustID"];

 

DataRelation relCustOrder;

relCustOrder = new DataRelation("CustomersOrders",

 

parentCol, childCol);

 

DataSet1.Relations.Add(relCustOrder);

 

DataReader Vs DataSet?

The ADO.NET DataReader is used to retrieve "read-only" / "forward-only" data from a database. Using the DataReader increases application performance and reduces system overheads. This is due to one row at a time is stored in memory. You create a DataReader by calling Command.ExecuteReader after creating an instance of the Command object.

The following line of code is used to retrieve rows from a data source.

SqlDataReader myReader = myCommand.ExecuteReader();

The Read method of the DataReader object is used to obtain a row from the results of the query, like so.

(myReader.Read())  Console.WriteLine("\t{0}\t{1}", 
myReader.GetInt32(0), myReader.GetString(1));
myReader.Close();

The DataSet is a in-memory representation of data that provides a consistent relational programming model regardless of the data source. It can be used with multiple data sources. The DataSet represents a complete set of data including related tables, constraints, and relationships among the tables. The methods and objects in a DataSet are consistent with those in the relational database model. The DataSet can also persist and reload its contents as XML and its schema as XML Schema definition language (XSD) schema.

The DataAdapter acts as a bridge between a DataSet and a data source for retrieving and saving data. The DataAdapter provides this bridge by "mapping Fill". Which changes the data in the DataSet to match the data in the data source. Upon this an Update occurs, which changes the data in the data source to match the data in the DataSet.

On connecting to a Microsoft  ?SQL Server database, an increase in overall performance can be obtained by using the SqlDataAdapter along with its associated SqlCommand and SqlConnection. For other OLE DB-supported databases, use the DataAdapter with its associated OleDbCommand and OleDbConnection.

How do I access a SQL stored procedure?

You can access SQL stored procedures the same way as executing other SQL commands.

Set the query string as the name of the stored procedure and then set the CommandType to be CommandType.StoredProcedure. Below is an example of one input and one output parameter.

if(myConn.State == ConnectionState.Closed)myConn.Open(); 
SqlCommand myCmd = new 

 

SqlCommand("sp_my_stored_procedure",myConn);

myCmd.CommandType = CommandType.StoredProcedure;

 

SqlParameter parm;

parm = myCmd.Parameters.Add(new SqlParameter("@custid",

 

SqlDbType.VarChar,50));

parm.Direction = ParameterDirection.Input;

myCmd.Parameters["@custid"].Value = OrderID;

 

parm = myCmd.Parameters.Add(new SqlParameter("@custName",

 

SqlDbType.VarChar,50));

parm.Direction = ParameterDirection.Output;

 

SqlDataAdapter da = new SqlDataAdapter();

da.TableMappings.Add("your mapping","your mapping");

da.SelectCommand = myCmd;

DataSet ds = new DataSet();

da.Fill(ds);

DataTable resultTable = ds.Tables[0];

 

Methods of the Command Objects with databases?

The Command object is represented by two corresponding classes: SqlCommand and OleDbCommand. Command objects are used to execute statements (commands) to a database via a data connection. The Command objects can be used to execute stored procedures on the Database, SQL statements, or return complete tables directly. Command objects provide three methods that are used to execute commands on the database:

  • ExecuteNonQuery. Executes direct SQL commands, such as INSERT, UPDATE or DELETE.
  • ExecuteScalar. Returns a single value from a Database Query.
  • ExecuteReader. Returns a result set by way of a DataReader object.

How do I display a data table in a data grid?

The following code queries an MS Access Table, then displays all the columns of that table in a Data Grid.

'Establish a connection to the data source.
Dim ConnString As String

 

ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" &  "Data Source=C:\Test.mdb"

Dim Conn As New System.Data.OleDb.OleDbConnection(ConnString)

Conn.Open()

Dim dapt As New System.Data.OleDb.OleDbDataAdapter("Table1", Conn)

 

Dim dst As New DataSet

 

dapt.Fill(dst, "Table1")

 

DataGrid1.SetDataBinding(dst, "Table1")

     

Conn.Close()

Upon running the above code, Table1 from the a "Test" database will be displayed in a DataGrid.

How do I insert data entered in a textbox into the database?

The data you enter in the textboxes will be inserted into the database when of the click of a Button.

The working senario is a database called "Emp" with a table named "Table1" with three columns. Also a Form with three TextBoxes and one Command Button.

Imports System.Data.OleDb
Dim cn As OleDbConnection
Dim cmd As OleDbCommand
Dim dr As OleDbDataReader
Dim icount As Integer
Dim str As String
 
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As  System.EventArgs) Handles_ Button2.Click
 
Try
cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\emp.mdb;Persist Security_ Info=False")
cn.Open()
 
str = "insert into table1 values(" & CInt(TextBox1.Text) & ",'" & TextBox2.Text & "','" & TextBox3.Text & "')" 'string stores the command
'and CInt is used to convert number, to string
 
cmd = New OleDbCommand(str, cn)
icount = cmd.ExecuteNonQuery
 
MessageBox.Show(icount) 'displays number of records inserted
Catch
 
End Try
 
End Sub

 

 

How do I access SQL server in VB.NET?

To access data from a table in  ?SQL Server you need to import the namespace System.Data.SqlClient and establish a connection from the application to the server.

The following code demonstrates how to connect to a  ?SQL Server and display data from the "Discounts" table in the sample database "PUBS".

 
Imports System.Data.SqlClient
 
Public Class Form1 Inherits System.Windows.Forms.Form
 
Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
Dim dr As New SqlDataReader()
 
'declaring the objects need
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As_
 
System.EventArgs) Handles MyBase.Load
 
myConnection = New SqlConnection("server=localhost;uid=sa;pwd=;database=pubs")
'establishing connection
 
Try
myConnection.Open()
'opening the connection
myCommand = New SqlCommand("Select * from discounts", myConnection)
 
'executing the command and assigning it to connection 
dr = myCommand.ExecuteReader()
 
While dr.Read()
'reading from the datareader
MessageBox.Show("discounttype" & dr(0).ToString())
MessageBox.Show("stor_id" & dr(1).ToString())
MessageBox.Show("lowqty" & dr(2).ToString())
MessageBox.Show("highqty" & dr(3).ToString())
MessageBox.Show("discount" & dr(4).ToString())
'displaying the data from the table
End While
 
dr.Close()
myConnection.Close()
 
Catch e As Exception
End Try
 
End Sub
End Class

 

 

How to insert an image in Access Database?

The following code asks for a path of a Gif image. Then inserts the Gif image to an Access database.

 
File name is Image.vb
 
Imports System
Imports System.IO
Imports System.Data
 
Public Class SaveImage
Shared Sub main()
 
Dim o As System.IO.FileStream
Dim r As StreamReader
Dim gifFile As String
 
Console.Write("Enter a Valid .Gif file path")
gifFile = Console.ReadLine
 
If Dir(gifFile) = "" Then
   Console.Write("Invalid File Path")
   Exit Sub
End If
 
o = New FileStream(gifFile, FileMode.Open, FileAccess.Read, FileShare.Read)
r = New StreamReader(o)
 
Try
 
Dim FileByteArray(o.Length - 1) As Byte
o.Read(FileByteArray, 0, o.Length)
Dim Con As New _ System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data 
 
Source=Test.mdb")
 
Dim Sql As String = "INSERT INTO Images (Pic,FileSize) VALUES (?,?)"
Dim Cmd As New System.Data.OleDb.OleDbCommand(Sql, Con)
Cmd.Parameters.Add("@Pic", System.Data.OleDb.OleDbType.Binary, o.Length).Value = FileByteArray
Cmd.Parameters.Add("@FileSize", System.Data.OleDb.OleDbType.VarChar, 100).Value = o.Length
 
Con.Open()
Cmd.ExecuteNonQuery()
Con.Close()
Catch ex As Exception
Console.Write(ex.ToString)
 
End Try
End Sub
End Class
 

A file will be inserted in the Database each time the code is executed.