Software Dev't Navigation

Latest news

27 Feb 2007: I decided to write a few lines about my experiences with C# and SharpDevelop, but got waylaid looking at some of my earlier experiences.

07 Mar 2007: SharpDevelop 2.1 Final has been released.

Data Queries

I found writing data queries a bit tedious and cumbersome using the ADO NET framework. I expected it to be a lot easier than having to write a lot of code to do something that can be achieved in a few lines in Delphi. But looking back this seems to be the same whatever I am converting. Once you have written a few queries then it is clear that they all adhere to the same types and then it's just a mechanical exercise to write a particular query. Admittedly I am not using Visual Studio and SharpDevelop still needs to support MySQL with visual database components.

LINQ (Language Integrated Query) is Microsofts chosen method of integrating a query language into the .NET framework. So far it has been quite easy to resist learning about LINQ because most of what I want to achieve can be quite easily accomplished in standard SQL.

Data Query Types

There are three main types that I have come across the first being a query that runs directly off the rows in a dataset which I refer to as a simple query. The second is where you are running a query that takes parameter(s). Thirdly there are queries where you wish to process the results from the query one row at a time, which I call a complex query.

Simple Queries

Here is a simple query that fetches all the books that are unread. First a new dataset MyDataSet is created, which is the filled with the results from the query. The dataset results could then be used to display all the records in a DataGridView.

try
{
MyDataSet = new DataSet();

string temp;
temp = "SELECT a.firstname, a.surname, c.subject, b.title"+
" FROM author a, book b"+
" LEFT OUTER JOIN subject c" +
" ON b.subjno = c.subjno" +
" WHERE"+
" a.authno=b.authno"+
" and (b.dateread is null)"+
" ORDER by a.surname";

da = new MySqlDataAdapter(temp, conn);

da.Fill(MyDataSet, "author");

}
catch (Exception Ex)
{
MessageBox.Show(Ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}

Queries with Parameters

Here is a query which takes a parameter acctid, so that the records selected are restricted to those for the particular account passed as a parameter to the query. Delphi query parameters allow you to visually supply parameters, but in actual fact there is not too much extra work in NET.

public void AccountDataSet(ref System.Data.DataSet dataSet6, ref int acctid)
{
try
{
dataSet6 = new DataSet();

string temp;
temp = "SELECT a.acct_id, a.cdate, a.notes, a.receipt, a.payment" +
", a.balance, a.trans_type, b.cat_descr, a.trans_no"+
" FROM t_dace a" +
" LEFT OUTER JOIN t_category b" +
" ON a.cat_no = b.cat_no" +
" WHERE acct_id =" + acctid;

da = new MySqlDataAdapter(temp, conn);

da.Fill(dataSet6,"t_dace");

}
catch (Exception Ex)
{
MessageBox.Show(Ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}

Complex Queries

I would like to have a different name for these type of queries because they are not particularly complex, but do for the first time involve use of the MySqlDataReader. I find the best way to envisage this is to consider writing a query to return a dataset but instead of using all the rows returned in the dataset you wish to only use selected values or employ the results in a loop process. So typically the datareader is used to select some specific results which are then processed (maybe displayed on a form) and then the datareader is used to get the next row until the end of the rows or the program terminates the loop after so many reads. The great thing is that individual values from the row can be manipulated; for example transformed to currency display. I have also read that using a Data Reader is much more efficient than returning all the rows in a Dataset. This may be true, but I have not proven this yet; I tend to use a Data Reader only for cases where it is more convenient.

Total Amount Query Example

Note in Delphi it would just be a simple matter of performing a read and converting the value :-

public void TotalQuery(ref string string3)
{
MySqlDataReader reader = null;
MySqlCommand cmd = new MySqlCommand();

SQLString = "SELECT MAX(acct_id) as cid, SUM(rate*balance)" +
" FROM t_item" +
" WHERE acct_id < =10000";

cmd.Connection = conn;
cmd.CommandText = SQLString ;

try
{
reader = cmd.ExecuteReader();

reader.Read();
string3 = reader.GetString(1);

}
catch (Exception Ex)
{

MessageBox.Show(Ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (reader != null) reader.Close();
}
}
}

ePlaice / For the Best Software on the Net

Mainly Free and Open Source Software