View Single Post
  #5 (permalink)  
Old November 29th, 2008, 12:00 PM
Bob Bedell Bob Bedell is offline
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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);
            }
        }
    }