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>
