SQL Server connection in WPF

Question

I have a data base in SQL Server 2008 and connecting it in WPF application.I want to read data from table and show in datagrid. Connection is successfully created but when I show it in grid,it show db error(Exception handling). This is what I am doing.Thanks in advance.

  try
  {
       SqlConnection thisConnection = new SqlConnection(@"Server=(local);Database=Sample_db;Trusted_Connection=Yes;");
       thisConnection.Open();

       string Get_Data = "SELECT * FROM emp";     

       SqlCommand cmd = new SqlCommand(Get_Data);              
       SqlDataAdapter sda = new SqlDataAdapter(cmd);               
       DataTable dt = new DataTable("emp");
       sda.Fill(dt);
       MessageBox.Show("connected");
       //dataGrid1.ItemsSource = dt.DefaultView;           
  }
  catch
  {
       MessageBox.Show("db error");
  }

It shows connected when i comment the line sda.Fill(dt);

Try to retrieve the exception error info: catch (Exception ex). — Novak
– Novak, Commented Jun 18, 2013 at 11:52
Please replace your catch block with catch (Exception ex) { MessageBox.Show(ex.Message) } to see the exception details. — Dennis Traub
– Dennis Traub, Commented Jun 18, 2013 at 11:52
You don't assigned the command any connection. You open the connection then create a command, but don't link the two. — Lloyd
– Lloyd, Commented Jun 18, 2013 at 11:52
@GuyDavid it shows that "SelectComand.Connection Property has not been initialized" — EHS
– EHS, Commented Jun 18, 2013 at 11:57
@Lloyd can u please tell me how to do ?Or you edit my code.I am totally new on WPf and my first day at job. — EHS
– EHS, Commented Jun 18, 2013 at 11:58

Novak · Accepted Answer · 2013-06-18 11:55:32Z

12

Your SqlCommand doesn't know you opened the connection- it requires an instance of SqlConnection.

try
{
       SqlConnection thisConnection = new SqlConnection(@"Server=(local);Database=Sample_db;Trusted_Connection=Yes;");
       thisConnection.Open();

       string Get_Data = "SELECT * FROM emp";     

       SqlCommand cmd = thisConnection.CreateCommand();
       cmd.CommandText = Get_Data;

       SqlDataAdapter sda = new SqlDataAdapter(cmd);               
       DataTable dt = new DataTable("emp");
       sda.Fill(dt);

       dataGrid1.ItemsSource = dt.DefaultView;           
}
catch
{
       MessageBox.Show("db error");
}

answered Jun 18, 2013 at 11:55

Novak

2,77010 gold badges46 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Amit Kumar Ghosh Over a year ago

or use the overload of SqlCommand to pas the SqlConnection object in.

Lloyd · Accepted Answer · 2013-06-18 11:55:08Z

0

You don't assign the command any connection. You open the connection then create a command, but don't link the two.

Try something like:

SqlConnection conn = new SqlConnection(@"Server(local);Database=Sample_db;Trusted_Connection=Yes;");
conn.Open();

string sql= "SELECT * FROM emp";     

SqlCommand cmd = new SqlCommand(sql); 
cmd.Connection = conn;

SqlDataAdapter sda = new SqlDataAdapter(cmd);               
DataTable dt = new DataTable("emp");
sda.Fill(dt);

answered Jun 18, 2013 at 11:55

Lloyd

29.7k5 gold badges89 silver badges100 bronze badges

3 Comments

EHS Over a year ago

Thanks,It works ,But can showing my data in grid.I have three records and here it is showing some lines.

EHS Over a year ago

I know ,But i need to solve this also.In SQl Server ,It is showing my records.But why not here?

Novak Over a year ago

If you used the exact example Lloyd made, make sure you set the ItemSource of dataGrid1.

2 revs · Accepted Answer · 2013-06-19 04:17:42Z

0

Assign Connection Object to SqlCommand Object.

using (SqlConnection connection = new SqlConnection(
           connectionString))
{
    SqlCommand command = new SqlCommand();
    command.Connection = connection;
    command.CommandTimeout = 15;
    command.CommandType = CommandType.Text;
    command.CommandText = queryString;

    connection.Open();
   //Perfom desired Action

}

edited Jun 19, 2013 at 4:17

community wiki

2 revs
Nik

Comments

Sankumarsingh · Accepted Answer · 2014-01-09 10:54:49Z

0

Add thisConnection as 2nd parameter in

Sqlcommand cmd = new SqlCommand(Get_Data, thisConnection)


try {
    string connectionstring = "@"
    Server = (local) Database = Sample_dbTrusted_Connection = Yes;
    "";
    SqlConnection thisConnection = new SqlConnection(connectionstring);
    thisConnection.Open();

    string Get_Data = "SELECT * FROM emp";

    SqlCommand cmd = new SqlCommand(Get_Data, thisConnection);
    SqlDataAdapter sda = new SqlDataAdapter(cmd);`
    DataTable dt = new DataTable("emp");
    sda.Fill(dt);
    MessageBox.Show("connected");
    //dataGrid1.ItemsSource = dt.DefaultView;           
} catch {
    MessageBox.Show("db error");
}

edited Jan 9, 2014 at 10:54

Sankumarsingh

10.1k11 gold badges54 silver badges75 bronze badges

answered Jan 9, 2014 at 10:33

Bhupinder

6911 gold badge5 silver badges7 bronze badges

Comments

Mike Laren · Accepted Answer · 2015-02-08 08:51:24Z

I use this code with Oracle and hope it will help you.

First add reference Oracle.DataAccess then add namespace using Oracle.DataAccess.Client;

And using the following code

try
{
  string MyConString = "Data Source=localhost;User Id= yourusername;Password=yourpassword;";
  using (OracleConnection connection = new OracleConnection(MyConString))
  {
  connection.Open();
  sqldb1 = "select * from DEMO_CUSTOMERS;";
  using (OracleCommand cmdSe1 = new OracleCommand(sqldb1, connection))
  {
    DataTable dt = new DataTable();
    OracleDataAdapter da = new OracleDataAdapter(cmdSe1);
    da.Fill(dt);
    db1.ItemsSource = dt.DefaultView;
  }
  connection.Close();
}
catch (Exception ex)
{
  MessageBox.Show(ex.ToString());
}

XAML code:

<DockPanel>
    <DataGrid Margin="10.0" DockPanel.Dock="Left" Name="db1"  AutoGenerateColumns="True" >
    </DataGrid>
</DockPanel>

Masoud Siahkali · Accepted Answer · 2016-11-07 08:47:02Z

public DataTable Execute(string cmd)
{
        bool networkUp = System.Net.NetworkInformation.NetworkInterface.GetIsNetworkAvailable();
        if (networkUp)
        {
            try
            {
                    SqlConnection connection = new SqlConnection("ConnectionString");
                    SqlCommand command = new SqlCommand(cmd);
                    using (SqlDataAdapter sda = new SqlDataAdapter())
                    {
                        DataTable dt = new DataTable();
                        sda.SelectCommand = command;
                        command.Connection = connection;
                        connection.Open();
                        sda.Fill(dt);
                        connection.Close();
                        if (dt != null && dt.Columns.Count > 0)
                            return dt;
                        else
                            return null;
                    }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return null;
            }
        }
        else
        {
            Console.WriteLine("Network is disconnect");
            return null;
        }
    return null;
}

or :

  public void ExecuteNonQuery(string cmd)
        {
            bool networkUp = System.Net.NetworkInformation.NetworkInterface.GetIsNetworkAvailable();
            if (networkUp)
            {
                try
                {
                  SqlConnection connection = new SqlConnection("ConnectionString");
                  SqlCommand command = new SqlCommand(cmd);
                  command.Connection = connection;
                  connection.Open();
                  command.ExecuteNonQuery();
                  connection.Close();
                }
                catch (Exception ex)
                {
                   Console.WriteLine(ex.Message);
                }
            }
        }

Collectives™ on Stack Overflow

SQL Server connection in WPF

6 Answers 6

1 Comment

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related