Download The Project
If you're writing a Database Manager application or simply want to retrieve all the instances,this trick might come in handy.
We'll be using SQL SMO(SQL Management Objects)
First of All you need to add reference to Microsoft.SqlServer.smo.dll file which is located in:
For 64-bit Windows 7:
[Your drive]:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll
For 32-bit Windows 7:
[Your drive]:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll
Now that we've added it to our project,we can start coding!
Add a ListBox control to your project:
DataTable dataTable = SmoApplication.EnumAvailableSqlServers(true);
listBox1.ValueMember = "Name";
listBox1.DataSource = dataTable;
By running this code alone,you'll get all the available sql servers inside your listbox control.
Ok lets develop it further.Lets see what databases our instances have
So to do this,you need to add another ListBox.Then in your listbox1's selectedindexchanged event you need to check which server selected.So you need to create a server object first.
After this,you'll iterate through this server to populate all the databases in newly created listbox.
Here is the code to do that:
listBox2.Items.Clear();
if (listBox1.SelectedIndex != -1)
{
string serverName = listBox1.SelectedValue.ToString();
Server server = new Server(serverName);
try
{
foreach (Database database in server.Databases)
{
listBox2.Items.Add(database.Name);
}
}
catch (Exception ex)
{
string exception = ex.Message;
}
}
After we run the project we'll be getting our Databases.
Hope it helps!
Tables can have computed columns. It is an expression defining the value of the specified column. If you are creating a table object in Query Window the columns don't have data by default. But using the Computed Columns method you can fill data as you like.
Lets make an example for it. Assuming you have a database named SampleDatabase, write a query:
Use SampleDatabase;
GO
Create Table ComputedColumns
(
Quantity int NULL,
Cost money NULL,
Investment as (Quantity * Cost)
);
We're creating a table named ComputedColumns and setting 3 columns "Quantity", "Cost" and "Investment"
Because of the multiply of Quantity and Cost will lead us to Investment costs, it would be useful if we define Investment as variable storing the data of multiplication of these 2 variables.
Because if there's no variable defined for Investment, you won't be able to insert any datas manually. Its data will be calculated and recorded after you passed it.
Lets see the results:
******** Added "2" in the Quantity Column ********
******** Added "1.000" in the Cost Column *******
******** You cant add any data into Investment column, so you passed ******
After this process, it automatically calculates the data as you can see.
Hope this helps