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 :)