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

Question

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

• Url to get to database (throughout browser also): Sqlweb.companyname.com
• Database username: username
• Server: Dcms-xxx
• Databasename: databaseName
• Databasepassword: password

?

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 ! –

Solution

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
                    }
                }
            }
        }

Comments

[Vilius]

Yes taht was one my mistake , thanks . Since first one was access and YES second is sql. And it is 2005 sql. 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 found or understand how to use that for this one ... Could you help, and just post hole connection ? Thanks

[Vilius]

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 !

[Vilius]

Thank you very much will try that now :) ;) database address witch one to use ? server (that Dcms-xxx), or url of the sql server (sqlweb.companyname.com)

[tskulbru]

Depends if its on a local network or not. If not use the complete address (sqlweb.companyname.com).

[Vilius]

so that server (that Dcms-xxx) we dont use at all when connecting ? It is not on local network (i think :))

[tskulbru]

You need to use a DNS which points to an IP. Since its not on a local network, then you can't use that name. Best of luck!

[Vilius]

with that i also get "System.InvalidOperationException: The 'System.Data.SqlClient' provider is not registered on the local machine"