C# WPF 中的 SQL Server 连接

SQL Server connection in WPF

提问by EHS

I have a data base in SQL Server 2008 and connecting it in WPFapplication.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.

我在 SQL Server 2008 中有一个数据库并在WPF应用程序中连接它。我想从表中读取数据并显示在datagrid. 连接已成功创建，但是当我在网格中显示它时，它显示数据库错误（异常处理）。这就是我正在做的。提前致谢。

  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 connectedwhen i comment the line sda.Fill(dt);

connected当我评论该行时 它会显示sda.Fill(dt);

采纳答案by Novak

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

你SqlCommand不知道你打开了连接——它需要一个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");
}

回答by Lloyd

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

回答by Nik

Assign Connection Object to SqlCommand Object.

将连接对象分配给 SqlCommand 对象。

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

}

回答by Bhupinder

Add thisConnection as 2nd parameter in

添加 thisConnection 作为第二个参数

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");
}

回答by Arpit Shah

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

我将此代码与 Oracle 一起使用，希望对您有所帮助。

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

首先添加引用Oracle.DataAccess然后使用 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:

XAML 代码：

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

回答by Masoud Siahkali

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);
                }
            }
        }