How to connect sql server 2008 with a C# desktop app in visual studio 2010?

[pro_learner]

I'm novice to C#.net & i'm using Visual Studio 2010 as the IDE.I have created a Desktop Application in C# & i want to connect it with the Database in SQL server 2008.Friends can you tell me the code or web links to solve this issue.....

[sandeepparekh9]

check here:

http://www.connectionstrings.com/sql-server-2008

http://www.sqlteam.com/article/sql-server-connection-string

[de Source]

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace DatabseConnection
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        SqlConnection con;
        SqlDataAdapter da;
        DataSet ds1;

        int MaxRow = 0;
        int CurRow = 0;

        private void Form1_Load(object sender, EventArgs e)
        {
            con = new SqlConnection();
            ds1 = new DataSet();

// i have described how to get connection string
            con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\MyWorkers.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";

            con.Open();

// instead of tblWorkers you have to write your table name
            string sql = "SELECT * From tblWorkers";
            da = new SqlDataAdapter(sql, con);
            da.Fill(ds1, "Workers");
            NavigateRecord();
            MaxRow = ds1.Tables["Workers"].Rows.Count;

            con.Close();            

        }

get connection string by clicking add new data source (in menu bar >>>> data >>>> add new data source)select database click next click new connection click change select data source (usually Microsoft SQL Server Compact 3.5 click OK browse your .mdf(database file)) click test connection if connection is succeeded click ok to msgbox and ok to ad connection dialog box now in datasource configuration wizard you'll find a plus button with connection string written beside it click on that button copy that string and paste it in your code cancel that wizard the only purpose was to get the connection string NOTE: after pasting in your code where ever you find back slash add one more to it as i did here

[de Source]

Navigate record is the method which retrive data from database and dump any where i've call it in a text box

public void NavigateRecords()
        {
           
            DataRow dRow = ds1.Tables["Workers"].Rows[inc];

            textBox1.Text = dRow.ItemArray.GetValue(1).ToString();
            textBox2.Text = dRow.ItemArray.GetValue(2).ToString();
            textBox3.Text = dRow.ItemArray.GetValue(3).ToString();
}

[de Source]

Oops Sory this for connecting sql 2005 with c#2008
i guess there wont b a lot difference

[pro_learner]

Thanks guys.....i'll try your solutions..:):)

[pro_learner]

oops.......i have another problem...as de Source told i have to lookup for a .mdf file,but i can't save my database with .mdf extention in sql server 2008.The extention default i get is .sql
I tried to save as a .mdf one.But i can't....what's that issue?

[ChrisHunter]

I made desktop app in VS 2010 for my final project in Uni an i made an app.config file with my connection string in which was like this:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <configSections>
    </configSections>
    <connectionStrings>
        <add name="ConnectionString"
            connectionString="Data Source=MyPC\MyDB;Initial Catalog=MyDB;Integrated Security=True"
            providerName="System.Data.SqlClient" />
    </connectionStrings>
</configuration>

Next i created a class called DBConnect with a method which when called, establishes connection to the DB:

class DBConnection
    {
        #region Database connection method
        public SqlConnection Connect()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
            
            try
            {
                conn.Open();
            }
            catch
            {
            }
            return conn;
        }
        #endregion
    }

and when i want to carry out an querry on the DB i created an instance of the DBConnection class and did something like this which counts the number of rows in tbl1 that mach the WEHRE clause:

public bool checkForUser(string name)
        {
            //booliean variable called noUsers set with a value of false
            bool Users = false;

            //SQL string to count the amount of rows within the OSDE_Users table
            string sql = "SELECT COUNT(LogonName) FROM MyDB.tbl1 WHERE LogonName = '" + name + "'";

            SqlCommand cmd = new SqlCommand(sql, db.Connect());

            try
            {
                //Sets the value of int variable called rowCount with the result of the executed Sql statement called sql
                int rowCount = Convert.ToInt32(cmd.ExecuteScalar());

                //Executed if value of rowCount is greater than or equal to 1
                if (rowCount >= 1)
                {
                    ////Sets the value of noUsers to false
                    Users = true;
                }
                //Executed if value of rowCount is less than or equal to 0
                else if (rowCount <= 0)
                {
                    //Sets the value of noUsers to true
                    Users = false;
                }
            }
            catch
            {
                //Sets the value of noUsers to true
                Users = false;
            }
            //Returns the value of noUsers
            return Users;
        }

[pro_learner]

@ ChrisHunter,
Thank you very much.......:)