Parameter Queries in ASP.NET with MS Access

原出處
Good!!
------------
A selection of code samples for executing queries against MS Access using parameters.


Making use of the ASP.NET 2.0 datasource controls is fine, but it is important to understand how to manually create data access code. Best practice dictates that, at the very least, parameters are used to represent values that are passed into the SQL to be executed, rather than un-sanitised values straight from the user. The main reason for this cannot be over-emphasised in terms of its importance - it protects the application against SQL Injection attacks. In addition, parameters do not require delimiters. Therefore there is no need to worry about octothorpes (#) or apostrophes for dates, or doubling single quotes in strings.

These samples all assume that the values being passed into the parameters have been properly validated for datatype, existence, range etc, according to the business rules for the application. The serverside validation code is not included, as it will differ from app to app, and is not the focus of these samples anyway. However, it is important to stress that all user input must be validated server-side before being included in a SQL statement. Better to reject it outright, rather than have to unpick rubbish that pollutes the database...

The required components are an OleDbConnection object, a ConnectionString property, an OleDbCommand object and an OleDbParameterCollection. These all reside in the System.Data.OleDb namespace, which needs to be referenced. Also, the connection string is held in the Web.Config, and a static method GetConnString() has been created in a class called Utils (also static) to retrieve it:

[C#]
public static string GetConnString()

{

return WebConfigurationManager.ConnectionStrings["myConnStr"].ConnectionString;

}

[VB]

Public Shared Function GetConnString() As String

Return WebConfigurationManager.ConnectionStrings("myConnStr").ConnectionString

End Function




For simplicity, you can replace Utils.GetConnString with a valid Access connection string such as:



"Microsoft.Jet.OleDb.4.0;Data Source=|DataDirectory|Northwind.mdb"



To make use of |DataDirectory| make sure that your database file is in the App_Data folder of your web site.

OleDb Parameters are recognised by their position, not by their name. Consequently, it is vital to ensure that parameters are added to the collection in the order they appear in the SQL, otherwise a "Too few parameters..." exception could occur. At the very least, your values will get inserted into the wrong fields, or nothing happens at all. For the sake of code readability, AddWithValues(string, object) can take a non-empty string giving a name to the parameter, although an empty string ("") will do.

One final note about parameter markers: in the samples below, the markers are represented by question marks ( ? ). Access (or the Jet provider) is also happy to work with SQL Server-style parameter markers that are prefixed with @, so the first example CommandText can be replaced with:



"Insert Into Contacts (FirstName, LastName) Values (@FirstName, @LastName)"



INSERT
[C#]

string ConnString = Utils.GetConnString();

string SqlString = "Insert Into Contacts (FirstName, LastName) Values (?,?)";

using (OleDbConnection conn = new OleDbConnection(ConnString))

{

using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))

{

cmd.CommandType = CommandType.Text;

cmd.Parameters.AddWithValue("FirstName", txtFirstName.Text);

cmd.Parameters.AddWithValue("LastName", txtLastName.Text);

conn.Open();

cmd.ExecuteNonQuery();

}

}



[VB]

Dim ConnString As String = Utils.GetConnString()

Dim SqlString As String = "Insert Into Contacts (FirstName, LastName) Values (?,?)"

Using conn As New OleDbConnection(ConnString)

Using cmd As New OleDbCommand(SqlString, conn)

cmd.CommandType = CommandType.Text

cmd.Parameters.AddWithValue("FirstName", txtFirstName.Text)

cmd.Parameters.AddWithValue("LastName", txtLastName.Text)

conn.Open()

cmd.ExecuteNonQuery()

End Using

End Using



UPDATE
[C#]

string ConnString = Utils.GetConnString();

string SqlString = "Update Contacts Set FirstName = ?, LastName = ?";

using (OleDbConnection conn = new OleDbConnection(ConnString))

{

using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))

{

cmd.CommandType = CommandType.Text;

cmd.Parameters.AddWithValue("FirstName", txtFirstName.Text);

cmd.Parameters.AddWithValue("LastName", txtLastName.Text);

conn.Open();

cmd.ExecuteNonQuery();

}

}



[VB]

Dim ConnString As String = Utils.GetConnString()

Dim SqlString As String = "Update Contacts Set FirstName = ?, LastName = ?"

Using conn As New OleDbConnection(ConnString)

Using cmd As New OleDbCommand(SqlString, conn)

cmd.CommandType = CommandType.Text

cmd.Parameters.AddWithValue("FirstName", txtFirstName.Text)

cmd.Parameters.AddWithValue("LastName", txtLastName.Text)

conn.Open()

cmd.ExecuteNonQuery()

End Using

End Using



DELETE
[C#]

string ConnString = Utils.GetConnString();

string SqlString = "Delete * From Contacts Where FirstName = ? And LastName = ?";

using (OleDbConnection conn = new OleDbConnection(ConnString))

{

using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))

{

cmd.CommandType = CommandType.Text;

cmd.Parameters.AddWithValue("FirstName", txtFirstName.Text);

cmd.Parameters.AddWithValue("LastName", txtLastName.Text);

conn.Open();

cmd.ExecuteNonQuery();

}

}



[VB]

Dim ConnString As String = Utils.GetConnString()

Dim SqlString As String = "Delete * From Contacts Where FirstName = ? And LastName = ?"

Using conn As New OleDbConnection(ConnString)

Using cmd As New OleDbCommand(SqlString, conn)

cmd.CommandType = CommandType.Text

cmd.Parameters.AddWithValue("FirstName", txtFirstName.Text)

cmd.Parameters.AddWithValue("LastName", txtLastName.Text)

conn.Open()

cmd.ExecuteNonQuery()

End Using

End Using



SELECT
[C#]

string ConnString = Utils.GetConnString();

string SqlString = "Select * From Contacts Where FirstName = ? And LastName = ?";

using (OleDbConnection conn = new OleDbConnection(ConnString))

{

using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))

{

cmd.CommandType = CommandType.Text;

cmd.Parameters.AddWithValue("FirstName", txtFirstName.Text);

cmd.Parameters.AddWithValue("LastName", txtLastName.Text);



conn.Open();

using (OleDbDataReader reader = cmd.ExecuteReader())

{

while (reader.Read())

{

Response.Write(reader["FirstName"].ToString() + " " + reader["LastName"].ToString());

}

}

}

}



[VB]

Dim ConnString As String = Utils.GetConnString()

Dim SqlString As String = "Select * From Contacts Where FirstName = ? And LastName = ?"

Using conn As New OleDbConnection(ConnString)

Using cmd As New OleDbCommand(SqlString, conn)

cmd.CommandType = CommandType.Text

cmd.Parameters.AddWithValue("FirstName", txtFirstName.Text)

cmd.Parameters.AddWithValue("LastName", txtLastName.Text)

conn.Open()

Using reader As OleDbDataReader = cmd.ExecuteReader()

While reader.Read()

Response.Write(reader("FirstName").ToString() + " " + reader("LastName").ToString())

End While

End Using

End Using

End Using



Saved Queries
The code samples above will work equally well with minimal changes for Saved Queries in MS Access. The CommandType will need to be changed to "StoredProcedure", and the name of the query needs to be passed as a string in place of the SQL statement. As an example, if a Saved Query was created in Access called AddContact, this is how the INSERT example would alter:

[C#]

string ConnString = Utils.GetConnString();

string SqlString = "AddContact";

using (OleDbConnection conn = new OleDbConnection(ConnString))

{

using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))

{

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("FirstName", txtFirstName.Text);

cmd.Parameters.AddWithValue("LastName", txtLastName.Text);

conn.Open();

cmd.ExecuteNonQuery();

}

}



[VB]

Dim ConnString As String = Utils.GetConnString()

Dim SqlString As String = "AddContact"

Using Conn As New OleDbConnection(ConnString)

Using Cmd As New OleDbCommand(SqlString, Conn)

Cmd.CommandType = CommandType.StoredProcedure

Cmd.Parameters.AddWithValue("FirstName", txtFirstName.Text)

Cmd.Parameters.AddWithValue("LastName", txtLastName.Text)

Conn.Open()

Cmd.ExecuteNonQuery()

End Using

End Using

You may end up using a legacy Access database, which has embedded spaces in the names of the queries. I know - only an idiot does this sort of thing. Well, the download version of Northwind.mdb (from Microsoft) has embedded spaces in object names... Anyway, the way to get round this is to surround the query name with [ ] brackets:


string query = "[Current Product List]";