Aug 21

Random C# and MS SQl Server #19

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=5300
Categories:19
Tags:
Post Data:2007-02-11 12:57:35

    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>