//_____________________________________WHY WE USE USING______________________
// using is used because
// Using calls Dispose() explicitely
// using (MyClass mine = new MyClass())
// {
// mine.Action();
// }
// would do the same as:
// MyClass mine = new MyClass();
// try
// {
// mine.Action();
// }
// finally
// {
// if (mine != null)
// mine.Dispose();
// }
// Using using is way shorter and easier to read.
//__________________________READING using SQLCOMMNAD with SQLDATAREADER_______________________________
public static List <DAOModelOfMyOwn> AddUsers()
{
DAOModelOfMyOwn ob = null;
List <DAOModelOfMyOwn> daolist = new List <DAOModelOfMyOwn>();
using (SqlConnection connection = new SqlConnection("Server=.; Database=ShoppingCartT4; Integrated Security=true")) //integrated security is the windows security..
{
int a = 0;
//connection.Open(); //opening the connection...
string sqlQuery = @"select * from Customer";
SqlCommand sqlcommand = new SqlCommand(sqlQuery, connection); //takes two parameters..sql query and connection string...
sqlcommand.Connection.Open(); //opens connection if its not open
a += sqlcommand.ExecuteNonQuery(); //executes the query..
Debug.WriteLine(a);
SqlDataReader reader = sqlcommand.ExecuteReader(); //this requires active and open database...so to read or fetch row by row
while (reader.Read())
{
ob = new DAOModelOfMyOwn()
{
customer_id = (int)reader["customer_id"],
username = (string)reader["username"],
firstname = (string)reader["firstname"],
password = (string)reader["password"],
session_id = (string)reader["session_id"],
lastname = (string)reader["lastname"]
};
daolist.Add(ob);
}
}
return(daolist);
}
public ActionResult SQLParameterWithSQlAdapter()
{
DAOModelOfMyOwn ob = new DAOModelOfMyOwn();
ob.customer_id = 13121;
ob.firstname = "eeeelalfdfda";
ob.password = "******";
ob.session_id = "eeelfgdfgala";
ob.username = "******";
ob.lastname = "eeffddfgdfgfs";
Dao.SQLParameterWithSQlAdapter(ob);
return(View("CallSqlDataAdapter"));
}
public ActionResult SQLParameter()
{
DAOModelOfMyOwn ob = new DAOModelOfMyOwn();
ob.customer_id = 1;
ob.firstname = "lala";
ob.password = "******";
ob.session_id = "lala";
ob.username = "******";
ob.lastname = "ffdfs";
Dao.ParameterBinding(ob);
return(null);
}
//______________________INSERTING USING SQLPARAMETER AND SQLCOMMAND ________________________
public static void ParameterBinding(DAOModelOfMyOwn obj)
{
using (SqlConnection connection = new SqlConnection("Server=.; Database=ShoppingCartT4; Integrated Security=true")) //integrated security is the windows security..
{
string sqlQuery = @"insert into customer(customer_id,username,firstname,password,session_id,lastname) values (@customer_id,@username,@firstname,@password,@session_id,@lastname)";
SqlParameter param1 = new SqlParameter();
SqlParameter param2 = new SqlParameter();
SqlParameter param3 = new SqlParameter();
SqlParameter param4 = new SqlParameter();
SqlParameter param5 = new SqlParameter();
SqlParameter param6 = new SqlParameter();
param1.ParameterName = "@customer_id";
param1.Value = obj.customer_id;
param2.ParameterName = "@username";
param2.Value = obj.username;
param3.ParameterName = "@firstname";
param3.Value = obj.firstname;
param4.ParameterName = "@password";
param4.Value = obj.password;
param5.ParameterName = "@session_id";
param5.Value = obj.session_id;
param6.ParameterName = "@lastname";
param6.Value = obj.lastname;
SqlCommand sqlcommand = new SqlCommand(sqlQuery, connection); //takes two parameters..sql query and connection string...
sqlcommand.Connection.Open(); //opens connection if its not open
sqlcommand.Parameters.Add(param1);
sqlcommand.Parameters.Add(param2);
sqlcommand.Parameters.Add(param3);
sqlcommand.Parameters.Add(param4);
sqlcommand.Parameters.Add(param5);
sqlcommand.Parameters.Add(param6);
sqlcommand.ExecuteNonQuery(); //executes the query..
//SqlDataReader reader = sqlcommand.ExecuteReader();
}
}
//___________________INSERTING USING SQL DATAADAPTER and SQL PARAMETER________________________________
public static void SQLParameterWithSQlAdapter(DAOModelOfMyOwn obj)
{
DataTable dt = new DataTable();
using (SqlConnection connection = new SqlConnection("Server=.; Database=ShoppingCartT4; Integrated Security=true")) //integrated security is the windows security..
{
string sqlQuery = @"insert into customer(customer_id,username,firstname,password,session_id,lastname) values (@customer_id,@username,@firstname,@password,@session_id,@lastname)";
SqlParameter param1 = new SqlParameter();
SqlParameter param2 = new SqlParameter();
SqlParameter param3 = new SqlParameter();
SqlParameter param4 = new SqlParameter();
SqlParameter param5 = new SqlParameter();
SqlParameter param6 = new SqlParameter();
param1.ParameterName = "@customer_id";
param1.Value = obj.customer_id;
param2.ParameterName = "@username";
param2.Value = obj.username;
param3.ParameterName = "@firstname";
param3.Value = obj.firstname;
param4.ParameterName = "@password";
param4.Value = obj.password;
param5.ParameterName = "@session_id";
param5.Value = obj.session_id;
param6.ParameterName = "@lastname";
param6.Value = obj.lastname;
SqlDataAdapter sqladapter = new SqlDataAdapter(sqlQuery, connection);
sqladapter.SelectCommand.Parameters.Add(param1);
sqladapter.SelectCommand.Parameters.Add(param2);
sqladapter.SelectCommand.Parameters.Add(param3);
sqladapter.SelectCommand.Parameters.Add(param4);
sqladapter.SelectCommand.Parameters.Add(param5);
sqladapter.SelectCommand.Parameters.Add(param6);
sqladapter.Fill(dt);
}
}