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>
