//_____________________________________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); } }