More Details SQL use in C#
The Fill method as we say before working with SQL statements. The Fill method retrieves the data from the data source using a SELECT statement. The IDbConnection object associated with the select command must be valid, but it does not need to be open. If the IDbConnection is closed before Fill is called, it is opened to retrieve data, and then closed. If the connection is open before Fill is called, it remains open. If an error is encountered while populating the data set, rows added prior to the occurrence of the error remain in the data set. The remainder of the operation is aborted. If a command does not return any rows, no tables are added to the DataSet, and no exception is raised.
If the DbDataAdapter object encounters duplicate columns while populating a DataTable, it will generate names for the subsequent columns, using the pattern "columnname1", "columnname2", "columnname3", and so on. If the incoming data contains unnamed columns, they are placed in the DataSet according to the pattern "Column1", "Column2", and so on. Here is syntax of Fill method:
public
override int Fill
{
DataSet dataSet;
}
When the query specified returns multiple results, the result set for each row returning query is placed in a separate table. Additional result sets are named by appending integral values to the specified table name (for example, "Table", "Table1", "Table2", and so on.). Since no table is created for a query that does not return rows, if you were to process an insert query followed by a select query, the table created for the select query would be named "Table" since it is the first table created. Applications should use caution when using column and table names to ensure that conflicts with these naming patterns does not occur. When the SELECT statement used to populate the DataSet returns multiple results, such as a batch SQL statements, if one of the results contains an error, all subsequent results are skipped and not added to the DataSet.
When using subsequent Fill calls to refresh the contents of the DataSet, two conditions must be met:
- The SQL statement should match the one initially used to populate the DataSet.
- The Key column information must be present.
If primary key information is present, any duplicate rows are reconciled and only appear once in the DataTable that corresponds to the DataSet. Primary key information may be set either through FillSchema, by specifying the PrimaryKey property of the DataTable, or by setting the MissingSchemaAction property to AddWithKey.
When handling batch SQL statements that return multiple results, the implementation of FillSchema for the OLE DB .NET Data Provider retrieves schema information for only the first result. To retrieve schema information for multiple results, use Fill with the MissingSchemaAction set to AddWithKey.
public
void GetMyRecords()
{
// ...
// create myDataSet and myDataAdapter
myDataAdapter = new OleDbDataAdapter();
// ...
myDataAdapter.Fill(myDataSet);
OleDbDataAdapter myDSCmdCopy = (OleDbDataAdapter) myDataAdapter.CloneInternals
);
}
We have seen how the Fill method covers an important role during the record retrieving point of data manipulation. This method populates the specified DataSet or DataTable object, retrieving a set of record from the database. The Fill method connects to the database using either the connection string or a connection object.
If you use the Fill method with an open connection, the method will use that connection without closing it after retrieving the record. In the code examined above, we have encountered just one of the Fill method's variants exposed by the DataAdapter. For example you can omit the string parameter that defines the table name that is created in the DataSet object. Let's see what happens when the second parameter is omitted:
// Create the data adapter object pointing to the authors table
OleDbDataAdapter dadp = new OleDbDataAdapter("SELECT au_id, au_lname,
au_fname FROM authors",
"Provider=Microsoft.Jet.OLEDB.4.0;Password=;User ID=Administrator;Data
ource=C:\\pubs.mdb");
DataSet dset = new DataSet("Authors");
dadp.Fill(dset);
// Foreach loop
foreach(DataRow row in dset.Tabkles[0].Rows)
{
Console.WriteLine("First name: (0)", row["au_name"]);
Console.WriteLine("Last name: (0)", row["au_name"]);
}
These small examples show to us just simple SELECT statements in C# programming language. Now, we will have more SQL statements in C#.
Using More Complex Queries in C#
So far we have used really simple queries where data has been retrieved from a single table. In real projects the SQL SELECT statements are more complex, where more than one table is joined to other tables using primary and foreign keys. However, what happens to the DataSet object when we specify a more complex query to execute? Let's see an example; First we will see SELECT statement and will put it in our C# code.
SELECT authours.au_fname, authors.au_lname FROM (authors INNER JOIN
titleauthor ON authors.au_id = titleauthor.au_id)
INNER JOIN titles ON titleauthor.title_id = titles.title_id
Executing a SELECT query like this will produce a single DataTable in the DataSet the database engine handles the relationships to produce a single result set combining data from three tables. Here is the source code using this SELECT statement in C# code:
using
System;
using System.Data;
using System.Data.OleDb;
namespace SQLExample2
{
/// <summary>
/// Summary description for Class1.
/// </summary>
class Class1
{
static void Main(string[] args)
{
// Create a connection object
OleDbConnection dbConn = new OleDbConnection
"Provider=Microsoft.Jet.OLEDB.4.0;Password=;User ID=Admin;Data
ource=C:\\pubs.mdb");
DataSet ds = new DataSet("AuthorsAndTitles");
// Create the data adapter object pointing to the authors table
OleDbDataAdapter daAuthors = new OleDbDataAdapter("SELECT au_id, au_fname,
u_lname FROM authors",dbConn);
// Fill the DataSet with author
daAuthors.Fill(ds,"Author");
// Create the data adapter object pointing to the titleauthor table
OleDbDataAdapter daTitleAuthor = new OleDbDataAdapter("SELECT au_id, title_id FROM itleauthor", dbConn);
// Fill the DataSet with titleauthor
daTitleAuthor.Fill(ds,"TitleAuthor");
// Create the data adapter object pointing to the titles table
OleDbDataAdapter daTitle = new OleDbDataAdapter("SELECT title_id,title FROM titles", dbConn);
// Fill the DataSet with titles
daTitle.Fill(ds,"Titles");
// Define primary keys
ds.Tables["Titles"].Columns["title_id"].Unique = true;
ds.Tables["Titles"].Columns["title_id"].AllowDBNull = false;
ds.Tables["Titles"].PrimaryKey = new DataColumn[] {ds.Tables["Titles"].Columns["title_id"]};
ds.Tables["Author"].Columns["au_id"].Unique = true;
ds.Tables["Author"].Columns["au_id"].AllowDBNull = false;
ds.Tables["Author"].PrimaryKey = new DataColumn[] {ds.Tables["Author"].Columns"au_id"]};
ds.Tables["TitleAuthor"].PrimaryKey = new DataColumn[] {ds.Tables["TitleAuthor"].Columns["au_id"],
ds.Tables["TitleAuthor"].Columns["title_id"]};
// Define constraints
ForeignKeyConstraint fk1 = new ForeignKeyConstraint("authorstitleauthor",ds.Tables
"Author"].Columns["au_id"],ds.Tables["TitleAuthor"].Columns["au_id"]);
ds.Tables["TitleAuthor"].Constraints.Add(fk1);
ForeignKeyConstraint fk2 = new ForeignKeyConstraint("titlestitleauthor",ds.Tables["Titles"].Columns["title_id"],ds.Tables["TitleAuthor"].Columns["title_id"]);
ds.Tables["TitleAuthor"].Constraints.Add(fk2);
// Write the related XML document representing the DataSet
ds.WriteXml("c:\\join.xml",XmlWriteMode.WriteSchema);
Console.WriteLine("Done! Press Enter to exit.");
Console.ReadLine();
}
}
}
This example is very example, because it has several SQL statement and specifications such as SELECT, Primary Key, and Foreign Key in C# code. Now, let's look some other examples with SQL using C#.
Where clause and C#
This example shows a where clause in C# code. You can create several different version of this code piece and use where clause.
OleDbConnection nwindCon =
new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=C:\\Program Files\\Microsoft Office\\" +"Office\\Samples\\Northwind.mdb;User ID=;Password=;");
OleDbAdapter orderAd =
new OleDbDataAdapter("Select * FROM Orders where EmployeeID = " +empId, nwindCon);
orderAd.Fill(nortwindSet, "Orders");
ordersGrid.DataSource = northwindSet.Tables["Orders"];
ordersGrid.DataBind();
}
We used similar select statement that we saw it before in SQL part of this article. First we open connection and than we use SELECT statement with WHERE clause and fill a dataset. At the end of code piece we bind the dataset to a datagrid.
Order By clause and C#
This example shows a where clause in C# code. You can create several different version of this code piece and use ORDER BY clause.
OleDbConnection nwindCon =
new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source=C:\\Program Files\\Microsoft Office\\" +"Office\\Samples\\Northwind.mdb;User ID=;Password=;");
OleDbAdapter orderAd =
new OleDbDataAdapter("Select * FROM Orders ORDER BY EmployeeID," +nwindCon);
orderAd.Fill(nortwindSet, "Orders");
ordersGrid.DataSource = northwindSet.Tables["Orders"];
ordersGrid.DataBind();
}
This example looks very familiar with previous one. We used ORDER BY instead WHERE
lause and than we open connection and than we use SELECT statement and fill a dataset.
INNER JOIN and C#
This example shows an INNER JOIN in C# code. You can create several different version of this code piece and use INNER JOIN.
OleDbConnection nwindCon =
new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=C:\\Program Files\\Microsoft Office\\" +
"Office\\Samples\\Northwind.mdb;User ID=sa;Password=;");
OleDbAdapter orderAd =
new OleDbDataAdapter("SELECT * FROM Orders INNER JOIN," +
Customers ON Orders.CustId = Customer.CustomerId, nwindCon);
orderAd.Fill(nortwindSet, "Orders");
ordersGrid.DataSource = northwindSet.Tables["Orders"];
ordersGrid.DataBind();
}
This example looks a little different than previous examples. We used INNER JOIN and
ompare two tables Customer ID's are equal or not and than we open connection and than
e use SELECT statement and fill a dataset.
OUTER JOIN and C#
This example shows an OUTER JOIN in C# code. You can create several different version of this code piece and use OUTER JOIN same as INNER JOIN.
OleDbConnection nwindCon =
new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=C:\\Program Files\\Microsoft Office\\" +
"Office\\Samples\\Northwind.mdb;User ID=sa;Password=;");
OleDbAdapter orderAd =
new OleDbDataAdapter("SELECT * FROM Orders, BOOKs WHERE," +Orders.BookID *= BOOKs.BookID, nwindCon);
orderAd.Fill(nortwindSet, "Orders");
ordersGrid.DataSource = northwindSet.Tables["Orders"];
ordersGrid.DataBind();
}
This example looks similar with INNER JOIN example. We used OUTER JOIN instead INNER JOIN and compare two tables BookID's and than we open connection and than we use SELECT statement and fill a dataset.
continue article