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"]; // DeleteDataRow dr = dt.Rows[3];dr.Delete();
da.Update(ds, "Article");
ds.AcceptChanges();
VB.Net Version
Dim dt As DataTabledt = 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=@artIdGO
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 = @artIdGO
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 ArticleGO
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.OleDbDim cn As OleDbConnectionDim cmd As OleDbCommandDim dr As OleDbDataReaderDim icount As IntegerDim str As String Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles_ Button2.Click Trycn = 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 insertedCatch 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 SqlConnectionDim myCommand As SqlCommandDim dr As New SqlDataReader() 'declaring the objects needPrivate 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 TrymyConnection.Open()'opening the connectionmyCommand = New SqlCommand("Select * from discounts", myConnection) 'executing the command and assigning it to connection dr = myCommand.ExecuteReader() While dr.Read()'reading from the datareaderMessageBox.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 tableEnd While dr.Close()myConnection.Close() Catch e As ExceptionEnd Try End SubEnd 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 SystemImports System.IOImports System.Data Public Class SaveImageShared Sub main() Dim o As System.IO.FileStreamDim r As StreamReaderDim gifFile As String Console.Write("Enter a Valid .Gif file path")gifFile = Console.ReadLine If Dir(gifFile) = "" Then Console.Write("Invalid File Path") Exit SubEnd If o = New FileStream(gifFile, FileMode.Open, FileAccess.Read, FileShare.Read)r = New StreamReader(o) Try Dim FileByteArray(o.Length - 1) As Byteo.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 = FileByteArrayCmd.Parameters.Add("@FileSize", System.Data.OleDb.OleDbType.VarChar, 100).Value = o.Length Con.Open()Cmd.ExecuteNonQuery()Con.Close()Catch ex As ExceptionConsole.Write(ex.ToString) End TryEnd SubEnd Class
A file will be inserted in the Database each time the code is executed.