CODEWITHSUNDEEP
c#.netsqldatabaseoledb
Vilius
Vilius

Reputation: 65

.Net C# how to connect to external SQL Server database ? OleDb or other?

Hi I would like to know how I should connect to the external SQL Server database in C# , .NET ?

For example if I have there parameters :

SQL info

?

I know how to connect to internal : Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + System.AppDomain.CurrentDomain.BaseDirectory + "..\\Files\\MapPlaces\\Database.mdb;";

But what about external ?

I have tried :

 string nowConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Sqlweb.companyname.com;Initial Catalog = databaseName; User Id = Username; Password = Password;";

System.Data.OleDb.OleDbConnection dbcon = new System.Data.OleDb.OleDbConnection(nowConString);

string sql = "SELECT * FROM XXXTable";

dbcon.Open();

System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sql, dbcon);

System.Data.OleDb.OleDbDataReader reader;

reader = cmd.ExecuteReader();

ScriptStuff.Append("Reader created!<br/>");

while (reader.Read())
{
    string companyName = reader.GetValue(1).ToString();

    ScriptStuff.Append(companyName+"<br/>");

}

Did not work ! Thank you for your help !

Edited from comments:

Yes that was one my mistake, thanks. Since first one was access and YES second is SQL Server. And it is SQL Server 2005. But I am new to .net and all that... I have found first one and second one in that connectionstring.com but I could not find or understand how to use that for this one ...

Could you help, and just post hole connection ? Thanks – Vilius 7 mins ago

I mean do I still need to use OleDB ? should there be "Provider=Microsoft.Jet.OLEDB.4.0;" in that connection string ? Where do i post what (server (that Dcms-xxx), or url of the sql server (sqlweb.companyname.com))? THANKS FOR YOUR HELP ! –

Upvotes: 5

Views: 14146

Answers (3)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52137

I would highly recommend taking a look at:

http://www.connectionstrings.com/

It's a quick, "in you face" treatment of the subject of connection strings for all major databases.

Upvotes: 0

tskulbru
tskulbru

Reputation: 2346

I would add a connectionString to my app/web.config.

<connectionStrings>
    <add name="AspnetdbConnectionString"
        connectionString="Data Source=<databaseadress>;Initial Catalog=<database>;User Id=<user>;Password=password>"
        providerName="System.Data.SqlClient"
    />
</connectionStrings>

The above example is how you specify an connectionstring for a MSSQL connection, and below a way to use this connectionstring.

 using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["AspnetdbconnectionString"].ConnectionString))
        {
            cn.Open();
            using (SqlCommand cm = cn.CreateCommand())
            {
                cm.CommandType = CommandType.Text;
                cm.CommandText = "SELECT * FROM ...";
                using (SafeDataReader dr = new SafeDataReader(cm.ExecuteReader()))
                {
                    while (dr.Read())
                    {
                        // do stuff
                    }
                }
            }
        }

Upvotes: 1

Christian Specht
Christian Specht

Reputation: 36451

Are you sure that it's a SQL Server database that you are trying to connect to?
Your "internal" example connects to a Microsoft Access database (OLEDB provider and database file extension .mdb)

If your external database is really a SQL Server database, the recommended way is using SqlConnection, SqlDataReader and so on instead of OleDbConnection etc.
Or, if you really want to use OleDb, you need a different connection string.
See connectionstrings.com (for SQL Server 2008, 2005 or 2000, depending what you're trying to connect to).

Upvotes: 0

Related Questions