connecting to a table

Hi

Please let me how to put a specific query to a database in the task and get the values from the db.

I have this code:

ConnectionManager cm = Microsoft.SqlServer.Dts.Runtime.DtsConvert.ToConnectionManager(

ComponentMetaData.RuntimeConnectionCollection["conRun"].ConnectionManager);

cm.Name = "TestConn";

cm.ConnectionString = "Data Source=srcServerName;Initial Catalog=srcDBName;" +

"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;" +

"Auto Translate=False;";

After doing this, if i need to write a specific query to a table. How to do it.

Thanks

Vipul

Hi Vipul:

Not sure if this is exactly what you're trying to do, but I think this might be close to what you need.

I'm using the following code to extract a stored flat file date/time stamp from a SQL Server table.
Notice that I'm using a DataAdapter to populate a DataTable with the rows returned by the in-line
SQL statement. This function returns just the first column of the first row of the DataTable, but could
just as easily return the entire DataTable, or just one row from the table:

Private Function GetPreviousDateTimeStamp(ByVal pstrFileName As String) As Date
Dim cn As Data.SqlClient.SqlConnection
Dim cm As Data.SqlClient.SqlCommand
Dim da As Data.SqlClient.SqlDataAdapter
Dim dt As DataTable
Dim strSQL As String

Try
cn = New SqlConnection

'Set up a Connection object:
cn = CType(Dts.Connections("My_Connection_ADO" _
).AcquireConnection(Nothing), Data.SqlClient.SqlConnection)

strSQL = "SELECT PreviousDateTimeStamp " _
& "FROM dbo._FileTimeStamps " _
& "WHERE ImportFileName = '" & pstrFileName & "'"

'Establish a Command object with this SQL statement:
cm = New Data.SqlClient.SqlCommand(strSQL)

'Set Command object's Connection property:
cm.Connection = cn

'Set up a SqlDataAdapter:
da = New SqlDataAdapter(cm)

'Instantiate a DataTable:
dt = New DataTable

'Populate the DataTable:
da.Fill(dt)

'Return the first cell of the first row:
Return CType(dt.Rows(0).Item(0), Date)

Catch ex As Exception

Throw ex

End Try

End Function

Hope this helps! BTW: I could only get this code to work with an ADO connection,
and could not get it to work with an OLEDB connection. I'm sure someone here knows
why I couldn't use OLEDB, but it's working for me and I'm okay with that.

- Mike

|||

Hi Mike:

Thanks for the reply. I was able to do it. This is my piece of code and i have made use of oledb only.

ConnectionManager cm = Microsoft.SqlServer.Dts.Runtime.DtsConvert.ToConnectionManager(

ComponentMetaData.RuntimeConnectionCollection["Source"].ConnectionManager);

ConnectionManagerOleDb cmoledb = cm.InnerObject as ConnectionManagerOleDb;

OleDbCommand oledbCommand;

String connMgrString = cm.ConnectionString;

OleDbConnection conn = new OleDbConnection(connMgrString);

conn.Open();

String query = "INSERT INTO Tab1 (First, Second) " + " VALUES ('TEST', 'TEST')";

oledbCommand = new OleDbCommand(query, conn);

oledbCommand.ExecuteNonQuery();

conn.Close();

You can check it if this helps.

Thanks

Vipul

|||

Hi Vipul:

Is your code working now? It looks okay to me.

- Mike

|||

Yes it works..

Thanks

Vipul