ADO.net – 就是C#讀取Database的Framework, 可以Call SQL, Store Procedure. 另外, 它為ENTITY FRAMEWORK的底層.
ADO.net好處
- ADO.net比起Entity Framework附加更少的CODE. Entity Framework的好處為Auto Generate Code或Database, 但問題在Entity Framework及Linq中, 自動產生大量附加的CODE, 這令它會在處理大量資料時會較ADO.net慢.
- 而要對Entity Frameworkm自動生成的資料作更改也不容易, 例如Database的資料表MAPPING的更改等. 另外, 由於是底層的關係, ADO.net的擴展可能性大, 要在底層上加上東西比在中層上加東西可能性更大.
以下就來介紹 – C# CURD, 以
- C# SqlConnection – 建立C#program與Sql Server的Connection
- C# SqlCommand – 以SQL或Store Procedure建立Query
- C# SqlCommand ExecuteScalar – 運行SqlCommand
- C# SqlDataAdapter – 以SQL或Store Procedure建立一Set的CURD Query
- C# SqlDataAdapter Fill – 運行SqlDataAdapter的CURD Query, 自動把DataTable 內的資料更新
Let’s Code!
| Create |
| public string Insert (Student student) { string connectingString = “Server=.\SQLExpress;Database=SchoolDB;Trusted_Connection=True;”; SqlConnection con = null; string result = “”; try { con = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(“Insert into students (studentId, studentName) values (@studentId, @studentName)”, con); cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue(“@studentId “, student.studentId); cmd.Parameters.AddWithValue(“@studentName “, student.studentName); con.Open(); result = cmd.ExecuteScalar().ToString(); return result; } catch { return result = “”; } finally { con.Close(); } } |
| Update |
| public string Update (Student student) { string connectingString = “Server=.\SQLExpress;Database=SchoolDB;Trusted_Connection=True;”; SqlConnection con = null; string result = “”; try { con = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(“Update Students set studentName = @studentName where studentId = @studentId”, con); cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue(“@studentId “, student.studentId); cmd.Parameters.AddWithValue(“@studentName “, student.studentName); con.Open(); result = cmd.ExecuteScalar().ToString(); return result; } catch { return result = “”; } finally { con.Close(); } } |
| Delete |
| public string Delete(Student student) { string connectingString = “Server=.\SQLExpress;Database=SchoolDB;Trusted_Connection=True;”; SqlConnection con = null; string result = “”; try { con = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(“delete Students where studentId = @studentId”, con); cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue(“@studentId “, student.studentId); con.Open(); result = cmd.ExecuteScalar().ToString(); return result; } catch { return result = “”; } finally { con.Close(); } } |
| Select All |
| public List<Student> SelectAll() { string connectingString = “Server=.\SQLExpress;Database=SchoolDB;Trusted_Connection=True;”; SqlConnection con = null; DataSet ds = null; try { con = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(“Select studentid, studentName from Students, con); cmd.CommandType = CommandType.Text; con.Open(); SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; ds = new DataSet(); da.Fill(ds); var students = new List<Student>(); for (int i = 0; i < ds.Tables[0].Rows.Count; i++ ) { Student cobj = new Student (); cobj.studentid = ds.Tables[0].Rows[i][“studentid”].ToString(); cobj.studentName = ds.Tables[0].Rows[i][“studentName”].ToString(); students.Add(cobj); } return students; } catch { return students; } finally { con.Close(); } } |