When you are coming from using SqlCommand and SqlConnection is difficult to move to another library for your database needs. For those people still in the limbo to make the decision to move to another DAL, here is a comparison to help you see the light or to move away for ever.
How to do a select query using SqlCommand:
1: SqlConnection myConnection = new SqlConnection(@"Data Source=PROG-3407\SQLEXPRESS;Initial Catalog=Demo;Integrated Security=True;Pooling=False");
2:
3: string sQuery = "SELECT * FROM Table1";
4:
5: SqlCommand myCommand = new SqlCommand(sQuery);
6: myCommand.Connection = myConnection;
7:
8: myConnection.Open();
9:
10: SqlDataReader dataReader = myCommand.ExecuteReader();
11:
12: while (dataReader.Read() == true)
13: {
14: string sID = dataReader[0].ToString();
15: string sName = dataReader[1].ToString();
16: string sCity = dataReader[2].ToString();
17:
18: MyData dat = new MyData()
19: {
20: ID = Int32.Parse(sID),
21: Name = sName,
22: City = sCity
23: };
24: myDataCollection.Add(dat);
25: }
26:
27: GridView1.DataSource = myDataCollection;
28: GridView1.DataBind();
29:
30: dataReader.Close();
31: myConnection.Close();
And now a select query in LINQ to SQL
1: Table1DataContext dataContext = new Table1DataContext();
2:
3: var all = from p in dataContext.Table1s orderby p.City select p;
4:
5: GridView1.DataSource = all;
6: GridView1.DataBind();
Pretty short isn’t it?, the select statement is inline with a little help from intellisense.
Insert in SqlCommand
1: using (SqlConnection myConnection = new SqlConnection(@"Data Source=PROG-3407\SQLEXPRESS;Initial Catalog=Demo;Integrated Security=True;Pooling=False"))
2: {
3: string sQuery = "INSERT INTO Table1 (Name,City) values(@Name,@City)";
4:
5: SqlCommand myCommand = new SqlCommand(sQuery);
6: myCommand.Connection = myConnection;
7:
8: myConnection.Open();
9:
10: myCommand.Parameters.AddWithValue("@Name", TextBoxName.Text);
11: myCommand.Parameters.AddWithValue("@City", TextBoxCity.Text);
12:
13: myCommand.ExecuteNonQuery();
14: }
Insert in LINQ in SQL
1: Table1DataContext dataContext = new Table1DataContext();
2:
3: Table1 row = new Table1()
4: {
5: City = TextBoxCity.Text,
6: Name = TextBoxName.Text
7: };
8:
9: dataContext.Table1s.InsertOnSubmit(row);
10: dataContext.SubmitChanges();
Hope this helps
Cheers
Al