How do you connect to a sql database using C#
 

Hello everyone,

I was wondering if anyone could tell me how to connect and query sql databases in c#.

Thanks in advance

Re: How do you connect to a sql database using C#
 

Umm, ok. I will give you some sample code, but you should really consider checking out the tutorials here at Daniweb, or C# websites with tutorials. The principles are the same no matter what language, the syntax is the only thing that differs. And google searches are a really good place to start.

Sample - as a console application in C#

using System;

using System.Data.SqlClient;



namespace ConsoleCSharp

{

        /// <summary>

        /// Summary description for Class1.

        /// </summary>

        class DataReader_SQL

        {

                /// <summary>

                /// The main entry point for the application.

                /// </summary>

                [STAThread]

                static void Main(string[] args)

                {

                        //

                        // TODO: Add code to start application here

                        //

                        try

                        {

                            SqlConnection thisConnection = new SqlConnection(@"Network Library=DBMSSOCN;Data Source=192.168.0.100,1433;database=Northwind;User id=Paladine;Password=;");

                                thisConnection.Open();

                         SqlCommand thisCommand = thisConnection.CreateCommand();

                         thisCommand.CommandText = "SELECT CustomerID, CompanyName FROM Customers";

                         SqlDataReader thisReader = thisCommand.ExecuteReader();

                                while (thisReader.Read())

                                {

                                 Console.WriteLine("\t{0}\t{1}", thisReader["CustomerID"], thisReader["CompanyName"]);

                                        }

                                thisReader.Close();

                                thisConnection.Close();



                        }

                        catch (SqlException e)

                        {

                                Console.WriteLine(e.Message);

                        }

                        

                }

        }

}

** NOTE **
If you are using Visual Studio, rather than the commandline compiler, use CTRL + F5 to compile, as that will put a pause in the Console app, and keep it open for you to see the results.

Hope this helps.

Re: How do you connect to a sql database using C#
 

how can you use the data to test conditions, not just write out data that you want.

Re: How do you connect to a sql database using C#
 

Here is an example of the principles of ADO.Net

pseudocode

...

Sqldataadapter custAdapter = new SqlDataAdapter("SELECT * FROM Customers", thisConnection);

custAdapter.Fill(thisDataSet, "Customers");

foreach (DataRow custRow in thisDataSet.Tables["CustomerID"].Rows)

{ 

                if (custRow["City"].ToString() == "Madrid")

                           {

                                 Console.WriteLine("Customer ID: " + custRow["CustomerID"]);

                                }

}

Hope this helps.

Re: How do you connect to a sql database using C#
 

thanks this was a lot of help

Re: How do you connect to a sql database using C#
 

Glad I could help.

If I ever get a free mo...I will try to post some C# Tutorials

Re: How do you connect to a sql database using C#
 

Hi, I'm trying to gain access to a postGres database. I've tried to use Npgsql but I get an error with the Mono.Security.dll file (the version is incorrect > but I can't find v2). So I thought I'd give the above method a try. I was just wondering in the statement:
SqlConnection thisConnection = new SqlConnection(@"Network Library=DBMSSOCN;Data Source=192.168.0.100,1433;database=Northwind;User id=Paladine;Password=;");
What does the 'Network Library=DBMSSOCN' refer to? And would it change if I'm using postGres? I'll try it out when I get to work tomorrow but just thought I'd ask.

Thanks!

Re: How do you connect to a sql database using C#
 

I've tested the above connection and there's an error in the detection of a SQLServer... which makes sense since I'm using the wrong provider... I think that 'Network Library=DBMSSOCN' specifies the provider? And a provider allows you to connect to the require database server... but I'm not sure. Regardless, I'm still unsure of how to connect to my PostGres database. Can some help please :)

Re: How do you connect to a sql database using C#
 

Well I've finally figured out how to get Ngpsql to work!! I downloaded the beta version and that came with the correct version of the Mono.Security.dll file. A piece of advice to those that plan to use it, perhaps you'd just want to copy the Mono.Security.dll file to the /bin/Debug and /bin/Release directories of your project instead of adding it as a reference.

Re: How do you connect to a sql database using C#
 

can anyone please specify the concept of
SqlConnection thisConnection = new SqlConnection(@"Network Library=DBMSSOCN;Data Source=192.168.0.100,1433;database=Northwind;User id=Paladine;Password=;");

Thanks in Advance :)

Re: How do you connect to a sql database using C#
 

how do you connect to a Excel database using c#

Re: How do you connect to a sql database using C#
 

Hmmm, perhaps I'm mistaken but there are no 'Excel databases.' I think you simply wish to read from an Excel file > that is, a spreadsheet. In which case, there are probably (or rather must be) API's out there and a Google search will definitely aid you in that.

Or perhaps you mean to access a Microsoft Access database?

can we use dataset in console mode and how

You can use one of the Microsoft database connectors, if you have it installed, some may be provided by installing Office or other products. Your DSN string will be something like:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";

at least, if you are using the Jet OLEDB type connection. There are others, see http://www.connectionstrings.com/ for better informed examples.

This will allow you to access a table within a worksheet, the HDR=Yes tells it to expect a row of column names at the top, you have to handle the data types yourself, I think. I have working code examples at work someplace but nothing available here.