FromSitesTree.com

Random C# and MS SQl Server #.Net Web Applications

Leave a comment

Brought from: http://salearningschool.com/displayArticle.php?table=Articles&articleID=1321&title=Random%20C#%20and%20MS%20SQl%20Server

Foreign Key in Table Declaration


CREATE TABLE ORDERS (
   ID integer primary key, 
   Order_Date datetime, 
   Customer_ID integer references CUSTOMER(ID), 
   Amount double
); 

ALTER TABLE ORDERS ADD FOREIGN KEY (customer_id) REFERENCES CUSTOMER(ID);

Stored Procedure Example

    CREATE PROCEDURE [dbo].[procedure_name]
      @param1 VARCHAR(100)
     ,@param2 VARCHAR(200) OUTPUT
    AS
    BEGIN
       DECLARE @param3 VARCHAR(100)
       SET @param3 = ' '
       IF @param1 IS NOT NULL AND LEN(@param1) > 1
            SELECT @param2 = 'The ' + @param1  + @param3
        ELSE 
            SELECT  @param2 = '...is cool!'
        RETURN
    
    END
    GO

C# and Prepared Statements

     
     int  id = 20;
     string  desc = "...." ;
     SqlConnection rConn = new SqlConnection("Persist Security Info=False;Integrated Security=SSPI;
        database=northwind;server=mySQLServer");
     rConn.Open();
     SqlCommand command = new SqlCommand(null, rConn);

     // Create and prepare an SQL statement.
     command.CommandText = "insert into Region (ID, Description) values (@id, @desc)" ;
     command.Parameters.Add ( "@id", id) ;
     command.Parameters.Add ( "@desc", desc) ;
     command.Prepare() ;  // Calling Prepare after having set the Commandtext and parameters.
     command.ExecuteNonQuery();

     // Change parameter values and call ExecuteNonQuery.
     command.Parameters[0].Value = 21;
     command.Parameters[1].Value = "mySecondRegion";
     command.ExecuteNonQuery();

   conn = new SqlConnection(strConnectionString);
   SqlCommand cmd = new SqlCommand();
   cmd.CommandText ="insert into Profile values(@Name,@Age)";
   cmd.Connection = conn;
   cmd.CommandType = CommandType.Text;

   SqlParameter nameP = new SqlParameter();
   nameP.SqlDbType =SqlDbType.NVarChar;
   nameP.ParameterName = "@Name";
   nameP.Size = 50;
   nameP.Value = model.Name;

   SqlParameter ageP = new SqlParameter();
   ageP.SqlDbType =SqlDbType.Int;
   ageP.ParameterName = "@Age";
   ageP.Value = model.Age;

   cmd.Parameters.Add(nameP);
   cmd.Parameters.Add(ageP);

   conn.Open();
   cmd.Prepare(); 

   cmd.ExecuteNonQuery();

C# and Stored Procedure

       conn = new SqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI");
       conn.Open();

       SqlCommand cmd  = new SqlCommand("sp_name", conn);

       cmd.CommandType = CommandType.StoredProcedure;
       cmd.Parameters.Add(new SqlParameter("@CustomerID", custId));

       // execute the command
       rdr = cmd.ExecuteReader();

       // iterate through results, printing each to console
       while (rdr.Read())
       {
          Console.WriteLine("" + rdr["Total"] );
       }

From: http://sitestree.com/?p=3724
Categories:.Net Web Applications
Tags:
Post Data:2016-07-16 11:01:19

    Shop Online: <a href='https://www.ShopForSoul.com/' target='new' rel="noopener">https://www.ShopForSoul.com/</a>
    (Big Data, Cloud, Security, Machine Learning): Courses: <a href='http://Training.SitesTree.com' target='new' rel="noopener"> http://Training.SitesTree.com</a> 
    In Bengali: <a href='http://Bangla.SaLearningSchool.com' target='new' rel="noopener">http://Bangla.SaLearningSchool.com</a>
    <a href='http://SitesTree.com' target='new' rel="noopener">http://SitesTree.com</a>
    8112223 Canada Inc./JustEtc: <a href='http://JustEtc.net' target='new' rel="noopener">http://JustEtc.net (Software/Web/Mobile/Big-Data/Machine Learning) </a>
    Shop Online: <a href='https://www.ShopForSoul.com'> https://www.ShopForSoul.com/</a>
    Medium: <a href='https://medium.com/@SayedAhmedCanada' target='new' rel="noopener"> https://medium.com/@SayedAhmedCanada </a>

Leave a Reply