SMO Retrieve Table Record

[savbace]

I'm using SMO (SQL Server Management Objects). Can I get table records i.e. (table data) using SMO like I can do it with ADO.NET? For example in DataTable or other form. Thanks!

[Sandeep Mewara]

Are you looking for something like this: Copy a table using SQL Server Management Objects[^]

Sandeep Mewara Microsoft ASP.NET MVP 2012 & 2013 [My Latest Article(s)]: How to extend a WPF Textbox to Custom Picker Server side Delimiters in ASP.NET

[savbace]

Thanks for reply. But it is not actually I'm looking for. I'll try to clarify. Assume we have table "Users":

| Id | Name | Pass |

| 1 | Kate | qwerty |
| 2 | Mike | asd$f5 |

I just want to get a value from second row and column "Name". In this case I'll get value "Mike". It's equal to query SQL:

"SELECT Name FROM Users WHERE Id=2"

I know that I can use ADO.NET, but in this case I must use SMO. Can anybody help me?

[Sandeep Mewara]

Here, a similar question[^] discussed. It says: Have a look at SMO's Scripter class. The following basic sample works for me:

using System.Data.SqlClient;
using System.IO;
using System.Text;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

namespace SqlExporter
{
class Program
{
static void Main(string[] args)
{
var server = new Server(new ServerConnection {ConnectionString = new SqlConnectionStringBuilder {DataSource = @"LOCALHOST\SQLEXPRESS", IntegratedSecurity = true}.ToString()});
server.ConnectionContext.Connect();
var database = server.Databases["MyDatabase"];
var output = new StringBuilder();

foreach (Table table in database.Tables)
{
    var scripter = new Scripter(server) {Options = {ScriptData = true}};
    var script = scripter.EnumScript(new SqlSmoObject\[\] {table});
    foreach (var line in script)
        output.AppendLine(line);
}
File.WriteAllText(@"D:\\MyDatabase.sql", output.ToString());

}
}
}

Sandeep Mewara Microsoft ASP.NET MVP 2012 & 2013 [My Latest Article(s)]: How to extend a WPF Textbox to Custom Picker Server side Delimiters in ASP.NET

[Lost User]

SMO isn't meant to be abused as a query-language. Why "must" you use SMO?

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] They hate us for our freedom![^]

[Sandeep Mewara]

Here, a similar question[^] discussed. It says: Have a look at SMO's Scripter class[^]. The following basic sample works for me:

using System.Data.SqlClient;
using System.IO;
using System.Text;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

namespace SqlExporter
{
class Program
{
static void Main(string[] args)
{
var server = new Server(new ServerConnection {ConnectionString = new SqlConnectionStringBuilder {DataSource = @"LOCALHOST\SQLEXPRESS", IntegratedSecurity = true}.ToString()});
server.ConnectionContext.Connect();
var database = server.Databases["MyDatabase"];
var output = new StringBuilder();

foreach (Table table in database.Tables)
{
    var scripter = new Scripter(server) {Options = {ScriptData = true}};
    var script = scripter.EnumScript(new SqlSmoObject\[\] {table});
    foreach (var line in script)
        output.AppendLine(line);
}
File.WriteAllText(@"D:\\MyDatabase.sql", output.ToString());

}
}
}

Sandeep Mewara Microsoft ASP.NET MVP 2012 & 2013 [My Latest Article(s)]: How to extend a WPF Textbox to Custom Picker Server side Delimiters in ASP.NET

[savbace]

Thanks all for your replies! I'm realized that it was not adequate question. I'm going to use one of these methods (just like in ADO.NET :))

Server.ConnectionContext.ExecuteReader
Server.ConnectionContext.ExecuteWithResults

Sorry for taking your time.