|
|
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!
|
|
|
|
|
|
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?
|
|
|
|
|
SMO isn't meant to be abused as a query-language. Why "must" you use SMO?
|
|
|
|
|
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());
}
}
}
|
|
|
|
|
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.
|
|
|
|
|
please ignore my poor english...
I Need Reporting This Table in this shape.plz help me...
SQL Table
----------
City Value
-- --
Londen 20
Tehran 12
Paris 15
Londen 12
My Needed Report
----------
Londen Tehran Paris
---- ---- ----
32 12 15
modified 26-Jan-13 8:52am.
|
|
|
|
|
You need to use SQL PIVOT:
MSDN: Using PIVOT and UNPIVOT[^]
Try:
SELECT
'TotalValue' AS Sum_Total_Values_By_City, [London], [Tehran], [Paris]
FROM
(SELECT City, Value
FROM MyTable) AS SourceTable
PIVOT
(
SUM(Value)
FOR City IN ([London], [Tehran], [Paris])
) AS PivotTable
|
|
|
|
|
Morning All,
I am trying to produce a query for a report that concatenates data from multiple rows. Easiest if I explain what I have, and what I need to end up with! This is Sage Line 500 data by the way.
The table I am querying contains sale order lines as follows:
order_no order_line_no line_type product long_description
100001 1 P 47020 1st description for 47020
100001 2 C 2nd line description for 47020
100001 3 P 47030 1st description for 47030
100001 4 C 2nd line description for 47030
100001 5 P 47040 1st description for 47040
100001 6 C 2nd line description for 47040
100001 7 P 47050 1st description for 47050
100001 8 C 2nd line description for 47050
100001 9 C 3rd line for 47050
So the lines with a line_type 'C' are a continuation line for the description. The problem is they are just numbered sequencially and there could be any number of them.
I want to end up with a results set something like this:
order_no order_line_no line_type product long_description
100001 1 P 47020 1st description for 47020 2nd line description for 47020
100001 3 P 47030 1st description for 47030 2nd line description for 47030
100001 5 P 47040 1st description for 47040 2nd line description for 47040
100001 7 P 47050 1st description for 47050 2nd line description for 47050 3rd line for 47050
Any help or pointers on where to start looking greatly apreciated.
Thanks,
Alex
|
|
|
|
|
Alex Lush wrote: Any help or pointers on where to start looking greatly apreciated.
I'd do this using a console-app; reading from the source-table, iterating order_no's, fetching them completely (the C's), and write the result to a destination-table.
You could also omit the line_type column in the destionation-table; they'd all be 'P', making it a redundant statement.
Whoever designed the structure should be introduced to "Normalization".
|
|
|
|
|
You didn't mention what database it is. For SQL Server, the query might be something like this
SELECT order_no, order_line_no, line_type, product, long_description + ' ' + (SELECT B.long_description FROM Table1 B WHERE B.line_type = 'C' AND A.order_no = B.order_no AND A.product = B.product) AS long_description
FROM Table1 A
WHERE line_type = 'P'
|
|
|
|
|
Apologies. It is for SQL Server 2008 R2. Good guess!! Will give your suggestion a go. Thanks.
|
|
|
|
|
How about a temporary table:
create table #t2
(
order_no varchar(255),
order_line_no int,
long_description varchar(8000),
done bit
)
Insert Into #t2 (order_no, order_line_no, long_description, done)
Select order_no,
order_line_no,
long_description,
0
From #t1
Where line_type = 'p'
Declare @i int
Set @i = 1
While (Select count(*) From #t2 where Done = 0) > 1
Begin
Update t2
Set t2.long_description = t2.long_description + ' ' + t1.long_description
From #t1 t1
inner join
#t2 t2
On t1.order_no = t2.order_no
And t1.order_line_no = t2.order_line_no + @i
And t1.line_type <> 'P'
And t2.done = 0
Update t2
Set t2.done = 1
From #t1 t1
inner join
#t2 t2
On t1.order_no = t2.order_no
And t1.order_line_no = t2.order_line_no + @i
And t1.line_type = 'P'
Set @i = @i + 1
End
You could also use a cursor.
Mike
|
|
|
|
|
Hallo Forum,
I am new to this forum and I would like to get your help. I am having issues with this code.
HERE is the error I am getting: Syntax error (Missing Operator) in Query expression ' :1
Dim CustomerTrans As OleDb.OleDbTransaction = Nothing
Dim MyConnection As OleDb.OleDbConnection = Nothing
'Creat connection and the transactions Object
Try
MyConnection = New OleDb.OleDbConnection(My.Settings.dbConnectionString)
MyConnection.Open()
'Begin Transaction
CustomerTrans = Myconnection.BeginTransaction
'Insert the New receipt
Dim SQL As String = "Insert into Receipts (ReceiptDate,ReceiptTotal) values ( :1)"
Dim CMD1 As New OleDb.OleDbCommand
CMD1.Connection = Myconnection
CMD1.Transaction = CustomerTrans
CMD1.CommandText = SQL
CMD1.Parameters.AddWithValue(" ", Now.Date)
CMD1.Parameters.AddWithValue(":1", TotalPriceTxt)
CMD1.ExecuteNonQuery()
CMD1.Dispose()
i AM Using Access 2007 and I set t
ANY HELP GIVEN IS HIGHLY APPRECIATED. i TRIED ALMOST ALL I CAN THINK OF.
Thank you in advance.
|
|
|
|
|
Read this article [^](I know it is long but you can skip to the parameterised query section) it will answer your question and give you additional information on sql.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
RAH
Thanks for the quick response. Although I have learned something new in the article you posted, I still can't find what the problem is with my code.
What am I doing wrong? it looks valid syntax to me, but what do I know, I am new to this. I have been reading about INSERT INTO statement all day long and can't find whats wrong with my code.
Thanks
|
|
|
|
|
What database system? Oracle? MySQL? SQL Server?
|
|
|
|
|
I am Using MS Access 2007
|
|
|
|
|
|
But I am not using Named Parameters. I am really confused. I guess I have been reading way too long about this.
|
|
|
|
|
It looks like you think you are.
|
|
|
|
|
Should there not be a comma between the two parameter identifiers like:
Dim SQL As String = "Insert into Receipts (ReceiptDate,ReceiptTotal) values (:0, :1)"
|
|
|
|
|
I think those just aren't valid names/markers.
|
|
|
|
|
PieBALDconsult,
What do you mean by not valid names?
I have have them on my database exactly as you can see it. I have checked everything on the naming....is there anything you think I am missing? Please help!
Thanks.
|
|
|
|