Using stored procedures to insert and pull data from database RSS

• • Reply
  

  ahlaj77

  Member

  28 Points

  100 Posts

  Using stored procedures to insert and pull data from database

  Mar 21, 2008 07:42 PM|LINK

  I have my database: "RequestTrack"

  My table (with its columns):
  "Request"
  RequestKey (automatically generated)..and the Primary Key
  EntryDate  (datetime)
  Summary (nvarchar)
  RequestStatusCodeKey (bigint)
  EntryUserID   (nvarchar)
  EntryUserEmail (nvarchar)
  
  I am wanting to create a basic web form where my user interface has 3 text boxes and a Submit button:

  txtUserID.Text
  txtEmailAddress.Text
  txtRequestSummary.Text

  **After I hit the submit button the information will then be inserted into the database. Also the RequestStatusCodeKey will be MANUALLY typed in so that will not require the user to add that. Please please please help ! I've been searching online for days and looking at various websites and still havent found anything. I've found somethings but they went into too much depth with too much information. I am just wanting to stay basic but w/o using SQLDataSource Controls. I would like to be able to store a lot of data. Thanks for your help!!! [Yes]

  "People will mainly remember you for who you are, not what you were a part of"
  • Moved by limno on Mar 21, 2008 07:47 PM
  • Edited by ahlaj77 on Mar 24, 2008 07:33 PM
• • Reply
  

  r_nassabeh

  Contributor

  3106 Points

  505 Posts

  Re: Using stored procedures to insert and pull data from database

  Mar 21, 2008 10:07 PM|LINK

  There are dozens of ways to do this, but one that you are looking for may be something like this:

  add the following stored procedure to your database:

   

  CREATE PROCEDURE InsertRequest
  
  
  @Summary nvarchar(50),
  @RequestStatusCodeKey bigint,
  @EntryUserID  nvarchar(50),
  @EntryUserEmail nvarchar(50)
  
  
  AS
  	INSERT INTO Request (EntryDate, Summary, RequestStatusCodeKey, EntryUserID, EntryUserEmail)
  	VALUES (GetDate(), @Summary, @RequestStatusCodeKey, @EntryUserID, @EntryUserEmail)
  RETURN
  GO

   

  Then you can put the following code in your page to insert a request:

  using System.Data.SqlClient;
  
  string connectionString = "Data Source=MyServer;Integrated Security=True;Initial Catalog=MyDataBase";
  
  using (SqlConnection connection = new SqlConnection(connectionString))
  {
      using (SqlCommand command = new SqlCommand("InsertRequest", connection))
      {
          command.CommandType = CommandType.StoredProcedure;
          command.Parameters.Add(new SqlParameter("@Summary", txtRequestSummary.Text));
          command.Parameters.Add(new SqlParameter("@RequestStatusCodeKey", "SomeValue"));
          command.Parameters.Add(new SqlParameter("@EntryUserId", txtUserID.Text));
          command.Parameters.Add(new SqlParameter("@EntryUserEmail", txtEmailAddress.Text));
          connection.Open();
          command.ExecuteNonQuery();
      }
  }

   

  Reza Nassabeh
  www.professionalcsharp.com
  

  ---

  
  Don't forget to click Mark as Answer on the post that helped. That way future readers will know which post solved the issue.
• • Reply
  

  ahlaj77

  Member

  28 Points

  100 Posts

  Re: Using stored procedures to insert and pull data from database

  Mar 24, 2008 12:24 PM|LINK

  using System.Data.SqlClient;
  
  
  string connectionString = "Data Source=MyServer;Integrated Security=True;Initial Catalog=MyDataBase";
  
  
  using (SqlConnection connection = new SqlConnection(connectionString))
  {
      using (SqlCommand command = new SqlCommand("InsertRequest", connection))
      {
          command.CommandType = CommandType.StoredProcedure;
          command.Parameters.Add(new SqlParameter("@Summary", txtRequestSummary.Text));
          command.Parameters.Add(new SqlParameter("@RequestStatusCodeKey", "SomeValue"));
          command.Parameters.Add(new SqlParameter("@EntryUserId", txtUserID.Text));
          command.Parameters.Add(new SqlParameter("@EntryUserEmail", txtEmailAddress.Text));
          connection.Open();
          command.ExecuteNonQuery();
      }
  }

  ************************

  I have three Pages  Default.aspx, AddRequest.aspx (this is the one that has the three text boxes on it), and RequestConfirmation.aspx. Do I place this code in a business object layer? I am using stored procedures. And wanting to connect the user interface through a business object I believe. Any help is appreciated :^)!! Thanks!

  "People will mainly remember you for who you are, not what you were a part of"
  • Edited by ahlaj77 on Mar 24, 2008 01:56 PM
  • Edited by ahlaj77 on Mar 24, 2008 07:32 PM
• • Reply
  

  r_nassabeh

  Contributor

  3106 Points

  505 Posts

  Re: Using stored procedures to insert and pull data from database

  Mar 24, 2008 08:51 PM|LINK

  Actually the best place for this code to be placed is a custom class in the Data Access Layer (DAL). You can wrap it inside a method which can be called by the Business Layer or directly by the AddRequest.aspx page. Here is a sample code for the DAL class:

   

  public class RequestDB
  {
      // It is better to be retrieved from the ConnectionStrings in the web.config
      private static string connectionString = "Data Source=MyServer;Integrated Security=True;Initial Catalog=MyDataBase";
  
  
      public static void AddRequest(string userId, string email, string summary)
      {
          using (SqlConnection connection = new SqlConnection(connectionString))
          {
              using (SqlCommand command = new SqlCommand("InsertRequest", connection))
              {
                  command.CommandType = CommandType.StoredProcedure;
                  command.Parameters.Add(new SqlParameter("@Summary", summary));
                  command.Parameters.Add(new SqlParameter("@RequestStatusCodeKey", "SomeValue"));
                  command.Parameters.Add(new SqlParameter("@EntryUserId", userId));
                  command.Parameters.Add(new SqlParameter("@EntryUserEmail", email));
                  connection.Open();
                  command.ExecuteNonQuery();
              }
          }
      }
  }

   and here is the code in AddRequest.aspx

  RequestDB.AddRequest(txtUserID.Text, txtEmailAddress.Text, txtRequestSummary.Text);

   

  Reza Nassabeh
  www.professionalcsharp.com
  

  ---

  
  Don't forget to click Mark as Answer on the post that helped. That way future readers will know which post solved the issue.
  • Marked as answer by ahlaj77 on Mar 25, 2008 05:23 PM
• • Reply
  

  ANILBABU

  Member

  67 Points

  127 Posts

  Re: Using stored procedures to insert and pull data from database

  Nov 08, 2012 04:30 AM|LINK

  using System.Data.SqlClient;

  using sysstem.data;
  
  
  string connectionString = "Data Source=MyServer;Integrated Security=True;Initial Catalog=MyDataBase";
  
  
   SqlConnection connection = new SqlConnection(connectionString);
      SqlCommand command = new SqlCommand("InsertRequest", connection);

      
          command.CommandType = CommandType.StoredProcedure;
          command.Parameters.Add(new SqlParameter("@Summary", txtRequestSummary.Text));
          command.Parameters.Add(new SqlParameter("@RequestStatusCodeKey", "SomeValue"));
          command.Parameters.Add(new SqlParameter("@EntryUserId", txtUserID.Text));
          command.Parameters.Add(new SqlParameter("@EntryUserEmail", txtEmailAddress.Text));

  try

  {

          connection.Open();
          command.ExecuteNonQuery();

   Label1.text="Insert data success";
      }

  catch (Exception ex) 
   { 
   throw ex; 
   } 
   finally 
   { 
   con.Close(); 
   con.Dispose(); 
   } 

  
  }