TADOStoredProc alternative for C#

0
votes

I have been developing for Delphi for quite some time and gotten quite used to the TADOStoredProc to deal with stored procedures. The Delphi IDE by Embarcadero did lots of nice things like automatically get all the parameters automatically generate properties for the StoredProc object to access the return types. All of this happened at design time.

I recently made the switch to C# and Visual Studio 2013 and I don't see any of those nice features. I have to manually add all the parameters in code and to access the return data, I have to use DataRow object. Is there a nice feature like TADOStoredProc in Visual Studio (MS or Third Party)?

I'm currently doing this

SqlConnection sc = new SqlConnection(connectionString);
SqlCommand com = new SqlCommand("StorePrc", sc);
com.CommandType = System.Data.CommandType.StoredProcedure;
com.Parameters.AddWithValue("@id", 1);

using (SqlDataReader sdr = com.ExecuteReader())
{
     ...
}

Thanks in advance

1
c#delphistored-proceduresvisual-studio-2013
I would expect there to be something, seeing as C# was written by the same guy who wrote the Delphi language :-) - Jerry Dodge
@Jerry Anders didn't design all of the libraries. For either Delphi or C#. - David Heffernan

1 Answers

0
votes

After spending a lot of time with this, I have concluded that there is no nice way of doing this. You can set it up so that you get the same results with the same type of objects but that way is very messy and horrible to maintain. Here is what I propose instead.

  1. First create your stored procedures. For insert, update, delete and select
  2. Follow this article -> http://msdn.microsoft.com/en-us/library/bb163707.aspx.

At the end of this step, you should have a dataset with the return params from your select stored procedure and a table adapter with a Fill and GetData Method

  1. Hit build before you continue
  2. Create a new form. Drag (from the toolbox) on a DataGridView, Dataset (the one you just created), TableAdapter (one you just created), TableAdapterManager (one you just created), BindingSource and ButtonNavigator

  3. Set the following properties for the objects in the property browser window

    • BindingSource: DataSource = dataset, DataMember = Select Stored Proc
    • TableAdapterManager: Should have a reference to the correct table adapter
    • DataGridView: DataSoruce = bindingSource
    • ButtonNavigator: DataSource = bindingSource

  4. The grid should auto generate the columns in the dataset. (Franlky the auto generated columns are completely useless and I would recommend turning that feature off and doing this yourself)

  5. To refresh the data you need to do

    tableAdapter.Fill(dataset, ... select stored proc params ...);

  6. To save the changes in the grid you need to do

    tableAdapterManager.UpdateAll(dataset);

This is quite different to how I used to do it in Delphi. In some ways it is better, in some ways it is worse; it's just what you have to deal with. I hope this helps someone (possibly me) in the future