How do you connect to a sql database using C#

[Dark_Omen]

Hello everyone,

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

Thanks in advance

[Paladine]

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.

[Dark_Omen]

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

[Paladine]

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.

[Dark_Omen]

thanks this was a lot of help

[Paladine]

Glad I could help.

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

[PoovenM]

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!

[PoovenM]

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

[PoovenM]

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.

[arsalanu1]

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

[mr.mukesh]

how do you connect to a Excel database using c#

[PoovenM]

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?

[kunalsingh4u]

can we use dataset in console mode and how

[LesF]

how do you connect to a Excel database using c#

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.

[rameshcpr]

OleDbConnection  con = new OleDbConnection (@"Network Library=DBMSSOCN;Data Source=10.16.132.1;database=craccount;User id=rameshc;Password=;Vishnu10");
            con.Open();
            int a, j;
            string gender;
                        a = int.Parse(textBox8.Text);
                        if ( radioButton1 .Checked == true )
                            gender = "male";
                        else
                            gender = "female";
                        MessageBox.Show(a.ToString());
                        if (a > 1000)
                        {
                            OleDbCommand  cmd = new OleDbCommand ("select *from bankinfo,con");
                            cmd.CommandType = CommandType.Text;
                            cmd.CommandText = "insert into account values ('" + textBox1.Text + "','" + textBox2.Text + "','" + textBox3.Text + "','" + textBox4.Text + "','" + textBox5.Text + "','" + gender + "','" + textBox6.Text + "','" + textBox7.Text + "','" + textBox8.Text + "')";
                            j = cmd.ExecuteNonQuery();
                            con.Close();
                        }
                        else
                        {
                            MessageBox.Show("No Sufficent data");
                        }

[karez]

you can use this statement to connect with sql

string str = MyProject.Properties.Settings.Default.dbnameConnectionString;
sqlconnection conn=new sqlconnection(str);

[arunkumars]

Even I have a doubt on @"Network Library=DBMSSOCN..? also, what is 'Persist info', that is in connection string...

[priya2010]

class sqlconnection
    {
        SqlConnection myconcection;

                
        private void getConnection()
        {
            String connstring = "";//your connection string
            SqlConnection myconcection = new SqlConnection(connstring);
        }
       

       // method to get data
        public DataSet getData(String sql)
        {
            getConnection();

            SqlDataAdapter adptre = new SqlDataAdapter();
            DataSet resultSet = new DataSet();

            SqlCommand sqlcmd = new SqlCommand(sql, myconcection);
            adptre.SelectCommand = sqlcmd;
            myconcection.Open();
            try
            {
                adptre.Fill(resultSet);            
            }

            catch(Exception ex)
            {
                
            }
            myconcection.Close();
            return resultSet;
        }
        // method to add data
        public void setData(string sqlcmd)
        {
            getConnection();
            myconcection.Open();

            string sqlcommand = sqlcmd;
            SqlCommand cmd = new SqlCommand(sqlcommand, myconcection);
            cmd.ExecuteNonQuery();
            myconcection.Close();
        }
     
    }

[anitha10]

How to connect a form to a sql server database from C# to ado.net. Please help me

[ajnabidost]

please help me out to connect oracle database in c# a complete example program is needed! please help