שיעור בסיסי על ADO
ניתן להוריד את דוגמת הקוד
מכאן.
בפוסט הזה אני רוצה לדבר על ADO, אנחנו נבין את המושגים הבאים:
-
SqlConnection
-
SqlCommand
-
SqlParameter
-
SqlDataReader
-
ExecuteNonQuery
-
ExecuteScalar
-
SqlTransaction
-
TransactionScope
המטרה של הפוסט היא - שמי שחדש בתחום ידע להתחיל לעבוד מול בסיסי נתונים בצורה הבסיסית ביותר - אני יוצר מתוך הנחה שהקורא מכיר SQL.
נתחיל.
בבסיס הנתונים הכי ידוע בעולם
Northwind יש כמה טבלאות - בדוגמא נעבוד עם טבלת Categroies ו - Products.
יש כמה שיטות לעבוד עם נתונים, אבל כאמור אנחנו מדברים כרגע על ADO הפשוט, למעשה גם בו יש שני שיטות אחת זה עבודה עם DataSets והשנייה שאותה נדגים זה עבודה עם אובייקטים שנייצר אותם בעצמנו בעזרת ADO.
אז נניח שיש לנו את האובייקטים הבאים
public enum State
{
Database,
Update,
Delete,
Insert
}
public class ItemBase
{
public int Id { get; set; }
public State CurrentState { get; set; }
}
האובייקט ItemBase הינו האבא של כל האובייקטים שלנו - הוא מכיל Id (מכיוון שלכל טבלה מן הסתם יש Id) ומכיל State, בעזרת ה - State נוכל יותר מאוחר להחליט איזה פעולות לעשות על האובייקט (וזה אחד מהחסרונות הכי גדולים של עבודה בשיטה הזאת - שצריך לנהל לבד את המצב של האובייקטים השונים כדי לדעת איזה שאילתות להריץ מול בסיס הנתונים)
בנוסף יש לנו את האובייקטים:
public class Product : ItemBase
{
public string ProductName { get; set; }
}
public class Categrory : ItemBase
{
public string CategoryName { get; set; }
public string Description { get; set; }
public List<Product> Products { get; set; }
public Categrory()
{
Products = new List<Product>();
}
}
הנה ה - GUI שלנו.
נתחיל עם הלחיצה על Bind Data - אננו רוצים ללכת לבסיס הנתונים להביא את כל האובייקטים ולהציג אותם בגריד.
כעת אנחנו צריכים להכיר כמה מושגים.
הראשון הוא - SqlConnection. נניח שאנחנו יודעים מה אנחנו רוצים לשאול את בסיס הנתונים ואיזה מידע בדיוק אנחנו רוצים לשלוף - אנחנו צריכים להגדיר איפה יושב אותו בסיס נתונים, בלי המידע הזה לא נוכל לשלוח את השאילתא לבסיס הנתונים - זה בדיוק כמו לשלוח פיצה בלי לספק לשליח את הכתובת היכן הפיצה צריכה להגיע, בעזרת SqlConnection נוכל להגדיר היכן השאילתות שלנו נשלחות.
נכתוב את הקוד הבא:
private SqlConnection _connection;
private SqlConnection GetConnection()
{
if (_connection == null)
{
_connection = new SqlConnection();
_connection.ConnectionString = "server=.;initial catalog=northwind;integrated security=true";
}
return _connection;
}
הבעייה היחידה במה שכתבנו - מה יקרה כשניתן את האפליקציה ללקוח - והכתובת או השם של בסיס הנתונים תשתנה, ולכן אנחנו צריכים להוציא את ההגדרה של הכתובת עצמה לקובץ קונפיגורצייה,
נוסיף לפרוייקט קובץ מסוג Application Configuration File שזה (app.config) ונוסיף שם את הקוד הבא:
<connectionStrings>
<add name="northwindDB" connectionString="server=.;initial catalog=northwind;integrated security=true"/>
</connectionStrings>
כעת נוסיף לפרוייקט שלנו reference בשם system.configuration ונשנה בקוד לשורה הבאה:
_connection.ConnectionString = ConfigurationManager.ConnectionStrings["northwindDB"].ConnectionString;
וכעת אם נצטרך לשנות את המיקום של בסיס הנתונים, לא נצטרך לקמפל מחדש אלא רק לשנות בקובץ הקונפיג.
נמשיך.
אז יש לנו פונקצייה שתתן לנו את ה - Connection לבסיס הנתונים, כעת אנחנו רוצים שנוכל לפנות לבסיס הנתונים ולבקש את כל הקטגוריות, נכתוב את הפונקצייה הבאה
private SqlCommand GetCategoryCommand()
{
SqlCommand categoryCommand = new SqlCommand();
categoryCommand.Connection = GetConnection();
categoryCommand.CommandText = "SELECT CategoryID, CategoryName, Description FROM Categories";
return categoryCommand;
}
אנחנו יוצרים אובייקט מסוג SqlCommand ומגדירים לו מי ה - Connection שלו ומה השאילתא.
בנוסף אנחנו יודעים שבתהליך בנייה של Category נצטרך לייבא גם את כל ה - Products שלו ולכן נכתוב את הפונקצייה הבאה
private SqlCommand GetProductCommand()
{
SqlCommand productCommand = new SqlCommand();
productCommand.Connection = GetConnection();
productCommand.CommandText = "SELECT ProductID, ProductName FROM Products
WHERE CategoryID = @CategoryID";
productCommand.Parameters.Add("CategoryID", SqlDbType.Int);
return productCommand;
}
אנחנו נפעיל את השאילתא בכל פעם עבור Category אחר, ולכן נצטרך לספק אילו רשומות לאחזר - נשתמש במשהו שנקרא Parameters, אנחנו מגדירים בשאילתא פרמטרים בעזרת הסימון @ ולאחר מכן אנחנו מוסיפים לאובייקט ה - Command את הפרמטרים בעזרת השורה הבאה
productCommand.Parameters.Add("CategoryID", SqlDbType.Int);
כעת נראה את הקוד בלחיצה על הלחצן BindData
private List<Categrory> _categories;
private void btnBind_Click(object sender, EventArgs e)
{
_categories = new List<Categrory>();
SqlCommand categoryCommand = GetCategoryCommand();
try
{
categoryCommand.Connection.Open();
SqlDataReader reader = categoryCommand.ExecuteReader();
while (reader.Read())
{
Categrory category = GetCategoryFromReader(reader);
AddProductsToCategory(category);
_categories.Add(category);
}
dgvCategories.DataSource = _categories;
}
finally
{
categoryCommand.Connection.Close();
}
}
אנחנו מייצרים את ה - list של ה - categories.
מקבלים את ה - Command עבור ה - Category
פותחים את ה - Connection כדי שנוכל להתחיל לשלוח לבסיס הנתונים שאילתות ולקבל תשובות, כשהקוד עטוף בבלוק של try finally כשב - finally אנחנו סוגרים את ה - Connection בכל מקרה גם אם קרה שגיאה בזמן קריאת הנתונים, אנחנו עדיין נסגור את ה - Connection.
מקבלים אובייקט מסוג SqlDataReader שהוא אובייקט שבעזרתו נוכל לקרוא את הנתונים שחוזרים מהשאילתא, ואנחנו מקבלים את זה בעזרת קריאה לפונקצייה ExecuteReader של ה - Command.
אנחנו תמיד נשתמש ב - ExeuteReader כשאנחנו מפעילים שאילתת Select ואנחנו יודעים שחוזר יותר מערך בודד (ערך לא בודד הכוונה לכמה עמודות או לכמה שורות).
נקרא לפונקצייה Read של ה - raeder בלולאה, הפונקצייה עושה שני דברים.
1. בודקת האם יש שורה לקרוא.
2. במידה וכן היא מחזירה true
כל עוד שאנחנו מקבלים true אנחנו מבצעים את הקוד הבא.
יוצרים אובייקט מסוג Category בעזרת ה - reader.
ממלאים את ה - Products שלו.
ומוסיפים את ה - Category ל - ל - categoris_.
בסוף הלולאה אנחנו מקשרים את הגריד ל - categoris_
נראה את שני הפונקציות שקראנו מתוך הלולאה. הראשונה GetCategoryFromReader
private static Categrory GetCategoryFromReader(SqlDataReader reader)
{
Categrory category = new Categrory()
{
CategoryName = reader["CategoryName"].ToString(),
CurrentState = State.Database,
Description = reader["Description"].ToString(),
Id = (int)reader["CategoryID"],
Products = new List<Product>()
};
return category;
}
והשנייה AddProductsToCategory
private void AddProductsToCategory(Categrory category)
{
SqlCommand productCommand = GetProductCommand();
productCommand.Parameters["CategoryID"].Value = category.Id;
SqlDataReader reader = productCommand.ExecuteReader();
while (reader.Read())
{
Product product = GetProductFromReader(reader);
category.Products.Add(product);
}
}
מאוד דומה לבניית Category.
מקבלים את ה - Command המתאים.
נותנים ערך לפרמטר CategoryID.
ובעזרת ה - SqlDataReader אנחנו מייצרים אובייקט Product.
המתודה GetProductFromReader
private static Product GetProductFromReader(SqlDataReader reader)
{
Product product = new Product()
{
CurrentState = State.Database,
Id = (int)reader["ProductID"],
ProductName = reader["ProductName"].ToString()
};
return product;
}
כשנריץ את הקוד כמו שהוא, נקבל את השגיאה הבאה:
There is already an open DataReader associated with this Command which must be closed first.
דברתי על זה
כאן, ומה שזה אומר - שאי אפשר להפעיל reader כשאנחנו נמצאים בתוך reader אחר, כלומר שעבור כל reader נצטרך לפתוח connection לבד (אנחנו מנסים לפתוח reader עבור ה - prodcts כשאנחנו באמצע בניית ה - categroy).
במידה ואתם עובדים עם NET 2.0 ומעלה ובסיס הנתונים הוא 2005, אפשר להוסיף ל - ConnectionString (שנמצא בקובץ הקונפיג) את ההוראה הבאה:
MultipleActiveResultSets=true
מה שאומר - שמותר לפתוח reader בתוך reader.
אחרי הלחיצה על הלחצן ה - GUI שלנו יראה כך:
אנחנו רוצים להציג תמיד את ה - Products לפי ה - Categry שנבחר בגדריד העליון.
נגדיר לגריד העליון את שני המאפיינים הבאים:
MultiSelect=False
SelectionMode=FullRowSelect
בנוסף נרשם לארוע SelectionChanged של הגריד - ונרשום את הקוד הבא.
private void dgvCategories_SelectionChanged(object sender, EventArgs e)
{
if (dgvCategories.SelectedRows.Count == 1)
{
Categrory selectedCategory =
(Categrory)dgvCategories.SelectedRows[0].DataBoundItem;
dgvProducts.DataSource = selectedCategory.Products;
}
}
אנחנו מוציאים מתוך השורה שנבחרה את ה - DataBoundItem שאנחנו יודעים שזה אובייקט מסוג Category מכיוון שכל הגריד הוא bound לאוסף של Categories.
עד עכשיו עברנו על ארבעת הנושאים הראשונים, דברנו על Connection, Command, Parameter, DataReader.
כעת אנחנו רוצים לעבור על ExecuteNonQuery ו - ExecuteScalar.
נתחיל במימוש עבור הוספת Category, נניח שהמשתמש כתב שם ותיאור עבור קטגורייה חדשה ולחץ על Insert.
נראה את הקוד
private void btnInsert_Click(object sender, EventArgs e)
{
Categrory category = new Categrory()
{
CategoryName = txtName.Text,
Description = txtDescription.Text,
CurrentState = State.Insert
};
_categories.Add(category);
dgvCategories.DataSource = null;
dgvCategories.DataSource = _categories;
}
מייצרים אובייקט מסוג Category כשאנחנו מגדירים את ה - State שלו כ - Insert (כדי שנדע איזה פעולות לעשות עליו).
מוספים את החדש ל - categories_
ומקשרים את הגריד מחדש.
כעת נראה את הקוד עבור DeleteSelected.
private void btnDelete_Click(object sender, EventArgs e)
{
if (dgvCategories.SelectedRows.Count == 1)
{
Categrory category = (Categrory)dgvCategories.SelectedRows[0].DataBoundItem;
if (category.CurrentState == State.Insert)
{
_categories.Remove(category);
}
else
{
category.CurrentState = State.Delete;
}
}
dgvCategories.DataSource = null;
dgvCategories.DataSource = _categories;
}
מוציאים את ה - Category שאנחנו עובדים עליו.
במידה ומדובר ב - Category חדש, אנחנו יכולים פשוט להסיר אותו מהרשימה,
במידה ומדובר ב - Category שהגיע מבסיס הנתונים, אנחנו מסמנים אותו כ - Delete, כדי שבלחיצה SaveToDB, נפעיל עליו שאילתת Delete.
נשאר לנו רק Update.
נרשם לאירוע CellValueChanged של הגריד, ונכתוב את הקוד הבא
private void dgvCategories_CellValueChanged(object sender, DataGridViewCellEventArgs e)
{
Categrory category = (Categrory)dgvCategories.Rows[e.RowIndex].DataBoundItem;
if (category.CurrentState == State.Database)
{
category.CurrentState = State.Update;
}
}
במידה ומדובר בשורה שהגיע מבסיס הנתונים ולא סומנה למחיקה, נסמן אותה כ - .Update
כעת נראה את הקוד ששומר לבסיס הנתונים (הלחצן Save To DB),
הרעיון שעומד מאחורי הדוגמא - היא שאנחנו לא מעוניים כל פעולה לרוץ לבסיס הנתונים, אלא לעבוד על האובייקטים, לסמן אותם איזה שינויים עשינו, ובסוף לעדכן את בסיס הנתונים בכל השינויים.
הנה הקוד:
private void btnSave_Click(object sender, EventArgs e)
{
try
{
_connection.Open();
foreach (Categrory item in _categories)
{
if (item.CurrentState == State.Delete)
{
DeleteItem(item);
}
else if (item.CurrentState == State.Update)
{
UpdateItem(item);
}
else if (item.CurrentState == State.Insert)
{
InsertItem(item);
}
item.CurrentState = State.Database;
}
}
finally
{
_connection.Close();
}
dgvCategories.DataSource = null;
dgvCategories.DataSource = _categories;
}
נפעיל את השאילתות המתאימות בבסיס הנתונים לפי מה שהאובייקט מסומן.
נראה את המתודות.
private void DeleteItem(Categrory item)
{
SqlCommand deleteCommand = new SqlCommand();
deleteCommand.Connection = GetConnection();
deleteCommand.CommandText = "DELETE Categories WHERE CategoryID = @CategoryID";
deleteCommand.Parameters.Add("CategoryID", SqlDbType.Int);
deleteCommand.Parameters["CategoryID"].Value = item.Id;
deleteCommand.ExecuteNonQuery();
הגדרנו אובייקט מסוג Command, נתנו לו את כל מה שהוא צריך.
והפעלנו בעזרת קריאה ל - ExecuteNonQuery, שלמעשה מפעיל שאילתא מול בסיס הנתונים ולא מחזיר ערך (למעשה הוא מחזיר את מספר השורות ששונו כתוצאה מהשאילתא - אבל כרגע זה לא מעניין אותנו)
מאוד דומה היא מתודות Update
private void UpdateItem(Categrory item)
{
SqlCommand updateCommand = new SqlCommand();
updateCommand.Connection = GetConnection();
updateCommand.CommandText = @"UPDATE Categories SET CategoryName = @CategoryName,
Description = @Description
WHERE CategoryID = @CategoryID";
updateCommand.Parameters.Add("CategoryName", SqlDbType.VarChar);
updateCommand.Parameters.Add("Description", SqlDbType.VarChar);
updateCommand.Parameters.Add("CategoryID", SqlDbType.Int);
updateCommand.Parameters["CategoryName"].Value = item.CategoryName;
updateCommand.Parameters["Description"].Value = item.Description;
updateCommand.Parameters["CategoryID"].Value = item.Id;
updateCommand.ExecuteNonQuery();
}
ומתודת Insert
private void InsertItem(Categrory item)
{
SqlCommand insertCommand = new SqlCommand();
insertCommand.Connection = GetConnection();
insertCommand.CommandText = @"INSERT INTO Categories (CategoryName, Description)
VALUES (@CategoryName, @Description);
SELECT CategoryID FROM Categories WHERE CategoryID = SCOPE_IDENTITY()";
insertCommand.Parameters.Add("CategoryName", SqlDbType.VarChar);
insertCommand.Parameters.Add("Description", SqlDbType.VarChar);
insertCommand.Parameters["CategoryName"].Value = item.CategoryName;
insertCommand.Parameters["Description"].Value = item.Description;
item.Id = (int)insertCommand.ExecuteScalar();
}
שימו לב - שבהגדרה של ה - CommandText בסוף ההגדרה של שאילתת ה - INSERT, אנחנו כותבים ; ולאחריו עוד שאילתא,
הסיבה היא, שאנחנו רוצים אחרי הכנסת השורה החדשה לבסיס הנתונים, לקבל את ה - Id החדש שנוצר בצורה אוטומטית, (אנחנו מקבלים בעזרת קריאה לפונקציית SCOPE_IDENTITY)
היות שאנחנו יודעים שהשאילתא תחזיר ערך בודד (את ה - id החדש) אנחנו מפעילים את השאילתא בעזרת ExecuteScalar, שמחזיר object ואנחנו ממירים ל - int.
כעת אנחנו כבר יודעים לעבוד עם ExeuteScalar ועם NonQuery.
נשאר לנו רק להבין מה זה טרנזקצייה.
הרבה פעמים אנחנו רוצים לעשות כמה פעולות מול בסיס הנתונים ולוודא שאו שהכל מצליח או שכלום לא מצליח, לדוגמא.
נחשוב על פעולה פשוטה בבנק, העברת כסף מחשבון לחשבון, מבחינת ADO מדובר בשני פעולות מול בסיס הנתונים, Update על השורה של מי שרוצה להוציא כסף מחשבונו, ו - Update לחשבון שאליו רוצים להעביר את הכסף.
נניח שאחרי שעדכנו את החשבון של מי שרוצה להעביר כסף יש הפסקת חשמל, עם הפעולות לא יוגדרו בתוך טרנזקצייה, החשבון של מי שרצה להעביר כסף יעודכן מבלי שהכסף באמת הגיע לחשבון השני.
ולכן אנחנו רוצים את היכולת להגדיר כמה פעולות מול בסיס הנתונים בטרנזקצייה.
יש שני סוגים, הראשון (והישן) כשמדובר ב - Connection אחד מול בסיס הנתונים, בדוגמא שלנו, אנחנו רוצים שכל הפעולות הלחיצה על Save יצליחו, או ששום דבר לא יצליח (עם נלחץ על Delete עבור אחד מהשורות שהיו במקור בבסיס הנתונים - נקבל שגיאה בזמן Save מכיון שמקושר Products אל אותו Category)
נשנה את הקוד של Save לזה.
private void btnSave_Click(object sender, EventArgs e)
{
SaveWithSqlTransaction();
dgvCategories.DataSource = null;
dgvCategories.DataSource = _categories;
}
הפונקצייה SaveWithSqlTransaction
private SqlTransaction _transaction;
private void SaveWithSqlTransaction()
{
_connection.Open();
_transaction = _connection.BeginTransaction();
try
{
foreach (Categrory item in _categories)
{
if (item.CurrentState == State.Delete)
{
DeleteItem(item);
}
else if (item.CurrentState == State.Update)
{
UpdateItem(item);
}
else if (item.CurrentState == State.Insert)
{
InsertItem(item);
}
item.CurrentState = State.Database;
}
_transaction.Commit();
}
catch
{
_transaction.Rollback();
}
finally
{
_connection.Close();
}
}
אחרי פתיחת ה - Connection אנחנו פותחים טרנזקצייה
במידה וקורה Exception כלשהו אנחנו קוראים ל - Rollback
במידה והכל בסדר אנחנו קוראים ל - Commit.
לפעמים אנחנו צריכים להפעיל טרנזקצייה כשמדובר ביותר מ - Connection אחד (לדוגמא - עבודה מול שני בסיס נתונים במקביל) במקרה הזה נעבוד עם TransactionScope,
נוסיף reference ל - System.Transaction, ונכתוב את הקוד הבא
private void SaveWithTransactionScope()
{
using (TransactionScope transaction = new TransactionScope())
{
_connection.Open();
try
{
foreach (Categrory item in _categories)
{
if (item.CurrentState == State.Delete)
{
DeleteItem(item);
}
else if (item.CurrentState == State.Update)
{
UpdateItem(item);
}
else if (item.CurrentState == State.Insert)
{
InsertItem(item);
}
item.CurrentState = State.Database;
}
transaction.Complete();
}
catch
{
}
finally
{
_connection.Close();
}
}
}
במקרה הזה, לא צריך להגדיר ל - Commands מי הטרנזקצייה, וכמו כן לא צריך בפירוש לבטל את הטרנזקצייה אם קרה Exception,
במידה ולא נקרא ל Complete הטרנזקצייה תתבטל.
מקווה שהפוסט יעזור לאלו שעושים את דרכם הראשונה ב - ADO
לא נגעתי בכלל בנושא של DataSet שזה צורה אחרת לעבוד מול ADO.
ניתן להוריד את דוגמת הקוד
מכאן.