ADO.NET

Here are six simple functions which allow you to quickly send an SQL command to get a DataRow, DataTable, integer, string, or date from your database (SQL Server). Another function allows you to execute an SQL statement (e.g. INSERT or UPDATE) on your database. Note that you need to have a connection string called "con" in your web.config file. For each function, you can send a simple SQL statement, but if you have variables, an overridden function provides you with a way to easily use SqlParameters (in one line). Using the parameter syntax, you can prevent sql injection. If you need error handling you will have to build it in. But other than that, these function are ready to save you lots of development time by simplying access to your data. You would probably in most cases want to put these functions in code behind (a .dll in your \bin directory) so that you have access to them in every file.

SHOW CODE

<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Xml" %>
<%@ Import Namespace="System.Collections" %>
<%@ Import Namespace="System.Globalization" %>
<%@ Import Namespace="System.ComponentModel" %>

public void Page_Load(Object sender, EventArgs e) {

//QUICK OVERVIEW OF SYNTAX:
/*
int numberOfEmployees = GetInteger("SELECT COUNT(*) FROM Employees");
int numberOfEmployees = GetInteger("SELECT COUNT(*) FROM Employees WHERE Country=@country","@country,varchar/255," + selectedCountry);
string currentLastName = GetString("SELECT LastName FROM Employees WHERE EmployeeId=8");
string currentLastName = GetString("SELECT LastName FROM Employees WHERE EmployeeId=@employeeId","@employeeId,int," + selectedId.ToString());
DateTime shippedDate = GetDate("SELECT ShippedDate FROM Orders WHERE OrderId=10259");
DateTime shippedDate = GetDate("SELECT ShippedDate FROM Orders WHERE OrderId=@orderId","@orderId,int," + currentOrderId.ToString());
DataRow drEmployee = GetDataRow("SELECT * FROM Employees WHERE EmployeeId=3");
DataRow drEmployee = GetDataRow("SELECT * FROM Employees WHERE EmployeeId=@employeeId","@employeeId,int," + currentEmployeeId.ToString());
DataTable dtEmployees = GetDataTable("SELECT * FROM Employees ORDER BY LastName");
DataTable dtEmployees = GetDataTable("SELECT * FROM Employees WHERE Country=@currentCountry AND BirthDate<@cutOffDate AND EmployeeId>@employeeId","@currentCountry,varchar/255,USA;@cutOffDate,date," + cutOffDate.ToString() + ";@employeeId,int,2");
DoCommand("UPDATE Employees SET LastName='Smithinew' WHERE EmployeeId=9");
DoCommand("UPDATE Employees SET LastName='Thompsonnew' WHERE EmployeeId=@id","@id,int," + theId.ToString());
*/

//get an integer
int numberOfEmployees = GetInteger("SELECT COUNT(*) FROM Employees");
litShow1.Text = "There are " + numberOfEmployees.ToString() + " employees";

//get an integer with parameters
string selectedCountry = "UK";
int numberOfEmployees2 = GetInteger("SELECT COUNT(*) FROM Employees WHERE Country=@country","@country,varchar/255," + selectedCountry);
litShow2.Text = "There are " + numberOfEmployees2.ToString() + " employees from: " + selectedCountry;

//get a string
string currentLastName = GetString("SELECT LastName FROM Employees WHERE EmployeeId=8");
litShow3.Text = "The last name of the person selected is <b>" + currentLastName + "</b>.";

//get a string with parameters
int selectedId = 4;
string currentLastName2 = GetString("SELECT LastName FROM Employees WHERE EmployeeId=@employeeId","@employeeId,int," + selectedId.ToString());
litShow4.Text = "The last name of the person with id " + selectedId.ToString() + " is <b>" + currentLastName2 + "</b>.";

//get a date
CultureInfo ci = new CultureInfo("en-US");
DateTime shippedDate = GetDate("SELECT ShippedDate FROM Orders WHERE OrderId=10259");
litShow5.Text = "Your order shipped on <b>" + shippedDate.ToString("dddd, MMMM d, yyyy",ci) + "</b>.";

//get a date with parameters
int currentOrderId = 10264;
CultureInfo ci2 = new CultureInfo("en-US");

post comment or question

ADO.NET CODE EXAMPLE - added on January 14, 2002

How to populate a DataTable manually with auto increment

This example shows that a DataSet is truly a RDBMS in memory: it sets the ID column of a DataTable to autoincrement. This is something that I was wondering how I was going to do with XML files, for instance. Very nice. Now I can use a DataSet to read in an XML file and you can be sure that ID get autoincremented.

SHOW CODE

post comment or question

ADO.NET CODE EXAMPLE - added on January 14, 2002

How to put the Page_Load in code behind

If you want ALL your code in a code behind so that you only have HTML in your aspx files (so that designed can edit it for example without having to deal with code), you can use this code as an example. It even accesses data in an SQL database.

SHOW CODE

post comment or question

ADO.NET CODE EXAMPLE - added on January 8, 2002

How to measure how long a task takes

If you want to compare the performance of two processes, you can use this code to measure and print out the time takes by each process.

SHOW CODE

post comment or question

ADO.NET CODE EXAMPLE - added on January 8, 2002

How to process multiple SQL statements in a DataReader

When possible, you should minimize trips to the data layer. One way to do this is to send multiple SELECT statement with a DataReader. This code shows you how. Notice that afterward I refer to the fields with numbers and they are not the same kinds of fields.

SHOW CODE

Robert: Hello While practising this exaple, I had a little problem using the following code. TextBox txt = new TextBox(); txt.ID = "TextBox2"; TextBox txt2 = new TextBox(); txt2.ID = "TextBox3"; this.PlaceHolder1.Controls.Add(txt); this.PlaceHolder1.Controls.Add(txt2); SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConStr"].ToString()); using (con) { SqlCommand command = new SqlCommand("SELECT Count(*) As CountEmp FROM Employees;SELECT Count(*) As CountCat FROM Categories", con); con.Open(); SqlDataReader dr = command.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { txt.Text = dr["CountEmp"].ToString(); txt2.Text = dr["CountCat"].ToString(); } } dr.Close(); } When I use a string to get the value of the second SELECT statement, I get an error with CountCat as the only description. If I use a number (number 1 for the second SELECT) i get an IndexOutOfRangeException. Is using different tables in the command text possible? If so, how can i access them? I can't get this to work. reply

post comment or question

ADO.NET CODE EXAMPLE - added on January 8, 2002

How to read in values from a DataReader as safe types

One of the advantages of .NET is that you (can) work with safe types so that you don't have to guess or test for what type a variable is before you use it. This code shows you how to explicitly read in values from a database using DataReader.

SHOW CODE

post comment or question

ADO.NET CODE EXAMPLE - added on January 8, 2002

How to return results from DataReader into an Array

Since the connection cannot be used until a DataReader has been closed, it is quite often necessary to read the results of a DataReader into an array, then close the DataReader, THEN you can process the data in the array while you use the connection for something else. I could only get this example to work with integers but not varchars (?).

SHOW CODE

post comment or question