Prizes & Awards
My Profile
Active Members
TodayLast 7 Days
more...
|
Resources » Articles » Databases »
Using SMOs, Read Tables Script using C#.NET
|
SQL Server Management Objects (SMOs) are used manipulate database for DBA activities via an application build by you, you can automate some of the DBA tasks by using SMOs in the application.
In this article I am try to explain how we can use SMO to retrieve all the Table listed in Nortwind database and selecting a Table I am display its create script and listing down all the columns in that table.
So lets start create an example how to use SMOs.
Create a Windows Forms application and Add following controls
1) 2 List boxes one is for listing all the tables and one is for listing all the columns in particular table
2) A Text box with multi line mode true and set scrollbars to Vertical
Refer Screen Shot: Design.JPG
Once we have added controls and designed, we need add SMO DLLs for reference so that we can use built-in class.
You need to add following 4 DLL for reference
1) Microsoft.SqlServer.ConnectionInfo
2) Microsoft.SqlServer.Smo
3) Microsoft.SqlServer.SmoEnum
4) Microsoft.SqlServer.SqlEnum
You will find these DLL at following location:
C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\
Once you have added DLL import them for use, please refer screen shot: References.JPG
Now we are ready with our design and references part, so let’s start writing code to get all the Tables in the Northwind Database and add to the ListBox1, for this I have to create a Server object which represents a SQL Server in the machine we need to pass the server name to create that object, here I am creating module level variable so that I can use it in other methods also.
Once you have created server object, you need to get the Database object and Database object will have collection of table, read the Table collection and add Table names to ListBox1, please can refer the code below and also screen shot:Form_Load.jpg
private Server server = new Server("Localhost");
private void Form1_Load(object sender, EventArgs e)
{
foreach (Table tmpTbl in server.Databases["Northwind"].Tables)
{
listBox1.Items.Add(tmpTbl.Name);
}
}
Now ListBox1 has all the Tables in the Northwind database on selecting any of the table in ListBox1 we want to show all the columns of that table in Listbox2 and also display Create Script of that table in multi line Text box, for that we need to write code in listBox1_SelectedIndexChanged , we create a Table object using selected Table Name in ListBox1 and read all the columns of that table and display in ListBox2, you can refer below code and also screen shot ListColumns.jpg
private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
{
//Create the table object by providing table name
Table tbl = server.Databases["Northwind"].Tables[listBox1.Text];
label1.Text = tbl.Name + " Table Script";
//create script of table is splited into lines so add all them
// into a multi line Textbox
foreach (string strLine in tbl.Script())
{
textBox1.Text += strLine + Environment.NewLine;
}
//Clear the previous data in the Columns list and add columns of
// selected table
listBox2.Items.Clear();
foreach (Column column in tbl.Columns)
{
listBox2.Items.Add(column.Name);
}
}
Now we are ready with our code just build, run and test.
Hope this helps.
SatishKumar J
Microsoft MVP(ASP.NET)
 Attachments
ScreenShots (28565-20316-SMO.zip)
|
Responses
|
No responses found. Be the first to respond and make money from revenue sharing program.
|
|