How do I make my first “Hello, ADO.Net” Application in C#?

Let’s now create a demonstration application for accessing data. First create a windows form application and make the layout like the following snapshot

http://www.programmersheaven.com/articles/images/faq/image007.gif

We have set the Name property of the text boxes (from top to bottom) as txtArticleID, txtArticleTitle, txtArticleTopic, txtAuthorId, txtAuthorName, txtNumOfLines and txtDateOfPublishing. Also we have set the ReadOnly property of all the text boxes to true as don’t want the user of application to change the text. The names of buttons (from top to bottom) are btnLoadTable, btnNext and btnPrevious. Initially we have disabled the Next and Previous buttons (by setting their Enabled property to false).

We have also defined three variables in the Form class as

public class ADOForm : System.Windows.Forms.Form
{
DataTable dataTable;
int currRec=0;
int totalRec=0;

The dataTable object will be used to reference the table returned as a result of the query. The currRec and totalRec integer variables are used to keep track of the current record and total records in the table.

Loading table

For LoadTable button, we have written the following event handler

private void btnLoadTable_Click(object sender, System.EventArgs e)
{
	// for Sql Server
	string connectionString =	"server=P-III; database=programmersheaven;" +
						"uid=sa; pwd=;";
	// for MS Access
	/*string connectionString =	"provider=Microsoft.Jet.OLEDB.4.0;" + 
						"data source = c:\\programmersheaven.mdb";*/
	// for Sql Server
	SqlConnection conn = new SqlConnection(connectionString);
	// for MS Access
	//OleDbConnection conn = new OleDbConnection(connectionString);
	string commandString = "SELECT " + 
						"artId, title, topic, " + 
						"article.authorId as authorId, " +
						"name, lines, dateOfPublishing " + 
				   "FROM " +  
						"article, author " +
				   "WHERE " + 
						"author.authorId = article.authorId";
	// for Sql Server
	SqlDataAdapter dataAdapter = new SqlDataAdapter(commandString, conn);
	// for MS Access
	//OleDbDataAdapter dataAdapter = new OleDbDataAdapter(commandString, conn);
	DataSet ds = new DataSet();
	dataAdapter.Fill(ds, "prog");
	dataTable = ds.Tables["prog"];
	currRec = 0;
	totalRec = dataTable.Rows.Count;
	FillControls();
	btnNext.Enabled = true;
	btnPrevious.Enabled = true;
}

In the start, we have created the connection, data adapter and filled the dataset object which we have discussed earlier. It should be noted that we have commented the code for OleDb provider (MS-Access) and are using the SQL Server specific code. If you like to use Access database, you can simply comment the SQL server code and de-comment the Access code.

Next we assigned the data table resulted from query to the dataTable object which we declared at class level, assigned zero to currRec variable and assigned the number of rows in the dataTable to totalRec variable

	
dataTable = ds.Tables["prog"];
currRec = 0;
totalRec = dataTable.Rows.Count;

Then we called the FillControls() method which fills the controls (text boxes) on the form with the current record of table “prog”. Finally we have enabled the Next and Previous Button

Filling the controls on the Form

The FillControls() method in our program fills the controls on the form with the current record of the data table. The method is defined as

private void FillControls()
{
txtArticleId.Text = dataTable.Rows[currRec]["artId"].ToString();
txtArticleTitle.Text = dataTable.Rows[currRec]["title"].ToString();
txtArticleTopic.Text = dataTable.Rows[currRec]["topic"].ToString();
txtAuthorId.Text = dataTable.Rows[currRec]["authorId"].ToString();
txtAuthorName.Text = dataTable.Rows[currRec]["name"].ToString();
txtNumOfLines.Text = dataTable.Rows[currRec]["lines"].ToString();
txtDateOfPublishing.Text = dataTable.Rows[currRec]["dateOfPublishing"].ToString();
}

Here we have set the Text property of the text boxes to the string values of the corresponding fields of current record. We have used the Rows collection of the dataTable and using its indexer we have got the DataRow representing the current record. We then accessed the indexer property of this DataRow using the column name to get the data in the respective field. If this explanation looks weird to you, you can simplify the above statements as

	
DataRow row = dataTable.Rows[currRec]; // getting current row
object data = row["artId"];	   // getting data in the artId field
string strData = data.ToString();	// converting to string
txtArticleId.Text = strData;		// display in the text box

which is equivalent to

txtArticleId.Text = dataTable.Rows[currRec]["artId"].ToString();

Hence when you start the application and press the LoadTable button, you will see the following output

http://www.programmersheaven.com/articles/images/faq/image008.gif

Back