p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   ASP.NET 3.5 Basics (http://p2p.wrox.com/forumdisplay.php?f=351)
-   -   How do I make a placeholder parameter for Ado.net (http://p2p.wrox.com/showthread.php?t=71636)

chobo2 November 27th, 2008 11:49 PM

How do I make a placeholder parameter for Ado.net
 
Hi

I usually use Sql server 2005 but I have to right now have to use access 2007 and I am wondering how do I make a parameter placeholder?

Like if this was sql server 2005 I would do something like this

SELECT StudentID, FirstName, LastName
FROM Students
WHERE (StudentID = '@StudentID')

* I am making this with a typed dataset(dataset.xsd file). So I usually would type that into the design builder and then execute it. Once I hit the execute the button it would then popup and ask to put some data in as the parameter.

When I try the exact same statement with an access 2007 db it just runs the statement and seems to ignore that it's a placeholder parameter and tries to use it as part of the where clause so I always get no results.


Lee Dumond November 28th, 2008 12:17 AM

You use a question mark:

SELECT StudentId, FirstName, Lastname FROM Students WHERE StudentID = ?

_________________________________

Visit my blog at http://leedumond.com

chobo2 November 28th, 2008 12:30 AM

So you can't give it a name?

Also where do you find ado.net stuff + access stuff I am having a hard time finding information on it.


Bob Bedell November 29th, 2008 01:13 AM

You can use the EXACT same approach to executing Access "stored procedures" as you can executing Sql Server stored procedures. Here is how its done. Since you are using Access 2007 you are using the Jet 4.0 engine whichh support Sql Server Compatible Syntax. But first you have to enable it. In your Access database do the following:

Tools --> Options --> Tables/Queries

In the Sql Server Compatible Syntax (ANSI 92) group check 'This database'

Now create a data definition query in the query designer SQL view:

Code:

CREATE PROCEDURE GetStudentById
(
[@studentId] int
)
AS
SELECT
   StudentID,
   FirstName,
   LastName
FROM
  Students
WHERE
  StudentID = [@studentId];

The barckets around the parameter a required!

Now run the query. You'll now see a saved, parameterized query named GetStudentById back in the Database Objects windows. Its SQL is:

Code:


PARAMETERS [@studentId] Long;
SELECT Students.StudentID, Students.FirstName, Students.LastName
FROM Students
WHERE (((Students.StudentID)=[@studentId]));

You now have a saved Access "stored procedure to work with just as you would work with a Sql Server Stored Procedure. The ADO.NET code to use it is IDENTICAL, except that you are of course using OleDb objects and the OleDb provider in your connection string. For example, here's a little Console app:

Code:


using System;
using System.Data;
using System.Data.OleDb;
using System.Diagnostics;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            GetStudentById(1);
        }

        public static void GetStudentById(int studentID)
        {
            using (OleDbConnection connection = new OleDbConnection
                 (@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Students.mdb;Persist Security Info=True"))
            {
                connection.Open();
                using (OleDbCommand command = connection.CreateCommand())
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = "GetStudentById";

                    command.Parameters.AddWithValue("@studentID", studentID);

                    using (OleDbDataReader reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                Trace.WriteLine(reader.GetInt32(0));
                                Trace.WriteLine(reader.GetString(1));
                                Trace.WriteLine(reader.GetString(2));
                            }
                        }
                    }
                }
            }
        }
    }
}

This approach allows you to essnetially reuse you Sql Server stored procedures and ADO.NET code when migrating between Sql Server and Access.

HTH,

Bob




Bob Bedell November 29th, 2008 12:00 PM

I don't work with typed datasets much, and even less with the "builders" and such, so I'm not quite sure what "I usually would type that into the design builder and then execute it" means. But I thought I'd add that using a named parameter (e.g., "@studentID") in code works fine with datasets too, if that would be at all helpful:

Code:


public static void GetStudentById(int studentID)
        {
            using (OleDbConnection connection = new OleDbConnection
                 (@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Students.mdb;Persist Security Info=True"))
            {
                connection.Open();
                using (OleDbCommand command = connection.CreateCommand())
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = "GetStudentById";

                    command.Parameters.AddWithValue("@studentID", studentID);

                    OleDbDataAdapter adapter = new OleDbDataAdapter(command);
                    adapter.TableMappings.Add("Table", "Students");

                    StudentsDataSet dataset = new StudentsDataSet();

                    adapter.Fill(dataset);

                    DataSetDump.ShowDSInOutputWindow(dataset);

                }
            }
        }

If you used the code approach, however, your project can't maintain an open Data Connection to the .mdb, or you'll be locked out. The connection has to be opened and closed in code as well.

Here's the dump routine if you want to check out the DataSet contents:

Code:


class DataSetDump
    {
        // Call with: DataSetDump.ShowDSInOutputWindow(ds);
        // using directives: System.Data, System.Data.OleDb & System.Diagnostics
        static public void ShowDSInOutputWindow(DataSet ds)
        {
            DataTable table = ds.Tables[0];

            Debug.WriteLine("---------------------------------------");
            Debug.WriteLine("\t\t DataSet Table: " + table.ToString());

            bool firstRow = true;

            foreach (DataRow row in table.Rows)
            {
                if (firstRow)
                {
                    foreach (DataColumn column in table.Columns)
                    {
                        if (column.DataType.Name != "Byte[]")
                        {
                            Debug.Write(column.ColumnName + "\t");
                        }
                    }
                    Debug.WriteLine(String.Empty);
                    firstRow = false;
                }
                foreach (DataColumn column in table.Columns)
                {
                    if (column.DataType.Name != "Byte[]")
                    {
                        Debug.Write(row[column] + "\t");
                    }
                }
                Debug.WriteLine(String.Empty);
            }
        }
    }


chobo2 November 29th, 2008 05:09 PM

Hi

where is tool->options->Table/Queries on the ribbon?

With this "I usually would type that into the design builder and then execute it"

What I mean is when you go through a typed dataset wizard it has a sql query builder where you can just click some options and it would generate the sql for you. It also has an execute button that allows you try the statements out so you can see if it's what you want to return.


I was using that to try my sql queries out since I did not know access had it's own tester.


Bob Bedell November 30th, 2008 01:00 AM

My apologies for pretty much missing the boat on this one. I didn't realize until after my second post that you were probably talking about the Table Adapter Query Configuration Wizard. Haven't used it since last December, and that was against a Sql Server database. Never tried using a typed dataset against Access, so generally speaking, I can't be of any help here (though you got me curious and I'll probably have to play with it tomorrow).

tool->options->Table/Queries refers to the main menu in Access. I focus all my limited data access attention on using custom business objects populated by SqlDataReaders and occassionally OleDbDataReaders. The code I posted would be useful if you were taking this approach, but is generally useless to you if you are using a disconnected typed dataset.

Bob



All times are GMT -4. The time now is 02:37 PM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.