How to connect .NET with sql database?

Question

Hello, I currently have Visual Studio 2005 and SQL Server 2005. I have installed an empty database into SQL Server, but I don't have any idea how to use VS to connect with the database.

What kind of project should I use (I'm going to use the database in a windows application) and exactly how will I be able to insert it to the project and use it?

EDIT: I have a database in Microsoft SQL Server Managament Studio, and I want to use it in a winForm, using c#. I have to connect these two somehow ?

Answer 1

The simplest way is using ADO.Net , there is no special project to use , just use a normal Winforms Project (which you said you are already doing).

Here are some basics that you can follow http://www.sitepoint.com/article/introduction-ado-net/

Answer 2

Hi,

You've a few options - the easiest (but least performant, ideal, or recommended for enterprise-scale use) is via the 'Server Explorer' in Visual Studio. Accessing via View...Server Explorer... and expand Data Connections node, and browse your newly created database.

From here you can drag data sources etc onto your Windows Application.

Ideally however you'd use ADO, stored procedure or SQL directly.

Answer 3

Use classes of System.Data.SqlClient namespace.

using System.Data.SqlClient;

class Test{
   static void Main(){  
      SqlConnection Cn=new SqlConnection(@"connection_string");
      Cn.Open();
      Cn.Close();
   }
}

Read this article - Learning ADO.NET

SUMMARY: ADO.NET is a data-access technology that enables applications to connect to data stores and manipulate data contained in them in various ways. It is based on the .NET Framework and it is highly integrated with the rest of the Framework class library.

Answer 4

You can create either of the project type:

1. Web Application
2. Windows Application
3. Console Application
4. Web Service
5. Class Library

To connect to database, use the code below:

SqlDataReader rdr = null;
SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI");
SqlCommand cmd = new SqlCommand("select * from Customers", conn);
rdr = cmd.ExecuteReader();
conn.Close();
conn.Dispose();

I suggest pick up a good book on ADO.NET and read through it. Serach for some articles in google.

Answer 5

try this ADO.NET Sample Application

using System;
using System.Data;
using System.Data.OleDb;

class Sample
{
  public static void Main() 
  {
    OleDbConnection nwindConn = new OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");

    OleDbCommand catCMD = nwindConn.CreateCommand();
    catCMD.CommandText = "SELECT CategoryID, CategoryName FROM Categories";

    nwindConn.Open();

    OleDbDataReader myReader = catCMD.ExecuteReader();

    while (myReader.Read())
    {
      Console.WriteLine("\t{0}\t{1}", myReader.GetInt32(0), myReader.GetString(1));
    }

    myReader.Close();
    nwindConn.Close();
  }
}

bye

Answer 6

In visual studio you got a Data menu option that can open a Data Sources view. Add a data connection and use the wizard to 'target' the database. After creating a local 'representation' of a table, drag that table from the datasource tab to you windows form application.

Look at all the automatically created objects and try to create a list of objects and how they interact. Can you spot the connectionstring or the table definition?

Answer 7

You may wish to consider nHibernate or a similiar ORM. Rather than using ADO.Net natively, the ORM layer can help reduce the amount of error prone an repetitive data access code that you right.

The learning curve is steeper than jumping into ADO.Net, but not significantly, and lots of the things you will need to learn about - relations, transactions and "business transactions", lazy loading, you will need to think about anyway - so I would suggest that being forced to think about them is no bad thing.

Checkout www.nhforge.org for some great getting started resources.