cyberavionics_systems

Ravi

17y9.5k1025Blog

Beginner Level Details

1 Comment

Join the conversation! Your thoughts help the community grow.

Ravi17y ago
Data presentation is an important goals of any application development process. ASP.NET 2.0 provides many server controls which render data in different rich formats and styles. for example Datalist, Gridview, repeater control etc. Here I am going to discuss about repeater control in detail. Repeater control is a template based container control. you define layout for the Repeater control by creating different templates based on your needs. The Repeater control may be bound to a database table, an XML file, or another list of items. Here we will show how to bind data to a Repeater control. Repeater Control Templates Repeater controls provides different kinds of templates which helps in determining the layout of control's content. Templates generate markup which determine final layout of content. These are as follows:<o:p></o:p> HeaderTemplate<o:p></o:p> ItemTemplate<o:p></o:p> AlternatingItemTemplate<o:p></o:p> FooterTemplate<o:p></o:p> SeparatorTemplate<o:p></o:p> ItemTemplate: ItemTemplate defines how the each item is rendered from data source collection. AlternatingItemTemplate: AlternatingItemTemplates define the markup for each Item but for AlternatingItems in DataSource collection like different background color and styles. HeaderTemplate: HeaderTemplate will emit markup for Header element for DataSource collection FooterTemplate: FooterTemplate will emit markup for footer element for DataSource collection SeparatorTemplate: SeparatorTemplate will determine separator element which separates each Item in Item collection. Usually, SeparateTemplate will be <br> html element or <hr> html element.<o:p></o:p> DataBinding in Repeater Control:<o:p></o:p> Like any other Data Bound control, Repeater control supports DataSource property which allows you to bind any valid DataSource, any datasets or XML files.<o:p></o:p> There are the following easy steps to bind repeater control.<o:p></o:p> Step 1: Create new web application.<o:p></o:p> Step 2: Drag repeater control from toolbox and drop on the page.<o:p></o:p> Step 3: add the varies templates like as follows:<o:p></o:p> InlineCode:<o:p></o:p> <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title>Untitled Page</title> </head> <body> <form id="form1" runat="server"> <div> <asp:Repeater ID="Repeater1" runat="server"> <HeaderTemplate> <table border="0" width="600px" cellpadding="2" cellspacing="1" style="border: 1px solid maroon;"> <tr bgcolor="maroon"> <th> Name</th> <th> Description</th> <th> Email</th> <th> Country</th> </tr> </HeaderTemplate> <ItemTemplate> <tr> <td width="100"> <%# DataBinder.Eval(Container, "DataItem.Name")%> </td> <td> <%# DataBinder.Eval(Container, "DataItem.Description")%> </td> <td width="150"> <%# DataBinder.Eval(Container, "DataItem.Email")%> </td> <td width="100" align=center> <%# DataBinder.Eval(Container, "DataItem.Country")%> </td> </tr> </ItemTemplate> <AlternatingItemTemplate> <tr bgcolor="#e8e8e8"> <td width="100"> <%# DataBinder.Eval(Container, "DataItem.Name")%> </td> <td> <%# DataBinder.Eval(Container, "DataItem.Description")%> </td> <td width="150"> <%# DataBinder.Eval(Container, "DataItem.Email")%> </td> <td width="100" align=center> <%# DataBinder.Eval(Container, "DataItem.Country")%> </td> </tr> </AlternatingItemTemplate> <FooterTemplate> </table> </FooterTemplate> </asp:Repeater> <div style="font-size:14px; color:Navy">Total Items: <asp:Label ID=totalcount runat=server></asp:Label> </div> </div> </form> </body> </html><o:p></o:p> Cpde Behind code:<o:p></o:p> using System; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Data.SqlClient;<o:p></o:p> public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { SqlCommand cmd = new SqlCommand("SELECT * FROM Users", new SqlConnection(@"Server=Puru\SQLSERVER2005;Database=Test; Uid=sa;Pwd=wintellect;"));<o:p></o:p> cmd.Connection.Open(); Repeater1.DataSource = cmd.ExecuteReader(); Repeater1.DataBind(); if (Repeater1.Items.Count > 0) { totalcount.Text = Repeater1.Items.Count.ToString(); } cmd.Connection.Close(); cmd.Connection.Dispose(); } }<o:p></o:p> Output: <o:p></o:p> >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><o:p> </o:p> Database connect and retrieve in c#<o:p></o:p> The code is public void ConnectToAccess() { System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(); database. conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data source= @"path \AccessFile.mdb"; try { conn.Open(); // Insert code to process data. } catch (Exception caught) { MessageBox.Show(caught.Message); } finally { conn.Close(); } }<o:p></o:p> <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< DSNless Connection Code Sample<o:p></o:p> If you have a secure folder on your server, you may want to avoid using DSN because it's a little bit slower. I've set my data folder to be "secure". This means, the folder has full permissions for the asp engine context, but no permissions for the visitors. Let's create the connection and recodset objects now.<o:p></o:p> <% set conn = Server.CreateObject("ADODB.Connection") set rs = Server.CreateObject("ADODB.Recordset") %><o:p></o:p> First way to define where your database is to use relative location by utilizing Server.MapPath method.<o:p></o:p> <% 'Define the location of your database 'as follows if you want to use a relative folder. cDBLocation = "/securedata/mydatabase.mdb" 'Construct the connection string using MapPath. sConnSample = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Persist Security Info=False;Data Source=" & _ Server.MapPath(cDBLocation) %><o:p></o:p> You may want to define your physical database location. You should always use the physical location if you can.<o:p></o:p> <% 'If you are sure about the pyhsical location 'of your database, it's better to use it that way. cDBLocation = "c:\securedata\mydatabase.mdb" sConnSample = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Persist Security Info=False;Data Source=" & _ cDBLocation %><o:p></o:p> After defining the location, it's time to connect to the database and get the records! As a sample, I've defined two re-usable functions for opening and closing the connections. Of course, there are much better ways depending on the functional needs of your application, but you can still use the following code.<o:p></o:p> <% 'ADO Constants Const adOpenForwardOnly = 0 Const adOpenStatic = 3 Const adCmdTable = 2 sub OpenDB(sConn) 'Opens the given connection 'and attachs the recordset to it conn.open sConn set rs.ActiveConnection = conn 'Using a static cursor, you will be able 'to use AddNew and Update methods. 'Consider using adOpenForwardOnly if you 'are only reading records (much faster). rs.CursorType = adOpenStatic end sub sub CloseDB() 'Closes the active connection 'And cleans up the memory rs.close conn.close set rs = nothing set conn = nothing end sub %><o:p></o:p> You may execute an SQL statement to get your recordset filled:<o:p></o:p> <% OpenDB sConnSample sSQL = "SELECT Field1, Field2 FROM TestTable" rs.Open sSQL,,, adCmdTable 'Display the records anyway you want CloseDB<o:p></o:p> >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><o:p> </o:p> In this Article I will try to cover ExecuteNonQuery method Provided in SqlCommand class. Here we will try to create 6 overload for this method and will try to see what it means :- Stuation 1 :- We need to Execute a SqlCommand (that returns no resultset and takes no parameters) against the database specified in the connection string. In this we can write the Following overload :- public static int ExecuteNonQuery(string connectionString, CommandType commandType, stringcommandText) { // Pass through the call providing null for the set of SqlParameters return ExecuteNonQuery(connectionString, commandType, commandText, (SqlParameter[])null); } Stuation 2 :- We need to Execute a SqlCommand (that returns no resultset) against the database specified in the connection string using the provided parameters. See the difference Here though No resultset(equivalent to situation 1) But Parameters are there. public static int ExecuteNonQuery(string connectionString, CommandType commandType, stringcommandText, params SqlParameter[] commandParameters) { if (connectionString == null || connectionString.Length == 0) throw newArgumentNullException("connectionString");<o:p></o:p> // Create & open a SqlConnection, and dispose of it after we are done using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open();<o:p></o:p> // Call the overload that takes a connection in place of the connection string return ExecuteNonQuery(connection, commandType, commandText, commandParameters); } } Situation 3 :- We need to Execute a stored procedure via a SqlCommand (that returns no resultset) against the database specified in the connection string using the provided parameter values. This method will query the database to discover the parameters for the stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. public static int ExecuteNonQuery(string connectionString, string procedureName, params object[] parameterValues) { if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); if (procedureName == null || procedureName.Length == 0) throw new ArgumentNullException("procedureName");<o:p></o:p> // If we receive parameter values, we need to figure out where they go if ((parameterValues != null) && (parameterValues.Length > 0)) { // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, procedureName);<o:p></o:p> // Assign the provided values to these parameters based on parameter order AssignParameterValues(commandParameters, parameterValues); // Call the overload that takes an array of SqlParameters return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, procedureName, commandParameters); } else { // Otherwise we can just call the SP without params return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, procedureName); } } Situation 4:- We need to Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlConnection. public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText) { // Pass through the call providing null for the set of SqlParameters return ExecuteNonQuery(connection, commandType, commandText, (SqlParameter[])null); } Situation 5:- We Need to Execute a SqlCommand (that returns no resultset) against the specified SqlConnection using the provided parameters. public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { if (connection == null) throw new ArgumentNullException("connection");<o:p></o:p> // Create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); bool mustCloseConnection = false; PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);<o:p></o:p> // Finally, execute the command int retval = cmd.ExecuteNonQuery();<o:p></o:p> // Detach the SqlParameters from the command object, so they can be used again cmd.Parameters.Clear(); if (mustCloseConnection) connection.Close(); return retval;<o:p></o:p> } Situation 6 :- We need to Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified SqlConnection using the provided parameter values. This method will query the database to discover the parameters for the stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. public static int ExecuteNonQuery(SqlConnection connection, string procedureName, params object[] parameterValues) { if (connection == null) throw new ArgumentNullException("connection"); if (procedureName == null || procedureName.Length == 0) throw new ArgumentNullException("procedureName");<o:p></o:p> // If we receive parameter values, we need to figure out where they go if ((parameterValues != null) && (parameterValues.Length > 0)) { // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, procedureName);<o:p></o:p> // Assign the provided values to these parameters based on parameter order AssignParameterValues(commandParameters, parameterValues);<o:p></o:p> // Call the overload that takes an array of SqlParameters return ExecuteNonQuery(connection, CommandType.StoredProcedure, procedureName, commandParameters); } else { // Otherwise we can just call the SP without params return ExecuteNonQuery(connection, CommandType.StoredProcedure, procedureName); } } To Implement All these Overload you need to write Your SQLHELPER Class and accomodate the above Code in that class.<o:p></o:p> >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Why did I use Generic <T, K>? This keeps the number of overloaded methods down to a minimum without using objects. Let me explain. Method One, without Generic <T, K>:<o:p></o:p> 1. You would be to have an overloaded method for every type of CONNECTION and DATATYPE.<o:p></o:p> 2. Because of the different DATATYPES, you would need to rename the methods.<o:p></o:p> 3. I am sure that you can see that very soon, you have tons of methods and tons of code.<o:p></o:p> 4. For a developer user of the component this becomes complex.<o:p></o:p> 5. From a maintenance/enhancement point of view, this becomes tiring and can be costly.<o:p></o:p> Method Two, without Generic <T, K>:<o:p></o:p> You have the same methods that I do, but both the CONNECTION and the DATATYPE are cast as objects.<o:p></o:p> Well we all know that we can have compile issues and usability issues with objects, which is where Generics <T, K> are now part of Visual Studio 2005.<o:p></o:p> Technical Details of the component<o:p></o:p> I have replaced the T with CONNECTION and the K with DATATYPE. <o:p></o:p> When initializing the object, the first parameter, your CONNECTION, can be any of the following types:<o:p></o:p> a. A Connection String<o:p></o:p> b. A System.Data.SqlClient.SqlConnection<o:p></o:p> c. A System.Data.OleDb.OleDbConnection<o:p></o:p> d. A System.Data.Odbc.OdbcConnection<o:p></o:p> e. An ADODB.Connection<o:p></o:p> f. A Oracle.DataAccess.Client.OracleConnection<o:p></o:p> g. To name a few...<o:p></o:p> 3. When Initializing the object, the second parameter, your DATATYPE, can be any of the following types:<o:p></o:p> a. A System.String, here your data would be a disconnection XML formatted string, needed by those systems that can only handle strings.<o:p></o:p> b. A System.Data.DataSet<o:p></o:p> c. A System.Data.DataTable<o:p></o:p> d. A System.Xml.XmlDocument<o:p></o:p> 4. There are four (4) exposed Public Methods, three (3) of them are overloaded:<o:p></o:p> a. public DATATYPE Create(CONNECTION, string sql, string tableName)<o:p></o:p> b. public DATATYPE Create(string provider, string sql)<o:p></o:p> c. public DATATYPE Create(CONNECTION, string sql, string[] parameterList, string tableName, intTimeout)<o:p></o:p> d. public void Update(CONNECTION, DATATYPE, string sql, string tableName)<o:p></o:p>  Usage here is when you want to update/insert/etc data utilizing Typed DataSets<o:p></o:p>  When the user updates the appropriate line/column  After editing the data, the next method called (as highlighted below), will update the table utilizing Typed Dataset. This also works for inserts as well. <o:p></o:p> >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><o:p> </o:p> MySQL Database Connection Test in C# ???<o:p></o:p> Posted by: Kevin Snyder ()<o:p></o:p> Date: June 12, 2008 02:27PM<o:p></o:p> <o:p> </o:p> I'm converting an MSSQL / C# project into MySQL / C# and need an equivalent code for the following code that finds MSSQL databases and returns a result via cmd.ExecuteScalar() I could easily run the connection string within a Try-Catch and use the error to Throw a message, but I would like to code something a bit more classy... [MSSQL / C#] string sql = " Select count(*) From master.dbo.sysdatabases Where name = 'MyDatabase' "; cn = new SqlConnection("server= (local);initial catalog=master;persist security info=false;integrated security=SSPI); cmd = new SqlCommand(sql, cn); if (cn.State == ConnectionState.Closed) { cn.Open(); } iRecordCount = System.Convert.ToInt32(cmd.ExecuteScalar()); if (cn.State == ConnectionState.Open) { cn.Close(); cn.Dispose(); cmd.Cancel(); cmd.Dispose(); } } if (iRecordCount != 0) { MessageBox.Show("Test Connection Successful!"); } else { MessageBox.Show("Test Connection NOT Successful!"); }<o:p></o:p> >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><o:p> </o:p> Database connectivity ASP.NET using C#<o:p></o:p>  ASP.NET, C#, Database connectivity, Web development<o:p></o:p> hi i am new in asp.net,so excuse me such a stupid question. i am trying to connect a page to the database but there may be some problem please help me...... this the aspx file code <table> <tr> <td style="width: 100px"> Name</td> <td style="width: 7px"> :</td> <td style="width: 185px"> <asp:TextBox ID="TextBox1" runat="server" Width="177px"></asp:TextBox></td> </tr> <tr> <td style="width: 100px"> Roll</td> <td style="width: 7px"> :</td> <td style="width: 185px"> <asp:TextBox ID="TextBox2" runat="server" Width="178px"></asp:TextBox></td> </tr> </table> <br /> <table> <tr> <td style="width: 100px"> <asp:Button ID="save" runat="server" Text="Save" OnClick="btn_save" /></td> <td style="width: 100px"> <asp:Button ID="cancel" runat="server" Text="Cancel" /></td> </tr> </table> this is the aspx.cs file code using System; using System.Data; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Data.SqlClient; using Connection; using System.IO; public partial class _hello : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } protected void btn_save(object sender, EventArgs e) { try { Connection.ConnectionCon con = new Connection.ConnectionCon(); String query = "insert into tbl_JobSeeker_Details(name,roll)" + " values('" + TextBox1.Text + "','" + TextBox2.Text + "' , 1)"; con.querryExecution(query); Response.Redirect("hello.aspx", false); } catch (Exception ex) { Response.Write(ex.Message); } } } this is the connection.cs page using System; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Data.SqlClient; /// <summary> /// Summary description for Connection /// </summary> namespace Connection { public class ConnectionCon { String constr = ConfigurationManager.ConnectionStrings["JobSearch">.ToString(); public void querryExecution(String querry) { SqlConnection con = null; SqlCommand com = null; con = new SqlConnection(constr); com = new SqlCommand(querry, con); try { con.Open(); com.ExecuteNonQuery(); con.Close(); } catch (Exception e) { string s = e.Message; con.Close(); } } public DataTable FetchDT(String query) { SqlConnection con = null; SqlDataAdapter da = null; DataTable dt = null; DataSet ds = null; try { con = new SqlConnection(constr); con.Open(); da = new SqlDataAdapter(query, con); con.Close(); ds = new DataSet(); da.Fill(ds); dt = ds.Tables[0]; return dt; } catch (Exception ex) { con.Close(); string s = ex.Message; return dt; } } public DataSet FetchDS(string query) { SqlConnection con = null; SqlDataAdapter da = null; DataSet ds = null; try { con = new SqlConnection(constr); con.Open(); da = new SqlDataAdapter(query, con); con.Close(); ds = new DataSet(); da.Fill(ds); return ds; } catch (Exception ex) { con.Close(); string s = ex.Message; return ds; } } } }<o:p></o:p> >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><o:p> </o:p> Objective: Objective of this article is to explain, how to use stored procedure with ADO.Net Data Service. For other articles on ADO.NET Data Service, follow these links Introduction of ADO.NET Data Service Working with ADO.NET Data Service Explanation of Database For Sample, here database DJ is being used. DJ database is containing two tables<o:p></o:p> Dept<o:p></o:p> Emp<o:p></o:p> Dept  Emp  Database Diagram  There is Stored Procedure called Get Data. This SP is retrieving all the records from Emp table. Stored Procedure look more or less like, below USE [dj] GO /****** Object: StoredProcedure [dbo].[GetData] Script Date: 05/14/2009 12:12:06 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= ALTER PROCEDURE [dbo].[GetData] -- Add the parameters for the stored procedure here<o:p></o:p> AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT * from Emp END Objective of this article is to use this Stored Procedure (GetData) in ADO.NET Data Service. Step 1 Create a new project as web application and give any name. here name is StoredProcedureTesting.  Step 2 Add Data Model. To do so, add new item and click on Data tab then select ADO.NET Entity Model. Give any name. Here name is csharp.edmx.   Select database from drop down list. If database in not listed there, create new connection and then select database. Here database dj is being selected. Schema of database is discussed above. Give any name to Entity. Here name is storedproceduretestingEntities.   Select the entire table and click on Stored Procedure tab and from there select GetData stored procedure as well. Give any name to model, here it isstoredproceduretestingModel  So final edmx file created will look more or less like below.  Step 3<o:p></o:p> Right click on edmx file. Select Add then Function Import.  <o:p></o:p> Give Function Import name as of Stored Procedure name. here GetData <o:p></o:p> Select Return type. Stored procedure is returning here Emp, so select Emp.  <o:p></o:p> In model browser of csharp.edmx , under FunctionImport tab GetData is listed. <o:p></o:p> In solution explorer, Right Click on csharp.edmx and select open with <o:p></o:p> A dialog box will appear. Select XML Editor from there then click on OK.  In Confirmation Box, select YES.<o:p></o:p>  Explanation of markup .edmx file Markup of edmx file contains three parts or segments.   On selecting stored procedure in entity model, by default below code will get added in SSDL part of markup of .edmx ssdl segment <Function Name="GetData" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion"Schema="dbo" /> Entire SSDL of edmx is, csdl segment <FunctionImport Name="Getdata" EntitySet="Emp" ReturnType="Collection(storedproceduretestingModel.Emp)" /><o:p></o:p> In above XML Name -> name of the stored procedure EntitySet -> Entity (Table) on which Stored Procedure is working ReturnType -> Type of the Return data from Stored Procedure. If procedure required any parameter, then markup of parameters will be added inside <FunctionImport> like below. FunctionImport Name ="GetData" EntitySet ="Emp" ReturnType ="Collection(storedproceduretestingModel.GetData)"> <Parameter Name ="" </FunctionImport> msl segment <FunctionImportMapping FunctionImportName="Getdata" FunctionName="storedproceduretestingModel.Store.GetData" /> Up to here Stored Procedure is mapped in entity model. Step 4 Creating Service Right click on project and add new item. Add ADO.NET Data Service. Give any name here name is DataService.cs  After this, click on DataService.svc and modify as below using System; using System.Collections.Generic; using System.Data.Services; using System.Linq; using System.ServiceModel.Web; using System.Web;<o:p></o:p> namespace StoredProcedureTesting { public class DataService : DataService<storedproceduretestingEntities> { // This method is called only once to initialize service-wide policies. public static void InitializeService(IDataServiceConfiguration2 config) { // TODO: set rules to indicate which entity sets and service operations are visible, updatable, etc. // Examples: config.SetEntitySetAccessRule("*", EntitySetRights.AllRead); config.SetServiceOperationAccessRule("*", ServiceOperationRights.All); } } }<o:p></o:p> Step 5 Testing Service Right click on DataService and view in browser to test it.  Step 6 Calling stored procedure in ADO.NET Data service [WebGet] public List<Emp> GetData() { storedproceduretestingEntities ent = new storedproceduretestingEntities(); return ent.Getdata().ToList(); } Explanation of code Create a new method in DataService class. Create instance of entities class. Call the stored procedure which is imported as function on instance of entity class. Complete code for DataService class is as below DataService.svc.cs using System; using System.Collections.Generic; using System.Data.Services; using System.Linq; using System.ServiceModel.Web; using System.Web;<o:p></o:p> namespace StoredProcedureTesting { public class DataService : DataService<storedproceduretestingEntities> { // This method is called only once to initialize service-wide policies. public static void InitializeService(IDataServiceConfiguration2 config) { // TODO: set rules to indicate which entity sets and service operations are visible, updatable, etc. // Examples: config.SetEntitySetAccessRule("*", EntitySetRights.AllRead); config.SetServiceOperationAccessRule("*", ServiceOperationRights.All); }<o:p></o:p> [WebGet] public List<Emp> GetData() { storedproceduretestingEntities ent = new storedproceduretestingEntities(); return ent.Getdata().ToList();<o:p></o:p> } } } Step 7 Testing Stored Procedure in browser Run the service in browser. Give GetData function name in browser to run the stored procedure. Let service is hosted on server 2989 then run stored procedure as http://localhost:2989/DataService.svc/GetData , output would be like below  Step 8 Consuming Stored Procedure at client Here console application is client which is going to consume stored procedure. Here I am adding client in same solution of service, by right clicking and adding new project then selecting console application from the Windows tab. Add reference to the client  Add Service Reference , just click Discover in solution ( if client and service is in same solution else copy paste URL of service there)  Add namespace using System.Data.Services.Client; using Client.ServiceReference1; Here Client is name of the test project. Program.cs using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.Services.Client; using Client.ServiceReference1;<o:p></o:p> namespace Client { class Program { static void Main(string[] args) { DataServiceContext context = new DataServiceContext(new Uri("http://localhost:2989/DataService.svc/")); IEnumerable<Emp> empResult = context.Execute<Emp>(new Uri("http://localhost:2989/DataService.svc/GetData")); foreach (Emp e in empResult) { Console.WriteLine(e.EmpName + e.EmpId + e.Dept);<o:p></o:p> }<o:p></o:p> Console.Read(); } } } Output  Conclusion This article explained about, how to use stored procedure in ADO.NET Data Service.<o:p></o:p> >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><o:p> </o:p> SSH Tunnel for Database Connection (such as ADO, ODBC, etc.)<o:p></o:p> bool success;<o:p></o:p> success = sshTunnel.UnlockComponent("30-day trial");<o:p></o:p> if (success != true) {<o:p></o:p> MessageBox.Show(sshTunnel.LastErrorText);<o:p></o:p> return;<o:p></o:p> }<o:p></o:p> <o:p> </o:p> // The destination host/port is the database server.<o:p></o:p> // The DestHostname may be the domain name or<o:p></o:p> // IP address (in dotted decimal notation) of the database<o:p></o:p> // server.<o:p></o:p> sshTunnel.DestPort = 1433;<o:p></o:p> sshTunnel.DestHostname = "myDbServer.com";<o:p></o:p> <o:p> </o:p> // Provide information about the location of the SSH server,<o:p></o:p> // and the authentication to be used with it. This is the<o:p></o:p> // login information for the SSH server (not the database server).<o:p></o:p> sshTunnel.SshHostname = "192.168.1.108";<o:p></o:p> sshTunnel.SshPort = 22;<o:p></o:p> sshTunnel.SshLogin = "mySshLogin";<o:p></o:p> sshTunnel.SshPassword = "mySshPassword";<o:p></o:p> <o:p> </o:p> // Start accepting connections in a background thread.<o:p></o:p> // The SSH tunnels are autonomously run in a background<o:p></o:p> // thread. There is one background thread for accepting<o:p></o:p> // connections, and another for managing the tunnel pool.<o:p></o:p> int listenPort;<o:p></o:p> listenPort = 3316;<o:p></o:p> success = sshTunnel.BeginAccepting(listenPort);<o:p></o:p> if (success != true) {<o:p></o:p> MessageBox.Show(sshTunnel.LastErrorText);<o:p></o:p> return;<o:p></o:p> }<o:p></o:p> <o:p> </o:p> // At this point you may connect to the database server through<o:p></o:p> // the SSH tunnel. Your database connection string would<o:p></o:p> // use "localhost" for the hostname and 3316 for the port.<o:p></o:p> // We're not going to show the database coding here,<o:p></o:p> // because it can vary depending on the API you're using<o:p></o:p> // (ADO, ODBC, OLE DB, etc. )<o:p></o:p> <o:p> </o:p> // This is where your database code would go...<o:p></o:p> <o:p> </o:p> // When you're finished with the database connection, you may<o:p></o:p> // stop the background tunnel threads:<o:p></o:p> // Stop the background thread that accepts new connections:<o:p></o:p> success = sshTunnel.StopAccepting();<o:p></o:p> if (success != true) {<o:p></o:p> MessageBox.Show(sshTunnel.LastErrorText);<o:p></o:p> return;<o:p></o:p> }<o:p></o:p> <o:p> </o:p> // If any background tunnels are still in existence (and managed<o:p></o:p> // by a single SSH tunnel pool background thread), stop them...<o:p></o:p> int maxWaitMs;<o:p></o:p> maxWaitMs = 1000;<o:p></o:p> success = sshTunnel.StopAllTunnels(maxWaitMs);<o:p></o:p> if (success != true) {<o:p></o:p> MessageBox.Show(sshTunnel.LastErrorText);<o:p></o:p> return;<o:p></o:p> }<o:p></o:p> >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><o:p> </o:p>  Figure 1: Snapshot of the generated Web Quiz.<o:p></o:p> Just when you thought you'd never see another test again, your back in school, sharpening that #2 pencil and blowing away erasure particles. Well maybe not anymore thanks to the power of C# and .NET! You may still be taking tests, but you probably won't have to go out and buy a sharpener. This article describes how to create a web quiz from the information in a database. For the purposes of this article, I chose to use MS Access, but the code can be altered easy enough to use SqlServer, MySql, Oracle, or whatever your favorite provider happens to be. Below is the Database Schema for our Quiz: <o:p></o:p>  Figure 2: This Access Database was reverse engineered using WithClass 2000<o:p></o:p> The structure of the Database is fairly simple. The questions are stored in the Questions Table along with the answers. The Choices are stored in the Choicestable and are pointed to by the QuestionID key. The StatsTable holds the title and NumberOfQuestions. Finally, the Testers table holds the information for the people taking the test. (This table is not utilized in this example, but will be in part II of this article.) <o:p></o:p> The web page for the quiz is constructed on the fly using Microsoft WebForms, Tables, TableRows, and TableCells. Below is the code that reads the MSAccess data using ADO.NET and creates the table. Note that all of this is done in the PageLoad Event:<o:p></o:p> private void Page_Load(object sender, System.EventArgs e)<o:p></o:p> { <o:p></o:p> if (!IsPostBack)<o:p></o:p> {<o:p></o:p> // This is the Initial Page Load. Draw the Quiz<o:p></o:p> ReadQuizTitle();<o:p></o:p> ReadQuestionsIntoTable();<o:p></o:p> AddSubmitButton();<o:p></o:p> }<o:p></o:p> else<o:p></o:p> {<o:p></o:p> // The User has pressed the score button, calculate and publish the results<o:p></o:p> HttpRequest r;<o:p></o:p> r = this.Request;<o:p></o:p> bool AreAllAnswered = CalculateScore(r);<o:p></o:p> HttpResponse rs;<o:p></o:p> rs = this.Response;<o:p></o:p> if (AreAllAnswered == false)<o:p></o:p> {<o:p></o:p> rs.Write("You missed a few questions. Go back in your browser and answer them<P>");<o:p></o:p> return;<o:p></o:p> }<o:p></o:p> // Write the score<o:p></o:p> rs.Write("Your score is " + NumberCorrect.ToString() + " out of " + NumberOfQuestions.ToString() +<o:p></o:p> "<P>");<o:p></o:p> // Print out the corrected answers<o:p></o:p> for (int num = 0; num < NumberOfQuestions; num++)<o:p></o:p> {<o:p></o:p> if (WrongArray[num].Length > 0)<o:p></o:p> rs.Write(WrongArray[num]);<o:p></o:p> }<o:p></o:p> // Rank the User<o:p></o:p> rs.Write(GetRanking());<o:p></o:p> }<o:p></o:p> } <o:p></o:p> The first half of the if statement is handled when the page first loads. This part is responsible for drawing the quiz. The second half of the if statement is executed after the user presses the Score Button. This part of the if statement will score the test and output a page giving the score, the missed questions, and a ranking.<o:p></o:p> Unfortunately, you can't tell much from the code above. So let's delve into what's happening in the ReadQuestionsIntoTable Routine:<o:p></o:p> private void ReadQuestionsIntoTable()<o:p></o:p> {<o:p></o:p> // Fill the questions and choices tables in memory<o:p></o:p> DataSet ds1 = new DataSet("questionsds");<o:p></o:p> oleDbDataAdapter1.Fill(ds1, "Questions");<o:p></o:p> oleDbDataAdapter2.Fill(ds1, "Choices");<o:p></o:p> DataTable QuestionsTable = ds1.Tables["Questions"];<o:p></o:p> DataTable ChoicesTable = ds1.Tables["Choices"];<o:p></o:p> // create a data relation between the Questions and Choices Tables<o:p></o:p> // so we can cycle through the choices for each question<o:p></o:p> DataRelation QALink = new DataRelation("QuestionLink", QuestionsTable.Columns["QuestionID"],<o:p></o:p> ChoicesTable.Columns["QuestionID"]);<o:p></o:p> QuestionsTable.ChildRelations.Add(QALink);<o:p></o:p> NumberOfQuestions = 0;<o:p></o:p> // go through every row in the questions table<o:p></o:p> // and place each question in the Table Web Control<o:p></o:p> foreach (DataRow dr in QuestionsTable.Rows)<o:p></o:p> {<o:p></o:p> // create a row for the question and read it from the database<o:p></o:p> TableRow tr = new TableRow();<o:p></o:p> Table1.Rows.Add(tr);<o:p></o:p> TableCell aCell = new TableCell();<o:p></o:p> // get the text for the question and stick it in the cell<o:p></o:p> aCell.Text = dr["QuestionText"].ToString();<o:p></o:p> tr.Cells.Add(aCell);<o:p></o:p> AnswerArray[NumberOfQuestions] = dr["Answer"].ToString();<o:p></o:p> // create a row for the choices and read from the database<o:p></o:p> int count = 0;<o:p></o:p> // go through the child rows of the question table<o:p></o:p> // established by the DataRelation QALink and<o:p></o:p> // fill the choices for the table<o:p></o:p> foreach (DataRow choiceRow in dr.GetChildRows(QALink))<o:p></o:p> {<o:p></o:p> TableRow tr2 = new TableRow();<o:p></o:p> Table1.Rows.Add(tr2);<o:p></o:p> // create a cell for the choice<o:p></o:p> TableCell aCell3 = new TableCell();<o:p></o:p> aCell3.Width = 1000;<o:p></o:p> // align the choices on the left<o:p></o:p> aCell3.HorizontalAlign = HorizontalAlign.Left;<o:p></o:p> tr2.Cells.Add(aCell3);<o:p></o:p> <o:p></o:p> // create a radio button in the cell<o:p></o:p> RadioButton rb = new RadioButton();<o:p></o:p> // assign the radio button to Group + QuestionID<o:p></o:p> rb.GroupName = "Group" + choiceRow["QuestionID"].ToString();<o:p></o:p> <o:p></o:p> // Assign the choice to the radio button<o:p></o:p> rb.Text = choiceRow["ChoiceLetter"].ToString() + ". " + choiceRow["ChoiceText"].ToString();<o:p></o:p> // Assign the radio button id corresponding to the choice and question # <o:p></o:p> rb.ID = "Radio" + NumberOfQuestions.ToString() + Convert.ToChar(count + 65);<o:p></o:p> rb.Visible = true;<o:p></o:p> // add the radio button to the cell<o:p></o:p> aCell3.Controls.Add(rb);<o:p></o:p> count++;<o:p></o:p> }<o:p></o:p> // add a table row between each question<o:p></o:p> // as a spacer<o:p></o:p> TableRow spacer = new TableRow();<o:p></o:p> spacer.Height = 30;<o:p></o:p> TableCell spacerCell = new TableCell();<o:p></o:p> spacerCell.Height = 30;<o:p></o:p> spacer.Cells.Add(spacerCell);<o:p></o:p> Table1.Rows.Add(spacer); // add a spacer<o:p></o:p> // Increment the # of Questions<o:p></o:p> NumberOfQuestions++;<o:p></o:p> }<o:p></o:p> } <o:p></o:p> The code above uses ADO.NET to cycle through all the questions and choices and place them in the table on the WebForm. The program uses the Table, TableCell, TableRow WebControls to display the questions in a decent format. <o:p></o:p> After the user fills in the quiz, It's time to score the test. The results are compared against the QuestionTable's answers and then computed and printed during the PostBack( after the Score button is pressed). The program takes advantage of the nice features of the HttpRequest object to extract the test results. The HttpRequest has a Form property that contains all the Key-Value pair information in a nice hash table. The program goes through each of the Keys in the Request and hashes out the results in each Radio Group. The Value passed by the Request contains the testtakers answers:<o:p></o:p> private bool CalculateScore(HttpRequest r)<o:p></o:p> {<o:p></o:p> // initialize wrong answer array<o:p></o:p> WrongArray.Initialize();<o:p></o:p> // Load up statistic table to get Number of Questions<o:p></o:p> DataSet ds = new DataSet("StatsDS");<o:p></o:p> oleDbDataAdapter4.MissingSchemaAction = MissingSchemaAction.AddWithKey;<o:p></o:p> this.oleDbDataAdapter4.Fill(ds, "StatsTable");<o:p></o:p> DataTable StatsTable = ds.Tables["StatsTable"];<o:p></o:p> NumberOfQuestions = (int)StatsTable.Rows[0]["NumberOfQuestions"];<o:p></o:p> // Load up Questions Table to Get Answers to<o:p></o:p> // compare to testtaker<o:p></o:p> oleDbDataAdapter1.MissingSchemaAction = MissingSchemaAction.AddWithKey;<o:p></o:p> DataSet ds1 = new DataSet("questionsds");<o:p></o:p> oleDbDataAdapter1.Fill(ds1, "Questions");<o:p></o:p> DataTable QuestionTable = ds1.Tables["Questions"];<o:p></o:p> // Load up choices table to print out correct choices<o:p></o:p> DataSet ds2 = new DataSet("choicesDS");<o:p></o:p> oleDbDataAdapter2.MissingSchemaAction = MissingSchemaAction.AddWithKey;<o:p></o:p> oleDbDataAdapter2.Fill(ds2, "Choices");<o:p></o:p> DataTable ChoicesTable = ds2.Tables["Choices"];<o:p></o:p> // make sure all questions were answered by the tester<o:p></o:p> int numAnswered = CalcQuestionsAnsweredCount(r);<o:p></o:p> if (numAnswered != NumberOfQuestions)<o:p></o:p> {<o:p></o:p> return false;<o:p></o:p> }<o:p></o:p> NumberCorrect = 0;<o:p></o:p> NumberWrong = 0;<o:p></o:p> // initialize wrong answer array to empty string<o:p></o:p> for (int j = 0; j < NumberOfQuestions; j++)<o:p></o:p> {<o:p></o:p> WrongArray[j] = "";<o:p></o:p> }<o:p></o:p> // cycle through all the keys in the returned Http Request Object<o:p></o:p> for (int i = 0; i < r.Form.Keys.Count; i++)<o:p></o:p> {<o:p></o:p> string nextKey = r.Form.Keys[i];<o:p></o:p> // see if the key contains a radio button Group<o:p></o:p> if (nextKey.Substring(0, 5) == "Group")<o:p></o:p> {<o:p></o:p> // It contains a radiobutton, get the radiobutton ID from the hashed Value-Pair Collection<o:p></o:p> string radioAnswer = r.Form.Get(nextKey);<o:p></o:p> // extract the letter choice of the tester from the button ID<o:p></o:p> string radioAnswerLetter = radioAnswer[radioAnswer.Length - 1].ToString();<o:p></o:p> // extract the question number from the radio ID<o:p></o:p> string radioQuestionNumber = radioAnswer.Substring(5);<o:p></o:p> radioQuestionNumber = radioQuestionNumber.Substring(0, radioQuestionNumber.Length - 1);<o:p></o:p> int questionNumber = Convert.ToInt32(radioQuestionNumber, 10) + 1;<o:p></o:p> // now compare the testers answer to the answer in the database<o:p></o:p> DataRow dr = QuestionTable.Rows.Find(questionNumber);<o:p></o:p> if (radioAnswerLetter == dr["Answer"].ToString())<o:p></o:p> {<o:p></o:p> // tester got it right, increment the # correct<o:p></o:p> NumberCorrect++;<o:p></o:p> CorrectArray[questionNumber - 1] = true;<o:p></o:p> WrongArray[questionNumber - 1] = "";<o:p></o:p> }<o:p></o:p> else<o:p></o:p> {<o:p></o:p> // tester got it wrong, increment the # incorrect<o:p></o:p> CorrectArray[questionNumber - 1] = false;<o:p></o:p> // look up the correct answer<o:p></o:p> string correctAnswer = ChoicesTable.Rows.Find(dr["AnswerID"])["ChoiceText"].ToString();<o:p></o:p> // put the correct answer in the Wrong Answer Array.<o:p></o:p> WrongArray[questionNumber - 1] = "Question #" + questionNumber + " - <B>" + dr<o:p></o:p> ["Answer"].ToString() + "</B>. " + correctAnswer + "<BR>\n";<o:p></o:p> // increment the # of wrong answers<o:p></o:p> NumberWrong++;<o:p></o:p> }<o:p></o:p> }<o:p></o:p> }<o:p></o:p> return true;<o:p></o:p> }<o:p></o:p> That's all there is to it! In part 2 I plan on adding a web form that gets the testers anonymous ID so that the scores can be collected for each test taker and a test curve can be generated. <o:p></o:p> >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><o:p> </o:p> This article summarizes the new and updated features of ADO.NET 2.0, which ships with .NET Framework 2.0. In my following articles I will discuss these features in more details with sample examples.<o:p></o:p> Here is a list of new and updated additions to ADO.NET:<o:p></o:p> 1. Bulk Copy Operation<o:p></o:p> Bulk copying of data from a data source to another data source is a new feature added to ADO.NET 2.0. Bulk copy classes provides the fastest way to transfer set of data from once source to the other. Each ADO.NET data provider provides bulk copy classes. For example, in SQL .NET data provider, the bulk copy operation is handled by SqlBulkCopy class, which can read a DataSet, DataTable, DataReader, or XML objects. Read more about Bulk Copy here.<o:p></o:p> 2. Batch Update<o:p></o:p> Batch update can provide a huge improvement in the performance by making just one round trip to the server for multiple batch updates, instead of several trips if the database server supports the batch update feature. The UpdateBatchSize property provides the number of rows to be updated in a batch. This value can be set up to the limit of decimal.<o:p></o:p> 3. Data Paging<o:p></o:p> Now command object has a new execute method called ExecutePageReader. This method takes three parameters - CommandBehavior, startIndex, and pageSize. So if you want to get rows from 101 - 200, you can simply call this method with start index as 101 and page size as 100.<o:p></o:p> 4. Connection Details<o:p></o:p> Now you can get more details about a connection by setting Connection's StatisticsEnabled property to True. The Connection object provides two new methods - RetrieveStatistics and ResetStatistics. The RetrieveStatistics method returns a HashTable object filled with the information about the connection such as data transferred, user details, curser details, buffer information and transactions.<o:p></o:p> 5. DataSet.RemotingFormat Property<o:p></o:p> When DataSet.RemotingFormat is set to binary, the DataSet is serialized in binary format instead of XML tagged format, which improves the performance of serialization and deserialization operations significantly.<o:p></o:p> 6. DataTable's Load and Save Methods<o:p></o:p> In previous version of ADO.NET, only DataSet had Load and Save methods. The Load method can load data from objects such as XML into a DataSet object and Save method saves the data to a persistent media. Now DataTable also supports these two methods.<o:p></o:p> You can also load a DataReader object into a DataTable by using the Load method.<o:p></o:p> 7. New Data Controls<o:p></o:p> In Toolbox, you will see these new controls - DataGridView, DataConnector, and DataNavigator. See Figure 1. Now using these controls, you can provide navigation (paging) support to the data in data bound controls.<o:p></o:p> <o:p></o:p> Figure 1. Data bound controls.<o:p></o:p> 8. DbProvidersFactories Class<o:p></o:p> This class provides a list of available data providers on a machine. You can use this class and its members to find out the best suited data provider for your database when writing a database independent applications.<o:p></o:p> 9. Customized Data Provider<o:p></o:p> By providing the factory classes now ADO.NET extends its support to custom data provider. Now you don't have to write a data provider dependent code. You use the base classes of data provider and let the connection string does the trick for you.<o:p></o:p> 10. DataReader's New Execute Methods<o:p></o:p> Now command object supports more execute methods. Besides old ExecuteNonQuery, ExecuteReader, ExecuteScaler, and ExecuteXmlReader, the new execute methods are ExecutePageReader, ExecuteResultSet, and ExecuteRow. Figure 2 shows all of the execute methods supported by the command object in ADO.NET 2.0.<o:p></o:p>  <o:p></o:p> Figure 2. Command's Execute methods.<o:p></o:p> Summary<o:p></o:p> ADO.NET 2.0 provides many new and improved features for developers to improve the performance and reduce the code. In this article, I discussed top 10 features of ADO.NET 2.0. In my forthcoming articles, I will be discussing these features in more details.<o:p></o:p> >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><o:p> </o:p> DataAdapter and Database Connections when Connection Pooling<o:p></o:p> We've all been drilled on the cardinal rule of connection pooling:<o:p></o:p> "open connections as late as possible, and close connections as early as possible."<o:p></o:p> Normally when you see code using a DataAdapter like below:<o:p></o:p> <o:p></o:p> using (SqlConnection connection =<o:p></o:p> new SqlConnection("...")<o:p></o:p> {<o:p></o:p> SqlCommand command = connection.CreateCommand();<o:p></o:p> command.CommandText = "Select * from Products";<o:p></o:p> <o:p></o:p> SqlDataAdapter adapter = new SqlDataAdapter();<o:p></o:p> adapter.SelectCommand = command;<o:p></o:p> <o:p></o:p> DataSet dataset = new DataSet();<o:p></o:p> <o:p></o:p> adapter.Fill(dataset);<o:p></o:p> }<o:p></o:p> <o:p></o:p> the connection is not explicitly opened in code, because the DataAdapter will take care of this for you. And by having the DataAdapter take care of this for you, you are following the cardinal rule of connection pooling mentioned above.<o:p></o:p> Today while reading I came across some code that explicitly opened the connection as opposed to allowing the DataAdapter to take care of the work. I thought it might be a typo or some mistake at first glance:<o:p></o:p> <o:p></o:p> using (SqlConnection connection =<o:p></o:p> new SqlConnection("...")<o:p></o:p> {<o:p></o:p> SqlCommand sqlCat = connection.CreateCommand();<o:p></o:p> sqlCat.CommandText = "Select * from Categories";<o:p></o:p> <o:p> </o:p> SqlCommand sqlProd = connection.CreateCommand();<o:p></o:p> sqlProd.CommandText = "Select * from Products";<o:p></o:p> <o:p></o:p> SqlDataAdapter adapter = new SqlDataAdapter();<o:p></o:p> adapter.SelectCommand = sqlCat;<o:p></o:p> <o:p></o:p> DataSet dataset = new DataSet();<o:p></o:p> <o:p></o:p> connection.Open();<o:p></o:p> <o:p></o:p> adapter.Fill(dataset, "Categories);<o:p></o:p> <o:p></o:p> adapter.SelectCommand = sqlProd;<o:p></o:p> adapter.Fill(dataset, "Products");<o:p></o:p> <o:p></o:p> connection.Close();<o:p></o:p> }<o:p></o:p> <o:p></o:p> However, then I realized that in the case of multiple back-to-back Fill requests to the DataAdapter, it is more performant to explicitly open the connection in the beginning so that each Fill request on the DataAdapter does not open and close the database connection, resulting in the database connection being opened and closed several times.<o:p></o:p> Because as stated so eloquently in Pro ADO.NET 2.0 (Page 190)<o:p></o:p> Thus, the SqlDataAdapter always leaves the connection in the same state it took it as.<o:p></o:p> >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><o:p> </o:p> Database Connectivity problem C# Posted by BiranSubba on 7 Apr 2006 at 11:26 PM<o:p></o:p> Hi all, i writing a small account program but i am getting problem. here is my source code. where is wrong please Help me. There is litlebit confusion code there is academyac.inx file and there is someny data base file name and i am comparing text to real database name if its match then its access. ////////////////////////////////////////// private void SaveAndAddnew_Click(object sender, EventArgs e) { string ext = ".mdb"; string line = null; string flname = null; StreamReader r1 = File.OpenText("academyAC.inx"); while ((line = r1.ReadLine()) != null) { flname = line; } try { string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;User Id=admin;Password=computer;Data Source=data\\" + flname + ext; OleDbConnection myConn = new OleDbConnection(strConn); myConn.Open(); string strInsert = "INSERT INTO Students(Students_Name,Father_Name,DOA, "; strInsert += "DOA,Phone_No) VALUES ( "; strInsert += Student_Name.Text + ", '"; strInsert += Father_Name.Text + "', '"; strInsert += DOB.Text + "', "; strInsert += DOA.Text + ", "; strInsert += Phone_Number.Text + ")"; OleDbCommand inst = new OleDbCommand(strInsert,myConn); inst.ExecuteNonQuery(); myConn.Close(); } catch (Exception ed) { MessageBox.Show("Error in Saving"+ed.ToString() , "Error"); } }<o:p></o:p> >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><o:p> </o:p> Connecting to Database and accessing the records in C#<o:p></o:p> <o:p></o:p> Posted Date: 22 Mar 2004 <o:p></o:p> Resource Type: Articles <o:p></o:p> Category: .NET Framework<o:p></o:p> Author: Lalitha Maheswaran<o:p></o:p> Member Level: Bronze <o:p></o:p> Rating: <o:p></o:p> Points: 5<o:p></o:p> <o:p> </o:p> <o:p></o:p> using System; using System.Data.SqlClient; using System.Data; namespace AdapterSamp { class DataAdapterSamp { static void Main(string[] args) { //Set the connection string for the database string connectionstring="Initial Catalog=NorthWind; Data Source=buildees;user id=sa;password=BaanIT00;"; //Create Connection and open it System.Data.SqlClient.SqlConnection conn = new SqlConnection(connectionstring); conn.Open (); //Create the command object SqlCommand comm = new SqlCommand(); comm.Connection = conn; comm.CommandText = "Select employeeid, lastname, firstname, city from Employees"; //Create an adapter object SqlDataAdapter adapter = new SqlDataAdapter("Select employeeid, lastname, firstname, city from Employees", connectionstring); //Create a dataset object and fill the values from Employees table DataSet oDataSet = new DataSet(); adapter.Fill(oDataSet, "Employees"); //Print the records in XML format Console.WriteLine(oDataSet.GetXml()); /************ Add a new record to the table ***************/ //Create a new row DataRow oDataRow; oDataRow = oDataSet.Tables["Employees"].NewRow(); //Set the filed values oDataRow["FirstName"] = "Lalitha"; oDataRow["LastName"] = "Maheswaran"; oDataSet.Tables["Employees"].Rows.Add(oDataRow); //Create the insert command object SqlCommand oInsertCmd = new SqlCommand(); oInsertCmd.Connection = conn; oInsertCmd.CommandText = "Insert into employees( FirstName, LastName) values (@FirstName, @LastName)"; //Declare the variables in the command oInsertCmd.Parameters.Add("@FirstName", System.Data.SqlDbType.NVarChar , 10, "FirstName"); oInsertCmd.Parameters.Add("@LastName", System.Data.SqlDbType.NVarChar, 20, "LastName"); //Set the insert command and update the table adapter.InsertCommand = oInsertCmd; adapter.Update(oDataSet, "Employees"); //Create a datatable to display the records in the table //Note: Refill the dataset so that any changes made to the tables are got adapter.Fill(oDataSet, "Employees"); DataTable oTable = oDataSet.Tables["Employees"]; foreach (DataRow Row in oTable.Rows) { Console.WriteLine(Row[0] + " " + Row[1]); } } } } >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><o:p> </o:p> database = ini.IniReadValue("Info","Database");<o:p></o:p> <o:p></o:p> connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;"; <o:p></o:p> connectionString += @"Data Source=" + database + ";"; <o:p></o:p> <o:p> </o:p> Connection.ConnectionString = connectionString;<o:p></o:p> Connection.Open();<o:p></o:p> >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><o:p> </o:p> OdbcConnection Example in C#<o:p></o:p> by Sam Allen - Updated August 11, 2009<o:p></o:p> <o:p></o:p> Problem. You need to use OdbcConnection in your ASP.NET website, written in the C# programming language. Fix problems with your syntax of OdbcConnection, and also connection strings or parameters to the SQL query. Solution. Here we see ways to use this database connection effectively.<o:p></o:p> 1. Using OdbcConnection<o:p></o:p> Here we explore the OdbcConnection objects in the C# language and .NET Framework. After I introduce the basics, I will break down other details, such as how to add the code and actually use the OdbcConnection.<o:p></o:p> The acronym's definition. The acronym Odbc stands for open database connectivity. The "open" refers to the platform status, not the connection state. It took me a long time to figure out what all those letters stand for.<o:p></o:p> When to use Odbc. Odbc connections work with more than one SQL server, so you can use Odbc in many different scenarios. The scenario I will outline in this article is using Odbc connections in an ASP.NET application that accesses a MySQL database.<o:p></o:p> Performance of the connection. Because of the compiled nature of C#, and the performance of MySQL, the applications you build with this combination have the potential to be very fast and responsive.<o:p></o:p> 2. Connection string<o:p></o:p> Usually, if you are using a third-party web host, they will provide you with a sample connection string. This is useful, but you need to be careful. There are some tricks. On my web apps, I have connection strings that look like the next block.<o:p></o:p> DRIVER={MySQL ODBC 3.51 Driver}; SERVER=p50mysq5555.secureserver.net; PORT=3306;<o:p></o:p> DATABASE=OkieData; USER=SamAllen; PASSWORD=CutiePie; OPTION=0;<o:p></o:p> <o:p> </o:p> Connection string: Driver<o:p></o:p> Tip: Specify the driver as MySQL ODBC 3.51 Driver in curly brackets.<o:p></o:p> <o:p> </o:p> Connection string: Quotes around driver section<o:p></o:p> Tip: Don't put quotes around your driver.<o:p></o:p> <o:p> </o:p> Connection string: Database server<o:p></o:p> Tip: Put your server URL in there.<o:p></o:p> You will have to get this from your web host.<o:p></o:p> <o:p> </o:p> Connection string: Attribute values<o:p></o:p> Tip: The database's name is OkieData.<o:p></o:p> The user name is SamAllen.<o:p></o:p> The password is CutiePie.<o:p></o:p> <o:p> </o:p> Connection string: All quotes<o:p></o:p> Tip: If you read nothing else here read this:<o:p></o:p> You can't put quotes around any of the values.<o:p></o:p> My experience. I spent a hectic half-hour or more trying to log in to the MySQL database with this configuration with quotes around my password. It didn't work. So don't use those quotes.<o:p></o:p> 3. Using Web.config<o:p></o:p> It is best practice, although not required, to put your connection string in Web.config in your ASP.NET project. Here are the lines I used. You will have to find the appropriate blocks in the XML config file yourself. In this next XML element example, I use the connection string name of WhateverName.<o:p></o:p> <o:p></o:p> <o:p> </o:p> <connectionstrings><o:p></o:p> <add name="WhateverName" connectionString="Exact string shown above"/><o:p></o:p> </connectionstrings><o:p></o:p> 4. Using statements<o:p></o:p> Your page will have a code-behind file. At the top are your using statements. Add these two using statements at the top. The first adds the database stuff, and the second allows you to access your connection string.<o:p></o:p> using System.Data.Odbc;<o:p></o:p> using System.Web.Configuration;<o:p></o:p> 5. How do I make a OdbcConnection?<o:p></o:p> By combining it with using. The next code block gets our special connection string from the Web.config file. The contents of that string are shown near the start of this article. In C#, you want to use the "using" blocks.<o:p></o:p> // Try to connect to the database based on our stored connection string.<o:p></o:p> string conString = WebConfigurationManager.<o:p></o:p> ConnectionStrings["WhateverName"].ConnectionString;<o:p></o:p> using (OdbcConnection con = new OdbcConnection(conString))<o:p></o:p> {<o:p></o:p> con.Open();<o:p></o:p> // We are now connected. Now we can use OdbcCommand objects<o:p></o:p> // to actually accomplish things.<o:p></o:p> }<o:p></o:p> Description of the example. The OdbcConnection 'con' is a new connection to the database. These are automatically pooled and shared. That code was written by Microsoft programmers very skilled in these things. So I won't try to improve upon that work.<o:p></o:p> 6. Read data from the MySQL database<o:p></o:p> In this next code block, I will declare a new OdbcCommand object, then add a parameter to the command object, and then read in data from the database. Note the question mark in the command text.<o:p></o:p> using (OdbcCommand com = new OdbcCommand(<o:p></o:p> "SELECT ColumnWord FROM OkieTable WHERE MagicKey = ?", con))<o:p></o:p> {<o:p></o:p> com.Parameters.AddWithValue("@var", paramWord);<o:p></o:p> <o:p> </o:p> using (OdbcDataReader reader = com.ExecuteReader())<o:p></o:p> {<o:p></o:p> while (reader.Read())<o:p></o:p> {<o:p></o:p> string word = reader.GetString(0);<o:p></o:p> // Word is from the database. Do something with it.<o:p></o:p> }<o:p></o:p> }<o:p></o:p> }<o:p></o:p> Description of the example code. The OdbcCommand takes the connection object as the second parameter. The paramWord string is added to the command. It is named @var but that doesn't matter. The parameter is added in the first ?. It looks through the table and find the rows where the MagicKey column is equal to paramWord.<o:p></o:p> 7. Summary<o:p></o:p> Here we saw ways you can fix your bugs with OdbcConnection. This can be applied to any ADO.NET provider, from SqlConnection objects to SqlCeConnections and SQLiteConnections. Use the material here as a launching point for learning more about ADO.NET.<o:p></o:p> >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><o:p> </o:p> Installing NHibernate<o:p></o:p> 1. Download NHibernate (http://sourceforge.net/projects/nhibernate/).<o:p></o:p> 2. Start a new project.<o:p></o:p> 3. Add reference to the NHibernate.dll by browsing the folders.<o:p></o:p> 4. Copy NHibernate.dll and NHibernate.xml to the Bin folder of the project. (This step is optional if the files are copied automatically)<o:p></o:p> 5. Now NHibernate is installed for the project.<o:p></o:p> Getting Started<o:p></o:p> In our example we have created a web application with the following database design.<o:p></o:p>  We will incorporate the basic database operations like Insert, Update and Load. Before that we need to take care of the following things to be added to our project.<o:p></o:p> 1. Add NHibernate Schema definition in Web.config File<o:p></o:p> 2. <Table Name>.hbm.xml File<o:p></o:p> 3. <Table Name>.cs File<o:p></o:p> 4. Insert, Update and Load Operations<o:p></o:p> 1. Adding NHibernate Schema definition in Web.config<o:p></o:p> Web.config is the basic configuration file for a web application. If we are not developing the web application the configuration file name should be App.config. But the concept and the content will remain same for the configuration files.<o:p></o:p> <o:p></o:p> <configSections><o:p></o:p> <section name="hibernate-configuration" type="NHibernate.Cfg.ConfigurationSectionHandler, NHibernate" /><o:p></o:p> </configSections><o:p></o:p> <o:p></o:p> <hibernate-configuration xmlns="urn:nhibernate-configuration-2.2" ><o:p></o:p> <session-factory><o:p></o:p> <property name="dialect"><o:p></o:p> NHibernate.Dialect.MsSql2005Dialect<o:p></o:p> </property><o:p></o:p> <property name="connection.provider"><o:p></o:p> NHibernate.Connection.DriverConnectionProvider<o:p></o:p> </property><o:p></o:p> <property name="connection.driver_class"><o:p></o:p> NHibernate.Driver.SqlClientDriver<o:p></o:p> </property><o:p></o:p> <property name="connection.connection_string"><o:p></o:p> Server=C849USS\SQLEXPRESS2K5;<o:p></o:p> Database=ReferenceDB;<o:p></o:p> Integrated Security=True;<o:p></o:p> </property><o:p></o:p> </session-factory> </hibernate-configuration><o:p></o:p> 2. Mapping the Business Model<o:p></o:p> Mapping is the heart of what NHibernate does, and it presents the greatest stumbling blocks for beginners. Once we have discussed mapping, we will turn to the code required to configure and use NHibernate.<o:p></o:p> Mapping simply specifies which tables in the database go with which classes in the business model. Note that we will refer to the table to which a particular class is mapped as the "mapping table" for that class.<o:p></o:p> Mapping can be done by separate XML files, or by attributes on classes, properties, and member variables. If files are used for mapping they can be incorporated in the project in any of several ways. To keep things simple, we are going to show one way of mapping i.e. map to XML files that are compiled as resources of an assembly.<o:p></o:p> You can map as many classes as you want in a mapping file, but it is conventional to create a separate mapping file for each class. This practice keeps the mapping files short and easy to read. We will follow our business model as described in figure.<o:p></o:p> Employees.hbm.xml<o:p></o:p> <?xml version="1.0" encoding="utf-8" ?><o:p></o:p> <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"><o:p></o:p> <class name="NHibernateWebSample.Employees, NHibernateWebSample" table="Employees"><o:p></o:p> <id name="EmpId" column="EmpId" type="Int32"><o:p></o:p> <generator class="identity" /><o:p></o:p> </id><o:p></o:p> <property name="FirstName" column="FirstName" type="String" length="50"/><o:p></o:p> <property name="SecondName" column="SecondName" type="String" length="50"/><o:p></o:p> <property name="DepId" column="DepId" type="String" length="10"/><o:p></o:p> <one-to-one name="empInfo" access="field"/><o:p></o:p> </class><o:p></o:p> <class name="NHibernateWebSample.EmployeeInfos, NHibernateWebSample" table="EmployeeInfos"><o:p></o:p> <id name="EmpId" column="EmpId" type="Int32"><o:p></o:p> <generator class="assigned" /><o:p></o:p> </id><o:p></o:p> <property name="EmailId" column="EmailId" type="String" length="50"/><o:p></o:p> <property name="Address" column="Address" type="String" length="50"/><o:p></o:p> <property name="DOJ" column="DOJ" type="DateTime"/><o:p></o:p> </class><o:p></o:p> </hibernate-mapping><o:p></o:p> EmployeeInfos.hbm.xml<o:p></o:p> <?xml version="1.0" encoding="utf-8" ?><o:p></o:p> <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"><o:p></o:p> <class name="NHibernateWebSample.EmployeeInfos, NHibernateWebSample" table="EmployeeInfos"><o:p></o:p> <id name="EmpId" column="EmpId" type="Int32"><o:p></o:p> <generator class="assigned" /><o:p></o:p> </id><o:p></o:p> <property name="EmailId" column="EmailId" type="String" length="50"/><o:p></o:p> <property name="Address" column="Address" type="String" length="50"/><o:p></o:p> <property name="DOJ" column="DOJ" type="DateTime"/><o:p></o:p> </class><o:p></o:p> </hibernate-mapping><o:p></o:p> Departments.hbm.xml<o:p></o:p> <?xml version="1.0" encoding="utf-8" ?><o:p></o:p> <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"><o:p></o:p> <class name="NHibernateWebSample.Departments, NHibernateWebSample" table="Departments"><o:p></o:p> <id name="DepId" column="DepId" type="String"><o:p></o:p> <generator class="assigned" /><o:p></o:p> </id><o:p></o:p> <property name="DepName" column="DepName" type="String" length="30"/><o:p></o:p> </class><o:p></o:p> </hibernate-mapping><o:p></o:p> The <class> Tag<o:p></o:p> The next tag identifies the class we are mapping in this file:  <class name="NHibernateWebSample.Employees, NHibernateWebSample" table=" Employees" lazy="false"><o:p></o:p> The <class> tag's attributes specifies the class being mapped, and its mapping table in the database:<o:p></o:p> The name attribute specifies the class being mapped<o:p></o:p> The table attribute specifies the mapping table for that class<o:p></o:p> The lazy attribute tells NHibernate not to use 'lazy loading' for this class<o:p></o:p> 'Lazy loading' tells NHibernate not to load an object from the database until the application needs to access its data. That approach helps reduce the memory footprint of a business model, and it can improve performance. To keep things simple, we aren't going to use lazy loading in this application. However, you should learn its ins and outs as soon as possible after you get up and running with NHibernate.<o:p></o:p> The <id> Tag<o:p></o:p> Once we have identified the class being identified and its mapping table, we need to specify the identity property of the class and its corresponding identity column in the mapping table. Note that when we set up the database, we specified the EmpId field as the primary key of the database. In the column's IdentitySpecification property, we specified that the column was the identity column that it should initialize at 1 and increment by the same value:<o:p></o:p>  So, what we need to do is this:<o:p></o:p> Specify the identity property in the Employees class;<o:p></o:p> Specify the record identity column in the Employee table; and<o:p></o:p> Tell NHibernate to let SQL Server set the value of the EmpId column in the Employees table.<o:p></o:p> The identity specification is set by a combination of attributes and enclosed tags:<o:p></o:p> The <id> tag's name attribute specifies the identity property in the Employees class. In this case, it is the Id property.<o:p></o:p> The <column> tag's name attribute specifies the record identity column in the Employees table. In this case, it's the EmpId column. <o:p></o:p> The <generator> tag's class attribute specifies that record identity values will be generated natively by SQL Server.<o:p></o:p> The <property> Tag<o:p></o:p> Once we have mapped the identity property for the class, we can begin mapping other properties. The Employees class has simple properties, FirstName, SecondName, and DepId. We want to map it to the respective columns of the Employees table. Since the property and column names are the same, our mapping is very simple.<o:p></o:p> <property name="FirstName" column="FirstName" type="String" length="50"/> <property name="SecondName" column="SecondName" type="String" length="50"/> â€¦<o:p></o:p> 3. Adding Tables to project as class files<o:p></o:p> This is a simple cs file which contains the properties of the respective table. In case of any relationships in between the tables we can add some additional properties.<o:p></o:p> Employees.cs<o:p></o:p> namespace NHibernateWebSample<o:p></o:p> {<o:p></o:p> public class Employees<o:p></o:p> {<o:p></o:p> private int _EmpId;<o:p></o:p> private string _FirstName;<o:p></o:p> private string _SecondName;<o:p></o:p> private string _DepId;<o:p></o:p> private EmployeeInfos empInfo = new EmployeeInfos();<o:p></o:p> <o:p></o:p> public virtual int EmpId<o:p></o:p> {<o:p></o:p> get { return _EmpId; }<o:p></o:p> set<o:p></o:p> {<o:p></o:p> _EmpId = value;<o:p></o:p> empInfo.EmpId = value;<o:p></o:p> }<o:p></o:p> }<o:p></o:p> public virtual string FirstName<o:p></o:p> {<o:p></o:p> get { return _FirstName; }<o:p></o:p> set { _FirstName = value; }<o:p></o:p> }<o:p></o:p> public virtual string SecondName<o:p></o:p> {<o:p></o:p> get { return _SecondName; }<o:p></o:p> set { _SecondName = value; }<o:p></o:p> }<o:p></o:p> <o:p></o:p> public virtual string DepId<o:p></o:p> {<o:p></o:p> get { return _DepId; }<o:p></o:p> set { _DepId = value; }<o:p></o:p> }<o:p></o:p> <o:p></o:p> //Properties of EmployeeInfos table<o:p></o:p> public virtual string EmailId<o:p></o:p> {<o:p></o:p> get { return empInfo.EmailId; }<o:p></o:p> set { empInfo.EmailId = value; }<o:p></o:p> }<o:p></o:p> public virtual string Address<o:p></o:p> {<o:p></o:p> get { return empInfo.Address; }<o:p></o:p> set { empInfo.Address = value; }<o:p></o:p> }<o:p></o:p> public virtual DateTime DOJ<o:p></o:p> {<o:p></o:p> get { return empInfo.DOJ; }<o:p></o:p> set { empInfo.DOJ = value; }<o:p></o:p> }<o:p></o:p> }<o:p></o:p> }<o:p></o:p> EmployeeInfos.cs<o:p></o:p> namespace NHibernateWebSample<o:p></o:p> {<o:p></o:p> public class EmployeeInfos<o:p></o:p> {<o:p></o:p> private int _EmpId;<o:p></o:p> private string _EmailId;<o:p></o:p> private string _Address;<o:p></o:p> private DateTime _DOJ;<o:p></o:p> <o:p></o:p> public virtual int EmpId<o:p></o:p> {<o:p></o:p> get { return _EmpId; }<o:p></o:p> set { _EmpId = value; }<o:p></o:p> }<o:p></o:p> public virtual string EmailId<o:p></o:p> {<o:p></o:p> get { return _EmailId; }<o:p></o:p> set { _EmailId = value; }<o:p></o:p> }<o:p></o:p> public virtual string Address<o:p></o:p> {<o:p></o:p> get { return _Address; }<o:p></o:p> set { _Address = value; }<o:p></o:p> }<o:p></o:p> public virtual DateTime DOJ<o:p></o:p> {<o:p></o:p> get { return _DOJ; }<o:p></o:p> set { _DOJ = value; }<o:p></o:p> }<o:p></o:p> }<o:p></o:p> }<o:p></o:p> Departments.cs<o:p></o:p> namespace NHibernateWebSample<o:p></o:p> {<o:p></o:p> public class Departments<o:p></o:p> {<o:p></o:p> private string _DepId;<o:p></o:p> private string _DepName;<o:p></o:p> <o:p></o:p> public virtual string DepId<o:p></o:p> {<o:p></o:p> get { return _DepId; }<o:p></o:p> set { _DepId = value; }<o:p></o:p> }<o:p></o:p> public virtual string DepName<o:p></o:p> {<o:p></o:p> get { return _DepName; }<o:p></o:p> set { _DepName = value; }<o:p></o:p> }<o:p></o:p> }<o:p></o:p> }<o:p></o:p> 4. Insert, Update and Load Operations<o:p></o:p> Our Web Application looks like the following.<o:p></o:p>  For Insert Operation<o:p></o:p>  The Insert operation takes three arguments to be updated, such as FirstName, SecondName and DepId. As soon as the data get inserted into Employees table; it fires a trigger for insert into EmployeeInfos table.<o:p></o:p> SQL Trigger for Insert into EmployeeInfos<o:p></o:p> USE [ReferenceDB]<o:p></o:p> GO<o:p></o:p> SET ANSI_NULLS ON<o:p></o:p> GO<o:p></o:p> SET QUOTED_IDENTIFIER ON<o:p></o:p> GO<o:p></o:p> CREATE TRIGGER [dbo].[Trigger_AddEmployee]<o:p></o:p> ON [dbo].[Employees]<o:p></o:p> AFTER INSERT<o:p></o:p> AS<o:p></o:p> BEGIN<o:p></o:p> Declare @tempEmpId int;<o:p></o:p> Select @tempEmpId=@@IDENTITY;<o:p></o:p> SET NOCOUNT ON;<o:p></o:p> INSERT INTO EmployeeInfos values(@tempEmpId,'Not Set', 'Not Set', GetDate())<o:p></o:p> END<o:p></o:p> Inserting data into Employees and EmployeeInfos<o:p></o:p> #region Insert<o:p></o:p> protected void btnInsert_Click(object sender, EventArgs e)<o:p></o:p> {<o:p></o:p> NHibernate.Cfg.Configuration cfg = new NHibernate.Cfg.Configuration();<o:p></o:p> cfg.AddAssembly("NHibernateWebSample");<o:p></o:p> <o:p></o:p> ISessionFactory factory = cfg.BuildSessionFactory();<o:p></o:p> ISession session = factory.OpenSession();<o:p></o:p> ITransaction transaction = session.BeginTransaction();<o:p></o:p> <o:p></o:p> Employees newUser = new Employees();<o:p></o:p> <o:p></o:p> newUser.FirstName = txtFirstName.Text;<o:p></o:p> newUser.SecondName= txtLastName.Text;<o:p></o:p> newUser.DepId = ddlDeptId.SelectedValue.ToString();<o:p></o:p> <o:p></o:p> // Tell NHibernate that this object should be saved<o:p></o:p> session.Save(newUser);<o:p></o:p> <o:p></o:p> // commit all of the changes to the DB and close the ISession<o:p></o:p> transaction.Commit();<o:p></o:p> // Closing the session<o:p></o:p> session.Close();<o:p></o:p> }<o:p></o:p> #endregion<o:p></o:p> For Update Operation<o:p></o:p>  The update operation includes two basic operations, such as adding EmpIds to dropdownlist and then updates the data.<o:p></o:p> Loading EmpIds to DropDownList<o:p></o:p> #region Loding EmpId and DepId into DropDownList<o:p></o:p> protected void Page_Load(object sender, EventArgs e)<o:p></o:p> {<o:p></o:p> if (!IsPostBack)<o:p></o:p> {<o:p></o:p> NHibernate.Cfg.Configuration cfg = new NHibernate.Cfg.Configuration();<o:p></o:p> cfg.AddAssembly("NHibernateWebSample");<o:p></o:p> <o:p></o:p> ISessionFactory factory = cfg.BuildSessionFactory();<o:p></o:p> ISession session = factory.OpenSession();<o:p></o:p> IList empIds = session.CreateCriteria(typeof(Employees)).List();<o:p></o:p> <o:p></o:p> ddlEmpId.DataSource = empIds;<o:p></o:p> ddlEmpId.DataTextField = "EmpId";<o:p></o:p> ddlEmpId.DataValueField = "EmpId";<o:p></o:p> ddlEmpId.DataBind();<o:p></o:p> <o:p></o:p> session.Close();<o:p></o:p> }<o:p></o:p> }<o:p></o:p> #endregion<o:p></o:p> Updating the data<o:p></o:p> #region Updating the Data for two Tables(Employees, EmployeeInfos)<o:p></o:p> protected void ddlEmpId_SelectedIndexChanged(object sender, EventArgs e)<o:p></o:p> {<o:p></o:p> string test = ddlEmpId.SelectedValue.ToString();<o:p></o:p> NHibernate.Cfg.Configuration cfg = new NHibernate.Cfg.Configuration();<o:p></o:p> cfg.AddAssembly("NHibernateWebSample");<o:p></o:p> <o:p></o:p> ISessionFactory factory = cfg.BuildSessionFactory();<o:p></o:p> ISession session = factory.OpenSession();<o:p></o:p> <o:p></o:p> session = factory.OpenSession();<o:p></o:p> Employees dataEmpId = (Employees)session.Load(typeof(Employees), Convert.ToInt32(test));<o:p></o:p> EmployeeInfos dataEmpIdInfo = (EmployeeInfos)session.Load(typeof(EmployeeInfos), Convert.ToInt32(test));<o:p></o:p> <o:p></o:p> txtFirstUpdate.Text = dataEmpId.FirstName.ToString();<o:p></o:p> txtLastUpdate.Text = dataEmpId.SecondName.ToString();<o:p></o:p> txtUpdateEmail.Text = dataEmpIdInfo.EmailId.ToString();<o:p></o:p> txtUpdateAddress.Text = dataEmpIdInfo.Address.ToString();<o:p></o:p> txtUpdateDOJ.Text = dataEmpIdInfo.DOJ.ToString();<o:p></o:p> }<o:p></o:p> <o:p></o:p> protected void btnUpdate_Click(object sender, EventArgs e)<o:p></o:p> {<o:p></o:p> string test = ddlEmpId.SelectedValue.ToString();<o:p></o:p> NHibernate.Cfg.Configuration cfg = new NHibernate.Cfg.Configuration();<o:p></o:p> cfg.AddAssembly("NHibernateWebSample");<o:p></o:p> <o:p></o:p> ISessionFactory factory = cfg.BuildSessionFactory();<o:p></o:p> ISession session = factory.OpenSession();<o:p></o:p> <o:p></o:p> // set property<o:p></o:p> Employees dataEmpId = (Employees)session.Load(typeof(Employees), Convert.ToInt32(test));<o:p></o:p> EmployeeInfos dataEmpIdInfo = (EmployeeInfos)session.Load(typeof(EmployeeInfos), Convert.ToInt32(test));<o:p></o:p> dataEmpId.FirstName = txtFirstUpdate.Text;<o:p></o:p> dataEmpId.SecondName = txtLastUpdate.Text;<o:p></o:p> <o:p></o:p> dataEmpIdInfo.EmailId = txtUpdateEmail.Text;<o:p></o:p> dataEmpIdInfo.Address = txtUpdateAddress.Text;<o:p></o:p> dataEmpIdInfo.DOJ = Convert.ToDateTime(txtUpdateDOJ.Text);<o:p></o:p> <o:p></o:p> // flush the changes from the Session to the Database<o:p></o:p> session.Flush();<o:p></o:p> }<o:p></o:p> #endregion<o:p></o:p> For Update Operation<o:p></o:p>  This is a simple operation where the gridview binds with the data source.<o:p></o:p> #region Loading Data into Gridview<o:p></o:p> protected void btnLoad_Click(object sender, EventArgs e)<o:p></o:p> {<o:p></o:p> NHibernate.Cfg.Configuration cfg = new NHibernate.Cfg.Configuration();<o:p></o:p> cfg.AddAssembly("NHibernateWebSample");<o:p></o:p> ISessionFactory factory = cfg.BuildSessionFactory();<o:p></o:p> ISession session = factory.OpenSession();<o:p></o:p> IList dataEmployee = session.CreateCriteria(typeof(Employees)).List();<o:p></o:p> grdEmployee.DataSource = dataEmployee;<o:p></o:p> grdEmployee.DataBind();<o:p></o:p> session.Close();<o:p></o:p> }<o:p></o:p> #endregion<o:p></o:p> >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Hello All, I can't get my ASP.NET pages to connect to an access database. I have no problem using ASP.NET C# to do anything else, but just can't get a connection. Can anyone tell me where I can going wrong? Here is the code for my page. olly3.aspx.cs, called by olly3.aspx THANKS! using System; using System.Data; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; public partial class olly : System.Web.UI.Page { void Page_Load(object sender, EventArgs e) { OleDbConnection conn = new OleDbConnection(); conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; DataSource=premsql2e.brinkster.com\ollywells\database\db2.mdb"; try { conn.Open(); conn.Close(); return true; } catch { return false; } } } <o:p></o:p> I tried this too, but can't get anywhere. I have look through all the forums and can get a VB connection in ASP to the database, but can't get it right in C# anyone know better? using System; using System.Data; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; public partial class olly : System.Web.UI.Page { void Page_Load(object sender, EventArgs e) { OleDbConnection conn = new OleDbConnection(); conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; DataSource="C:\sites\single33\ollywells\database\db2.mdb""; try { conn.Open(); conn.Close(); return true; } catch { return false; } } }<o:p></o:p> Hi, well you have to understand the folder structure of your application. when you get the error, in that page there you will find the path of your Default.aspx. for the educational package they put your application folder under 2/3 layers of subfolder, so you have to add all of them. here is my connection string: string conString = "c:/sites/content/c/z/i/czium/db/test.mdb"; conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + conString +"'"; as you can see, czium is my application folder, i had to add everything else to work it properly. goodluck!<o:p></o:p> >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><o:p> </o:p> <o:p> </o:p> .NET Framework Class Library<o:p></o:p> SqlConnection Class<o:p></o:p> Represents an open connection to a SQL Server database. This class cannot be inherited.<o:p></o:p> For a list of all members of this type, see SqlConnection Members.<o:p></o:p> System.Object System.MarshalByRefObject System.ComponentModel.Component System.Data.SqlClient.SqlConnection<o:p></o:p> [Visual Basic]<o:p></o:p> NotInheritable Public Class SqlConnection<o:p></o:p> Inherits Component<o:p></o:p> Implements IDbConnection, ICloneable<o:p></o:p> [C#]<o:p></o:p> public sealed class SqlConnection : Component, IDbConnection,<o:p></o:p> ICloneable<o:p></o:p> [C++]<o:p></o:p> public __gc __sealed class SqlConnection : public Component,<o:p></o:p> IDbConnection, ICloneable<o:p></o:p> [JScript]<o:p></o:p> public class SqlConnection extends Component implements<o:p></o:p> IDbConnection, ICloneable<o:p></o:p> Thread Safety<o:p></o:p> Any public static (Shared in Visual Basic) members of this type are thread safe. Any instance members are not guaranteed to be thread safe.<o:p></o:p> Remarks<o:p></o:p> A SqlConnection object represents a unique session to a SQL Server data source. In the case of a client/server database system, it is equivalent to a network connection to the server. SqlConnection is used in conjunction with SqlDataAdapter and SqlCommand to increase performance when connecting to a Microsoft SQL Server database. For all third-party SQL server products, as well as other OLE DB-supported data sources, use OleDbConnection.<o:p></o:p> When you create an instance of SqlConnection, all properties are set to their initial values. For a list of these values, see the SqlConnection constructor.<o:p></o:p> If the SqlConnection goes out of scope, it is not closed. Therefore, you must explicitly close the connection by calling Close or Dispose.<o:p></o:p> Note To deploy high-performance applications, you need to use connection pooling. When you use the .NET Framework Data Provider for SQL Server, you do not need to enable connection pooling because the provider manages this automatically, although you can modify some settings. For more information about using connection pooling with the .NET Framework Data Provider for SQL Server, see Connection Pooling for the .NET Framework Data Provider for SQL Server.<o:p></o:p> If a SqlException is generated by the method executing a SqlCommand, the SqlConnection remains open when the severity level is 19 or less. When the severity level is 20 or greater, the server usually closes the SqlConnection. However, the user can reopen the connection and continue.<o:p></o:p> An application that creates an instance of the SqlConnection object can require all direct and indirect callers to have adequate permission to the code by setting declarative or imperative security demands. SqlConnection makes security demands using the SqlClientPermission object. Users can verify that their code has adequate permissions by using the SqlClientPermissionAttribute object. Users and administrators can also use the Code Access Security Policy Tool (Caspol.exe) to modify security policy at the machine, user, and enterprise levels. For more information, see Securing Applications.<o:p></o:p> Note If you are using Microsoft .NET Framework version 1.0, the FullTrust named permission set is required to connect to SQL Server using Open. This requirement does not apply if you are using .NET Framework version 1.1. For more information, see Requesting Permissions and Named Permission Sets.<o:p></o:p> For more information about handling warning and informational messages from the server, see Working with Connection Events.<o:p></o:p> Example<o:p></o:p> [Visual Basic, C#, C++] The following example creates a SqlCommand and a SqlConnection. The SqlConnection is opened and set as the Connection for theSqlCommand. The example then calls ExecuteNonQuery, and closes the connection. To accomplish this, the ExecuteNonQuery is passed a connection string and a query string that is a Transact-SQL INSERT statement.<o:p></o:p> [Visual Basic] <o:p></o:p> Public Sub InsertRow(myConnectionString As String)<o:p></o:p> ' If the connection string is null, use a default.<o:p></o:p> If myConnectionString = "" Then<o:p></o:p> myConnectionString = "Initial Catalog=Northwind;Data Source=localhost;Integrated Security=SSPI;"<o:p></o:p> End If<o:p></o:p> Dim myConnection As New SqlConnection(myConnectionString)<o:p></o:p> Dim myInsertQuery As String = "INSERT INTO Customers (CustomerID, CompanyName) Values('NWIND', 'Northwind Traders')"<o:p></o:p> Dim myCommand As New SqlCommand(myInsertQuery)<o:p></o:p> myCommand.Connection = myConnection<o:p></o:p> myConnection.Open()<o:p></o:p> myCommand.ExecuteNonQuery()<o:p></o:p> myCommand.Connection.Close()<o:p></o:p> End Sub 'SelectSqlClientSrvRows<o:p></o:p> <o:p> </o:p> [C#] <o:p></o:p> public void InsertRow(string myConnectionString) <o:p></o:p> {<o:p></o:p> // If the connection string is null, use a default.<o:p></o:p> if(myConnectionString == "") <o:p></o:p> {<o:p></o:p> myConnectionString = "Initial Catalog=Northwind;Data Source=localhost;Integrated Security=SSPI;";<o:p></o:p> }<o:p></o:p> SqlConnection myConnection = new SqlConnection(myConnectionString);<o:p></o:p> string myInsertQuery = "INSERT INTO Customers (CustomerID, CompanyName) Values('NWIND', 'Northwind Traders')";<o:p></o:p> SqlCommand myCommand = new SqlCommand(myInsertQuery);<o:p></o:p> myCommand.Connection = myConnection;<o:p></o:p> myConnection.Open();<o:p></o:p> myCommand.ExecuteNonQuery();<o:p></o:p> myCommand.Connection.Close();<o:p></o:p> }<o:p></o:p> <o:p> </o:p> [C++] <o:p></o:p> public:<o:p></o:p> void InsertRow(String* myConnectionString) <o:p></o:p> {<o:p></o:p> // If the connection string is null, use a default.<o:p></o:p> if(myConnectionString->Equals(S""))<o:p></o:p> {<o:p></o:p> myConnectionString = S"Initial Catalog=Northwind;Data Source=localhost;Integrated Security=SSPI;";<o:p></o:p> }<o:p></o:p> SqlConnection* myConnection = new SqlConnection(myConnectionString);<o:p></o:p> String* myInsertQuery = S"INSERT INTO Customers (CustomerID, CompanyName) Values('NWIND', 'Northwind Traders')";<o:p></o:p> SqlCommand* myCommand = new SqlCommand(myInsertQuery);<o:p></o:p> myCommand->Connection = myConnection;<o:p></o:p> myConnection->Open();<o:p></o:p> myCommand->ExecuteNonQuery();<o:p></o:p> myCommand->Connection->Close();<o:p></o:p> }<o:p></o:p> [JScript] No example is available for JScript. To view a Visual Basic, C#, or C++ example, click the Language Filter button  in the upper-left corner of the page.<o:p></o:p> Requirements<o:p></o:p> Namespace: System.Data.SqlClient<o:p></o:p> Platforms: Windows 98, Windows NT 4.0, Windows Millennium Edition, Windows 2000, Windows XP Home Edition, Windows XP Professional, Windows Server 2003 family, .NET Compact Framework<o:p></o:p> Assembly: System.Data (in System.Data.dll)<o:p></o:p> >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><o:p> </o:p> <o:p> </o:p> .NET Framework Class Library<o:p></o:p> System.Data.Odbc Namespace<o:p></o:p> The System.Data.Odbc namespace is the .NET Framework Data Provider for ODBC.<o:p></o:p> The .NET Framework Data Provider for ODBC describes a collection of classes used to access an ODBC data source in the managed space. Using the OdbcDataAdapter class, you can fill a memory-resident DataSet that you can use to query and update the data source.<o:p></o:p> For more information about how to use this namespace, see the OdbcDataReader, OdbcCommand, and OdbcConnectionclasses.<o:p></o:p>  Classes<o:p></o:p> <o:p></o:p> Class<o:p></o:p> Description<o:p></o:p> <o:p></o:p> OdbcCommand<o:p></o:p> Represents an SQL statement or stored procedure to execute against a data source. This class cannot be inherited.<o:p></o:p> <o:p></o:p> OdbcCommandBuilder<o:p></o:p> Automatically generates single-table commands that are used to reconcile changes made to a DataSet with the associated data source. This class cannot be inherited.<o:p></o:p> <o:p></o:p> OdbcConnection<o:p></o:p> Represents an open connection to a data source.<o:p></o:p> <o:p></o:p> OdbcConnectionStringBuilder<o:p></o:p> Provides a simple way to create and manage the contents of connection strings used by theOdbcConnection class.<o:p></o:p> <o:p></o:p> OdbcDataAdapter<o:p></o:p> Represents a set of data commands and a connection to a data source that are used to fill the DataSet and update the data source. This class cannot be inherited.<o:p></o:p> <o:p></o:p> OdbcDataReader<o:p></o:p> Provides a way of reading a forward-only stream of data rows from a data source. This class cannot be inherited.<o:p></o:p> <o:p></o:p> OdbcError<o:p></o:p> Collects information relevant to a warning or error returned by the data source.<o:p></o:p> <o:p></o:p> OdbcErrorCollection<o:p></o:p> Collects all errors generated by the OdbcDataAdapter. This class cannot be inherited.<o:p></o:p> <o:p></o:p> OdbcException<o:p></o:p> The exception that is generated when a warning or error is returned by an ODBC data source. This class cannot be inherited.<o:p></o:p> <o:p></o:p> OdbcFactory<o:p></o:p> Represents a set of methods for creating instances of the ODBC provider's implementation of the data source classes.<o:p></o:p> <o:p></o:p> OdbcInfoMessageEventArgs<o:p></o:p> Provides data for the InfoMessage event.<o:p></o:p> <o:p></o:p> OdbcMetaDataCollectionNames<o:p></o:p> Provides a list of constants for use with the GetSchema method to retrieve metadata collections.<o:p></o:p> <o:p></o:p> OdbcMetaDataColumnNames<o:p></o:p> Provides static values that are used for the column names in the OdbcMetaDataCollectionNames objects contained in the DataTable. The DataTable is created by the GetSchema method.<o:p></o:p> <o:p></o:p> OdbcParameter<o:p></o:p> Represents a parameter to an OdbcCommand and optionally, its mapping to a DataColumn. This class cannot be inherited.<o:p></o:p> <o:p></o:p> OdbcParameterCollection<o:p></o:p> Represents a collection of parameters relevant to an OdbcCommand and their respective mappings to columns in a DataSet. This class cannot be inherited.<o:p></o:p> <o:p></o:p> OdbcPermission<o:p></o:p> Enables the .NET Framework Data Provider for ODBC to help make sure that a user has a security level sufficient to access an ODBC data source. This class cannot be inherited.<o:p></o:p> <o:p></o:p> OdbcPermissionAttribute<o:p></o:p> Associates a security action with a custom security attribute.<o:p></o:p> <o:p></o:p> OdbcRowUpdatedEventArgs<o:p></o:p> Provides data for the RowUpdated event.<o:p></o:p> <o:p></o:p> OdbcRowUpdatingEventArgs<o:p></o:p> Provides data for the RowUpdating event.<o:p></o:p> <o:p></o:p> OdbcTransaction<o:p></o:p> Represents an SQL transaction to be made at a data source. This class cannot be inherited.<o:p></o:p>  Delegates<o:p></o:p> <o:p></o:p> Delegate<o:p></o:p> Description<o:p></o:p> <o:p></o:p> OdbcInfoMessageEventHandler<o:p></o:p> Represents the method that will handle the InfoMessage event of an OdbcConnection.<o:p></o:p> <o:p></o:p> OdbcRowUpdatedEventHandler<o:p></o:p> Represents the method that will handle the RowUpdated event of an OdbcDataAdapter.<o:p></o:p> <o:p></o:p> OdbcRowUpdatingEventHandler<o:p></o:p> Represents the method that will handle the RowUpdating event of an OdbcDataAdapter.<o:p></o:p>  Enumerations<o:p></o:p> <o:p></o:p> Enumeration<o:p></o:p> Description<o:p></o:p> <o:p></o:p> OdbcType<o:p></o:p> Specifies the data type of a field, property, for use in an OdbcParameter.<o:p></o:p> >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><o:p> </o:p> <o:p> </o:p> Introduction<o:p></o:p> We can use INI file to auto generate the database connection string.<o:p></o:p> Background<o:p></o:p> It can be used in Webform and winForm.<o:p></o:p> Using the code<o:p></o:p> A brief description of how to use the article or code. The class names, the methods and properties, any tricks or tips.<o:p></o:p> Blocks of code should be set as style "Formatted" like this:<o:p></o:p>  Collapse<o:p></o:p> using System;<o:p></o:p> using System.Resources;<o:p></o:p> using System.Collections.Generic;<o:p></o:p> using System.Runtime.InteropServices;<o:p></o:p> using System.Text;<o:p></o:p> using System.IO;<o:p></o:p> using System.Data;<o:p></o:p> <o:p> </o:p> namespace www.treaple.com<o:p></o:p> {<o:p></o:p> public class DBBase<o:p></o:p> {<o:p></o:p> public string hostName = null;<o:p></o:p> public string baseName = null;<o:p></o:p> public string loginName = null;<o:p></o:p> public string passWord = null;<o:p></o:p> <o:p> </o:p> public DBBase()<o:p></o:p> {<o:p></o:p> }<o:p></o:p> }<o:p></o:p> <o:p> </o:p> public class DBConnectioin : DBBase<o:p></o:p> {<o:p></o:p> private INI ini;<o:p></o:p> public static string conStr = null;<o:p></o:p> <o:p> </o:p> public DBConnectioin()<o:p></o:p> {<o:p></o:p> ini = new INI();<o:p></o:p> }<o:p></o:p> <o:p> </o:p> private void Read()<o:p></o:p> {<o:p></o:p> ini.GetPara();<o:p></o:p> this.hostName = ini.hostName;<o:p></o:p> this.baseName = ini.baseName;<o:p></o:p> this.loginName = ini.loginName;<o:p></o:p> this.passWord = ini.passWord;<o:p></o:p> }<o:p></o:p> <o:p> </o:p> public void Write()<o:p></o:p> {<o:p></o:p> ini.hostName = this.hostName ;<o:p></o:p> ini.baseName = this.baseName ;<o:p></o:p> ini.loginName= this.loginName ;<o:p></o:p> ini.passWord = this.passWord;<o:p></o:p> ini.SetPara();<o:p></o:p> }<o:p></o:p> <o:p> </o:p> public void GetConString()<o:p></o:p> {<o:p></o:p> this.Read();<o:p></o:p> conStr = "workstation id=" + this.hostName + ";data source=" + this.hostName + ";initial catalog=" + this.baseName + ";user id=" + this.loginName + ";password=" + this.passWord;<o:p></o:p> }<o:p></o:p> }<o:p></o:p> <o:p> </o:p> public class INI : DBBase<o:p></o:p> {<o:p></o:p> protected string path = null;<o:p></o:p> protected string fileName = "Config.ini";<o:p></o:p> <o:p> </o:p> [DllImport("kernel32")]<o:p></o:p> private static extern long WritePrivateProfileString(string section, string key, string val, string filePath);<o:p></o:p> [DllImport("kernel32")]<o:p></o:p> private static extern int GetPrivateProfileString(string section, string key, string def, StringBuilder retVal, int size, string filePath);<o:p></o:p> <o:p> </o:p> public INI()<o:p></o:p> {<o:p></o:p> this.path = Directory.GetCurrentDirectory();<o:p></o:p> }<o:p></o:p> <o:p> </o:p> private bool Write(string section, string key, string value)<o:p></o:p> {<o:p></o:p> try<o:p></o:p> {<o:p></o:p> WritePrivateProfileString(section, key, value, this.path + "\\" + this.fileName); <o:p></o:p> return true;<o:p></o:p> }<o:p></o:p> catch<o:p></o:p> {<o:p></o:p> return false;<o:p></o:p> }<o:p></o:p> }<o:p></o:p> <o:p> </o:p> private string Read(string section, string key)<o:p></o:p> {<o:p></o:p> StringBuilder temp = new StringBuilder(255);<o:p></o:p> int i = GetPrivateProfileString(section, key, "", temp, 255, this.path + "\\" + this.fileName);<o:p></o:p> return temp.ToString();<o:p></o:p> }<o:p></o:p> <o:p> </o:p> public void GetPara()<o:p></o:p> {<o:p></o:p> this.hostName = Read("Info", "HostName");<o:p></o:p> this.baseName = Read("Info", "baseName");<o:p></o:p> this.loginName = Read("Info", "LoginName");<o:p></o:p> this.passWord = Read("Info", "PassWord");<o:p></o:p> }<o:p></o:p> <o:p> </o:p> public void SetPara()<o:p></o:p> {<o:p></o:p> Write("Info", "HostName", this.hostName);<o:p></o:p> Write("Info", "baseName", this.baseName);<o:p></o:p> Write("Info", "LoginName", this.loginName);<o:p></o:p> Write("Info", "PassWord", this.passWord);<o:p></o:p> }<o:p></o:p> }<o:p></o:p> }<o:p></o:p> <o:p> </o:p> using System;<o:p></o:p> using System.Collections.Generic;<o:p></o:p> using System.ComponentModel;<o:p></o:p> using System.Data;<o:p></o:p> using System.Drawing;<o:p></o:p> using System.Text;<o:p></o:p> using System.Windows.Forms;<o:p></o:p> <o:p> </o:p> namespace www.treaple.com<o:p></o:p> {<o:p></o:p> public partial class Form1 : Form<o:p></o:p> {<o:p></o:p> DBConnectioin dBConnectioin = new DBConnectioin();<o:p></o:p> public Form1()<o:p></o:p> {<o:p></o:p> InitializeComponent();<o:p></o:p> }<o:p></o:p> <o:p> </o:p> private void btnSetStr_Click(object sender, EventArgs e)<o:p></o:p> {<o:p></o:p> dBConnectioin.hostName = txtHostName.Text.Trim();<o:p></o:p> dBConnectioin.baseName = txtBaseName.Text.Trim();<o:p></o:p> dBConnectioin.loginName = txtLoginName.Text.Trim();<o:p></o:p> dBConnectioin.passWord = txtPassWord.Text.Trim();<o:p></o:p> dBConnectioin.Write();<o:p></o:p> }<o:p></o:p> <o:p> </o:p> private void btnGetStr_Click(object sender, EventArgs e)<o:p></o:p> {<o:p></o:p> dBConnectioin.GetConString();<o:p></o:p> txtConStr.Text = DBConnectioin.conStr;<o:p></o:p> }<o:p></o:p> <o:p> </o:p> private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)<o:p></o:p> {<o:p></o:p> System.Diagnostics.<o:p></o:p> Process.Start("http://www.treaple.com/Contact_Us.htm");<o:p></o:p> }<o:p></o:p> <o:p> </o:p> }<o:p></o:p> }<o:p></o:p> <o:p></o:p> Remember to set the Language of your code snippet using the Language dropdown.<o:p></o:p> Use the "var" button to to wrap Variable or class names in <code> tags like this.<o:p></o:p> Points of Interest<o:p></o:p> Did you learn anything interesting/fun/annoying while writing the code? Did you do anything particularly clever or wild or zany?<o:p></o:p> History<o:p></o:p> If you have any questions,You are welcomed to contact me by email or visit my own site www.treaple.com .<o:p></o:p> >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><o:p> </o:p> <o:p> </o:p> I am proud to present you our unique image comparison application in C#.Net. C# .Net provides a good support for processing on the image, and the purpose of this article is not to give you a lot of insight into the image processing, rather it is written to help you start your image processing career using C#.<o:p></o:p> Lets create a project and compare two images using this application. to do this follows the given steps:<o:p></o:p> Step 1: In Visual studio 2005. click on file menu -> New -> Project then the following dialogbox will appear.<o:p></o:p>  Figure 1:<o:p></o:p> Step 2: Now drag and drop the following tools on the Form from toolbox.<o:p></o:p> Two LinkLable One Button Two OpenFileDialog ProgressBar PictureBox<o:p></o:p>  Figure 2:<o:p></o:p> Step 3: Go to properties window and design the form as you want.<o:p></o:p>  Figure 3:<o:p></o:p> Step 4: Double click on form and write the following line on the form_load. private void Form1_Load(object sender, EventArgs e) { progressBar1.Visible = false; pictureBox1.Visible = false; }<o:p></o:p> Step 5: Add using System.IO; the namespace.<o:p></o:p> Step 6: Write the following line on the linkLabel1_LinkClicked event<o:p></o:p> private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e) { openFileDialog1.FileName = ""; openFileDialog1.Title = "Images"; openFileDialog1.Filter = "All Images|*.jpg; *.bmp; *.png"; openFileDialog1.ShowDialog(); if (openFileDialog1.FileName.ToString() != "") { fname1 = openFileDialog1.FileName.ToString(); } }<o:p></o:p> Step 7: Write the following line on the linkLabel2_LinkClicked event<o:p></o:p> private void linkLabel2_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e) { openFileDialog2.FileName = ""; openFileDialog2.Title = "Images"; openFileDialog2.Filter = "All Images|*.jpg; *.bmp; *.png"; openFileDialog2.ShowDialog(); if (openFileDialog2.FileName.ToString() != "") { fname2 = openFileDialog2.FileName.ToString(); } }<o:p></o:p> Step 8: Finally write the follwoing code in the button click event.<o:p></o:p> private void button1_Click(object sender, EventArgs e) { progressBar1.Visible = true; string img1_ref, img2_ref; img1 = new Bitmap(fname1); img2 = new Bitmap(fname2); progressBar1.Maximum = img1.Width; if (img1.Width == img2.Width && img1.Height == img2.Height) { for (int i = 0; i < img1.Width; i++) { for (int j = 0; j < img1.Height; j++) { img1_ref = img1.GetPixel(i, j).ToString(); img2_ref = img2.GetPixel(i, j).ToString(); if (img1_ref != img2_ref) { count2++; flag = false; break; } count1++; } progressBar1.Value++; } if (flag == false) MessageBox.Show("Sorry, Images are not same , " + count2 + " wrong pixels found"); else MessageBox.Show(" Images are same , " + count1 + " same pixels found and " + count2 + " wrong pixels found"); } else MessageBox.Show("can not compare this images"); this.Dispose(); }<o:p></o:p> Step 9: Now debug the application. When you compare same images like as follows:<o:p></o:p>  Img 1:<o:p></o:p>  Img 2:<o:p></o:p> Then the following output will occur.<o:p></o:p>  Figure 4:<o:p></o:p> Step 10: When you compare different images like as follows:<o:p></o:p>  Img 3_Different:<o:p></o:p> Then the following output will occur.<o:p></o:p>  Figure 5:<o:p></o:p> >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><o:p> </o:p> <o:p> </o:p> Using Database Connections In order to access the database, you need to provide connection parameters, such as the machine that the database is running on, and possibly your login credentials. Anyone who has worked with ADO will be immediately familiar with the .NET connection classes, OleDbConnection and SqlConnection:<o:p></o:p>  Connection Interfaces<o:p></o:p> The following code snippet illustrates how to create, open, and close a connection to the Northwind database. In the examples within this chapter I use the Northwind database, which is installed with the .NET Framework SDK samples:<o:p></o:p> using System.Data.SqlClient;<o:p></o:p> <o:p> </o:p> string source = "server=(local)\\NetSDK;" +<o:p></o:p> "uid=QSUser;pwd=QSPassword;" +<o:p></o:p> "database=Northwind";<o:p></o:p> <o:p></o:p> SqlConnection conn = new SqlConnection(source);<o:p></o:p> conn.Open();<o:p></o:p> <o:p></o:p> // Do something useful<o:p></o:p> <o:p></o:p> conn.Close();<o:p></o:p> The connection string should be very familiar to you if you've ever used ADO or OLE DB before - indeed, you should be able to cut and paste from your old code if you use the OleDb provider. In the example connection string, the parameters used are as follows. The parameters are delimited by a semicolon in the connection string.<o:p></o:p> server=(local)\\NetSDK - This denotes the database server to connect to. SQL Server permits a number of separate database server processes to be running on the same machine, so here we're connecting to the NetSDK processes on the local machine.<o:p></o:p> uid=QSUser - This parameter describes the database user. You can also use User ID.<o:p></o:p> pwd=QSPassword - And this is the password for that user. The .NET SDK comes with a set of sample databases, and this user/password combination is added during the installation of the .NET samples. You can also use Password.<o:p></o:p> database=Northwind - This describes the database instance to connect to - each SQL Server process can expose several database instances.<o:p></o:p> The example opens a database connection using the defined connection string, and then closes that connection. Once the connection has been opened, you can issue commands against the data source, and when you're finished, the connection can be closed.<o:p></o:p> SQL Server has another mode of authentication - it can use Windows integrated security, so that the credentials supplied at logon are passed through to SQL Server. This is catered for by removing the uid and pwd portions of the connection string , and adding in Integrated Security=SSPI.<o:p></o:p> In the download code available for this chapter, you will find a file Login.cs that simplifies the examples in this chapter. It is linked to all the example code, and includes database connection information used for the examples; you can alter this to supply your own server name, user, and password as appropriate.<o:p></o:p> This by default uses Windows integrated security; however, you can change the username and password as appropriate. Now that we know how to open connections, before we move on we should consider some good practices concerning the handling of connections.<o:p></o:p> Using Connections Efficiently In general, when using "scarce" resources in .NET, such as database connections, windows, or graphics objects, it is good practice to ensure that each resource is closed after use. Although the designers of .NET have implemented automatic garbage collection, which will tidy up eventually, it is necessary to actively release resources as early as possible.<o:p></o:p> This is all too apparent when writing code that accesses a database, as keeping a connection open for slightly longer than necessary can affect other sessions. In extreme circumstances, not closing a connection can lock other users out of an entire set of tables, considerably hurting application performance. Closing database connections should be considered mandatory, so this section shows how to structure your code so as to minimize the risk of leaving a resource open.<o:p></o:p> There are two main ways to ensure that database connections and the like are released after use.<o:p></o:p> Option One - try/catch/finally The first option to ensure that resources are cleaned up is to utilize try_catch_finally blocks, and ensure that you close any open connections within the finally block. Here's a short example:<o:p></o:p> try {<o:p></o:p> // Open the connection<o:p></o:p> conn.Open();<o:p></o:p> // Do something useful<o:p></o:p> } catch (Exception ex) {<o:p></o:p> // Do something about the exception<o:p></o:p> } finally {<o:p></o:p> // Ensure that the connection is freed<o:p></o:p> conn.Close();<o:p></o:p> }<o:p></o:p> Within the finally block you can release any resources you have used. The only trouble with this method is that you have to ensure that you close the connection - it is all too easy to forget to add in the finally clause, so something less prone to vagaries in coding style might be worthwhile.<o:p></o:p> Also, you may find that you open a number of resources (say two database connections and a file) within a given method, so the cascading of try?catch?finally blocks can sometimes become less easy to read. There is however another way to guarantee resource cleanup - the using statement.<o:p></o:p> Option Two - The using Block Statement During development of C#, .NET's method of clearing up objects after they are no longer referenced using nondeterministic destruction became a topic of very heated discussion. In C++, as soon as an object went out of scope, its destructor would be automatically called. This was great news for designers of resource-based classes, as the destructor was the ideal place to close the resource if the user had forgotten to do so. A C++ destructor is called in any and every situation when an object goes out of scope - so for instance if an exception was raised and not caught, all objects with destructors would have them called.<o:p></o:p> With C# and the other managed languages, there is no concept of automatic, deterministic destruction - instead there is the garbage collector, which will dispose of resources at some point in the future. What makes this nondeterministic is that you have little say over when this process actually happens. Forgetting to close a database connection could cause all sorts of problems for a .NET executable. Luckily, help is at hand. The following code demonstrates how to use the using clause to ensure that objects that implement the IDisposable interface (discussed in Chapter 2) are cleared up immediately the block exits.<o:p></o:p> string source = "server=(local)\\NetSDK;" +<o:p></o:p> "uid=QSUser;pwd=QSPassword;" +<o:p></o:p> "database=Northwind";<o:p></o:p> <o:p> </o:p> using ( SqlConnection conn = new SqlConnection ( source ) )<o:p></o:p> {<o:p></o:p> // Open the connection<o:p></o:p> conn.Open();<o:p></o:p> // Do something useful<o:p></o:p> }<o:p></o:p> The using clause was introduced in Chapter 2. The object within the using clause must implement the IDisposable interface, or a compilation error will be flagged if the object does not support this interface. The Dispose() method will automatically be called on exiting the using block.<o:p></o:p> Looking at the IL code for the Dispose() method of SqlConnection (and OleDbConnection), both of these check the current state of the connection object, and if open will call the Close() method.<o:p></o:p> When programming, you should use at least one of these methods, and probably both. Wherever you acquire resources it is good practice to utilize the using () statement, as even though we all mean to write the Close() statement, sometimes we forget, and in the face of exceptions the using clause does the right thing. There is no substitute for good exception handling either, so in most instances I would suggest you use both methods together as in the following example:<o:p></o:p> try {<o:p></o:p> using (SqlConnection conn = new SqlConnection ( source )) {<o:p></o:p> // Open the connection<o:p></o:p> conn.Open();<o:p></o:p> // Do something useful<o:p></o:p> // Close it myself<o:p></o:p> conn.Close();<o:p></o:p> }<o:p></o:p> } catch (Exception e) {<o:p></o:p> // Do something with the exception here...<o:p></o:p> }<o:p></o:p> Here I have explicitly called Close() which isn't strictly necessary as the using clause will ensure that this is done anyway; however, you should ensure that any resources such as this are released as soon as possible - you may have more code in the rest of the block and there's no point locking a resource unnecessarily.<o:p></o:p> In addition, if an exception is raised within the using block, the IDisposable.Dispose method will be called on the resource guarded by the using clause, which in this case will ensure that the database connection is always closed. This produces easier to read code than having to ensure you close a connection within an exception clause.<o:p></o:p> One last word - if you are writing a class that wraps a resource, whatever that resource may be, always implement the IDisposable interface to close the resource. That way anyone coding with your class can utilize the using() statement and guarantee that the resource will be cleared up.<o:p></o:p> Transactions Often when there is more than one update to be made to the database, these updates must be performed within the scope of a transaction. A transaction in ADO.NET is begun by calling one of the BeginTransaction() methods on the database connection object. These methods return an object that implements the IDbTransaction interface, defined within System.Data.<o:p></o:p> The following sequence of code initiates a transaction on a SQL Server connection:<o:p></o:p> string source = "server=(local)\\NetSDK;" +<o:p></o:p> "uid=QSUser;pwd=QSPassword;" +<o:p></o:p> "database=Northwind";<o:p></o:p> <o:p></o:p> SqlConnection conn = new SqlConnection(source);<o:p></o:p> conn.Open();<o:p></o:p> SqlTransaction tx = conn.BeginTransaction();<o:p></o:p> <o:p> </o:p> // Execute some commands, then commit the transaction<o:p></o:p> <o:p> </o:p> tx.Commit();<o:p></o:p> conn.Close();<o:p></o:p> When you begin a transaction, you can choose the isolation level for commands executed within that transaction. The level determines how isolated your transaction is from others occurring on the database server. Certain database engines may support fewer than the four presented here. The options are as follows:<o:p></o:p>  Transaction Commands<o:p></o:p>  Serializable Interface<o:p></o:p> The SQL Server default isolation level, ReadCommitted, is a good compromise between data coherence and data availability, as fewer locks are required on data than in RepeatableRead or Serializable modes. However, there are situations where the isolation level should be increased, and so within .NET you can simply begin a transaction with a different level from the default. There are no hard and fast rules as to which levels to pick - that comes with experience.<o:p></o:p> Note: One last word on transactions - if you are currently using a database that does not support transactions, it is well worth changing to a database that does!<o:p></o:p> Commands I briefly touched on the idea of issuing commands against a database in the Using Database Connections section. A command is, in its simplest form, a string of text containing SQL statements that is to be issued to the database. A command could also be a stored procedure, or the name of a table that will return all columns and all rows from that table (in other words, a SELECT *-style clause).<o:p></o:p> A command can be constructed by passing the SQL clause as a parameter to the constructor of the SqlCommand class, as shown below:<o:p></o:p> string source = "server=(local)\\NetSDK;" +<o:p></o:p> "uid=QSUser;pwd=QSPassword;" +<o:p></o:p> "database=Northwind";<o:p></o:p> <o:p></o:p> string select = "SELECT ContactName,CompanyName FROM Customers";<o:p></o:p> SqlConnection conn = new SqlConnection(source);<o:p></o:p> conn.Open();<o:p></o:p> <o:p> </o:p> SqlCommand cmd = new SqlCommand(select, conn);<o:p></o:p> The SqlCommand and OleDbCommand classes have a property called CommandType, which is used to define whether the command is a SQL clause, a call to a stored procedure, or a full table statement (which simply selects all columns and rows from a given table). The following table summarizes the CommandType enumeration:<o:p></o:p>  Command Type<o:p></o:p> When executing a stored procedure, it may be necessary to pass parameters to that procedure. The example above sets the @CustomerID parameter directly, although there are other ways of setting the parameter value, which we will look at later in the chapter.<o:p></o:p> Note: Note: The TableDirect command type is only valid for the OleDb provider - an exception is thrown by the Sql provider if you attempt to use this command type with it.<o:p></o:p> Executing Commands Once you have the command defined, you need to execute it. There are a number of ways to issue the statement, depending on what you expect to be returned (if anything) from that command. The SqlCommand and OleDbCommand classes provide the following execute methods:<o:p></o:p> ExecuteNonQuery() - Execute the command but do not return any output<o:p></o:p> ExecuteReader() - Execute the command and return a typed IDataReader<o:p></o:p> ExecuteScalar() - Execute the command and return a single value<o:p></o:p> In addition to the above methods, the SqlCommand class also exposes the following method<o:p></o:p> ExecuteXmlReader() - Execute the command, and return an XmlReader object, which can be used to traverse the XML fragment returned from the database.<o:p></o:p> The example code in this section can be found in the Chapter 09\01_ExecutingCommands subdirectory of the code download.<o:p></o:p> ExecuteNonQuery() : This method is commonly used for UPDATE, INSERT, or DELETE statements, where the only returned value is the number of records affected. This method can, however, return results if you call a stored procedure that has output parameters.<o:p></o:p> using System;<o:p></o:p> using System.Data.SqlClient;<o:p></o:p> <o:p> </o:p> public class ExecuteNonQueryExample {<o:p></o:p> public static void Main(string[] args) {<o:p></o:p> string source = "server=(local)\\NetSDK;" +<o:p></o:p> "uid=QSUser;pwd=QSPassword;" +<o:p></o:p> "database=Northwind";<o:p></o:p> string select = "UPDATE Customers " +<o:p></o:p> "SET ContactName = 'Bob' " +<o:p></o:p> "WHERE ContactName = 'Bill'";<o:p></o:p> <o:p> </o:p> SqlConnection conn = new SqlConnection(source);<o:p></o:p> conn.Open();<o:p></o:p> <o:p></o:p> SqlCommand cmd = new SqlCommand(select, conn);<o:p></o:p> int rowsReturned = cmd.ExecuteNonQuery();<o:p></o:p> <o:p></o:p> Console.WriteLine("{0} rows returned.", rowsReturned);<o:p></o:p> conn.Close();<o:p></o:p> }<o:p></o:p> }<o:p></o:p> ExecuteNonQuery() returns the number of rows affected by the command as an int.<o:p></o:p> ExecuteReader() This method executes the command and returns a SqlDataReader or OleDbDataReader object, depending on the provider in use. The object returned can be used to iterate through the record(s) returned, as shown in the following code:<o:p></o:p> using System;<o:p></o:p> using System.Data.SqlClient;<o:p></o:p> <o:p> </o:p> public class ExecuteReaderExample {<o:p></o:p> public static void Main(string[] args) {<o:p></o:p> string source = "server=(local)\\NetSDK;" +<o:p></o:p> "uid=QSUser;pwd=QSPassword;" +<o:p></o:p> "database=Northwind";<o:p></o:p> string select = "SELECT ContactName,CompanyName FROM Customers";<o:p></o:p> <o:p> </o:p> SqlConnection conn = new SqlConnection(source);<o:p></o:p> conn.Open();<o:p></o:p> <o:p> </o:p> SqlCommand cmd = new SqlCommand(select, conn);<o:p></o:p> SqlDataReader reader = cmd.ExecuteReader();<o:p></o:p> <o:p></o:p> while(reader.Read()) {<o:p></o:p> Console.WriteLine("Contact : {0,-20} Company : {1}",<o:p></o:p> reader[0] , reader[1]);<o:p></o:p> } <o:p></o:p> }<o:p></o:p> }<o:p></o:p>  Command Prompt<o:p></o:p> The SqlDataReader and OleDbDataReader objects will be discussed later in this chapter.<o:p></o:p> ExecuteScalar() On many occasions it is necessary to return a single result from a SQL statement, such as the count of records in a given table, or the current date/time on the server. The ExecuteScalar method can be used in such situations:<o:p></o:p> using System;<o:p></o:p> using System.Data.SqlClient;<o:p></o:p> <o:p> </o:p> public class ExecuteScalarExample {<o:p></o:p> public static void Main(string[] args) {<o:p></o:p> string source = "server=(local)\\NetSDK;" +<o:p></o:p> "uid=QSUser;pwd=QSPassword;" +<o:p></o:p> "database=Northwind";<o:p></o:p> string select = "SELECT COUNT(*) FROM Customers";<o:p></o:p> <o:p></o:p> SqlConnection conn = new SqlConnection(source);<o:p></o:p> conn.Open();<o:p></o:p> <o:p></o:p> SqlCommand cmd = new SqlCommand(select, conn);<o:p></o:p> object o = cmd.ExecuteScalar();<o:p></o:p> <o:p></o:p> Console.WriteLine(o);<o:p></o:p> }<o:p></o:p> }<o:p></o:p> The method returns an object, which you can cast into the appropriate type if required.<o:p></o:p> ExecuteXmlReader() (SqlClient Provider Only) As its name implies, this method will execute the command and return an XmlReader object to the caller. SQL Server permits a SQL SELECT statement to be extended with a FOR XML clause. This clause can take one of three options:<o:p></o:p> FOR XML AUTO - builds a tree based on the tables in the FROM clause<o:p></o:p> FOR XML RAW - result set rows are mapped to elements, with columns mapped to attributes<o:p></o:p> FOR XML EXPLICIT -you must specify the shape of the XML tree to be returned<o:p></o:p> Professional SQL Server 2000 XML (Wrox Press, ISBN 1-861005-46-6) includes a complete description of these options. For this example I shall use AUTO:<o:p></o:p> using System;<o:p></o:p> using System.Data.SqlClient;<o:p></o:p> using System.Xml;<o:p></o:p> <o:p> </o:p> public class ExecuteXmlReaderExample {<o:p></o:p> public static void Main(string[] args) {<o:p></o:p> string source = "server=(local)\\NetSDK;" +<o:p></o:p> "uid=QSUser;pwd=QSPassword;" +<o:p></o:p> "database=Northwind";<o:p></o:p> string select = "SELECT ContactName,CompanyName " +<o:p></o:p> "FROM Customers FOR XML AUTO";<o:p></o:p> <o:p></o:p> SqlConnection conn = new SqlConnection(source);<o:p></o:p> conn.Open();<o:p></o:p> <o:p></o:p> SqlCommand cmd = new SqlCommand(select, conn);<o:p></o:p> XmlReader xr = cmd.ExecuteXmlReader();<o:p></o:p> <o:p></o:p> while(xr.Read()) {<o:p></o:p> Console.WriteLine(xr.ReadOuterXml());<o:p></o:p> }<o:p></o:p> <o:p></o:p> conn.Close();<o:p></o:p> }<o:p></o:p> }<o:p></o:p> Note that we have to import the System.Xml namespace in order to output the returned XML. This namespace and further XML capabilities of the .NET Framework are explored in more detail in Chapter 11.<o:p></o:p> Here we include the FOR XML AUTO clause in the SQL statement, then call the ExecuteXmlReader() method. A screenshot of the possible output from this code is shown below:<o:p></o:p>  Command Prompt<o:p></o:p> In the SQL clause, we specified FROM Customers, so an element of type Customers is shown in the output. To this are added attributes, one for each column selected from the database. This builds up an XML fragment for each row selected from the database.<o:p></o:p> Calling Stored Procedures Calling a stored procedure with a command object is just a matter of defining the name of the stored procedure, adding a parameter's definition for each parameter of the procedure, then executing the command with one of the methods presented in the previous section.<o:p></o:p> In order to make the examples in this section more useful, I have defined a set of stored procedures that can be used to insert, update, and delete records from the Region table in the Northwind example database. I have chosen this table despite its small size, as it can be used to define examples for each of the types of stored procedures you will commonly write.<o:p></o:p> Calling a Stored Procedure that Returns Nothing The simplest example of calling a stored procedure is one that returns nothing to the caller. There are two such procedures defined below, one for updating a pre-existing Region record, and the other for deleting a given Region record.<o:p></o:p> Record Update Updating a Region record is fairly trivial, as there is only one column that can be modified (assuming primary keys cannot be updated). You can type these examples directly into the SQL Server Query Analyzer, or run the StoredProcs.sql file in the Chapter 09\02_StoredProcs subdirectory, which will install each of the stored procedures in this section:<o:p></o:p> CREATE PROCEDURE RegionUpdate (@RegionID INTEGER,<o:p></o:p> @RegionDescription NCHAR(50)) AS<o:p></o:p> <o:p></o:p> SET NOCOUNT OFF<o:p></o:p> UPDATE Region SET RegionDescription = @RegionDescription<o:p></o:p> WHERE RegionID = @RegionID<o:p></o:p> GO<o:p></o:p> An update command on a more real-world table might need to re-select and return the updated record in its entirety. This stored procedure takes two input parameters (@RegionID and @RegionDescription), and issues an UPDATE statement against the database.<o:p></o:p> To run this stored procedure from within .NET code, you need to define a SQL command and execute it:<o:p></o:p> SqlCommand aCommand = new SqlCommand("RegionUpdate", conn);<o:p></o:p> aCommand.CommandType = CommandType.StoredProcedure;<o:p></o:p> aCommand.Parameters.Add(new SqlParameter("@RegionID",<o:p></o:p> SqlDbType.Int, 0, "RegionID"));<o:p></o:p> aCommand.Parameters.Add(new SqlParameter("@RegionDescription",<o:p></o:p> SqlDbType.NChar, 50, "RegionDescription"));<o:p></o:p> aCommand.UpdatedRowSource = UpdateRowSource.None;<o:p></o:p> This code creates a new SqlCommand object named aCommand, and defines it as a stored procedure. We then add each parameter in turn, and finally set the expected output from the stored procedure to one of the values in the UpdateRowSource enumeration, which is discussed later in this chapter.<o:p></o:p> The stored procedure takes two parameters: the unique primary key of the Region record being updated, and the new description to be given to this record.<o:p></o:p> Once the command has been created, it can be executed by issuing the following commands:<o:p></o:p> aCommand.Parameters[0].Value = 999;<o:p></o:p> aCommand.Parameters[1].Value = "South Western England";<o:p></o:p> aCommand.ExecuteNonQuery();<o:p></o:p> Here we are setting the value of the parameters, then executing the stored procedure. As the procedure returns nothing, ExecuteNonQuery() will suffice.<o:p></o:p> Command parameters may be set by ordinal as shown above, or set by name.<o:p></o:p> Record Deletion The next stored procedure required is one that can be used to delete a Region record from the database:<o:p></o:p> CREATE PROCEDURE RegionDelete (@RegionID INTEGER) AS<o:p></o:p> SET NOCOUNT OFF<o:p></o:p> DELETE FROM Region WHERE RegionID = @RegionID<o:p></o:p> GO<o:p></o:p> This procedure only requires the primary key value of the record. The code uses a SqlCommand object to call this stored procedure as follows:<o:p></o:p> SqlCommand aCommand = new SqlCommand("RegionDelete" , conn);<o:p></o:p> aCommand.CommandType = CommandType.StoredProcedure;<o:p></o:p> aCommand.Parameters.Add(new SqlParameter("@RegionID",<o:p></o:p> SqlDbType.Int , 0 , "RegionID"));<o:p></o:p> aCommand.UpdatedRowSource = UpdateRowSource.None;<o:p></o:p> This command only accepts a single parameter as shown in the following code, which will execute the RegionDelete stored procedure; here we see an example of setting the parameter by name:<o:p></o:p> aCommand.Parameters["@RegionID"].Value= 999; aCommand.ExecuteNonQuery();<o:p></o:p> Calling a Stored Procedure that Returns Output Parameters Both of the previous examples execute stored procedures that return nothing. If a stored procedure includes output parameters, then these need to be defined within the .NET client so that they can be filled when the procedure returns.<o:p></o:p> The following example shows how to insert a record into the database, and return the primary key of that record to the caller.<o:p></o:p> Record Insertion The Region table only consists of a primary key (RegionID) and description field (RegionDescription). To insert a record, this numeric primary key needs to be generated, then a new row inserted into the database. I have chosen to simplify the primary key generation in this example by creating one within the stored procedure. The method used is exceedingly crude, which is why I have devoted a section to key generation later in the chapter. For now this primitive example will suffice:<o:p></o:p> CREATE PROCEDURE RegionInsert(@RegionDescription NCHAR(50),<o:p></o:p> @RegionID INTEGER OUTPUT) AS<o:p></o:p> <o:p></o:p> SET NOCOUNT OFF<o:p></o:p> SELECT @RegionID = MAX(RegionID)+ 1 FROM Region<o:p></o:p> INSERT INTO Region(RegionID, RegionDescription)<o:p></o:p> VALUES(@RegionID, @RegionDescription)<o:p></o:p> <o:p> </o:p> GO<o:p></o:p> The insert procedure creates a new Region record. As the primary key value is generated by the database itself, this value is returned as an output parameter from the procedure (@RegionID). This is sufficient for this simple example, but for a more complex table (especially one with default values), it is more common not to utilize output parameters, and instead select the entire inserted row and return this to the caller. The .NET classes can cope with either scenario.<o:p></o:p> SqlCommand aCommand = new SqlCommand("RegionInsert" , conn);<o:p></o:p> aCommand.CommandType = CommandType.StoredProcedure;<o:p></o:p> aCommand.Parameters.Add(new SqlParameter("@RegionDescription",<o:p></o:p> SqlDbType.NChar , 50 , "RegionDescription"));<o:p></o:p> aCommand.Parameters.Add(new SqlParameter("@RegionID",<o:p></o:p> SqlDbType.Int, 0 , ParameterDirection.Output , false,<o:p></o:p> 0 , 0 , "RegionID" , DataRowVersion.Default , null));<o:p></o:p> aCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;<o:p></o:p> Here, the definition of the parameters is much more complex. The second parameter, @RegionID, is defined to include its parameter direction, which in this example is Output. In addition to this flag, on the last line of the code, we utilize the UpdateRowSource enumeration to indicate that we expect to return data from this stored procedure via output parameters. This flag is mainly used when issuing stored procedure calls from a DataTable (covered later in the chapter).<o:p></o:p> Calling this stored procedure is similar to the previous examples, except in this instance we need to read the output parameter after executing the procedure:<o:p></o:p> aCommand.Parameters["@RegionDescription"].Value = "South West";<o:p></o:p> aCommand.ExecuteNonQuery(); int newRegionID =<o:p></o:p> (int)aCommand.Parameters["@RegionID"].Value;<o:p></o:p> After executing the command, we read the value of the @RegionID parameter and cast this to an integer.<o:p></o:p> You may be wondering what to do if the stored procedure you call returns output parameters and a set of rows. In this instance, define the parameters as appropriate, and rather than calling ExecuteNonQuery(), call one of the other methods (such as ExecuteReader()) that will permit you to traverse any record(s) returned.<o:p></o:p> Quick Data Access: The Data Reader A data reader is the simplest and fastest way of selecting some data from a data source, but also the least capable. You cannot directly instantiate a data reader object - an instance is returned from a SqlCommand or OleDbCommand object having called the ExecuteReader() method - from a SqlCommand object, a SqlDataReader object is returned, and from the OleDbCommand object, a OleDbDataReader object is returned.<o:p></o:p> The following code demonstrates how to select data from the Customers table in the Northwind database. The example connects to the database, selects a number of records, loops through these selected records and outputs them to the console.<o:p></o:p> This example utilizes the OLE DB provider as a brief respite from the SQL provider. In most cases the classes have a one-to-one correspondence with their SqlClient cousins, so for instance there is the OleDbConnection object, which is similar to the SqlConnection object used in the previous examples.<o:p></o:p> To execute commands against an OLE DB data source, the OleDbCommand class is used. The following code shows an example of executing a simple SQL statement and reading the records by returning an OleDbDataReader object.<o:p></o:p> The code for this example can be found in the Chapter 09\03_DataReader directory.<o:p></o:p> Note the second using directive below that makes available the OleDb classes:<o:p></o:p> using System;<o:p></o:p> using System.Data.OleDb;<o:p></o:p> All the data providers currently available are shipped within the same DLL, so it is only necessary to reference the System.Data.dll assembly to import all classes used in this section:<o:p></o:p> public class DataReaderExample {<o:p></o:p> public static void Main(string[] args) {<o:p></o:p> string source = "Provider=SQLOLEDB;" +<o:p></o:p> "server=(local)\\NetSDK;" +<o:p></o:p> "uid=QSUser;pwd=QSPassword;" +<o:p></o:p> "database=northwind";<o:p></o:p> string select = "SELECT ContactName,CompanyName FROM Customers";<o:p></o:p> <o:p></o:p> OleDbConnection conn = new OleDbConnection(source);<o:p></o:p> conn.Open();<o:p></o:p> <o:p></o:p> OleDbCommand cmd = new OleDbCommand(select , conn);<o:p></o:p> OleDbDataReader aReader = cmd.ExecuteReader();<o:p></o:p> <o:p></o:p> while(aReader.Read())<o:p></o:p> Console.WriteLine("'{0}' from {1}",<o:p></o:p> aReader.GetString(0) , aReader.GetString(1));<o:p></o:p> <o:p></o:p> aReader.Close();<o:p></o:p> conn.Close();<o:p></o:p> }<o:p></o:p> }<o:p></o:p> The preceding code includes many familiar aspects of C# covered in other chapters. To compile the example, issue the following command:<o:p></o:p> csc /t:exe /debug+ DataReaderExample.cs /r:System.Data.dll<o:p></o:p> The following code from the example above creates a new OLE DB .NET database connection, based on the source connection string:<o:p></o:p> OleDbConnection conn = new OleDbConnection(source);<o:p></o:p> conn.Open();<o:p></o:p> <o:p> </o:p> OleDbCommand cmd = new OleDbCommand(select, conn);<o:p></o:p> The third line creates a new OleDbCommand object, based on a particular SELECT statement, and the database connection to be used when the command is executed. When you have a valid command, you then need to execute it, which returns an initialized OleDbDataReader:<o:p></o:p> OleDbDataReader aReader = cmd.ExecuteReader();<o:p></o:p> An OleDbDataReader is a forward-only "connected" cursor. In other words, you can only traverse through the records returned in one direction, and the database connection used is kept open until the data reader has been closed.<o:p></o:p> Note: An OleDbDataReader keeps the database connection open until explicitly closed.<o:p></o:p> The OleDbDataReader class cannot be directly instantiated - it is always returned by a call to the ExecuteReader() method of the OleDbCommand class. Once you have an open data reader, there are various ways to access the data contained within the reader.<o:p></o:p> When the OleDbDataReader object is closed (via an explicit call to Close(), or the object being garbage collected), the underlying connection may also be closed, depending on which of the ExecuteReader() methods is called. If you call ExecuteReader() and pass CommandBehavior.CloseConnection, you can force the connection to be closed when the reader is closed.<o:p></o:p> The OleDbDataReader class has an indexer that permits access (although not type-safe access) to any field using the familiar array style syntax:<o:p></o:p> object o = aReader[0];<o:p></o:p> object o = aReader["CategoryID"];<o:p></o:p> Assuming that the CategoryID field was the first in the SELECT statement used to populate the reader, these two lines are functionally equivalent, although the second is slower than the first - I wrote a simple test application that performed a million iterations of accessing the same column from an open data reader, just to get some numbers that were big enough to read. I know - you probably don't read the same column a million times in a tight loop, but every (micro) second counts, and you might as well write code that is as close to optimal as possible.<o:p></o:p> Just for interest, the numeric indexer took on average 0.09 seconds for the million accesses, and the textual one 0.63 seconds. The reason for this difference is that the textual method looks up the column number internally from the schema and then accesses it using its ordinal. If you know this information beforehand you can do a better job of accessing the data.<o:p></o:p> So should you use the numeric indexer? Maybe, but there is a better way.<o:p></o:p> In addition to the indexers presented above, the OleDbDataReader has a set of type-safe methods that can be used to read columns. These are fairly self-explanatory, and all begin with Get. There are methods to read most types of data, such as GetInt32, GetFloat, GetGuid, and so on.<o:p></o:p> My million iterations using GetInt32 took 0.06 seconds. The overhead in the numeric indexer is incurred while getting the data type, calling the same code as GetInt32, then boxing (and in this instance unboxing) an integer. So, if you know the schema beforehand, are willing to use cryptic numbers instead of column names, and you can be bothered to use a type-safe function for each and every column access, you stand to gain somewhere in the region of a ten fold speed increase over using a textual column name (when selecting those million copies of the same column).<o:p></o:p> Needless to say, there is a tradeoff between maintainability and speed. If you must use numeric indexers, define constants within class scope for each of the columns that you will be accessing.<o:p></o:p> The code above can be used to select data from any OLE DB database; however, there are a number of SQL Server-specific classes that can be used with the obvious portability tradeoff.<o:p></o:p> The following example is the same as the above, except in this instance I have replaced the OLE DB provider and all references to OLE DB classes with their SQL counterparts. The changes in the code from the previous example have been highlighted. The example is in the 04_DataReaderSql directory:<o:p></o:p> using System;<o:p></o:p> using System.Data.SqlClient;<o:p></o:p> <o:p> </o:p> public class DataReaderSql {<o:p></o:p> public static int Main(string[] args) {<o:p></o:p> string source = "server=(local)\\NetSDK;" +<o:p></o:p> "uid=QSUser;pwd=QSPassword;" +<o:p></o:p> "database=northwind";<o:p></o:p> string select = "SELECT ContactName,CompanyName FROM Customers";<o:p></o:p> <o:p> </o:p> SqlConnection conn = new SqlConnection(source);<o:p></o:p> conn.Open();<o:p></o:p> <o:p> </o:p> SqlCommand cmd = new SqlCommand(select , conn);<o:p></o:p> SqlDataReader aReader = cmd.ExecuteReader();<o:p></o:p> <o:p></o:p> while(aReader.Read())<o:p></o:p> Console.WriteLine("'{0}' from {1}" ,<o:p></o:p> aReader.GetString(0),<o:p></o:p> aReader.GetString(1));<o:p></o:p> <o:p> </o:p> aReader.Close();<o:p></o:p> conn.Close();<o:p></o:p> <o:p> </o:p> return 0;<o:p></o:p> }<o:p></o:p> }<o:p></o:p> Notice the difference? If you're typing this in then do a global replace on OleDb with Sql, change the data source string and recompile. It's that easy!<o:p></o:p> I ran the same performance tests on the indexers for the SQL provider, and this time the numeric indexers were both exactly the same at 0.13 seconds for the million accesses, and the string-based indexer ran at about 0.65 seconds. You would expect the native SQL Server provider to be faster than going through OleDb, which up until I tested this section under the release version of .NET it was. I'm reasonably sure that this is an anomaly due to the simplistic test approach I am using (selecting the same value 1,000,000 times), and would expect a real-world test to show better performance from the managed SQL provider.<o:p></o:p> If you are interested in running the code on your own computer to see what performance is like, see the 05_IndexerTestingOleDb and 06_IndexerTestingSql examples included in the code download.<o:p></o:p> Managing Data and Relationships: The DataSet The DataSet class has been designed as an offline container of data. It has no notion of database connections. In fact, the data held within a DataSet doesn't necessarily need to have come from a database - it could just as easily be records from a CSV file, or points read from a measuring device.<o:p></o:p> A DataSet consists of a set of data tables, each of which will have a set of data columns and data rows. In addition to defining the data, you can also define links between tables within the DataSet. One common scenario would be when defining a parent-child relationship(commonly known as master/detail). One record in a table (say Order) links to many records in another table (say Order_Details). This relationship can be defined and navigated within the DataSet.<o:p></o:p>  DataSet<o:p></o:p> The following sections describe the classes that are used with a DataSet.<o:p></o:p> Data Tables A data table is very similar to a physical database table - it consists of a set of columns with particular properties, and may contain zero or more rows of data. A data table may also define a primary key, which can be one or more columns, and may also contain constraints on columns. The generic term for this information used throughout the rest of the chapter is schema.<o:p></o:p> There are several ways to define the schema for a particular data table (and indeed the DataSet as a whole). These are discussed after we introduce data columns and data rows.<o:p></o:p> The following diagram shows some of the objects that are accessible through the data table:<o:p></o:p>  DataTable<o:p></o:p> A DataTable object (and also a DataColumn) can have an arbitrary number of extended properties associated with it. This collection can be populated with any user-defined information pertaining to the object. For example, a given column might have an input mask used to validate the contents of that column - the typical example would be the US social security number. Extended properties are especially useful when the data is constructed within a middle tier and returned to the client for some processing. You could, for example, store validation criteria (such as min and max) for numeric columns.<o:p></o:p> When a data table has been populated, either by selecting data from a database, reading data from a file, or manually populating within code, the Rows collection will contain this retrieved data.<o:p></o:p> The Columns collection contains DataColumn instances that have been added to this table. These define the schema of the data, such as the data type, nullability, default values, and so on. The Constraints collection can be populated with either unique or primary key constraints.<o:p></o:p> One example of where the schema information for a data table is used is when displaying that data in a DataGrid (which we'll discuss at length in the next chapter). The DataGrid control uses properties such as the data type of the column to decide what control to use for that column. A bit field within the database will be displayed as a checkbox within the DataGrid. If a column is defined within the database schema as NOT NULL, then this fact will be stored within the DataColumn so that it can be tested when the user attempts to move off a row.<o:p></o:p> Data Columns A DataColumn object defines properties of a column within the DataTable, such as the data type of that column, whether the column is read only, and various other facts. A column can be created in code, or can be automatically generated by the runtime.<o:p></o:p> When creating a column, it is also useful to give it a name; otherwise the runtime will generate a name for you in the form Columnn where n is an incrementing number.<o:p></o:p> The data type of the column can be set either by supplying it in the constructor, or by setting the DataType property. Once you have loaded data into a data table you cannot alter the type of a column - you'll just receive an ArgumentException.<o:p></o:p> Data columns can be created to hold the following .NET Framework data types:<o:p></o:p>  .NET Framework data types<o:p></o:p> Once created, the next thing to do with a DataColumn object is to set up other properties, such as the nullability of the column or the default value. The following code fragment shows a few of the more common options to set on a DataColumn:<o:p></o:p> DataColumn customerID = new DataColumn("CustomerID" , typeof(int));<o:p></o:p> customerID.AllowDBNull = false;<o:p></o:p> customerID.ReadOnly = false;<o:p></o:p> customerID.AutoIncrement = true;<o:p></o:p> customerID.AutoIncrementSeed = 1000;<o:p></o:p> <o:p> </o:p> DataColumn name = new DataColumn("Name" , typeof(string));<o:p></o:p> name.AllowDBNull = false;<o:p></o:p> name.Unique = true;<o:p></o:p> The following properties can be set on a DataColumn:<o:p></o:p>  DataColumn<o:p></o:p> Data Rows This class makes up the other part of the DataTable class. The columns within a data table are defined in terms of the DataColumn class. The actual data within the table is accessed using the DataRow object. The following example shows how to access rows within a data table. The code for this example is available in the 07_SimpleDatasetSql directory. First, the connection details:<o:p></o:p> string source = "server=(local)\\NetSDK;" +<o:p></o:p> "uid=QSUser;pwd=QSPassword;" +<o:p></o:p> "database=northwind";<o:p></o:p> string select = "SELECT ContactName,CompanyName FROM Customers";<o:p></o:p> <o:p> </o:p> SqlConnection conn = new SqlConnection(source);<o:p></o:p> The following code introduces the SqlDataAdapter class, which is used to place data into a DataSet. The SqlDataAdapter will issue the SQL clause, and fill a table in the DataSet called Customers with the output of this following query. We'll be discussing the data adapter class further in the Populating a DataSet section.<o:p></o:p> SqlDataAdapter da = new SqlDataAdapter(select, conn);<o:p></o:p> DataSet ds = new DataSet();<o:p></o:p> da.Fill(ds , "Customers");<o:p></o:p> In the code below, you may notice the use of the DataRow indexer to access values from within that row. The value for a given column can be retrieved using one of the several overloaded indexers. These permit you to retrieve a value knowing the column number, name, or DataColumn:<o:p></o:p> foreach(DataRow row in ds.Tables["Customers"].Rows)<o:p></o:p> Console.WriteLine("'{0}' from {1}" , row[0] ,row[1]);<o:p></o:p> One of the most appealing aspects of a DataRow is that it is versioned. This permits you to receive various values for a given column in a particular row. The versions are described in the following table:<o:p></o:p>  DataRow<o:p></o:p> The version of a given column could be used in many ways. One example is when updating rows within the database, in which instance it is common to issue an SQL statement such as the following:<o:p></o:p> UPDATE Products SET Name = Column.Current WHERE<o:p></o:p> ProductID = xxx AND Name = Column.Original;<o:p></o:p> Obviously this code would never compile, but it shows one use for original and current values of a column within a row.<o:p></o:p> To retrieve a versioned value from the DataRow, use one of the indexer methods that accept a DataRowVersion value as a parameter. The following code snippet shows how to obtain all values of each column in a DataTable:<o:p></o:p> foreach (DataRow row in ds.Tables["Customers"].Rows ) {<o:p></o:p> foreach ( DataColumn dc in ds.Tables["Customers"].Columns ) {<o:p></o:p> Console.WriteLine("{0} Current = {1}",<o:p></o:p> dc.ColumnName , row[dc,DataRowVersion.Current]);<o:p></o:p> Console.WriteLine(" Default = {0}",<o:p></o:p> row[dc,DataRowVersion.Default]);<o:p></o:p> Console.WriteLine(" Original = {0}",<o:p></o:p> row[dc,DataRowVersion.Original]);<o:p></o:p> }<o:p></o:p> }<o:p></o:p> The whole row has a state flag called RowState, which can be used to determine what operation is needed on the row when it is persisted back to the database. The RowState property is set to keep track of all the changes made to the DataTable, such as adding new rows, deleting existing rows, and changing columns within the table. When the data is reconciled with the database, the row state flag is used to determine what SQL operations should occur. These flags are defined by the DataRowState enumeration:<o:p></o:p>  DataRowState enumeration<o:p></o:p> The state of the row depends also on what methods have been called on the row. The AcceptChanges() method is generally called after successfully updating the data source (that is, after persisting changes to the database).<o:p></o:p> The most common way to alter data in a DataRow is to use the indexer; however, if you have a number of changes to make you also need to consider the BeginEdit() and EndEdit() methods.<o:p></o:p> When an alteration is made to a column within a DataRow, the ColumnChanging event is raised on the row's DataTable. This permits you to override the ProposedValue property of the DataColumnChangeEventArgs class classes, and change it as required. This is one way of performing some data validation on column values. If you call BeginEdit() before making changes, the ColumnChanging event will not be raised. This permits you to make multiple changes and then call EndEdit() to persist these changes. If you wish to revert to the original values, call CancelEdit().<o:p></o:p> A DataRow can be linked in some way to other rows of data. This permits the creation of navigable links between rows, which is common in master/detail scenarios. The DataRow contains a GetChildRows() method that will return an array of associated rows from another table in the same DataSet as the current row. These are discussed in the Data Relationships section later in this chapter.<o:p></o:p> Schema Generation There are three ways to create the schema for a DataTable. These are:<o:p></o:p> Let the runtime do it for you<o:p></o:p> Write code to create the table(s)<o:p></o:p> Use the XML schema generator<o:p></o:p> Runtime Schema Generation The DataRow example shown earlier presented the following code for selecting data from a database and populating a DataSet:<o:p></o:p> SqlDataAdapter da = new SqlDataAdapter(select , conn);<o:p></o:p> DataSet ds = new DataSet();<o:p></o:p> da.Fill(ds , "Customers");<o:p></o:p> This is obviously easy to use, but it has a few drawbacks too. One example is that you have to make do with the column names selected from the database, which may be fine, but in certain instances you might want to rename a physical database column (say PKID) to something more user-friendly.<o:p></o:p> You could naturally rename columns within your SQL clause, as in SELECT PID AS PersonID FROM PersonTable; I would always recommend not renaming columns within SQL, as the only place a column really needs to have a "pretty" name is on screen.<o:p></o:p> Another potential problem with automated DataTable/DataColumn generation is that you have no control over the column types that the runtime chooses for your data. It does a fairly good job of deciding the correct data type for you, but as usual there are instances where you need more control. You might for example have defined an enumerated type for a given column, so as to simplify user code written against your class. If you accept the default column types that the runtime generates, the column will likely be an integer with a 32-bit range, as opposed to an enum with five options.<o:p></o:p> Lastly, and probably most problematic, is that when using automated table generation, you have no type-safe access to the data within the DataTable - you are at the mercy of indexers, which return instances of object rather than derived data types. If you like sprinkling your code with typecast expressions then skip the following sections.<o:p></o:p> Hand-Coded Schema Generating the code to create a DataTable, replete with associated DataColumns is fairly easy. The examples within this section will access the Products table from the Northwind database shown below. The code for this section is available in the 08_ManufacturedDataSet example.<o:p></o:p>  Hand-Coded Schema<o:p></o:p> The following code manufactures a DataTable, which corresponds to the above schema.<o:p></o:p> public static void ManufactureProductDataTable(DataSet ds) {<o:p></o:p> DataTable products = new DataTable("Products");<o:p></o:p> products.Columns.Add(new DataColumn("ProductID", typeof(int)));<o:p></o:p> products.Columns.Add(new DataColumn("ProductName", typeof(string)));<o:p></o:p> products.Columns.Add(new DataColumn("SupplierID", typeof(int)));<o:p></o:p> products.Columns.Add(new DataColumn("CategoryID", typeof(int)));<o:p></o:p> products.Columns.Add(new DataColumn("QuantityPerUnit", typeof(string)));<o:p></o:p> products.Columns.Add(new DataColumn("UnitPrice", typeof(decimal)));<o:p></o:p> products.Columns.Add(new DataColumn("UnitsInStock", typeof(short)));<o:p></o:p> products.Columns.Add(new DataColumn("UnitsOnOrder", typeof(short)));<o:p></o:p> products.Columns.Add(new DataColumn("ReorderLevel", typeof(short)));<o:p></o:p> products.Columns.Add(new DataColumn("Discontinued", typeof(bool)));<o:p></o:p> ds.Tables.Add(products);<o:p></o:p> }<o:p></o:p> You can alter the code in the DataRow example to utilize this newly generated table definition as follows:<o:p></o:p> string source = "server=localhost;" +<o:p></o:p> "integrated security=sspi;" +<o:p></o:p> "database=Northwind";<o:p></o:p> string select = "SELECT * FROM Products";<o:p></o:p> <o:p> </o:p> SqlConnection conn = new SqlConnection(source);<o:p></o:p> SqlDataAdapter cmd = new SqlDataAdapter(select, conn);<o:p></o:p> <o:p> </o:p> DataSet ds = new DataSet();<o:p></o:p> ManufactureProductDataTable(ds);<o:p></o:p> cmd.Fill(ds, "Products");<o:p></o:p> <o:p> </o:p> foreach(DataRow row in ds.Tables["Products"].Rows)<o:p></o:p> Console.WriteLine("'{0}' from {1}", row[0], row[1]);<o:p></o:p> The ManufactureProductDataTable() method creates a new DataTable, adds each column in turn, and finally appends this to the list of tables within the DataSet. The DataSet has an indexer that takes the name of the table and returns that DataTable to the caller.<o:p></o:p> The above example is still not really type-safe, as I'm using indexers on columns to retrieve the data. What would be better is a class (or set of classes) derived from DataSet, DataTable, and DataRow, that define type-safe accessors for tables, rows, and columns. You can generate this code yourself - it's not particularly tedious and you end up with truly type-safe data access classes.<o:p></o:p> If you don't like the sound of generating these type-safe classes yourself then help is at hand. The .NET Framework includes support for using XML schemas to define a DataSet, DataTable, and the other classes that we have touched on in this section. The XML Schemas section later in the chapter details this method; but first, we will look at relationships and constraints within a DataSet.<o:p></o:p> Data Relationships When writing an application, it is often necessary to obtain and cache various tables of information. The DataSet class is the container for this information. With regular OLE DB it was necessary to provide a strange SQL dialect to enforce hierarchical data relationships, and the provider itself was not without its own subtle quirks.<o:p></o:p> The DataSet class on the other hand has been designed from the start to establish relationships between data tables with ease. For the code in this section I decided to hand-generate and populate two tables with data. So, if you haven't got SQL Server or the NorthWind database to hand, you can run this example anyway. The code is available in the 09_DataRelationships directory:<o:p></o:p> DataSet ds = new DataSet("Relationships");<o:p></o:p> ds.Tables.Add(CreateBuildingTable());<o:p></o:p> ds.Tables.Add(CreateRoomTable());<o:p></o:p> ds.Relations.Add("Rooms", ds.Tables["Building"].Columns["BuildingID"],<o:p></o:p> ds.Tables["Room"].Columns["BuildingID"]);<o:p></o:p> The tables simply contain a primary key and name field, with the Room table having BuildingID as a foreign key.<o:p></o:p>  Tables<o:p></o:p> These tables were kept deliberately simple, as my fingers were wearing out at this point so I didn't want to add too many columns to either one.<o:p></o:p> I then added some default data to each table. Once that was done, I could then iterate through the buildings and rooms using the code below.<o:p></o:p> foreach(DataRow theBuilding in ds.Tables["Building"].Rows) {<o:p></o:p> DataRow[] children = theBuilding.GetChildRows("Rooms");<o:p></o:p> int roomCount = children.Length;<o:p></o:p> <o:p></o:p> Console.WriteLine("Building {0} contains {1} room{2}",<o:p></o:p> theBuilding["Name"], roomCount, roomCount > 1 ? "s" : "");<o:p></o:p> // Loop through the rooms<o:p></o:p> foreach(DataRow theRoom in children)<o:p></o:p> Console.WriteLine("Room: {0}", theRoom["Name"]);<o:p></o:p> }<o:p></o:p> The big difference between the DataSet and the old-style hierarchical Recordset object is in the way the relationship is presented. In a hierarchical Recordset, the relationship was presented as a pseudo-column within the row. This column itself was a Recordset that could be iterated through. Under ADO.NET, however, a relationship is traversed simply by calling the GetChildRows() method:<o:p></o:p> DataRow[] children = theBuilding.GetChildRows("Rooms");<o:p></o:p> This method has a number of forms, but the simple example shown above just uses the name of the relationship to traverse between parent and child rows. It returns an array of rows that can be updated as appropriate by using the indexers as shown in earlier examples.<o:p></o:p> What's more interesting with data relationships is that they can be traversed both ways. Not only can you go from a parent to the child rows, but you can also find a parent row (or rows) from a child record simply by using the ParentRelations property on the DataTable class. This property returns a DataRelationCollection, which can be indexed using the [] array syntax (for example, ParentRelations["Rooms"]), or as an alternative the GetParentRows() method can be called as shown below:<o:p></o:p> foreach(DataRow theRoom in ds.Tables["Room"].Rows) {<o:p></o:p> DataRow[] parents = theRoom.GetParentRows("Rooms");<o:p></o:p> <o:p></o:p> foreach(DataRow theBuilding in parents)<o:p></o:p> Console.WriteLine("Room {0} is contained in building {1}",<o:p></o:p> theRoom["Name"], theBuilding["Name"]);<o:p></o:p> }<o:p></o:p> There are two methods with various overrides available for retrieving the parent row(s) - GetParentRows() (which returns an array of zero or more rows), or GetParentRow() (which retrieves a single parent row given a relationship).<o:p></o:p> Data Constraints Changing the data type of columns created on the client is not the only thing a DataTable is good for. ADO.NET permits you to create a set of constraints on a column (or columns), which are then used to enforce rules within the data.<o:p></o:p> The runtime currently supports the following constraint types, embodied as classes in the System.Data namespace.<o:p></o:p>  Constraint Types<o:p></o:p> Setting a Primary Key : As is common for a table in a relational database, you can supply a primary key, which can be based on one or more columns from the DataTable.<o:p></o:p> The code below creates a primary key for the Products table, whose schema we constructed by hand earlier, and can be found in the 08_ManufactureDataSet folder.<o:p></o:p> Note that a primary key on a table is just one form of constraint. When a primary key is added to a DataTable, the runtime also generates a unique constraint over the key column(s). This is because there isn't actually a constraint type of PrimaryKey - a primary key is simply a unique constraint over one or more columns.<o:p></o:p> public static void ManufacturePrimaryKey(DataTable dt) {<o:p></o:p> DataColumn[] pk = new DataColumn[1];<o:p></o:p> pk[0] = dt.Columns["ProductID"];<o:p></o:p> dt.PrimaryKey = pk;<o:p></o:p> }<o:p></o:p> As a primary key may contain several columns, it is typed as an array of DataColumns. A table's primary key can be set to those columns simply by assigning an array of columns to the property.<o:p></o:p> To check the constraints for a table, you can iterate through the ConstraintCollection. For the auto- generated constraint produced by the above code, the name of the constraint is Constraint1. That's not a very useful name, so to avoid this problem it is always best to create the constraint in code first, then define which column(s) make up the primary key, as we shall do now.<o:p></o:p> As a long time database programmer, I find named constraints much simpler to understand, as most databases produce cryptic names for constraints, rather than something simple and legible. The code below names the constraint before creating the primary key:<o:p></o:p> DataColumn[] pk = new DataColumn[1];<o:p></o:p> pk[0] = dt.Columns["ProductID"];<o:p></o:p> dt.Constraints.Add(new UniqueConstraint("PK_Products", pk[0]));<o:p></o:p> dt.PrimaryKey = pk;<o:p></o:p> Unique constraints can be applied to as many columns as you wish.<o:p></o:p> Setting a Foreign Key In addition to unique constraints, a DataTable may also contain foreign key constraints. These are primarily used to enforce master/detail relationships, but can also be used to replicate columns between tables if you set the constraint up correctly. A master/detail relationship is one where there is commonly one parent record (say an order) and many child records (order lines), linked by the primary key of the parent record.<o:p></o:p> A foreign key constraint can only operate over tables within the same DataSet, so the following example utilizes the Categories table from the Northwind database, and assigns a constraint between it and the Products table.<o:p></o:p>  Categories - Products Tables<o:p></o:p> The first step is to generate a new data table for the Categories table. The 08_ManufactureDataSet example includes this code:<o:p></o:p> DataTable categories = new DataTable("Categories");<o:p></o:p> categories.Columns.Add(new DataColumn("CategoryID", typeof(int)));<o:p></o:p> categories.Columns.Add(new DataColumn("CategoryName", typeof(string)));<o:p></o:p> categories.Columns.Add(new DataColumn("Description", typeof(string)));<o:p></o:p> categories.Constraints.Add(new UniqueConstraint("PK_Categories",<o:p></o:p> categories.Columns["CategoryID"]));<o:p></o:p> categories.PrimaryKey = new DataColumn[1] {<o:p></o:p> categories.Columns["CategoryID"]<o:p></o:p> };<o:p></o:p> The last line of the above code creates the primary key for the Categories table. The primary key in this instance is a single column; however, it is possible to generate a key over multiple columns using the array syntax shown.<o:p></o:p> Then I need to create the constraint between the two tables:<o:p></o:p> DataColumn parent = ds.Tables["Categories"].Columns["CategoryID"];<o:p></o:p> DataColumn child = ds.Tables["Products"].Columns["CategoryID"];<o:p></o:p> ForeignKeyConstraint fk = new ForeignKeyConstraint("FK_Product_CategoryID",<o:p></o:p> parent, child); fk.UpdateRule = Rule.Cascade;<o:p></o:p> fk.DeleteRule = Rule.SetNull;<o:p></o:p> ds.Tables["Products"].Constraints.Add(fk);<o:p></o:p> This constraint applies to the link between Categories.CategoryID and Products.CategoryID. There are four different constructors for ForeignKeyConstraint, but again I would suggest using those that permit you to name the constraint.<o:p></o:p> Setting Update and Delete Constaints In addition to defining the fact that there is some type of constraint between parent and child tables, you can define what should happen when a column in the constraint is updated.<o:p></o:p> The above example sets the update rule and the delete rule. These rules are used when an action occurs to a column (or row) within the parent table, and the rule is used to decide what should happen to row(s) within the child table that could be affected. There are four different rules that can be applied through the Rule enumeration:<o:p></o:p> Cascade - If the parent key was updated then copy the new key value to all child records. If the parent record was deleted, delete the child records also. This is the default option.<o:p></o:p> None - No action whatsoever. This option will leave orphaned rows within the child data table.<o:p></o:p> SetDefault - Each child record affected has the foreign key column(s) set to their default value, if one has been defined.<o:p></o:p> SetNull - All child rows have the key column(s) set to DBNull. (Following on from the naming convention that Microsoft uses, this should really be SetDBNull).<o:p></o:p> Note: Constraints are only enforced within a DataSet if the EnforceConstraints property of the DataSet is true.<o:p></o:p> I have covered the main classes that make up the constituent parts of the DataSet, and shown how to manually generate each of these classes in code. There is another way to define a DataTable, DataRow, DataColumn, DataRelation, and Constraint - by using the XML schema file(s) and the XSD tool that ships with .NET. The following section describes how to set up a simple schema and generate type-safe classes to access your data.<o:p></o:p> XML Schemas XML is firmly entrenched into ADO.NET - indeed, the remoting format for passing data between objects is now XML. With the .NET runtime, it is now possible to describe a DataTable within an XML schema definition file (XSD). What's more, you can define an entire DataSet, with a number of DataTables, a set of relationships between these tables, and include various other details to fully describe the data.<o:p></o:p> When you have defined an XSD file, there is a new tool in the runtime that will convert this schema to the corresponding data access class(es), such as the type-safe product DataTable class shown above. In this section we'll start with a simple XSD file that describes the same information as the Products sample previously shown, and then extend this to include some extra functionality. This file is Products.xsd, found in the 10_XSD_DataSet folder:<o:p></o:p> <?xml version="1.0" encoding="utf-8" ?><o:p></o:p> <xs:schema id="Products" targetNamespace="http://tempuri.org/XMLSchema1.xsd"<o:p></o:p> elementFormDefault="qualified" xmlns="http://tempuri.org/XMLSchema1.xsd"<o:p></o:p> xmlns:mstns="http://tempuri.org/XMLSchema1.xsd"<o:p></o:p> xmlns:xsd="http://www.w3.org/2001/XMLSchema"<o:p></o:p> xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"><o:p></o:p> <xs:element name="Product"><o:p></o:p> <xs:complexType><o:p></o:p> <xs:sequence><o:p></o:p> <xs:element name="ProductID" type="xs:int" /><o:p></o:p> <xs:element name="ProductName" type="xs:string" /><o:p></o:p> <xs:element name="SupplierID" type="xs:int" minOccurs="0" /><o:p></o:p> <xs:element name="CategoryID" type="xs:int" minOccurs="0" /><o:p></o:p> <xs:element name="QuantityPerUnit" type="xs:string" minOccurs="0" /><o:p></o:p> <xs:element name="UnitPrice" type="xs:decimal" minOccurs="0" /><o:p></o:p> <xs:element name="UnitsInStock" type="xs:short" minOccurs="0" /><o:p></o:p> <xs:element name="UnitsOnOrder" type="xs:short" minOccurs="0" /><o:p></o:p> <xs:element name="ReorderLevel" type="xs:short" minOccurs="0" /><o:p></o:p> <xs:element name="Discontinued" type="xs:boolean" /><o:p></o:p> </xs:sequence><o:p></o:p> </xs:complexType><o:p></o:p> </xs:element><o:p></o:p> </xs:schema><o:p></o:p> We'll take a closer look at some of the options within this file in Chapter 11; for now, this file basically defines a schema with the id attribute set to Products. A complex type called Product is defined, which contains a number of elements, one for each of the fields within the Products table.<o:p></o:p> These items map onto data classes as follows. The Products schema maps to a class derived from DataSet. The Product complex type maps to a class derived from DataTable. Each sub-element maps to a class derived from DataColumn. The collection of all columns maps onto a class derived from DataRow.<o:p></o:p> Thankfully there is a tool within the .NET Framework that will produce all of the code for these classes given only the input XSD file. Because its sole job in life is to perform various functions on XSD files, the tool itself is called XSD.EXE.<o:p></o:p> Generating Code with XSD Assuming you save the above file as Product.xsd, you would convert the file into code by issuing the following command in a command prompt:<o:p></o:p> xsd Product.xsd /d<o:p></o:p> This creates the file Product.cs.<o:p></o:p> There are various switches that can be used with XSD to alter the output generated. Some of the more commonly used are shown in the table below.<o:p></o:p>  XSD Switches<o:p></o:p> An abridged version of the output from XSD for the Products schema is shown below. I've removed some of the less necessary code to concentrate on the most important aspects, and done some reformatting so that it will fit within the confines of a couple of pages. To see the complete output, run XSD.EXE on the Products schema (or one of your own making) and take a look at the .cs file generated. The example includes the entire sourcecode plus the Product.xsd file, and can be found in the 10_XSD_DataSet directory:<o:p></o:p> //------------------------------------------------------------------------------<o:p></o:p> // <autogenerated><o:p></o:p> // This code was generated by a tool.<o:p></o:p> // Runtime Version: 1.0.3512.0<o:p></o:p> //<o:p></o:p> // Changes to this file may cause incorrect behavior and will be lost if<o:p></o:p> // the code is regenerated.<o:p></o:p> // </autogenerated><o:p></o:p> //------------------------------------------------------------------------------<o:p></o:p> <o:p> </o:p> //<o:p></o:p> // This source code was auto-generated by xsd, Version=1.0.3512.0.<o:p></o:p> //<o:p></o:p> <o:p> </o:p> using System;<o:p></o:p> using System.Data;<o:p></o:p> using System.Xml;<o:p></o:p> using System.Runtime.Serialization;<o:p></o:p> <o:p> </o:p> [Serializable()]<o:p></o:p> [System.ComponentModel.DesignerCategoryAttribute("code")]<o:p></o:p> [System.Diagnostics.DebuggerStepThrough()]<o:p></o:p> [System.ComponentModel.ToolboxItem(true)]<o:p></o:p> public class Products : DataSet {<o:p></o:p> private ProductDataTable tableProduct;<o:p></o:p> public Products()<o:p></o:p> public ProductDataTable Product<o:p></o:p> public override DataSet Clone()<o:p></o:p> public delegate void ProductRowChangeEventHandler(object sender,<o:p></o:p> ProductRowChangeEvent e);<o:p></o:p> <o:p></o:p> [System.Diagnostics.DebuggerStepThrough()]<o:p></o:p> public class ProductDataTable : DataTable, System.Collections.IEnumerable<o:p></o:p> <o:p></o:p> [System.Diagnostics.DebuggerStepThrough()]<o:p></o:p> public class ProductRow : DataRow<o:p></o:p> }<o:p></o:p> I have taken some liberties with this sourcecode, as I have split it into three sections and removed any protected and private members so that we can concentrate on the public interface. The emboldened ProductDataTable and ProductRow definitions show the positions of two nested classes, which we're going to implement next. We'll look at the code for these after a brief explanation of the DataSet derived class.<o:p></o:p> The Products() constructor calls a private method, InitClass(), which constructs an instance of the DataTable class derived class ProductDataTable, and adds the table to the Tables collection of the DataSet. The Products data table can be accessed by the following code:<o:p></o:p> DataSet ds = new Products();<o:p></o:p> DataTable products = ds.Tables["Products"];<o:p></o:p> Or, more simply by using the property Product, available on the derived DataSet object:<o:p></o:p> DataTable products = ds.Product;<o:p></o:p> As the Product property is strongly typed, you could naturally use ProductDataTable rather than the DataTable reference I showed above.<o:p></o:p> The ProductDataTable class includes far more code:<o:p></o:p> [System.Diagnostics.DebuggerStepThrough()]<o:p></o:p> public class ProductDataTable : DataTable,<o:p></o:p> System.Collections.IEnumerable {<o:p></o:p> private DataColumn columnProductID;<o:p></o:p> private DataColumn columnProductName;<o:p></o:p> private DataColumn columnSupplierID;<o:p></o:p> private DataColumn columnCategoryID;<o:p></o:p> private DataColumn columnQuantityPerUnit;<o:p></o:p> private DataColumn columnUnitPrice;<o:p></o:p> private DataColumn columnUnitsInStock;<o:p></o:p> private DataColumn columnUnitsOnOrder;<o:p></o:p> private DataColumn columnReorderLevel;<o:p></o:p> private DataColumn columnDiscontinued;<o:p></o:p> <o:p></o:p> internal ProductDataTable() : base("Product") {<o:p></o:p> this.InitClass();<o:p></o:p> }<o:p></o:p> The ProductDataTable class, derived from DataTable and implementing the IEnumerable interface, defines a private DataColumn instance for each of the columns within the table. These are initialized again from the constructor by calling the private InitClass() member. Each column is given an internal accessor, which the DataRow class described later uses.<o:p></o:p> [System.ComponentModel.Browsable(false)]<o:p></o:p> public int Count {<o:p></o:p> get {<o:p></o:p> return this.Rows.Count;<o:p></o:p> }<o:p></o:p> }<o:p></o:p> internal DataColumn ProductIDColumn {<o:p></o:p> get {<o:p></o:p> return this.columnProductID;<o:p></o:p> }<o:p></o:p> }<o:p></o:p> <o:p> </o:p> // Other row accessors removed for clarity - there is one for each of the columns<o:p></o:p> Adding rows to the table is taken care of by the two overloaded (and significantly different, except unfortunately by name) AddProductRow() methods. The first takes an already constructed DataRow and returns a void. The latter takes a set of values, one for each of the columns in the DataTable, constructs a new row, sets the values within this new row, adds the row to the DataTable and returns the row to the caller. Such widely different functions shouldn't really have the same name, in my opinion.<o:p></o:p> public void AddProductRow(ProductRow row) {<o:p></o:p> this.Rows.Add(row);<o:p></o:p> }<o:p></o:p> <o:p> </o:p> public ProductRow AddProductRow(string ProductName, int SupplierID,<o:p></o:p> int CategoryID, string QuantityPerUnit, System.Decimal UnitPrice,<o:p></o:p> short UnitsInStock, short UnitsOnOrder, short ReorderLevel,<o:p></o:p> bool Discontinued) {<o:p></o:p> <o:p> </o:p> ProductRow rowProductRow = ((ProductRow)(this.NewRow()));<o:p></o:p> rowProductRow.ItemArray = new object[] {<o:p></o:p> null, ProductName, SupplierID, CategoryID, QuantityPerUnit, <o:p></o:p> UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued<o:p></o:p> };<o:p></o:p> <o:p></o:p> this.Rows.Add(rowProductRow);<o:p></o:p> return rowProductRow;<o:p></o:p> }<o:p></o:p> Just like the InitClass() member in the DataSet derived class, which added the table into the DataSet, the InitClass() member in ProductDataTable adds in columns to the DataTable. Each column's properties are set as appropriate, and the column is then appended to the columns collection.<o:p></o:p> private void InitClass() {<o:p></o:p> this.columnProductID = new DataColumn "ProductID", typeof(int),<o:p></o:p> null, System.Data.MappingType.Element);<o:p></o:p> this.Columns.Add(this.columnProductID);<o:p></o:p> <o:p></o:p> // Other columns removed for clarity<o:p></o:p> <o:p> </o:p> this.columnProductID.AutoIncrement = true;<o:p></o:p> this.columnProductID.AllowDBNull = false;<o:p></o:p> this.columnProductID.ReadOnly = true;<o:p></o:p> this.columnProductName.AllowDBNull = false;<o:p></o:p> this.columnDiscontinued.AllowDBNull = false;<o:p></o:p> }<o:p></o:p> <o:p> </o:p> public ProductRow NewProductRow() {<o:p></o:p> return ((ProductRow)(this.NewRow()));<o:p></o:p> }<o:p></o:p> The last method I want to discuss, NewRowFromBuilder(), is called internally from the DataTable's NewRow() method. Here it creates a new strongly typed row. The DataRowBuilder instance is created by the DataTable, and its members are only accessible within the System.Data assembly.<o:p></o:p> protected override DataRow NewRowFromBuilder(DataRowBuilder builder) {<o:p></o:p> return new ProductRow(builder);<o:p></o:p> }<o:p></o:p> The last class to discuss is the ProductRow class, derived from DataRow. This class is used to provide type- safe access to all fields in the data table. It wraps the storage for a particular row, and provides members to read (and write) each of the fields in the table.<o:p></o:p> In addition, for each nullable field, there are functions to set the field to null, and check if the field is null. The example below shows the functions for the SupplierID column:<o:p></o:p> [System.Diagnostics.DebuggerStepThrough()]<o:p></o:p> public class ProductRow : DataRow {<o:p></o:p> <o:p> </o:p> private ProductDataTable tableProduct;<o:p></o:p> <o:p> </o:p> internal ProductRow(DataRowBuilder rb) : base(rb) {<o:p></o:p> this.tableProduct = ((ProductDataTable)(this.Table));<o:p></o:p> }<o:p></o:p> <o:p> </o:p> public int ProductID {<o:p></o:p> get {<o:p></o:p> return ((int)(this[this.tableProduct.ProductIDColumn]));<o:p></o:p> }<o:p></o:p> set {<o:p></o:p> this[this.tableProduct.ProductIDColumn] = value;<o:p></o:p> }<o:p></o:p> }<o:p></o:p> <o:p></o:p> // Other column accessors/mutators removed for clarity<o:p></o:p> <o:p></o:p> public bool IsSupplierIDNull() {<o:p></o:p> return this.IsNull(this.tableProduct.SupplierIDColumn);<o:p></o:p> }<o:p></o:p> <o:p></o:p> public void SetSupplierIDNull() {<o:p></o:p> this[this.tableProduct.SupplierIDColumn] = System.Convert.DBNull;<o:p></o:p> }<o:p></o:p> }<o:p></o:p> Now that the sourcecode for these data access classes has been generated by XSD.EXE, we can incorporate the classes into code. The following code utilizes these classes to retrieve data from the Products table and display that data to the console:<o:p></o:p> using System;<o:p></o:p> using System.Data;<o:p></o:p> using System.Data.SqlClient;<o:p></o:p> <o:p> </o:p> public class XSD_DataSet {<o:p></o:p> public static void Main(){<o:p></o:p> string source = "server=(local)\\NetSDK;" +<o:p></o:p> "uid=QSUser;pwd=QSPassword;" +<o:p></o:p> "database=northwind";<o:p></o:p> string select = "SELECT * FROM Products";<o:p></o:p> <o:p></o:p> SqlConnection conn = new SqlConnection(source);<o:p></o:p> SqlDataAdapter da = new SqlDataAdapter(select , conn);<o:p></o:p> Products ds = new Products();<o:p></o:p> <o:p></o:p> da.Fill(ds , "Product");<o:p></o:p> foreach(Products.ProductRow row in ds.Product )<o:p></o:p> Console.WriteLine("'{0}' from {1}",<o:p></o:p> row.ProductID , row.ProductName);<o:p></o:p> }<o:p></o:p> }<o:p></o:p> The main areas of interest are highlighted. The output of the XSD file contains a class derived from DataSet, Products, which is created and then filled by the use of the data adapter. The foreach statement utilizes the strongly-typed ProductRow and also the Product property, which returns the Product data table.<o:p></o:p> To compile this example, issue the following commands:<o:p></o:p> xsd product.xsd /d and csc /recurse:*.cs<o:p></o:p> The first generates the Products.cs file from the Products.XSD schema, and then the csc command utilizes the /recurse:*.cs parameter to go through all files with the extension .cs and add these to the resulting assembly.<o:p></o:p> Populating a DataSet Once you have fully defined the schema of your data set, replete with DataTables, DataColumns, Constraints, and whatever else was necessary, you need to be able to populate the DataSet with some information. There are two main ways to read data from an external source and insert it into the DataSet:<o:p></o:p> Use a data adapter<o:p></o:p> Read XML into the DataSet<o:p></o:p> · Populating a DataSet Using a DataAdapter The section on data rows briefly introduced the SqlDataAdapter class, as shown in the following code:<o:p></o:p> · string select = "SELECT ContactName,CompanyName FROM Customers";<o:p></o:p> · <o:p> </o:p> · SqlConnection conn = new SqlConnection(source);<o:p></o:p> · SqlDataAdapter da = new SqlDataAdapter(select , conn);<o:p></o:p> · DataSet ds = new DataSet();<o:p></o:p> · <o:p> </o:p> · da.Fill(ds , "Customers");<o:p></o:p> · The two highlighted lines show the SqlDataAdapter in use - the OleDbDataAdapter is again virtually identical in functionality to the Sql equivalent.<o:p></o:p> · The SqlDataAdapter and OleDbDataAdapter are two of the classes that are derived from a common base class rather than a set of interfaces, as are most of the other SqlClient- or OleDb- specific classes. The inheritance hierarchy is shown below:<o:p></o:p> · System.Data.Common.DataAdapter<o:p></o:p> · System.Data.Common.DbDataAdapter<o:p></o:p> · System.Data.OleDb.OleDbDataAdapter<o:p></o:p> · System.Data.SqlClient.SqlDataAdapter<o:p></o:p> · In order to retrieve data into a DataSet, it is necessary to have some form of command that is executed to select that data. The command in question could be a SQL SELECT statement, a call to a stored procedure, or for the OLE DB provider, a TableDirect command. The example above utilizes one of the constructors available on SqlDataAdapter that converts the passed SQL SELECT statement into a SqlCommand, and issues this when the Fill() method is called on the adapter.<o:p></o:p> · Going back to the example on stored procedures earlier in the chapter, I defined stored procedures to INSERT, UPDATE, and DELETE, but didn't present a procedure to SELECT data. We'll fill that gap in this next section, and show how you can call a stored procedure from an SqlDataAdapter to populate data in a DataSet.<o:p></o:p> · Using a Stored Procedure in a DataAdapter First off we need to define a stored procedure and install it into the database. The code for this example is available in the 11_DataAdapter directory. The stored procedure to SELECT data is as follows:<o:p></o:p> · CREATE PROCEDURE RegionSelect AS<o:p></o:p> · SET NOCOUNT OFF<o:p></o:p> · SELECT * FROM Region<o:p></o:p> · GO<o:p></o:p> · Again this example is fairly trivial, and not really worthy of a stored procedure, as a direct SQL statement would normally suffice. This stored procedure can be typed directly into the SQL Server Query Analyzer, or you can run the StoredProc.sql file that is provided for use by this example.<o:p></o:p> · Next, we need to define a SqlCommand that will execute this stored procedure. Again the code is very simple, and most of it was already presented in the earlier section on issuing commands:<o:p></o:p> · private static SqlCommand GenerateSelectCommand(SqlConnection conn ) {<o:p></o:p> · SqlCommand aCommand = new SqlCommand("RegionSelect" , conn);<o:p></o:p> · aCommand.CommandType = CommandType.StoredProcedure;<o:p></o:p> · aCommand.UpdatedRowSource = UpdateRowSource.None;<o:p></o:p> · return aCommand;<o:p></o:p> · }<o:p></o:p> · This method generates the SqlCommand that will call the RegionSelect procedure when executed. All that remains is to hook this command up to a SqlDataAdapter, and call the Fill() method:<o:p></o:p> · DataSet ds = new DataSet();<o:p></o:p> · <o:p> </o:p> · // Create a data adapter to fill the DataSet<o:p></o:p> · SqlDataAdapter da = new SqlDataAdapter();<o:p></o:p> · <o:p> </o:p> · // Set the data adapter's select command<o:p></o:p> · da.SelectCommand = GenerateSelectCommand (conn);<o:p></o:p> · da.Fill(ds , "Region");<o:p></o:p> · Here I create a new SqlDataAdapter, assign the generated SqlCommand to the SelectCommand property of the data adapter, and then call Fill(), which will execute the stored procedure and insert all rows returned into the Region DataTable (which in this instance is generated by the runtime).<o:p></o:p> · There's more to a data adapter than just selecting data by issuing a command. In the Persisting DataSet Changes section I will explore the rest of the facilities of the data adapter.<o:p></o:p> · Populating a DataSet from XML In addition to generating the schema for a given DataSet and associated tables and so on, a DataSet can read and write data in native XML, such as a file on disk, a stream, or a text reader.<o:p></o:p> · To load XML into a DataSet, simply call one of the ReadXML() methods, such as that shown below, which will read data from a disk file:<o:p></o:p> · DataSet ds = new DataSet();<o:p></o:p> · ds.ReadXml(".\\MyData.xml");<o:p></o:p> · The ReadXml() method attempts to load any inline schema information from the input XML, and if found, uses this schema in the validation of any data loaded from that file. If no inline schema is found then the DataSet will extend its internal structure as data is loaded. This is similar to the behavior of Fill() in the previous example, which retrieves the data and constructs a DataTable based on the data selected.<o:p></o:p> · Persisting DataSet Changes After editing data within a DataSet, it is probably necessary to persist these changes. The most common example would be selecting data from a database, displaying it to the user, and returning those updates back to the database.<o:p></o:p> · In a less "connected" application, changes might be persisted to an XML file, transported to a middle-tier application server, and then processed to update several data sources.<o:p></o:p> · A DataSet can be used for either of these examples, and what's more it's really easy to do.<o:p></o:p> · Updating with Data Adapters In addition to the SelectCommand that an SqlDataAdapter most likely includes, you can also define an InsertCommand, UpdateCommand, and DeleteCommand. As these names imply, these objects are instances of SqlCommand (or OleDbCommand for the OleDbDataAdapter), so any of these commands could be straight SQL or a stored procedure.<o:p></o:p> · With this level of flexibility, you are free to tune the application by judicious use of stored procedures for frequently used commands (say SELECT and INSERT), and use straight SQL for less commonly used commands such as DELETE.<o:p></o:p> · For the example in this section I have resurrected the stored procedure code from the Calling Stored Procedures section for inserting, updating, and deleting Region records, coupled these with the RegionSelect procedure written above, and produced an example utilizes each of these commands to retrieve and update data in a DataSet. The main body of code is shown below; the full sourcecode is available in the 12_DataAdapter2 directory.<o:p></o:p> · Inserting a New Row There are two ways to add a new row to a DataTable. The first way is to call the NewRow() method, which returns a blank row that you then populate and add to the Rows collection, as follows:<o:p></o:p> · DataRow r = ds.Tables["Region"].NewRow();<o:p></o:p> · r["RegionID"]=999;<o:p></o:p> · r["RegionDescription"]="North West";<o:p></o:p> · ds.Tables["Region"].Rows.Add(r);<o:p></o:p> · The second way to add a new row would be to pass an array of data to the Rows.Add() method as shown in the following code:<o:p></o:p> · DataRow r = ds.Tables["Region"].Rows.Add (new object [] {<o:p></o:p> · 999 , "North West"<o:p></o:p> · });<o:p></o:p> · Each new row within the DataTable will have its RowState set to Added. The example dumps out the records before each change is made to the database, so after adding the following row (either way) to the DataTable, the rows will look something like the following. Note that the right-hand column shows the row state.<o:p></o:p> · New row pending inserting into database<o:p></o:p> · 1 Eastern Unchanged<o:p></o:p> · 2 Western Unchanged<o:p></o:p> · 3 Northern Unchanged<o:p></o:p> · 4 Southern Unchanged<o:p></o:p> · 999 North West Added<o:p></o:p> · To update the database from the DataAdapter, call one of the Update() methods as shown below:<o:p></o:p> · da.Update(ds , "Region");<o:p></o:p> · For the new row within the DataTable, this will execute the stored procedure (in this instance RegionInsert), and subsequently I dump the records in the DataTable again.<o:p></o:p> New row updated and new RegionID assigned by database<o:p></o:p> 1 Eastern Unchanged<o:p></o:p> 2 Western Unchanged<o:p></o:p> 3 Northern Unchanged<o:p></o:p> 4 Southern Unchanged<o:p></o:p> 5 North West Unchanged<o:p></o:p> Look at the last row in the DataTable. I had set the RegionID in code to 999, but after executing the RegionInsert stored procedure the value has been changed to 5. This is intentional - the database will often generate primary keys for you, and the updated data in the DataTable is due to the fact that the SqlCommand definition within our sourcecode has the UpdatedRowSource property set to UpdateRowSource.OutputParameters:<o:p></o:p> SqlCommand aCommand = new SqlCommand("RegionInsert", conn);<o:p></o:p> aCommand.CommandType = CommandType.StoredProcedure;<o:p></o:p> aCommand.Parameters.Add(new SqlParameter("@RegionDescription",<o:p></o:p> SqlDbType.NChar , 50 , "RegionDescription"));<o:p></o:p> aCommand.Parameters.Add(new SqlParameter("@RegionID",<o:p></o:p> SqlDbType.Int, 0 , ParameterDirection.Output , false , 0 , 0 ,<o:p></o:p> "RegionID" ,<o:p></o:p> // Defines the SOURCE column<o:p></o:p> DataRowVersion.Default , null));<o:p></o:p> aCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;<o:p></o:p> What this means is that whenever a data adapter issues this command, the output parameters should be mapped back to the source of the row, which in this instance was a row in a DataTable. The flag states what data should be updated - the stored procedure has an output parameter that is mapped back into the DataRow. The column it applies to is RegionID, as this is defined within the command definition.<o:p></o:p> The values for UpdateRowSource are as follows:<o:p></o:p>  UpdateRowSource<o:p></o:p>  UpdateRowSource<o:p></o:p> Updating an Existing Row Updating a row that already exists within the DataTable is just a case of utilizing the DataRow class's indexer with either a column name or column number, as shown in the following code:<o:p></o:p> r["RegionDescription"]="North West England";<o:p></o:p> r[1] = "North East England";<o:p></o:p> Both of these statements are equivalent (in this example):<o:p></o:p> Changed RegionID 5 description<o:p></o:p> 1 Eastern Unchanged<o:p></o:p> 2 Western Unchanged<o:p></o:p> 3 Northern Unchanged<o:p></o:p> 4 Southern Unchanged<o:p></o:p> 5 North West England Modified<o:p></o:p> Prior to updating the database, the row updated has its state set to Modified as shown above.<o:p></o:p> Deleting a Row :<o:p></o:p> Deleting a row is a matter of calling the Delete() method:<o:p></o:p> r.Delete();<o:p></o:p> A deleted row has its row state set to Deleted, but you cannot read columns from the deleted DataRow as these are no longer valid. When the adaptor's Update() method is called, all deleted rows will utilize the DeleteCommand, which in this instance executes the RegionDelete stored procedure.<o:p></o:p> Writing XML Output As you have seen already, the DataSet has great support for defining its schema in XML, and as you can read data from an XML document, you can also write data to an XML document.<o:p></o:p> The DataSet.WriteXml() method permits you to output various parts of the data stored within the DataSet. You can elect to output just the data, or the data and the schema. The following code shows an example of both for the Region example shown above:<o:p></o:p> ds.WriteXml(".\\WithoutSchema.xml");<o:p></o:p> ds.WriteXml(".\\WithSchema.xml" , XmlWriteMode.WriteSchema);<o:p></o:p> The first file, WithoutSchema.xml is shown below:<o:p></o:p> <?xml version="1.0" standalone="yes"?><o:p></o:p> <NewDataSet><o:p></o:p> <Region><o:p></o:p> <RegionID>1</RegionID><o:p></o:p> <RegionDescription>Eastern</RegionDescription><o:p></o:p> </Region><o:p></o:p> <Region><o:p></o:p> <RegionID>2</RegionID><o:p></o:p> <RegionDescription>Western</RegionDescription><o:p></o:p> </Region><o:p></o:p> <Region><o:p></o:p> <RegionID>3</RegionID><o:p></o:p> <RegionDescription>Northern</RegionDescription><o:p></o:p> </Region><o:p></o:p> <Region><o:p></o:p> <RegionID>4</RegionID><o:p></o:p> <RegionDescription>Southern</RegionDescription><o:p></o:p> </Region><o:p></o:p> </NewDataSet><o:p></o:p> The closing tag on RegionDescription is over to the right of the page as the database column is defined as NCHAR(50), which is a 50 character string padded with spaces.<o:p></o:p> The output produced in the WithSchema.xml file includes, not surprisingly, the XML schema for the DataSet as well as the data itself:<o:p></o:p> <?xml version="1.0" standalone="yes"?><o:p></o:p> <NewDataSet><o:p></o:p> <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema"<o:p></o:p> xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"><o:p></o:p> <xs:element name="NewDataSet" msdata:IsDataSet="true"><o:p></o:p> <xs:complexType><o:p></o:p> <xs:choice maxOccurs="unbounded"><o:p></o:p> <xs:element name="Region"><o:p></o:p> <xs:complexType><o:p></o:p> <xs:sequence><o:p></o:p> <xs:element name="RegionID" msdata:AutoIncrement="true"<o:p></o:p> msdata:AutoIncrementSeed="1" type="xs:int" /><o:p></o:p> <xs:element name="RegionDescription" type="xs:string" /><o:p></o:p> </xs:sequence><o:p></o:p> </xs:complexType><o:p></o:p> </xs:element><o:p></o:p> </xs:choice><o:p></o:p> </xs:complexType><o:p></o:p> </xs:element><o:p></o:p> </xs:schema><o:p></o:p> <Region><o:p></o:p> <RegionID>1</RegionID><o:p></o:p> <RegionDescription>Eastern</RegionDescription><o:p></o:p> </Region><o:p></o:p> <Region><o:p></o:p> <RegionID>2</RegionID><o:p></o:p> <RegionDescription>Western</RegionDescription><o:p></o:p> </Region><o:p></o:p> <Region><o:p></o:p> <RegionID>3</RegionID><o:p></o:p> <RegionDescription>Northern</RegionDescription><o:p></o:p> </Region><o:p></o:p> <Region><o:p></o:p> <RegionID>4</RegionID><o:p></o:p> <RegionDescription>Southern</RegionDescription><o:p></o:p> </Region><o:p></o:p> </NewDataSet><o:p></o:p> Note the use in this file of the msdata schema, which defines extra attributes for columns within a DataSet, such as AutoIncrement and AutoIncrementSeed - these attributes correspond directly with the properties definable on a DataColumn.<o:p></o:p> Working with ADO.NET This last section will attempt to address some common scenarios when developing data access applications with ADO.NET.<o:p></o:p> Tiered Development Producing an application that interacts with data is often done by splitting the application up into tiers. A common model is to have an application tier (the front end), a data services tier, and the database itself.<o:p></o:p> One of the difficulties with this model is deciding what data to transport between tiers, and the format that it should be transported in. With ADO.NET you'll be pleased to hear that these wrinkles have been ironed out, and support for this style of architecture has been designed in from the start.<o:p></o:p> Copying and Merging Data Ever tried copying an entire OLE DB recordset? In .NET it's easy to copy a DataSet:<o:p></o:p> DataSet source = {some dataset};<o:p></o:p> DataSet dest = source.Copy();<o:p></o:p> This will create an exact copy of the source DataSet - each DataTable, DataColumn, DataRow, and Relation will be copied across verbatim, and all data will be in exactly the same state as it was in the source. If all you want to copy is the schema of the DataSet, you can try the following:<o:p></o:p> DataSet source = {some dataset};<o:p></o:p> DataSet dest = source.Clone();<o:p></o:p> This will again copy all tables, relations, and so on. However, each copied DataTable will be empty. It really couldn't be more straightforward.<o:p></o:p> A common requirement when writing a tiered system, whether based on Win32 or the web, is to be able to ship as little data as possible between tiers. This reduces the amount of resources consumed.<o:p></o:p> To cope with this requirement, the DataSet has the GetChanges() method. This simple method performs a huge amount of work, and returns a DataSet with only the changed rows from the source dataset. This is ideal for passing between tiers, as only a minimal set of data has to be passed across the wire.<o:p></o:p> The following example shows how to generate a "changes" DataSet:<o:p></o:p> DataSet source = {some dataset};<o:p></o:p> DataSet dest = source.GetChanges();<o:p></o:p> Again, this is trivial. Under the covers things are a little more interesting. There are two overloads of the GetChanges() method. One overload takes a value of the DataRowState enumeration, and returns only rows that correspond to that state (or states). GetChanges() simply calls GetChanges(Deleted | Modified | Added), and first checks to ensure that there are some changes by calling HasChanges(). If no changes have been made, then a null is returned to the caller immediately.<o:p></o:p> The next operation is to clone the current DataSet. Once done, the new DataSet is set up to ignore constraint violations (EnforceConstraints = false ), and then each changed row for every table is copied into the new DataSet.<o:p></o:p> Once you have a DataSet that just contains changes, you can then move these off to the data services tier for processing. Once the data is updated in the database, the "changes" DataSet can be returned to the caller (as there may, for example, be some output parameters from the stored procedures that have updated values in the columns). These changes can then be merged into the original DataSet using the Merge() method. This sequence of operations is depicted below:<o:p></o:p>  Sequence of Operations<o:p></o:p> Key Generation with SQL Server The RegionInsert stored procedure presented earlier in the chapter was one example of generating a primary key value on insertion into the database. The method for generating the key was fairly crude and wouldn't scale well, so for a real application you should look at utilizing some other strategy for generating keys.<o:p></o:p> Your first instinct might be simply to define an identity column, and return the @@IDENTITY value from the stored procedure. The following stored procedure shows how this might be defined for the Categories table in the Northwind example database. Type this stored procedure into SQL Query Analyzer, or run the StoredProcs.sql file in the 13_SQLServerKeys directory:<o:p></o:p> CREATE PROCEDURE CategoryInsert(<o:p></o:p> @CategoryName NVARCHAR(15),<o:p></o:p> @Description NTEXT,<o:p></o:p> @CategoryID INTEGER OUTPUT)<o:p></o:p> AS<o:p></o:p> SET NOCOUNT OFF<o:p></o:p> INSERT INTO Categories (CategoryName, Description)<o:p></o:p> VALUES(@CategoryName, @Description)<o:p></o:p> SELECT @CategoryID = @@IDENTITY<o:p></o:p> GO<o:p></o:p> This inserts a new row into the Category table, and returns the generated primary key to the caller. You can test the procedure by typing in the following SQL in Query Analyzer:<o:p></o:p> DECLARE @CatID int;<o:p></o:p> EXECUTE CategoryInsert 'Pasties', 'Heaven Sent Food', @CatID OUTPUT;<o:p></o:p> PRINT @CatID;<o:p></o:p> When executed as a batch of commands, this will insert a new row into the Categories table, and return the identity of the new record, which is then displayed to the user.<o:p></o:p> Let's say that some months down the line, someone decides to add in a simple audit trail, which will record all insertions and modifications made to the category name. You define a table such as that shown below, which will record the old and new value of the category:<o:p></o:p>  Table<o:p></o:p> The creation script for this table is included in the StoredProcs.sql file. The AuditID column is defined as an IDENTITY column. You then construct a couple of database triggers that will record changes to the CategoryName field:<o:p></o:p> CREATE TRIGGER CategoryInsertTrigger<o:p></o:p> ON Categories<o:p></o:p> AFTER UPDATE<o:p></o:p> AS<o:p></o:p> INSERT INTO CategoryAudit(CategoryID, OldName, NewName )<o:p></o:p> SELECT old.CategoryID, old.CategoryName, new.CategoryName<o:p></o:p> FROM Deleted AS old, Categories AS new<o:p></o:p> WHERE old.CategoryID = new.CategoryID;<o:p></o:p> GO<o:p></o:p> For those of you used to Oracle stored procedures, SQL Server doesn't exactly have the concept of OLD and NEW rows, instead for an insert trigger there is an in memory table called Inserted, and for deletes and updates the old rows are available within the Deleted table.<o:p></o:p> This trigger retrieves the CategoryID of the record(s) affected, and stores this together with the old and new value of the CategoryName column.<o:p></o:p> Now, when you call your original stored procedure to insert a new CategoryID, you receive an identity value; however, this is no longer the identity value from the row inserted into the Categories table, it is now the new value generated for the row in the CategoryAudit table. Ouch!<o:p></o:p> To view the problem first hand, open up a copy of SQL Server Enterprise manager, and view the contents of the Categories table.<o:p></o:p>  Content of Categories Table<o:p></o:p> This lists all the categories I have in my instance of the database.<o:p></o:p> The next identity value for the Categories table should be 21, so we'll insert a new row by executing the code shown below, and see what ID is returned as follows:<o:p></o:p> DECLARE @CatID int;<o:p></o:p> EXECUTE CategoryInsert 'Pasties', 'Heaven Sent Food', @CatID OUTPUT;<o:p></o:p> PRINT @CatID;<o:p></o:p> The output value of this on my PC was 17. If I look into the CategoryAudit table, I find that this is the identity of the newly inserted audit record, not that of the category record created.<o:p></o:p>  Newly Inserted Record<o:p></o:p> The problem lies in the way that @@IDENTITY actually works. It returns the LAST identity value created by your session, so as shown above it isn't completely reliable.<o:p></o:p> There are two other identity functions that you can utilize instead of @@IDENTITY, but neither are free from possible problems. The first, SCOPE_IDENTITY(), will return the last identity value created within the current "scope". SQL Server defines scope as a stored procedure, trigger, or function. This may work most of the time, but if for some reason someone adds another INSERT statement into the stored procedure, then you will receive this value rather than the one you expected.<o:p></o:p> The other, IDENT_CURRENT() will return the last identity value generated for a given table in any scope, so for instance, if two users were accessing SQL Server at exactly the same time, it might be possible to receive the other user's generated identity value.<o:p></o:p> As you might imagine, tracking down a problem of this nature isn't easy. The moral of the story is to beware when utilizing IDENTITY columns in SQL Server.<o:p></o:p> Naming Conventions Having worked with database applications all my working life, I've picked up a few recommendations for naming entities, which are worth sharing. I know, this isn't really .NET related, but the conventions are useful especially when naming constraints as above. Feel free to skip this section if you already have your own views on the subject.<o:p></o:p> Database Tables<o:p></o:p> Always use singular names - Product rather than Products. This one is largely due to having to explain to customers a database schema - it's much better grammatically to say "The Product table contains products" than "The Products table contains products". Have a look at the Northwind database as an example of how not to do this.<o:p></o:p> Adopt some form of naming convention for the fields that go into a table - ours is <Table>_ID for the primary key of a table (assuming that the primary key is a single column), Name for the field considered to be the user-friendly name of the record, and Description for any textual information about the record itself. Having a good table convention means you can look at virtually any table in the database and instinctively know what the fields are used for.<o:p></o:p> Database Columns<o:p></o:p> Use singular rather than plural names again.<o:p></o:p> Any columns that link to another table should be named the same as the primary key of that table. So, a link to the Product table would be Product_ID, and to the Sample table Sample_ID. This isn't always possible, especially if one table has multiple references to another. In that case use your own judgment.<o:p></o:p> Date fields should have a suffix of _On, as in Modified_On, Created_On. Then it's easy to read some SQL output and infer what a column means just by its name.<o:p></o:p> Fields that record the user should be suffixed with _By, as in Modified_By and Created_By. Again, this aids legibility.<o:p></o:p> Constraints<o:p></o:p> If possible, include in the name of the constraint the table and column name, as in CK_<Table>_<Field>. Examples would be CK_PERSON_SEX for a check constraint on the SEX column of the PERSON table. A foreign key example would be FK_Product_Supplier_ID, for the foreign key relationship between product and supplier.<o:p></o:p> Show the type of constraint with a prefix, such as CK for a check constraint and FK for a foreign key constraint. Feel free to be more specific, as in CK_PERSON_AGE_GT0 for a constraint on the age column indicating that the age should be greater than zero.<o:p></o:p> If you have to trim the length of the constraint, do it on the table name part rather than the column name. When you get a constraint violation, it's usually easy to infer which table was in error, but sometimes not so easy to check which column caused the problem. Oracle has a 30-character limit on names, which you can easily hit.<o:p></o:p> Stored Procedures Just like the obsession many have fallen into over the past few years of putting a 'C' in front of each and every class they have declared (you know you have!), many SQL Server developers feel compelled to prefix every stored procedure with 'sp_' or something similar. It's not a good idea.<o:p></o:p> SQL Server uses the 'sp_' prefix for all (well, most) system stored procedures. So, on the one hand, you risk confusing your users into thinking that 'sp_widget' is something that comes as standard with SQL Server. In addition, when looking for a stored procedure, SQL Server will treat procedures with the 'sp_' prefix differently from those without.<o:p></o:p> If you use this prefix, and do not qualify the database/owner of the stored procedure, then SQL Server will look in the current scope, then jump into the master database and look up the stored procedure there. Without the 'sp_' prefix your users would get an error a little earlier. What's worse, and also possible to do, is to create a local stored procedure (one within your database) that has the same name and parameters as an system stored procedure. I'd avoid this at all costs - if in doubt, don't prefix.<o:p></o:p> Above all, when naming entities, whether within the database or within code, be consistent.<o:p></o:p> Performance The current set of managed providers available for .NET are somewhat limited - you can choose OleDb or SqlClient; OleDb permits connection to any data source exposed with an OLE DB driver (such as Oracle), and the SqlClient provider is tailored for SqlServer.<o:p></o:p> The SqlClient provider has been written completely in managed code, and uses as few layers as possible to connect to the database. This provider writes TDS (Tabular Data Stream) packets direct to SQL Server, which should be substantially faster than the OleDb provider, which naturally has to go through a number of layers before actually hitting the database.<o:p></o:p> To test the theory, the following code was run against the same database on the same machine, the only difference being the use of the SqlClient managed provider over the ADO provider:<o:p></o:p> SqlConnection conn = new SqlConnection(Login.Connection);<o:p></o:p> conn.Open();<o:p></o:p> <o:p> </o:p> SqlCommand cmd = new SqlCommand("update tempdata set AValue=1 Where ID=1",<o:p></o:p> conn);<o:p></o:p> <o:p> </o:p> DateTime initial, elapsed;<o:p></o:p> initial = DateTime.Now;<o:p></o:p> <o:p> </o:p> for(int i = 0; i < iterations; i++)<o:p></o:p> cmd.ExecuteNonQuery();<o:p></o:p> <o:p></o:p> elapsed = DateTime.Now ;<o:p></o:p> conn.Close();<o:p></o:p> Naturally the OLE DB version utilizes OleDbCommand rather than SqlCommand. I created a simple database table with two columns as shown below, and manually added a single row:<o:p></o:p>  TempData Table<o:p></o:p> The SQL clause used was a simple UPDATE command:<o:p></o:p> UPDATE TempData SET AValue = 1 WHERE ID = 1<o:p></o:p> The SQL was kept deliberately simple to attempt to highlight the differences in the providers. The results (in seconds) achieved for various combinations of iterations were as follows :<o:p></o:p>  OleDb vs Sql Providers<o:p></o:p> If you are only targeting SQL Server then the obvious choice is the Sql provider. Back in the real world, if you target anything other than SQL Server you naturally have to use the OleDb provider. Or do you?<o:p></o:p> As Microsoft has done an excellent job of making database access generic with the System.Data.Common classes, it would be better to write code against those classes, and use the appropriate managed provider at run time. It's fairly simple to swap between OleDb and Sql now, and if other database vendors write managed providers for their products, you will be able to swap out ADO for a native provider with little (or no) code changes. For an example of the versatility of .NET data access, The "Scientific Data Center" case study in "Data-Centric .NET Programming with C#" (Wrox Press, ISBN 1-861005-92-x) details using C# to query a MySQL database.<o:p></o:p> Summary The subject of data access is a large one, especially in .NET as there is an abundance of new material to cover. This chapter has provided an outline of the main classes in the ADO.NET namespaces, and shown how to use the classes when manipulating data from a data source.<o:p></o:p> Firstly, we explored the use of the Connection object, through the use of both the SqlConnection (SQL Server specific) and OleDbConnection (for any OLE DB data sources). The programming model for these two classes is so similar that one can normally be substituted for the other and the code will continue to run.<o:p></o:p> After illustrating how to connect to and disconnect from the data source, we then discussed how to do it properly, so that scarce resources, such as database connections, could be closed as early as possible.<o:p></o:p> Both of the connection classes implement the IDisposable interface, called when the object is placed within a using clause. If there's one thing I'd like you to take away from this chapter is the importance of closing database connections as early as possible. We then discussed database commands, through examples that executed with no returned data, to calling stored procedures with input and output parameters. Various execute methods were described, including the ExecuteXmlReader method available only on the SQL Server provider. This vastly simplifies the selection and manipulation of XML-based data.<o:p></o:p> The generic classes within the System.Data namespace were all described in detail, from the DataSet class through DataTable, DataColumn, DataRow and on to relationships and constraints. The DataSet class is an excellent container of data, and various methods make it ideal for cross tier data flow. The data within a DataSet can be represented in XML for transport, and in addition, methods are available that will pass a minimal amount of data between tiers. The ability of having many tables of data within a single DataSet can greatly increase its usability; being able to maintain relationships automatically between master/details rows will be expanded upon in the next chapter.<o:p></o:p> Having the schema stored within a DataSet is one thing, but .NET also includes the data adapter that along with various Command objects can be used to select data into a DataSet and subsequently update data in the data store. One of the beneficial aspects of a data adapter is that a distinct command can be defined for each of the four actions – SELECT, INSERT, UPDATE and DELETE. The system can create a default set of commands based on database schema information and a SELECT statement, but for the best performance, a set of stored procedures can be used, with the DataAdapter's commands defined appropriately to pass only the necessary information to these stored procedures.<o:p></o:p> As XML and XSD schemas have become feverishly popular over the past couple of years, we discussed how to convert an XSD schema into a set of database classes using the XSD tool XSD.EXE that ships with .NET. The classes produced are ready to be used within an application, and their automatic generation can save many hours of laborious typing.<o:p></o:p> During the last few pages of the chapter we've gone through some best practices and naming conventions for database development. Although not strictly .NET-related, these were thought to be a worthwhile inclusion. A set of conventions should always be adhered to when programming, whether in C# against a SQL Server database or in Perl scripts on Linux.<o:p></o:p> Armed with this knowledge, we're now in a good position to move on to the next chapter, where we'll explore the use of Visual Studio and .NET's Windows Forms data controls.<o:p></o:p> >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><o:p> </o:p> Perhaps you can extract what you need from these two VS2005 C# loginform classes, one for SQL Server 2005 and one for MySQL. <o:p></o:p> // SQL SERVER 2005<o:p></o:p> public partial class LoginForm : Form {<o:p></o:p> <o:p> </o:p> string sConn;<o:p></o:p> private TheUsual parent;<o:p></o:p> <o:p></o:p> public LoginForm( TheUsual _parent ) {<o:p></o:p> InitializeComponent();<o:p></o:p> parent = ( TheUsual ) _parent;<o:p></o:p> this.FormClosed += new FormClosedEventHandler( LoginClosed );<o:p></o:p> }<o:p></o:p> <o:p> </o:p> private void btnOK_Click(object sender, EventArgs e) {<o:p></o:p> RegistryKey rkSW, rkART;<o:p></o:p> if( textBoxServer.Text.Length > 0 ) {<o:p></o:p> if( rbWinAuth.Checked ) {<o:p></o:p> textBoxServer.Text = sqlClean( textBoxServer.Text );<o:p></o:p> sConn = "data source=" + textBoxServer.Text + <o:p></o:p> ";integrated security=SSPI;persist security info=False;Trusted_Connection=Yes";<o:p></o:p> }<o:p></o:p> else if( textBoxUser.Text.Length > 0 && textBoxPassword.Text.Length > 0 ) {<o:p></o:p> textBoxPassword.Text = sqlClean( textBoxPassword.Text );<o:p></o:p> textBoxUser.Text = sqlClean( textBoxUser.Text ); <o:p></o:p> sConn = "Server=" + textBoxServer.Text + <o:p></o:p> ";User ID=" + textBoxUser.Text +<o:p></o:p> ";Password=" + textBoxPassword.Text +<o:p></o:p> ";Trusted_Connection=False";<o:p></o:p> }<o:p></o:p> else {<o:p></o:p> return;<o:p></o:p> }<o:p></o:p> try {<o:p></o:p> parent.Connector.Init( sConn );<o:p></o:p> parent.Connector.Open();<o:p></o:p> if ( parent.Connector.State != System.Data.ConnectionState.Open ) {<o:p></o:p> MessageBox.Show( "Incorrect server name, user name or password" );<o:p></o:p> Application.Exit();<o:p></o:p> }<o:p></o:p> lblConnected.Text = "Connected";<o:p></o:p> // SAVE IF SQL AUTHENTICATION AND IF REMEMBER ME IS CHECKED<o:p></o:p> while( cbRememberMe.Checked == true ) {<o:p></o:p> object o;<o:p></o:p> rkSW = Registry.CurrentUser.OpenSubKey( "Software", true );<o:p></o:p> rkART = rkSW.CreateSubKey( "artfulsoftware" );<o:p></o:p> using ( RegistryKey rkConn = rkART.CreateSubKey( this.parent.DbmsName + "_connect" ) ) {<o:p></o:p> rkConn.SetValue( textBoxServer.Text + "_" + textBoxUser.Text, sConn );<o:p></o:p> }<o:p></o:p> break;<o:p></o:p> }<o:p></o:p> }<o:p></o:p> catch ( Exception ex ) {<o:p></o:p> MessageBox.Show( ex.Message.ToString() );<o:p></o:p> parent.Close();<o:p></o:p> }<o:p></o:p> finally {<o:p></o:p> this.Close();<o:p></o:p> }<o:p></o:p> }<o:p></o:p> }<o:p></o:p> <o:p> </o:p> private void rbWinAuth_CheckedChanged(object sender, EventArgs e) {<o:p></o:p> textBoxUser.Enabled = false;<o:p></o:p> textBoxPassword.Enabled = false;<o:p></o:p> cbRememberMe.Checked = false;<o:p></o:p> cbRememberMe.Enabled = false;<o:p></o:p> }<o:p></o:p> <o:p> </o:p> private void rbSqlAuth_CheckedChanged(object sender, EventArgs e) {<o:p></o:p> textBoxUser.Enabled = true;<o:p></o:p> textBoxPassword.Enabled = true;<o:p></o:p> cbRememberMe.Enabled = true; <o:p></o:p> }<o:p></o:p> <o:p> </o:p> private void btnCancel_Click(object sender, EventArgs e) {<o:p></o:p> this.Close();<o:p></o:p> Application.Exit();<o:p></o:p> }<o:p></o:p> <o:p> </o:p> private void LoginClosed( object sender, EventArgs e ) {<o:p></o:p> this.Close();<o:p></o:p> }<o:p></o:p> <o:p> </o:p> private string sqlClean( string txt ) {<o:p></o:p> return txt.Replace( "--", "" ).Replace( ";", "" ).Replace( ".", "" );<o:p></o:p> }<o:p></o:p> <o:p> </o:p> }<o:p></o:p> <o:p> </o:p> // MySQL<o:p></o:p> public partial class LoginForm : Form {<o:p></o:p> <o:p> </o:p> string sConn;<o:p></o:p> private TheUsual parent;<o:p></o:p> <o:p></o:p> public LoginForm( TheUsual _parent ) {<o:p></o:p> InitializeComponent();<o:p></o:p> parent = ( TheUsual ) _parent;<o:p></o:p> this.FormClosed += new FormClosedEventHandler( LoginClosed );<o:p></o:p> }<o:p></o:p> <o:p> </o:p> private void textBoxPassword_TextChanged(object sender, EventArgs e) {<o:p></o:p> }<o:p></o:p> <o:p> </o:p> private void textBoxUser_TextChanged(object sender, EventArgs e) {<o:p></o:p> }<o:p></o:p> <o:p> </o:p> private void btnOK_Click(object sender, EventArgs e) {<o:p></o:p> RegistryKey rkSW, rkART;<o:p></o:p> if( textBoxServer.Text.Length > 0 ) {<o:p></o:p> if( textBoxUser.Text.Length > 0 && textBoxPassword.Text.Length > 0 ) {<o:p></o:p> textBoxServer.Text = sqlClean( textBoxServer.Text );<o:p></o:p> textBoxPassword.Text = sqlClean( textBoxPassword.Text );<o:p></o:p> textBoxUser.Text = sqlClean( textBoxUser.Text );<o:p></o:p> sConn = "Server=" + textBoxServer.Text + ";uid=" + textBoxUser.Text + ";pwd=" + textBoxPassword.Text;<o:p></o:p> }<o:p></o:p> else {<o:p></o:p> return;<o:p></o:p> }<o:p></o:p> try {<o:p></o:p> parent.Connector.Init( sConn );<o:p></o:p> parent.Connector.Open();<o:p></o:p> if ( parent.Connector.State != System.Data.ConnectionState.Open ) {<o:p></o:p> MessageBox.Show( "Incorrect server name, user name or password" );<o:p></o:p> Application.Exit();<o:p></o:p> }<o:p></o:p> lblConnected.Text = "Connected";<o:p></o:p> while( cbRememberMe.Checked == true ) {<o:p></o:p> rkSW = Registry.CurrentUser.OpenSubKey( "Software", true );<o:p></o:p> rkART = rkSW.CreateSubKey( "artfulsoftware" );<o:p></o:p> using ( RegistryKey rkConn = rkART.CreateSubKey( this.parent.DbmsName + "_connect" ) ) {<o:p></o:p> rkConn.SetValue( textBoxServer.Text + "_" + textBoxUser.Text, sConn );<o:p></o:p> }<o:p></o:p> break;<o:p></o:p> }<o:p></o:p> }<o:p></o:p> catch ( Exception ex ) {<o:p></o:p> MessageBox.Show( ex.Message.ToString() );<o:p></o:p> Application.Exit();<o:p></o:p> }<o:p></o:p> finally {<o:p></o:p> this.Close();<o:p></o:p> }<o:p></o:p> }<o:p></o:p> }<o:p></o:p> <o:p> </o:p> private void btnCancel_Click(object sender, EventArgs e) {<o:p></o:p> this.Close();<o:p></o:p> Application.Exit();<o:p></o:p> }<o:p></o:p> <o:p> </o:p> private void LoginClosed( object sender, FormClosedEventArgs e ) {<o:p></o:p> this.Close();<o:p></o:p> }<o:p></o:p> <o:p> </o:p> private string sqlClean( string txt ) {<o:p></o:p> return txt.Replace( "--", "" ).Replace( ";", "" ).Replace(".",""); <o:p></o:p> }<o:p></o:p> <o:p></o:p> }<o:p></o:p> >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><o:p> </o:p> <o:p> </o:p> How to Create a Database with SQL Server Express<o:p></o:p> To create your database, launch your C# .NET software. Start a new Windows project by clicking File > New Project. Call it anything you like, because we won't be using this form. But without SQL Server Management Studio Express installed, you need a new Windows Application to create a SQL Server Express database.<o:p></o:p> From the menu bars at the top of C# .NET, click Project. From the Project menu, selectAdd New Item:<o:p></o:p> <o:p></o:p> When you click on Add New Item, you should see the following dialogue box appear (we've chopped a few templates out for convenience sake):<o:p></o:p> <o:p></o:p> Select SQL Database, and give your database a name. Call it MyWorkers. We'll create a database of fictitious people who work for us, and give them job descriptions.<o:p></o:p> Click the Add button, and you'll see (eventually) the following:<o:p></o:p> <o:p></o:p> Select Tables, and then Finish. (The Dataset is important, and you'll see how they work later).<o:p></o:p> It may seem as though nothing has happened. But take a look at the Solution Explorer on the right and you'll see that your database has been added to your project:<o:p></o:p> <o:p></o:p> However, your database is empty at the moment. We need to add a table to it. So right click on MyWorkers.mdf. From the menu that appears, click Open.<o:p></o:p> You should see the Database Explorer appear:<o:p></o:p> <o:p></o:p> Right click on Tables to see the following menu:<o:p></o:p> <o:p></o:p> Select Add New Table from the menu, and a new table will appear in your main window:<o:p></o:p> <o:p></o:p> The Columns are all the Columns that you want in your table. The idea is that you type a Name for the Column, and then specify what kind of data (Data Type) you want to go in it, such as text, numbers, Yes/No values, etc. Allow Nulls means, "Does this column need to be filled in?" For example, if you had a middle name column then this will often be left blank, because not everybody has a middle name. In which case that particular column can have Nulls. If you need the data, such as an Identifying Number, then you leave the Allow Nulls box unchecked.<o:p></o:p> We'll created a very simple table with just four columns:<o:p></o:p> Worker_ID first_Name last_Name job_Title<o:p></o:p> The first Column, Worker_ID, will be a number. We can let the database itself handle this. Every time a new worker is added, SQL Server Express will then take care of assigning a new number for that worker. This is known as Auto Increment in other databases, such as Access.<o:p></o:p> So type Worker_ID under Column Name and your screen will look like this:<o:p></o:p> <o:p></o:p> The next thing to do is tell SQL Server Express what kind of data is going in to the Worker_ID column. Click inside of Data Type and you'll see it's a dropdown list of options:<o:p></o:p> <o:p></o:p> As you can see, there's an awful lot of them! Select int from the list, though. Leave theAllow Null box unchecked, and your screen will look like this:<o:p></o:p> <o:p></o:p> One more thing to do with this Column. Have a look at the bottom of your screen and you'll see a list of Properties. The one we want is Identity Specification. Set Is Identity to Yes, and Identity Increment and Identity Seed will appear:<o:p></o:p> <o:p></o:p> By setting these values, SQL Server Express will add 1 to the Worker_ID column when we add a new worker.<o:p></o:p> Click under Column Name and type the second of our table fields:<o:p></o:p> <o:p></o:p> For the Data Type, select nvarchar(50). The varchar is short for variable-length character string. With nvarchar, the n is short for Unicode, and the data will be stored in the UTF-16 format. Use nvarchar if you're going to be storing non-English characters. Otherwise, use varchar. The same is true of all the n's on the list.<o:p></o:p> Check the Allow Nulls box and your new Column should look like this:<o:p></o:p> <o:p></o:p> Set the following values for the other two Columns in our table:<o:p></o:p> Column Name: last_Name Data Type: nvarchar(50) Allow Nulls: Yes<o:p></o:p> <o:p> </o:p> Column Name: job_Title Data Type: nvarchar(50) Allow Nulls: Yes<o:p></o:p> Your table should then look like this:<o:p></o:p> <o:p></o:p> At this stage, you can set a Primary Key for the table. A Primary Key is one that uniquely identifies a particular row in your table. It has to be unique, with no duplicates allowed. You couldn't set the first name column as the Primary Key, for example, because lots of people will have that name. The Worker_ID column is the one column in our table that is unique, so we could use that as the Primary Key. If we had a second table, we could then use Primary Keys and Foreign Keys to link the two tables together. SQL Server is a relational database, and Primary Keys are used a lot for linking purposes.<o:p></o:p> To set a Primary Key, right click on Worker_ID. From the menu that appears select Primary Key. We're going to leave the table without a Primary Key, however, as we want to keep things simple. If you want to become a database guru, though, you need to get to grips with the relational aspect of SQL Server and SQL Server Express.<o:p></o:p> But let's continue.<o:p></o:p> Click File > Save All to save your work. You will be prompted to enter a name for your new table. Call it tblWorkers:<o:p></o:p> <o:p></o:p> Click OK and you'll be returned to the main screen and the Database Explorer. Expand the Tables section and you should see your new columns appear:<o:p></o:p> <o:p></o:p> The only thing left to do is to enter some default data into the table.<o:p></o:p> To add some data to your Table, right click on your Table name. You should see the following menu appear:<o:p></o:p> <o:p></o:p> Select Show Table Data and you'll see a new tab appear:<o:p></o:p> <o:p></o:p> All our Column names are there, waiting to be filled in. To enter data, simply click inside a cell and start typing.<o:p></o:p> Click inside the first_Name column. (The Worker_ID Column will take care of itself.) Type a first name. Click inside of last_Name and type a last name. Click inside of job_Title and enter a job title. Enter the same details as ours, if you prefer (all made up):<o:p></o:p> <o:p></o:p> Notice the warning symbols in the cells. These appear when the cell data has changed. The Worker_ID is still NULL in the image above. When we click in the next row, however, notice that a number will appear in the first Worker_ID cell:<o:p></o:p> <o:p></o:p> We have now created one row in our database table. Fill out a few more rows. You can use the same details as in the image below:<o:p></o:p> <o:p></o:p> Save your work, and you will have created your very first SQL Server Express database! But it's a huge subject, and whole books have been written about SQL Server. We can only touch on the very basics here. What we do have, though, is a database we can open with C# programming code.<o:p></o:p> Connecting to a SQL Server Express Database with C# .NET<o:p></o:p> Close down the project you have open, and click File > New Project to create a new one.<o:p></o:p> If you created a SQL Server Express database in the previous section, copy and paste it somewhere handy, like the root of C:\. This is so that you're not working with very long file paths. Once you have copied it over to your C drive, you'd then only have a path like this:<o:p></o:p> C:\ MyWorkers.mdf<o:p></o:p> If you leave it where it is, the file path would be this:<o:p></o:p> C:\Documents and Settings\pc_name\My Documents\Visual Studio 2005\Projects\cSharp\dbtests\MyWorkers.mdf<o:p></o:p> Which is a bit long and unwieldy!<o:p></o:p> If you didn't create a SQL Server Express database then you can use ours. It is amongstthe files you downloaded at the start of the course, in the databases folder. You will also find an Access version of the same database, just in case the SQL Server Express one doesn't work for you.<o:p></o:p> <o:p></o:p> How to Connect to a SQL Server Express Database<o:p></o:p> To connect to a database using SQL Server Express, you first need to set up a SQL Connection object. You then need something called a connection string to tell C# where the database is.<o:p></o:p> To set up a connection object, double click the blank form. Just outside of the Form Load event add the following:<o:p></o:p> System.Data.SqlClient.SqlConnection con;<o:p></o:p> Your coding window should look like this:<o:p></o:p> <o:p></o:p> Inside of the Form Load event, add the following:<o:p></o:p> con = new System.Data.SqlClient.SqlConnection();<o:p></o:p> When the form loads, a new SQL Connection object will be created with the name of con. Here's what your code should look like:<o:p></o:p> <o:p></o:p> Now that we have a connection object, we can access the ConnectionString property. To see what the string should be, click the Data menu item at the top of the C# .NET software. Then select Show Data Sources. This will display a new tab where the Solution Explore is:<o:p></o:p> <o:p></o:p> Click Add New Data Source and you'll see a Wizard appear. On the first screen, make sure Database is selected and then click the Next button get to the Choose your Data Connection step. Click the New Connection button, and you'll see the following:<o:p></o:p> <o:p></o:p> The Data Source area has a Change button. If you were using an Access database, you'd click this button and select Microsoft Access Database File. The default is for a SQL Server Database. That's what we want to connect to, so leave this as it is.<o:p></o:p> Click the Browse button and browse to the location where you saved your database. The Add Connection box will then look like this:<o:p></o:p> <o:p></o:p> Click the Test Connection button to see if everything is working. Then click OK to get back to the Choose your Data Connection step. Expand the Connection Stringarea, and the dialogue box should look like this:<o:p></o:p> <o:p></o:p> Highlight the entire string:<o:p></o:p> <o:p></o:p> Now hold down the CTRL key on your keyboard. Press the letter C to copy the string. Go back to the Form Load event in your coding window and press CTRL then the letter V to paste the connection string. You'll have lost of red underlines, but don't worry about that. Cancel the wizard, because we're done with it - we only wanted the connection string!<o:p></o:p> Just after the SqlConnection( ) line, type the following:<o:p></o:p> con.ConnectionString<o:p></o:p> Type an equals sign then a double quote:<o:p></o:p> con.ConnectionString = "<o:p></o:p> Now move your connection string up to just after the quote mark:<o:p></o:p> con.ConnectionString ="DataSource=.\SQLEXPRESS; AttachDbFilename =C:\MyWorkers.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";<o:p></o:p> At the end of that long connection string, type another double quote mark. End the line with the usual semicolon. Your coding window will then look something like ours below (we've got word wrap switched on):<o:p></o:p> <o:p></o:p> Notice that we still have error underlines in the connection string. There are two of them in the image above, both after the backslash character. It is the backslash character that is the problem. This is considered a special character in database programming, so it needs to be escaped. To escape a backslash character (or any other character) just type another backslash before it:<o:p></o:p> <o:p></o:p> There are now four backslash characters in the code above, two before SQLEXPRESS, and two before MyWorkers.mdf. Change your code to match.<o:p></o:p> All the code does, though, is to tell C# where the database is, and sets a few properties. You can add more database properties here, as well. For example, if the database required a user name, you'd add this:<o:p></o:p> User ID=your_user_name;<o:p></o:p> After your connection string, you can then try to open up a connection to the database. Again we use our con object:<o:p></o:p> con.Open();<o:p></o:p> When the connection is open, we'll be writing code to get all the records. Once we've done that, we can close the connection:<o:p></o:p> con.Close();<o:p></o:p> Add two message boxes to your code, and your coding window should look like ours:<o:p></o:p> <o:p></o:p> Run your programme and test it out. You should see the "Database Open" message appear first, followed by the "Database closed" message. Then the form should load.<o:p></o:p> Congratulations! All that hard work and you have now made a connection to your SQL Server Express database!<o:p></o:p> In the next lesson, you'll learn how to connect to an Access Database.<o:p></o:p> How to Connect to an Access Database with C# .NET<o:p></o:p> Connecting to an Access database requires a different connection object and connection string.<o:p></o:p> Access uses something called OLEDB, so you need to create a database object of this type.<o:p></o:p> Double click your form. Outside of the form load event, type the following:<o:p></o:p> System.Data.OleDb.OleDbConnection con;<o:p></o:p> Inside of the form load event, type this:<o:p></o:p> con = new System.Data.OleDb.OleDbConnection();<o:p></o:p> This sets up a new connection object called con. It is an OLEDB connection object.<o:p></o:p> The connection string can be found using the same technique as for SQL Server Express, just outlined. Once the string is pasted over, it will look like this:<o:p></o:p> con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0; Data Source=C:/AddressBook.mdb";<o:p></o:p> The Provider you use for Access databases is called Microsoft Jet. We're using version 4.0 in the code above. Again, we need to tell C# where the database is. This is done with Data Source.<o:p></o:p> The connection to the database is done with the Open method of your connection object:<o:p></o:p> con.Open();<o:p></o:p> Close the connection in a similar way:<o:p></o:p> con.Close();<o:p></o:p> You can also issue a Dispose command at the end, if you want. This will do the "tidying up" for you:<o:p></o:p> con.Dispose();<o:p></o:p> Add a few message boxes and your coding window should look like this:<o:p></o:p> <o:p></o:p> Run your programme and you should see the two message boxes display. The form will display after you click OK on these.<o:p></o:p> Now that you have made a connection to a Database, you need to learn about Datasets and DataAdapters.<o:p></o:p> Datasets and Data Adapters in C# .NET<o:p></o:p> The connection to the database has been made. The next step is to pull the records from our workers table. To do that, a Dataset and a DataAdapter are needed.<o:p></o:p> A Dataset is where all your data is held when it is pulled from the database table. Think of it like a grid that you see on a spreadsheet. The Columns in the grid are the Columns from your database table. The Rows represent a single entry in the table.<o:p></o:p> The Dataset needs to be filled with data. However, because the Dataset and Connection object can't see each other, they need someone in the middle to help them out - the DataAdapter. The DataAdapter will fill the Dataset with records from the database.<o:p></o:p> So we need to set up two more objects, a Dataset and a DataAdapter. This is true whether you use a SQL Server database or an Access one.<o:p></o:p> To create a Dataset object, add the following just above the form load event:<o:p></o:p> DataSet ds1;<o:p></o:p> Inside of the form load event, create a new object from the Dataset type we've called ds1:<o:p></o:p> ds1 = new DataSet();<o:p></o:p> You coding window will then look like this for Access users:<o:p></o:p> <o:p></o:p> And like this for SQL Server Express users:<o:p></o:p> <o:p></o:p> For the DataAdapter, add the following outside of the form load event (Access Users):<o:p></o:p> System.Data.OleDb.OleDbDataAdapter da;<o:p></o:p> For SQL Server Express users, you need to add this instead:<o:p></o:p> System.Data.SqlClient.SqlDataAdapter da;<o:p></o:p> In both cases, we're setting up a DataAdapter variable and calling it da.<o:p></o:p> Inside of the form load event, we can create a new object from our da variable. Here's the code for Access users:<o:p></o:p> string sql = "SELECT * From tblWorkers";<o:p></o:p> da = new System.Data.OleDb.OleDbDataAdapter( sql, con );<o:p></o:p> And here's the code for SQL Server Express users:<o:p></o:p> string sql = "SELECT * From tblWorkers";<o:p></o:p> da = new System.Data.SqlClient.SqlDataAdapter( sql, con );<o:p></o:p> The first line of both is the same:<o:p></o:p> string sql = "SELECT * From tblWorkers";<o:p></o:p> This sets up a string variable called sql. SQL stands for Structured Query Language. It's a language used to pull records from a database, and variants of it are used for all database systems. Whether you use Access or SQL Server, you use the Structured Query Language on the database itself. (SQL Server's variant is called T-SQL. The T stands for Transact.)<o:p></o:p> Keywords in SQL are SELECT, UPDATE, WHERE, and a whole lot more besides. The * symbol means "all the records". So we're saying, "Select all the records from the table called tblWorkers".<o:p></o:p> The DataAdapter object will use your SQL commands to pull the records from the database. But you need to tell it which connection object to use. That's why, in between the round brackets of both code, we have this:<o:p></o:p> ( sql, con );<o:p></o:p> Our new DataAdapter object will then know what records to pull (sql), and where to pull them from (con).<o:p></o:p> But here's what your coding windows should look like. Access first:<o:p></o:p> <o:p></o:p> And here's the code for SQL Server Express (minus the message boxes):<o:p></o:p> <o:p></o:p> Run your programme and see if it works. The only way you can tell at the moment, though, is through any message boxes you may have. Or if it crashes!<o:p></o:p> To fill the dataset with records from the database, you use the DataAdapter and issue the Fill command. This is the same for both Access and SQL Server Express:<o:p></o:p> da.Fill( ds1, "Workers" );<o:p></o:p> What this does is to Fill a Dataset called ds1. After the comma, you can type an identifying name for this particular Fill. We've called ours "Workers".<o:p></o:p> After the Fill command has been issued, the records from the SQL command are stored in the Dataset. This, remember, is just like a grid with Columns and Rows.<o:p></o:p> So add the line to your code. Put it just before the con.Close() line.<o:p></o:p> We can now display the data from the database on a form.<o:p></o:p> Display Data from a Dataset in C# .NET<o:p></o:p> At the moment, we have a Dataset filled with records from the database table. But we can't actually see anything. What we'd like to do is to display the records on a form. We'll put the data in textboxes.<o:p></o:p> Add three text boxes and three labels to your form, so that the design looks something like ours below:<o:p></o:p> <o:p></o:p> When the form loads, we want the first record from the Dataset to appear in the text boxes.<o:p></o:p> We'll do all that from a method. So just after your form load code, add a new method called NavigateRecords. It's not going to return a value, so you can make it a voidmethod (the code below is for an Access database):<o:p></o:p> <o:p></o:p> Trying to get at the data from a Dataset can be a torturous business, because there's so many properties and methods to access. The easiest way is to first set up a newDataRow variable:<o:p></o:p> DataRow dRow;<o:p></o:p> This will refer to a row from the Dataset:<o:p></o:p> DataRow dRow = ds1.Tables["Workers"].Rows[0];<o:p></o:p> So after the equals sign, we have this:<o:p></o:p> ds1.Tables["Workers"].Rows[0];<o:p></o:p> You first type the name of your Dataset, which is ds1 for us. After a dot, select Tablesfrom the IntelliSense list. Tables is a collection, and stores a list of all the available Tables (a Table is just that grid that we mentioned). To tell C# which Table you want, type its name between square brackets and a pair of double quotes. After another dot, select Rows from the IntelliSense list. In between square brackets, you specify which Row from the Dataset you want. Row zero [0] is the first Row in the Table.<o:p></o:p> So add that line to your code, and your NavigateRecords method will look like this:<o:p></o:p> <o:p></o:p> But that's not the end of it! We've only pointed to a Row in the Dataset. We also need to specify a column.<o:p></o:p> To get at a Column in the Row, the code is this:<o:p></o:p> dRow.ItemArray.GetValue(1).ToString()<o:p></o:p> We've started with our Row object, which we've called dRow. After a dot, selectItemArray from the IntelliSense list. This is an Array of all the Items (Columns) in your Row. We had four columns in our database table: Worker_ID, first_name, last_name and job_title. ItemArray starts at zero, so Worker_ID will be Item 0, first_name will be Item 1, last_name will be Item 2, and job_title will be Item 3.<o:p></o:p> After another dot, then, select GetValue from the list. As its name suggests, this will Get the Values from your Columns. In between round brackets, you need the Item number from the array. GetValue(1) will refer to the first_name column in our Dataset. Finally, you need to convert it to a string with ToString(). Once converted to a string, you can put it straight into a text box.<o:p></o:p> Putting all that together, add the following code to your NavigateRecords method:<o:p></o:p> <o:p></o:p> If you prefer, you can put everything on one line. But it will be a very long line. Here it is:<o:p></o:p> textBox1.Text = ds1.Tables["Workers"].Rows[0].ItemArray.GetValue(1).ToString();<o:p></o:p> The line is so long we had to make the font size smaller just to fit it on this page!<o:p></o:p> But you are almost ready to test it all out. The final thing to do is to add a call to your NavigateRecords method. Put the call just before the con.Close line, as in the image below:<o:p></o:p> <o:p></o:p> Now you can test it out. Run your programme and the form should display the first record from your database. It should look like ours:<o:p></o:p> <o:p></o:p> Now that we have one record displayed, we can add buttons to navigate backwards and forward through all the records in our database.<o:p></o:p> Database Navigation Buttons<o:p></o:p> The first thing we'll do is to allow users to move forward through each record in our database. This is done with just a bit of programming logic, and manipulating the Rowvalue in the Dataset.<o:p></o:p> To make this work, we need to set up a few variables. So return to your coding window, and add the following two variables outside the form load event, just below the three you already have:<o:p></o:p> int MaxRows = 0; int inc = 0;<o:p></o:p> Your coding window will then look like this: (The first three lines for SQL Server Express users will be slightly different.)<o:p></o:p> <o:p></o:p> The MaxRows variable will hold how many Rows there are in the Dataset. This is so that we don't go past the last record when the Next Record button is clicked. If we try to go past the last record, the programme will crash! (We'll add the button shortly.)<o:p></o:p> The inc variable will be used to change the current Row number.<o:p></o:p> To get at the number of Rows in the DataSet, you can use the Count property of Rows. Add this code to your form load event, just below your call to NavigateRecords( ):<o:p></o:p> MaxRows = ds1.Tables["Workers"].Rows.Count;<o:p></o:p> Instead of specifying a particular Row in square brackets, this time we type a dot, and then select Count from the IntelliSense list. This will return how many rows there are in this particular Dataset. Here's what your code should look like<o:p></o:p> <o:p></o:p> When the form loads, then, MaxRows will contain a count of how many Rows are in the Dataset called ds1.<o:p></o:p> For the NavigateRecords method, we need to make one slight change. At the moment, we have this code:<o:p></o:p> DataRow dRow = ds1.Tables["Workers"].Rows[0];<o:p></o:p> But this will point to Row[0] all the time. We can use the inc variable here. What we'll do is to increment the value when the Next Record button is clicked, adding 1 to inc every time.<o:p></o:p> Change the line to this:<o:p></o:p> DataRow dRow = ds1.Tables["Workers"].Rows[inc];<o:p></o:p> The only change is in between the square brackets of Rows.<o:p></o:p> Run your programme to test if it works. You should still see the first record displayed in your text boxes.<o:p></o:p> Stop your programme and return to the design environment. Add a button to your form. Change the Text property to Next Record. Change the Name property to btnNext. Your form will then look like this:<o:p></o:p> <o:p></o:p> Double click your button to get at the coding window. For the code, we need to check what is inside of the MaxRows variable and make sure we don't go past it. We also need to increment the inc variable. It is this variable that will move us on to the next record.<o:p></o:p> Add the following If Statement to your button<o:p></o:p> <o:p></o:p> The first line of the If Statement says "If inc does not equal MaxRows minus 1". If it doesn't then we increment the inc variable and call NavigateRecords. But can you see why we need to say MaxRows - 1? It's because of the Rows[inc] line in our NavigateRecords method. The count for Rows starts at zero. So if we only have 4 records in the database, the count will be for 0 to 3. MaxRows, however, will be 4. If we don't deduct 1, the programme will crash with an error: IndexOutOfRange.<o:p></o:p> If the MaxRows is reached, then we can display a message for the user.<o:p></o:p> Run your programme and test it out. You should be able to move forward through your database. Here's what your form should look like when the last record is reached:<o:p></o:p> <o:p></o:p> In the next lesson, you'll learn how to move backwards through the database.<o:p></o:p> Move Backwards through the Database<o:p></o:p> We can use similar code to move backwards through the records in the database. Add another button to your form. Change the Text property to Previous Record. Change the Name property to btnPrevious.<o:p></o:p> Double click your new button to get at the coding window. Now add the following:<o:p></o:p> <o:p></o:p> The If statement is now only checking the inc variable. We need to check if it's greater than zero. If it is, we can deduct 1 from inc, and then call our NavigateRecordsmethods. When the form loads, remember, inc will be 0. So if we tried to move back one record after the form first loads the programme would crash. It would crash because we'd be trying to access Rows[-1].<o:p></o:p> Run your programme and test it out. Click you Previous Record button and you should see this:<o:p></o:p> <o:p></o:p> Click both of your buttons and make sure you can move back and forward through the records. You programme shouldn't crash!<o:p></o:p> Next, we'll see how to jump to the end, and to the start of the database.<o:p></o:p> <o:p></o:p> How to Move to the First and Last Record in a Datase<o:p></o:p> <o:p> </o:p> Jump to the Last Record in your Database<o:p></o:p> To move to the last record of your database, you only need to make sure that the inc variable and MaxRows have the same value.<o:p></o:p> Add a new button to your form. Set the Text property as Last Record, and the Name property as btnLast. Double click, and add the following code:<o:p></o:p> <o:p></o:p> The If Statement again checks that inc is not equal to MaxRows minus 1. If it isn't, we have this:<o:p></o:p> inc = MaxRows - 1;<o:p></o:p> MaxRows minus 1 would equal 3 in our four record database. Because Rows[inc] goes from 0 to 3, this is enough to move to the last record after the call to NavigateRecords.<o:p></o:p> Here's what your form should look like, when you test it out and click your new button:<o:p></o:p> <o:p></o:p> <o:p></o:p> Jump to the First Record in your Database<o:p></o:p> To move to the first record in the database, we only need to set inc to zero.<o:p></o:p> Add another button to your form. Change the Text property to First Record. Change the Name property to btnFirst. Double click your new button and add the following code:<o:p></o:p> <o:p></o:p> This just checks to see if inc isn't already zero. If it isn't, we set the inc variable to 0. Then we call the NavigateRecords method.<o:p></o:p> When you test out your new button, your form should look like this:<o:p></o:p> <o:p></o:p> You should now be able to move through the records in your database without the programme crashing. What we'll do now is to allow the user to add a new record to the database. This is more complex than the navigation, so you may need to pay close attention!<o:p></o:p> Add a New Record to the Database<o:p></o:p> When you add a new record, you'll want to add it to the Dataset and the underlying database. Let's see how.<o:p></o:p> Add two new buttons to the form. Set the following properties for your buttons:<o:p></o:p> Name: btnAddNew Text: Add New<o:p></o:p> Name: btnSave Text: Save<o:p></o:p> The Add New button won't actually add a new record. The only thing it will do is to clear the text boxes, ready for a new record to be added. The Save button is where we'll add the record to the Dataset and to the Database.<o:p></o:p> Double click your Add New button, and add code to clear the text boxes:<o:p></o:p> <o:p></o:p> That's all we need to do here. You can test it out, if you want. But all the code does is to clear the three text boxes of text. The user can then enter a new record.<o:p></o:p> After a new record has been entered into the text boxes, we can Save it. So double click your Save button to get at the code.<o:p></o:p> To save a record, you need to do two things: save it to the Dataset, and save it to the underlying database. You need to do it this way because the Dataset with its copy of the records is disconnected from the database. Saving to the Dataset is NOT the same as saving to the database.<o:p></o:p> To add a record to the Dataset, you need to create a new Row:<o:p></o:p> DataRow dRow = ds1.Tables["Workers"].NewRow();<o:p></o:p> This creates a New DataRow called dRow. But the Row will not have any data. To add data to the row, the format is this:<o:p></o:p> dRow[1] = textBox1.Text;<o:p></o:p> So after your DataRow variable (dRow for us) you need a pair of square brackets. In between the square brackets type its position in the Row. This is the Column number.dRow[1] refers to the first_name column, for us. After an equals sign, you type whatever it is you want to add to that Column - the text from textBox1, in our case.<o:p></o:p> Finally, you issue the Add command:<o:p></o:p> ds1.Tables["Workers"].Rows.Add( dRow );<o:p></o:p> After Add, and in between a pair of round brackets, you type the name of the Row you want to add, which was dRow in our example. The new Row will then get added to the end of the Dataset.<o:p></o:p> So add this code to your Save button:<o:p></o:p> <o:p></o:p> Notice the last two lines:<o:p></o:p> MaxRows = MaxRows + 1; inc = MaxRows - 1;<o:p></o:p> Because we have added a new Row to the Dataset, we also need to add 1 to the MaxRows variable. The inc variable can be set to the last record in the Dataset.<o:p></o:p> Try it out. When you start your programme, click the Add New button to clear the text boxes. Enter a new record in the blank text boxes and then click your Save button. Click your Previous and Next. You'll see that the new record appears.<o:p></o:p> (Obviously, you'll want to add error checking code to check that the Save button is not clicked before the Add button. Or simply set the Enabled property to false for the Save button when the form loads. You can then set Enabled to true in your Add button.)<o:p></o:p> If you close the programme down, and start it back up again you'll find that the new record has disappeared! It's disappeared because we haven't yet added it to the underlying database. We've only added it to the Dataset.<o:p></o:p> To add a new record to the Database, you need to use the DataAdapter again. This has an Update method that will do the job for you. The only thing you need to do is tell it which Dataset holds all the records, and its name:<o:p></o:p> da.Update( ds1, "Workers" );<o:p></o:p> The code above refers to a DataAdapter called da. In between the round brackets of the Update method, we first have the Dataset (ds1) and then the name we gave to this Dataset (Workers).<o:p></o:p> However, because the connection to the Database is closed (we closed it during the form load event), we need one more rather curious object - something called aCommandBuilder.<o:p></o:p> The CommandBuilder will reconnect to the database for you. The only thing you need to do is to pass it a DataAdapter. The code to create a CommandBuilder object is this for Access:<o:p></o:p> System.Data.OleDb.OleDbCommandBuilder cb; cb = new System.Data.OleDb.OleDbCommandBuilder( da );<o:p></o:p> And this for SQL Server Express:<o:p></o:p> System.Data.SqlClient.SqlCommandBuilder cb; cb = new System.Data.SqlClient.SqlCommandBuilder( da );<o:p></o:p> In both cases, we have created a CommandBuilder object called cb. In between the round brackets in the code above, we have our DataAdapter variable, which was da.<o:p></o:p> You don't need to do anything with the CommandBuilder. It knows what to do! But here's what your code should look like if you're using an Access database:<o:p></o:p> <o:p></o:p> And here's what your code should look like if you are using a SQL Server Express database:<o:p></o:p> <o:p></o:p> You can now try your programme out. You should find that the new record gets added to the Dataset AND the underlying Database. Close your programme down, reopen it, and check.<o:p></o:p> In the next lesson, learn how to Update and Delete records.<o:p></o:p> Update and Delete Records<o:p></o:p> Sometimes, all you want to do is to update a record in the database. This is very similar to Adding a new record. Examine the following code:<o:p></o:p> <o:p></o:p> The only thing you're not doing is adding a new Row. After creating a new Row calleddRow2, we set it to the current Row:<o:p></o:p> = ds1.Tables["Workers"].Rows[inc];<o:p></o:p> Whatever is in the text boxes then gets transferred to dRow2[1], dRow2[2] and dRow2[3]. These are the Columns in the Row. Then we update the database:<o:p></o:p> da.Update( ds1, "Workers" );<o:p></o:p> Before trying it out, comment out the line that says con.Dispose(), if you added one. Otherwise you'll get a Connection String error.<o:p></o:p> When you run your form, amend one of your records. Close down the form and open it back up again. You should find that your amendments are still there.<o:p></o:p> <o:p></o:p> Delete a Record<o:p></o:p> To delete a record from the Dataset, you use the Delete method:<o:p></o:p> ds1.Tables["Workers"].Rows[inc].Delete();<o:p></o:p> This is enough to Delete the entire Row ( Rows[inc] ). But it is only deleted from the Dataset. Here's the code to delete the record from the database, as well:<o:p></o:p> <o:p></o:p> Notice that we're deducting 1 from the MaxRows variable ( MaxRows-- ), as well as setting inc to 0.<o:p></o:p> Try it out for yourself. Add a new record to your database. Then try to Delete it. You may get an error about concurrency violations. If you close down the programme and open it back up again, you should find that you'll be able to delete it without any errors.<o:p></o:p> Concurrency violations can happen for many reasons, but in general it's because the Dataset and Database are out of sync with one another. The easiest solution is to set a Boolean value when a new record is added. If you try to delete, check if this value is true. If it is, then let your user known that they can't delete this new record.<o:p></o:p> <o:p></o:p> Exercise Examine this version of our form:<o:p></o:p> <o:p></o:p> If you look at the bottom, you'll see a label that says "Record 2 of 4". Implement this in your own programme. If you set up a method, you can just call it when the form loads, and again from NavigateRecords.<o:p></o:p> <o:p></o:p> In the next lesson. learn how to find records in a Database.<o:p></o:p> <o:p></o:p> Finding Records in a Database<o:p></o:p> A useful feature to add to your form is a Find button. When a find button is clicked, you then display the record that the user was searching for. Or display a "Not found" message if there were no matching records.<o:p></o:p> So add a new button to your form. Set the Text property to Find, and the Name property to btnFind. Double click your button to get at the coding window.<o:p></o:p> There are quite a few different ways you can implement a search. The method we'll use is to Select a row from the dataset. We'll allow a user to search using a last name.<o:p></o:p> Add the following three lines to your btnFind code:<o:p></o:p> string searchFor = "Khan"; int results = 0; DataRow[] returnedRows;<o:p></o:p> The first variable sets up a string called searchFor. This is obviously the record we want to find. We've hard-coded the value, here, and just entered a last name from our database table. But you'd want this value to come from a text box on your form.<o:p></o:p> The second variable, results, will be used to tell us whether or not any results were found.<o:p></o:p> The third line is a DataRow array, which we've called returnedRows. We're using an array because more than one record might be found. Each record will then be stored in a position in the array.<o:p></o:p> To get at a particular Row in your Dataset, you can use the Select method. Here's the code. It's a bit long, so we've had to spread it over two lines. It should be one line in your code:<o:p></o:p> returnedRows = ds1.Tables["Workers"].Select("last_Name='" + searchFor + "'");<o:p></o:p> So you start with your Dataset, which was ds1 for us. Then you need the name of a Table in your Dataset. We want to search the "Workers" table. After a dot, we have the Select method:<o:p></o:p> Select("last_Name=' " + searchFor + " ' ");<o:p></o:p> It looks a bit messy with all those quote marks. But first we have an outer pair:<o:p></o:p> Select(" ");<o:p></o:p> Inside of these two double quotes, we have this:<o:p></o:p> last_Name=<o:p></o:p> You need to type the name of a Column from your Dataset, here. We're using the last_Name Column. But we could have used the first_Name Column instead:<o:p></o:p> first_Name=<o:p></o:p> The Column names are the same ones we used in our database table. But notice the equals sign. The Select method allows you to use other SQL keywords. If you don't want an exact search, for example, you can use Like instead of =.<o:p></o:p> Select("last_Name Like 'Khan' ")<o:p></o:p> Note where the single quotes are - surrounding the text you want to search for. Because our search used a variable, we're using plus symbols to concatenate. Which is why it's so messy!<o:p></o:p> But here's what your code should look like: (We're using word wrap)<o:p></o:p> <o:p></o:p> If a row is found, it will then be stored in the returnedRows array. To get a count of how many rows were found, we can used this code:<o:p></o:p> results = returnedRows.Length;<o:p></o:p> This just uses the Length property of the returnedRows array. The length is how many items are in the array. If it's greater than zero, it means we've found a match. We can use an IF Statement to check:<o:p></o:p> if (results > 0) { //RECORD FOUND } else { MessageBox.Show("No such Record"); }<o:p></o:p> If a record is found, we need to get at the values in the Columns. We can create a new Row for this:<o:p></o:p> if (results > 0) { DataRow dr1;<o:p></o:p> dr1 = returnedRows[0]; }<o:p></o:p> We now set up a DataRow called dr1. We want the first returned Row to be stored here. The first Row is returnedRows[0];<o:p></o:p> Putting it all together, here's the full code for the search:<o:p></o:p> <o:p></o:p> Notice the line that display the job title in a Message box:<o:p></o:p> MessageBox.Show( dr1["job_Title"].ToString() );<o:p></o:p> Because dr1 is now a DataRow, you can access its data by either using the Column name, or the index number. So these lines return the same values:<o:p></o:p> dr1["job_Title"] dr1["first_Name"] dr1["last_Name"]<o:p></o:p> dr1[3] dr1[1] dr1[2]<o:p></o:p> It's up to you which ones you want to use.<o:p></o:p> But try your programme out. Click your Find button and the job title of the person named Khan should appear in the message box.<o:p></o:p> Close your programme down. Change the name of the person being searched for and try again.<o:p></o:p> <o:p></o:p> Exercise Add a text box to your form. Get the name of the person from this text box, rather than using the hard coded value that you have at the moment.<o:p></o:p> Exercise Add a drop down list next to the text box. The drop down list should allow a user to search by last name, or by job title. (Searching by first name doesn't make much sense - too many people with the same first name!)<o:p></o:p> When you have finished these two exercises, your form might look something like this:<o:p></o:p> <o:p></o:p> Exercise As you can see from the form above, although the Supervisor is displayed in the message box, it's the IT Manager who appears in the text boxes. For this exercise, display the person's details in the text boxes rather than in a message box.<o:p></o:p> OK, that's enough of databases! It's a huge subject, obviously, and many books have been written on the subject. We've only touched the surface in these lessons, and encourage you to delve deeper. Especially if you want a job as a programmer! In the next and final section, we'll take a look at some other things you can do with C# .NET. Up first is multiple forms.<o:p></o:p> Creating Multiple Forms in C# .NET<o:p></o:p> There aren't many programmes that have only one form. Most programmes have other forms that are accessible from the main one that loads at start up. In this section, you'll learn how to create programmes with more than form.<o:p></o:p> The programme we'll create is very simple one. It will have a main form with a text box and a button. When the button is clicked, it will launch a second form. On the second form, we'll allow a user to change the case of the text in the text box on form one.<o:p></o:p> Here's what form one looks like:<o:p></o:p> <o:p></o:p> Design the above form. Set the Name property of the text box to txtChangeCase. For the Text property, add some default text, but all in lowercase letters. Set the Name property of the button to btnFormTwo.<o:p></o:p> Adding a new form to the project is easy. Click Project from the menu bar at the top of the Visual C# software. From the Project menu, select Add New Windows Form. You'll see the Add New Item dialogue box appear. Make sure Windows Form is selected. For the Name, leave it on the default of Form2.cs. When you click OK, you should see a new blank form appear:<o:p></o:p> <o:p></o:p> It will also be in the Solution Explorer on the right:<o:p></o:p> <o:p></o:p> Adding the form to the project is the easy part - getting it to display is an entirely different matter!<o:p></o:p> To display the second form, you have to bear in mind that forms are classes. When the programme first runs, C# will create an object from your Form1 class. But it won't do anything with your Form2 class. You have to create the object yourself.<o:p></o:p> So double click the button on your Form1 to get at the coding window.<o:p></o:p> To create a Form2 object, declare a variable of Type Form2:<o:p></o:p> Form2 secondForm;<o:p></o:p> Now create a new object:<o:p></o:p> secondForm = new Form2();<o:p></o:p> Or if you prefer, put it all on one line:<o:p></o:p> Form2 secondForm = new Form2();<o:p></o:p> What we've done here is to create a new object from the Class called Form2. The name of our variable is secondForm.<o:p></o:p> To get this new form to appear, you use the Show( ) method of the object:<o:p></o:p> secondForm.Show();<o:p></o:p> Your code should now look like this:<o:p></o:p> <o:p></o:p> Run your programme and test it out. Click your button and a new form should appear - the blank second form.<o:p></o:p> However, there's a slight problem. Click the button again and a new form will appear. Keep clicking the button and your screen will be filled with blank forms!<o:p></o:p> To stop this from happening, move the code that creates the form outside of the button. Like this:<o:p></o:p> <o:p></o:p> Try your programme again. Click the button and you won't get lots of forms filling the screen.<o:p></o:p> In the next lesson, you'll learn what a Modal form is.<o:p></o:p> Modal Forms in C# .NET<o:p></o:p> Return to the code for your button. Instead of using the Show method, change it to this:<o:p></o:p> secondForm.ShowDialog();<o:p></o:p> The method we're now using is ShowDialog. This creates what's known as a Modal form. A Modal form is one where you have to deal with it before you can continue. Run your programme to test it out. Click the button and a new form appears. Move it out of the way and try to click the button again. You won't be able to.<o:p></o:p> Modal forms have a neat trick up their sleeves. Add two buttons to your blank second form. Set the following properties for them:<o:p></o:p> Name: btnOK Text: OK<o:p></o:p> Name: btnCancel Text: Cancel<o:p></o:p> Your second form will then look like this:<o:p></o:p> <o:p></o:p> Double click the OK button and add the following:<o:p></o:p> this.DialogResult = DialogResult.OK;<o:p></o:p> After you type the equals sign, the IntelliSense list will appear. Select DialogResult again, then a dot. The IntelliSense list will then show you this:<o:p></o:p> <o:p></o:p> Select OK. What this does is to record the result of the button click, and set it to OK.<o:p></o:p> Double click your Cancel button and add the following code:<o:p></o:p> this.DialogResult = DialogResult.Cancel;<o:p></o:p> It's the same code, except we've chosen Cancel as the Result. Your coding window for form 2 should look like this:<o:p></o:p> <o:p></o:p> You can use Form1 to get which of the buttons was clicked on Form2. Was it OK or was it Cancel?<o:p></o:p> Change the button code on Form1 to this:<o:p></o:p> <o:p></o:p> The code checks to see if the OK button was clicked. If so, it displays a message. We'll get it to do something else in a moment. But you don't have to do anything with the Cancel button: C# will just unload the form for you.<o:p></o:p> Try it out. Click your Change Case button on Form1. When your new form appears, click the OK button. You should see the message. Try it again, and click the Cancel button. The form just unloads.<o:p></o:p> Getting at the values on other Forms<o:p></o:p> Turn your Form2 into a Change Case dialogue box, just like ours below:<o:p></o:p> <o:p></o:p> When the OK button is clicked, we want the text in the text box on Form1 to change case, depending on which of the three options was chosen.<o:p></o:p> The problem we face is that the text box is private to Form1, and can't be seen from outside it. If you tried to refer to the text box from Form2, you'd just get errors.<o:p></o:p> One solution is to set up a public static variable, of type TextBox. You then assign textBox1 to this new variable.<o:p></o:p> So add the following to Form1:<o:p></o:p> public static TextBox tb = new TextBox();<o:p></o:p> This creates a new TextBox object called tb. Add the line just under your Form variable, and your coding window will look like this:<o:p></o:p> <o:p></o:p> Notice that we've deleted the message box code, and went back to the original. That's because we don't need the message box anymore. Delete yours as well.<o:p></o:p> Now that we have a TextBox object, we can assign our text box on form one to it. In the Form Load event of Form1, add the following line:<o:p></o:p> tb = txtChangeCase;<o:p></o:p> (The easiest way to bring up the code stub for the Form Load event is to double click a blank area of the form in design view.)<o:p></o:p> Here's what all the Form1 code looks like now:<o:p></o:p> <o:p></o:p> When the main form (Form1) loads, the text box will now be available to Form2.<o:p></o:p> So double click your OK button on Form2 to bring up its code stub. Enter the following:<o:p></o:p> string changeCase = Form1.tb.Text;<o:p></o:p> We're setting up a string variable called changeCase. The contents of this new string variable will be the Text from the text box called tb on Form1.<o:p></o:p> To change the case of the text, we can use string methods for two of them: Uppercase and Lowercase. Like this:<o:p></o:p> changeCase = changeCase.ToUpper();<o:p></o:p> changeCase = changeCase.ToLower();<o:p></o:p> Unfortunately, C# .NET does not have a direct string method to change text to Proper Case (or Title case as it's also know). Proper Case is capitalising the first letter of each word. For example, "This Is Proper Case".<o:p></o:p> In order to get Proper Case, you have to reference two System namespaces. One called Globalization and one called Threading. Add the following to the very top of the coding window:<o:p></o:p> using System.Globalization; using System.Threading;<o:p></o:p> The code window will then look something like this:<o:p></o:p> <o:p></o:p> Now that we have these two references, the next thing to do is to set up something called a CultureInfo object:<o:p></o:p> CultureInfo properCase = Thread.CurrentThread.CurrentCulture;<o:p></o:p> The CurrentCulture tells you information about the various language options of your particular country. Our CultureInfo object is called properCase.<o:p></o:p> That's not the end of it, though! You also need a TextInfo object:<o:p></o:p> TextInfo textInfoObject = properCase.TextInfo;<o:p></o:p> It's this TextInfo object that has the methods we need. We're setting up a TextInfo object called textInfoObject. We're handing it the TextInfo property of our properCase CultureInfo object.<o:p></o:p> Finally, we can change the case:<o:p></o:p> changeCase = textInfoObject.ToTitleCase( changeCase );<o:p></o:p> The TextInfo object has a method called ToTitleCase. In between the round brackets of the method, you type what it is you want to convert.<o:p></o:p> Hopefully, in future versions of C#, they'll add an easier way to convert to Proper Case!<o:p></o:p> To get which of the options on our Form2 was chosen, we can add a series of if … else statements:<o:p></o:p> <o:p></o:p> So we're just checking to see which radio button was selected. We're then doing the case conversion.<o:p></o:p> To put the changed text into the text box on Form1, add the following line:<o:p></o:p> Form1.tb.Text = changeCase;<o:p></o:p> Add the line just before your DialogResult line. The full code for the button should be this<o:p></o:p> <o:p></o:p> Run your programme and test it out. Click your button to bring up Form2. Select the Upper Case option and then click your OK button. You should find that the text in txtChangeCase on your main form will now be in uppercase.<o:p></o:p> <o:p></o:p> The next section of the C# course deals with Dates and Times.<o:p></o:p> <o:p> </o:p>