1,105,320 Community Members

How do you connect to a sql database using C#

Member Avatar
Dark_Omen
Posting Pro
573 posts since Apr 2004
Reputation Points: 5 [?]
Q&As Helped to Solve: 6 [?]
Skill Endorsements: 0 [?]
 
0
 

Hello everyone,

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

Thanks in advance

Member Avatar
Paladine
Master Poster
812 posts since Feb 2003
Reputation Points: 138 [?]
Q&As Helped to Solve: 28 [?]
Skill Endorsements: 5 [?]
Team Colleague
 
1
 

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.

Member Avatar
Dark_Omen
Posting Pro
573 posts since Apr 2004
Reputation Points: 5 [?]
Q&As Helped to Solve: 6 [?]
Skill Endorsements: 0 [?]
 
1
 

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

Member Avatar
Paladine
Master Poster
812 posts since Feb 2003
Reputation Points: 138 [?]
Q&As Helped to Solve: 28 [?]
Skill Endorsements: 5 [?]
Team Colleague
 
1
 

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.

Member Avatar
Dark_Omen
Posting Pro
573 posts since Apr 2004
Reputation Points: 5 [?]
Q&As Helped to Solve: 6 [?]
Skill Endorsements: 0 [?]
 
1
 

thanks this was a lot of help

Member Avatar
Paladine
Master Poster
812 posts since Feb 2003
Reputation Points: 138 [?]
Q&As Helped to Solve: 28 [?]
Skill Endorsements: 5 [?]
Team Colleague
 
1
 

Glad I could help.

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

Member Avatar
PoovenM
Junior Poster
148 posts since Aug 2006
Reputation Points: 30 [?]
Q&As Helped to Solve: 11 [?]
Skill Endorsements: 0 [?]
 
0
 

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!

Member Avatar
PoovenM
Junior Poster
148 posts since Aug 2006
Reputation Points: 30 [?]
Q&As Helped to Solve: 11 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
PoovenM
Junior Poster
148 posts since Aug 2006
Reputation Points: 30 [?]
Q&As Helped to Solve: 11 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
arsalanu1
Newbie Poster
1 post since Aug 2008
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
mr.mukesh
Newbie Poster
2 posts since Jan 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
-1
 

how do you connect to a Excel database using c#

Member Avatar
PoovenM
Junior Poster
148 posts since Aug 2006
Reputation Points: 30 [?]
Q&As Helped to Solve: 11 [?]
Skill Endorsements: 0 [?]
 
0
 

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?

Member Avatar
kunalsingh4u
Newbie Poster
1 post since Nov 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

can we use dataset in console mode and how

Member Avatar
LesF
Newbie Poster
13 posts since May 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 3 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
rameshcpr
Newbie Poster
1 post since Jul 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 
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");
                        }
Member Avatar
karez
Newbie Poster
1 post since Jul 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

you can use this statement to connect with sql

string str = MyProject.Properties.Settings.Default.dbnameConnectionString;
sqlconnection conn=new sqlconnection(str);
Member Avatar
arunkumars
Posting Whiz in Training
204 posts since Jul 2009
Reputation Points: 16 [?]
Q&As Helped to Solve: 22 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
priya2010
Newbie Poster
1 post since Jul 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 
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();
        }
     
    }
Member Avatar
anitha10
Newbie Poster
19 posts since Aug 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
ajnabidost
Newbie Poster
1 post since Aug 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article