ASP.NET Week 4 - Stored Procedures w/ OleDb

Welcome back Sportsball Fans! Today we’re going to review the basics of using a stored procedure in a database using and OleDb command / connection object. This is pretty much the same as using an SQLCommand, all the syntax is (seems to be) the same, so this information should work whether you are using Ole, or the regular kind of connection. Here is a brief overview of what you're going to learn:

Our outline

1. Creating the necessary command and connection objects & Setting them up.
2. Configuring those objects to use a stored procedure.
3. Setting up variables for passing arguments to a stored procedure.
4. Putting it all in a try catch block for safety.

Here we go!

Section 1:

You create the objects, and initialize them to connect to the database, in pretty much the exact same way you would for any other C# database access in code.

1 2 3 4 5 6 7 8

OleDbConnection conn = new OleDbConnection(); OleDbCommand cmd = new OleDbCommand(); string strOledbConnection = @"Provider=SQLOLEDB; Data Source=.; Integrated Security=SSPI; Initial Catalog=yourDbNameHere"; conn.ConnectionString = strOledbConnection; conn.Open();

Section 2:

Configuring the object(s) is actually also very easy and fast. First, we need to set the connection and commandText of the cmd object. The first is trivial, as we have all done many times before, however setting the command text might be new-ish to some, so just remember that when setting the commandText to use only the name of the stored procedure – no arguments or parameters, and it is case sensitive (of course). After you’ve set these, all you have to do is set the CommandType to “CommandType.StoredProcedure” and you’re ready to move on to the next (slightly more complicated) step.

1 2 3

cmd.Connection = conn; cmd.CommandText = "nameOfStoredProcedure"; cmd.CommandType = CommandType.StoredProcedure;

Section 3:

In order to pass arguments to our stored Procedure, we first need to set up a few temporary variables, these should be of the ‘OleBdParameter’ type and will have data stored in them the ‘cmd.Parameters.Add()’ method. You will need one variable per parameter. In this example we have four.

1 2 3 4

OleDbParameter strName = cmd.Parameters.Add("@Name", OleDbType.VarChar, 50); OleDbParameter strEmail = cmd.Parameters.Add("@Email", OleDbType.VarChar, 50); OleDbParameter strHandle = cmd.Parameters.Add("@Handle", OleDbType.VarChar, 50); OleDbParameter strReason = cmd.Parameters.Add("@Reason", OleDbType.VarChar, 50);

Each Parameter has 3 … well, … parameters. The 1st is a handle for when you want to pass them in as a combined string format when creating “standard” SQL commands, as in: “Insert into [Logins] ([Name],[EmailAddress],[LoginName],[ReasonForAccess]) Values (@Name, @EmailAddress, @LoginName, @ReasonForAccess );”. The 2nd and 3rd parameters should match the variable type in the db in type and size. After setting them up, you will want to set each one’s ‘direction’ to ‘input’ as shown.

1 2 3 4

strName.Direction = ParameterDirection.Input; strEmail.Direction = ParameterDirection.Input; strHandle.Direction = ParameterDirection.Input; strReason.Direction = ParameterDirection.Input;

After successfully setting up your Stored Procedure input variables as above, you are now ready to assign data to them in a very simple C# fashion, and can then run your (non)Query as usual:

1 2 3 4 5 6

strName.Value = tbName.Text; strEmail.Value = tbEmail.Text; strHandle.Value = tbHandle.Text; strReason.Value = tbReasons.Text; cmd.ExecuteNonQuery();

Section 4:

All this should really be done in a try catch block for safety. I don’t think I should really need to explain why this is the case, but I will show you a complete code block here below so that you can see out “final product”.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44

protected void Button1_Click(object sender, EventArgs e) { OleDbConnection conn = new OleDbConnection(); OleDbCommand cmd = new OleDbCommand(); try { //1. Make a Connection string strOledbConnection = @"Provider=SQLOLEDB; Data Source=.; Integrated Security=SSPI; Initial Catalog=ASPNetHomework"; conn.ConnectionString = strOledbConnection; conn.Open(); //2. Issue a Command cmd.Connection = conn; cmd.CommandText = "pInsLogins"; cmd.CommandType = CommandType.StoredProcedure; OleDbParameter strName = cmd.Parameters.Add("@Name", OleDbType.VarChar, 50); OleDbParameter strEmail = cmd.Parameters.Add("@Email", OleDbType.VarChar, 50); OleDbParameter strHandle = cmd.Parameters.Add("@Handle", OleDbType.VarChar, 50); OleDbParameter strReason = cmd.Parameters.Add("@Reason", OleDbType.VarChar, 50); strName.Direction = ParameterDirection.Input; strEmail.Direction = ParameterDirection.Input; strHandle.Direction = ParameterDirection.Input; strReason.Direction = ParameterDirection.Input; strName.Value = tbName.Text; strEmail.Value = tbEmail.Text; strHandle.Value = tbHandle.Text; strReason.Value = tbReasons.Text; cmd.ExecuteNonQuery(); //3. Process the Results Label1.Text += "Request Submitted Successfully."; } catch (Exception ex) { Label1.Text += "<b>" + cmd.CommandText.ToString() + "</b><br /><br />"; Label1.Text += ex.ToString(); } finally { conn.Close(); } //4. Run clean up code }

Best of luck to you in your coding.
See you next time!