Read data from database table

[leo88]

Hi, I am doing a window service application using C#. I wants to select many columns from many table and print into text file. Below is my coding:

protected override void OnStart(string[] args)
        {
            try
            {
                base.OnStart(args);
                Access_Db.OpenTransaction();
                Access_Db.BeginTransaction();

                DataTable dtPartNo = new DataTable();

                SQLStr = "SELECT IDTbl.ModelID,WIPTbl.WIP FROM IDTbl,WIPTbl";
                dtPartNo = AccessDb_Cmd.RetrieveData(SQLStr, Access_Db).Tables[0];

                FileStream fs = new FileStream("c:\\AccessoriesPacking.txt", FileMode.Append, FileAccess.Write, FileShare.Write);
                fs.Close();
                StreamWriter sw = new StreamWriter("c:\\AccessoriesPacking.txt", true, Encoding.ASCII);
                
                sw.WriteLine(dtPartNo );
                sw.Close();
            }
            catch (Exception ex)
            {
               // event
            }
            finally
            {
                Access_Db.CloseTransaction();
            }
        }

After i build the program, there is no error but it doesn't create a text file and print anything in the expected location, how comes like that?

[Mitja Bonca]

Hi,
take a look at here how this has to be done:
http://www.dotnetspider.com/resources/5069-Writing-DataTable-into-text-file.aspx

[VIeditorlover]

You are a bit off the target. During the OnStart you have to create another thread and start it before you leave OnStart. All service code is performed by the new thread - its simple as Kalashnikov.

[leo88]

I had modified the codes for the sql, and I copy all the codes into console application to try it, at the end it shows the exception as below:
ex=" The .Net Framework Data Provider for SQL Server is not registered on the local machine".

How come will appeared this problem? I copy the name of the provider from the properties of the database, as below:

public OleDbConnection TransConn = new OleDbConnection("Provider=.NET Framework Data Provider for SQL Server;Data Source=LEO-PC/SQLEXPRESS;Initial Catalog=AccessoriesPacking;Integrated Security=True");

[VIeditorlover]

I had modified the codes for the sql, and I copy all the codes into console application to try it, at the end it shows the exception as below:
ex=" The .Net Framework Data Provider for SQL Server is not registered on the local machine".

How come will appeared this problem? I copy the name of the provider from the properties of the database, as below:

public OleDbConnection TransConn = new OleDbConnection("Provider=.NET Framework Data Provider for SQL Server;Data Source=LEO-PC/SQLEXPRESS;Initial Catalog=AccessoriesPacking;Integrated Security=True");

use System.Data.OleDb.OleDbConnectionStringBuilder

[leo88]

HI VIeditorlover,
I am using sql express 2005, I thinks the problem is on the data provider name,when I looked into the properties of the database connection, it just show me the data source and don't have the name of provider. Who knows the name of provider of sql express 2005? I had search on Google but it still cannot work :(

[leo88]

I am using Window 7 of 32bits

[Stefan_Lam88]

@leo88

try the following
System.Data.SqlClient

as your provider name:)

[Stefan_Lam88]

Sorry you are using microsoft access right?

try again for
Microsoft.Jet.OLEDB.4.0

as your provider name
previous post is for sqlserver.sorry

[leo88]

Hi lam,
I am using sql server 2005 express edition, not MS Access, by the way, I had tried the provider name(Microsoft.Jet.OLEDB.4.0),but it appeared errors as below:

ex="Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. "

[Stefan_Lam88]

how about System.Data.SqlClient?failed too?

[leo88]

still cannot connect, "not registered on the local machine" exception..
I am using database in local machine, sql server 2005 express

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;

    
namespace testsql
{
    class SQLClass
    {
        public OleDbConnection TransConn = new OleDbConnection("Provider= System.Data.SqlClient;Data Source=LEO-PC/SQLEXPRESS;Initial Catalog=AccessoriesPacking;Integrated Security=True");

        public OleDbTransaction Trans;

        public void OpenTransaction()
        {
            TransConn.Open();
        }

        public void BeginTransaction()
        {
            Trans = TransConn.BeginTransaction();
        }
            
        public void CommitTransaction()
        {
            Trans.Commit();
        }

        public void RollBackTransaction()
        {
            Trans.Rollback();
        }

        public void CloseTransaction()
        {
            TransConn.Close();
        }
    }

Whenthe program run till OpenTransaction, the program will then go to the catch statement (exception)

[Stefan_Lam88]

Do you import/add the library as your reference?

[Stefan_Lam88]

why your datasource is =LEO-PC/SQLEXPRESS? instead of =LEO-PC\SQLEXPRESS?

I thought "\"?
Correct if I m wrong

[leo88]

Oh, in program cannot write "\", but u can use either "/" or "\\" :)

[Stefan_Lam88]

oh yea.I thought u are doing in web config.

using System.Data.SqlClient; add this in your program and see what the result.

on your top of your program.import there..

[leo88]

ok, i will try, but what is the provider name for the System.Data.SqlClient?

http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/b1cf6249-cbc2-4ab7-90d9-dbea8acf523a
(I think in my case should use Sqlclient）

[Stefan_Lam88]

Because there are many kinds of database.

System.Data.SqlClient is a provider name for Sql server. So I guess your sql server 2005 express should use this.(Correct me If i m wrong)

Good luck.

If you wish to get more detail
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.aspx

and the link you provided is an extra bonus resource.GAMBATEH!

[leo88]

OK, thanks:)

[VIeditorlover]

Hey man, you do not have to use oledb, just use sql client right ?

cnn = new SqlConnection("data source=localhost;user id=xyz;password=abc;initial catalog=def");
cnn.Open();

[leo88]

Hey man, you do not have to use oledb, just use sql client right ?

cnn = new SqlConnection("data source=localhost;user id=xyz;password=abc;initial catalog=def");
cnn.Open();

ya, i now change to use sql client, but I am using window authentication, so i don't have user id and password.

[Mitja Bonca]

Can you access to the dataBase now, using SqlConnection class?

[leo88]

Below is my connection string:

public SqlConnection TransConn = new SqlConnection("Data Source=LEO-PC/SQLEXPRESS;Initial Catalog=AccessoriesPacking;Integrated Security=True");

the error appeared:
"a network related or instance-specific error occured while establishing a connection to SQL server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. <provider name : Named Pipes Provider, error : 40 - Could not open a connection to SQL Server> "

I got this errot message after changed to SQL conncetion class and remove the provider in the connection string, what had happened?

[Mitja Bonca]

As i told you, you dont have to correct connection string. I cannot not do much about it here, since i dont know which database do you use.
For accurate connection string take a look at here.