How to run a sp from a C# code?

Question

Hi,

I am new to ADO.net. I actually created a sample database and a sample stored procedure. I am very new to this concept. I am not sure of how to make the connection to the database from a C# windows application. Please guide me with some help or sample to do the same.

Answer 1

It sounds like you are looking for a tutorial on ADO.NET.

Here is one about straight ADO.NET.
Here is another one about LINQ to SQL.

Answer 2

Something like this... (assuming you'll be passing in a Person object)

public int Insert(Person person)
{
SqlConnection conn = new SqlConnection(connStr);
conn.Open();
SqlCommand dCmd = new SqlCommand("InsertData", conn);
dCmd.CommandType = CommandType.StoredProcedure;
try
{
dCmd.Parameters.AddWithValue("@firstName", person.FirstName);
dCmd.Parameters.AddWithValue("@lastName", person.LastName);
dCmd.Parameters.AddWithValue("@age", person.Age);
return dCmd.ExecuteNonQuery();
}
catch
{
throw;
}
finally
{
dCmd.Dispose();
conn.Close();
conn.Dispose();
}
}

Answer 3

This is the usual pattern (it might be a bit different for different databases, Sql Server does not require you to specify the parameters in the command text, but Oracle does, and in Oracle, parameters are prefixed with : not with @)

using(var command = yourConnection.CreateCommand())
{
   command.CommandText = "YOUR_SP_CALL(@PAR1, @PAR2)";
   command.CommandType = CommandType.StoredProcedure;
   command.Parameters.Add(new OdbcParameter("@PAR1", "lol"));
   command.Parameters.Add(new OdbcParameter("@PAR2", 1337));
   command.ExecuteNonQuery();
}

Answer 4

Something like this:

var connectionString = ConfigurationManager.ConnectionStrings["YourConnectionString"].ConnectionString;

        var conn = new SqlConnection(connectionString);

        var comm = new SqlCommand("YourStoredProc", conn) { CommandType = CommandType.StoredProcedure };

        try
        {
            conn.Open();


            // Create variables to match up with session variables
            var CloseSchoolID = Session["sessCloseSchoolID"];


            //  SqlParameter for each parameter in the stored procedure YourStoredProc
            var prmClosedDate = new SqlParameter("@prmClosedDate", closedDate);
            var prmSchoolID = new SqlParameter("@prmSchoolID", CloseSchoolID);

            // Pass the param values to YourStoredProc
            comm.Parameters.Add(prmClosedDate);
            comm.Parameters.Add(prmSchoolID);

            comm.ExecuteNonQuery();
        }

        catch (SqlException sqlex)
        {

        }

        finally
        {
            conn.Close();
        }

Answer 5

Here is a good starting point http://support.microsoft.com/kb/310130

OdbcConnection cn; OdbcCommand cmd; OdbcParameter prm; OdbcDataReader dr;

try { //Change the connection string to use your SQL Server. cn = new OdbcConnection("Driver={SQL Server};Server=servername;Database=Northwind;Trusted_Connection=Yes");

//Use ODBC call syntax.
cmd = new OdbcCommand("{call CustOrderHist (?)}", cn);

prm = cmd.Parameters.Add("@CustomerID", OdbcType.Char, 5);
prm.Value = "ALFKI";

cn.Open();

dr = cmd.ExecuteReader();

//List each product.
while (dr.Read()) 
Console.WriteLine(dr.GetString(0));

//Clean up.
dr.Close();
cn.Close();

} catch (OdbcException o) { MessageBox.Show(o.Message.ToString()); }

Answer 6

If using SQL Server:

SqlConnection connection = new SqlCOnnection("Data Source=yourserver;Initial Catalog=yourdb;user id=youruser;passowrd=yourpassword");
SqlCommand cmd = new SqlCommand("StoredProcName", connection);
cmd.CommandType=StoredProcedureType.Command;
connection.Open();
cmd.ExecuteNonQuery();
connection.Close();

If not then replace Sql with Ole and change the connection string.