לייצא טבלאות מה DB, כולל סכמה ונתונים מהאפליקציה באמצעות SMO

2 בMarch 2010

תגיות: , , , ,
אין תגובות

מה זה SMO?

SMO, ר”ת של SQL Server Management Object, זה אוסך של אובייקטים החושפים פונקציונאליות של ניהול שרת SQL Server על רכיביו השונים ובכל הרמות (ניהול שרת, ניהול דטאבייסים, ניהול הרכיבים בדטאבייס מסויים וכו’).
מדובר, למעשה, במקבילה דוט.נט-ית לרכיב שידוע בשם SQL-DMO (עוד ד”ת – SQL Distributed Management Objects).

האסמבליס (קבצי הDLL) של SMO נמצאים בנתיב C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll (מדובר בקבצים של SQL SERVER 2008, כאשר c:\Program Files זה כמובן תיקיית האפליקציות שלכם).

המטרה

המטרה בפוסט זה היא להדגים דרך לכתוב מתודה שמייצרת קובץ המכיל משפטי SQL היכולים לייצר טבלאות שונות. פונקציונאליות דומה לפונקציונאליות המתקבלת ב SQL Server Management Studio כשלוחצים קליק ימני על DB ובוחרים ב Generate Scripts.

השיטה

תחילה, נצטרך להוסיף רפרנס לחברים הבאים:

  1. Microsoft.SqlServer.ConnectionInfo
  2. Microsoft.SqlServer.Management.Sdk.Sfc
  3. Microsoft.SqlServer.Smo

[האסמבליס שלהם נמצאים בתיקייה שציינתי, באותו שם, פשוט בתוספת .dll]

בדוגמא הבאה, אנחנו מעוניינים לגבות מספר טבלאות ספיציפיות, ששמם ידוע מראש הנמצאים ב DB ששמו ופרטי הגישה אליו ידועים מראש.

ככה תיראה המתודה שלנו:

   1: public static void Backup(string FileName, string[] Tables)

   2: {

   3:     StringBuilder sb = new StringBuilder();

   4:     Server srv = new Server(new Microsoft.SqlServer.Management.Common.ServerConnection("<db name>", "<user name>", "<password>"));

   5:     Database dbs = srv.Databases["<db name>"];

   6:     ScriptingOptions options = new ScriptingOptions();

   7:     options.ScriptData = true;

   8:     options.ScriptDrops = false;

   9:     options.FileName = FileName;

  10:     options.EnforceScriptingOptions = true;

  11:     options.ScriptSchema = true;

  12:     options.IncludeHeaders = true;

  12:     options.AppendToFile = true;

  13:     options.Indexes = true;

  14:     options.WithDependencies = true;

  15:     foreach (var tbl in Tables)

  16:     {

  17:         dbs.Tables[tbl].EnumScript(options);

  18:     }

  19: }

המתודה שלנו מקבלת במקרה הזה שני פרמטרים, הראשון הוא שם הקובץ שאותו אנחנו רוצים לייצא והשני זה מערך עם שמות הטבלאות.

בשורה 4 במתודה מתחיל הדבר המעניין. אנחנו יוצרים מופע של אובייקט בשם Server המייצג, כמה מפתיע, את שרת ה SQL עצמו ומתחברים באמצעות השם משתמש והסיסמא שלנו.

בשורה 5, אנחנו בוררים מתוך כל ה DB’s שבשרת את זה שמעניין אותנו. שימו לב, שלמרות שבקוד הזה לא דאגתי לנושא כלל, חובה להתייחס ל exceptions שיכולים לעוף בדרך. שרת לא זמין, שמות משתמש וסיסמא לא תקינים, DB שאין הרשאות אליו, DB שלא קיים – כל אלה הם פוטנציאל לבעיות.

בשורות הבאות, אנחנו מגדירים סט של הגדרות. המעניינות שבהם זה שאנחנו מגדירים לו לייצא את הנתונים עצמם (ScriptData) בנוסף לסכימה (ScriptSchema) וכמובן שאנחנו מעוניינים באינדקסים ובתלויות אחרות (כשאנחנו מגדירים WithDependencies = true, זה אומר שאם יש איזשהו FK לטבלה אחרת, אז הטבלה ההיא תיווצר גם. אם לא היינו מגדירים את זה, היה נוצר רק מה שאנחנו מבקשים – שימו לב, שבמצב כזה תיתכן יצירה כפולה של טבלאות, לא טיפלתי בזה במקרה הזה – אבל צריך לשים לב, או לחלופין להגדיר ל false ובאופן ידני לוודא שמעבירים את השמות של הטבלאות הנחוצות).

הסבר על ההגדרות הקיימות אפשר למצוא כאן.

שורות 15-18 הן המעניינות – אנחנו עוברים על כל הטבלאות במערך שהועבר, ועבור כל טבלה קוראים למתודה Table.EnumScript שעובדת לפי ההגדרות שמועברות אליה. המתודה הזאת מחזירה, בנוסף, גם collection של סטרינגים המכיל למעשה את ה משפטי SQL.

במקרה הזה, אני לא מטפל ולא קולט את הערך המוחזר, כי הגדרתי בכל אופן FileName, כלומר המתודה מייצאת את המידע לקובץ (מכיוון שאנחנו עובדים עם מספר מרובה של טבלאות, כלומר מספר רב של פעמים תיקרא EnumScript, חשוב גם להגדיר AppendToFile = true).

למה EnumScript ולא Script?

מי שיסתכל בתיעוד, או סתם יכתוב בעצמו ויסתכל באינטיליסנס, יראה שקיימת גם מתודה בשם Script (שמגיע בכלל בירושה מ TableViewTableTypeBase.Script) אז למה לא להשתמש בה?

מסתבר (וזה לא ממש מתועד), שאם מגדירים ScriptData = true, חובה להשתמש ב EnumScript כי Script תזרוק, במידה שנשתמש בה, Exception שאומר This method does not support scripting data. אז לכן, אנחנו משתמשים ב EnumScript במקום.

סיכום

לצורך עבודה עם SQL Server מאפליקציות דוט נט קיים רכיב בשם SMO.

ניתן לייצר משפטי SQL המשמשים ליצירת טבלאות והכנסת המידע אליהן עם המתודה Table.EnumScript כאשר את ההגדרות מעבירים עם אובייקט ScriptingOptions.

למי שמתעניין בנושא, מתודות בעלות שם דומה קיימות גם לאובייקט Databse ולאובייקטים של ישויות DB נוספות. קיימת גם מחלקה בשם Scripter המשמשת (גם לעיתים מאחורי הקלעים) ליצירת סקריפטים שונים של SQL מרכיבי DB קיימים.

בהצלחה.

הוסף תגובה
facebook linkedin twitter email

Leave a Reply

Your email address will not be published. Required fields are marked *