CODEWITHSUNDEEP
c#sql-serverwpf
version2.xx
version2.xx

Reputation: 51

Best method to connect to SQL Server in C# WPF

I'm a beginner.

I already found a way to connect to SQL SERVER using the codes below:

private void getListBtn_Click(object sender, RoutedEventArgs e)
    {
        SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=myDB;Integrated Security=true;");
        SqlDataAdapter sda = new SqlDataAdapter("SELECT ID,Date,Name,City FROM Info;", con);
        DataTable dt = new DataTable();
        sda.Fill(dt);
        dataGridForm.ItemsSource = dt.DefaultView;

I also wanted to get number of rows from a TABLE and set it to a label, But it's not a good idea to copy and paste this code again, I want to have a method for sqlconnection so i won't rewrite this code again and again for every single query.

Sorry i'm an absolute beginner, 3 days since i started learning C# WPF.

Upvotes: 5

Views: 14073

Answers (4)

user17922872
user17922872

Reputation: 1

If we copy paste your code, then the error is appearing. I have corrected it and maybe others don't need to struggle like me to find this. :)

                 // Object exists and State is open
                 if (Conex != null && Conex.Con.State == 
                 System.Data.ConnectionState.Open)  
                 {
                // Create a String to hold the query
                string query = "insert into Xray_Table values 
                 (25,'zzz','hij',3,'uuu',6,'2012-06-18 
                  10:34:09.000')";

                // Create a SqlCommand object and pass the constructor the connection string and the query string
                SqlCommand queryCommand = new SqlCommand(query, Conex.Con);

                // Execute the query to update to the database
                queryCommand.ExecuteNonQuery();

                // method to close the connection.
                Conex.conClose();
            }

Upvotes: 0

Nicolas C
Nicolas C

Reputation: 235

Yes some frameworks and/or ADO's solutions are good and maybe the best "professionnal" approch, you say you're a beginner and I was it not so far ;-).

So the simpliest way is to add a new class for the sql connection. In example add a Sqlconnect.cs class.

using System.Data.SqlClient;

public class Sqlconnect 
{
    public SqlConnection Con { get; set; }//the object
    private string conString { get; set; }//the string to store your connection parameters
}

This class will have a method to open the connection and one to close it.

public void conOpen()
{
    conString = "Data Source=..."; //the same as you post in your post
    Con = new SqlConnection(conString);//
    try
    {
        Con.Open();//try to open the connection
    }
    catch (Exception ex)
    {
        //you do stuff if the connection can't open, returning a massagebox with the error, write the error in a log.txt file...
    }
}
public void conClose()
{
    Con.Close();//close the connection
}

In your other(s) classe(s) where you need a sql query you first instantiate an new object. 

private void getListBtn_Click(object sender, RoutedEventArg e)
{
    Sqlconnect con = new Sqlconnect();//instantiate a new object 'Con' from the class Sqlconnect.cs
    con.conOpen();//method to open the connection.

    //you should test if the connection is open or not
    if(con!= null && con.State == ConnectionState.Open)//youtest if the object exist and if his state is open
    {
        //make your query
        SqlDataAdapter sda = new SqlDataAdapter("your query", con);
        //etc

        con.conClose();//close your connection
    }
    else
    {
        //the connection failed so do some stuff, messagebox...as you want
        return;//close the event
    }

}

this example need some ameliorations, it's evident but I wrote it like this to be clearest.

Upvotes: 4

Piotr Pierowicz
Piotr Pierowicz

Reputation: 1

Best choice if you don't need so much performance is ORM like Entity Framework. Here is something of basics.

Just use it like in MVC app.

Upvotes: 0

Ashish
Ashish

Reputation: 131

First thing this is not related to WPF, this is general coding even I would not consider this to be related to .net.

For your current problem to show the count, you dont have to make a call again. You can get the count from the datatable row count. But, I would suggest few things:

  1. You should have one or different separate layers like business, data access etc. as per your needs.
  2. You should not give the connection as the way you have provided here.
  3. You can choose to use any ORMs like entity framework, NHibernate etc based on your needs. This just a direction, you can choose to stick with ADO.Net as you have it your choice. But I would definitely suggest to throw in more layers to avoid duplicate codes and more structured approach.

Upvotes: 1

Related Questions