创建数据库查询方法 [英] creating a database query METHOD

问题描述

我不知道，如果IM delluded但我希望做的就是创建一个将返回查询的结果，这样我可以重用的连接代码的方法。据我了解，查询返回一个对象，但我怎么传递对象回来？我想查询送入方法作为字符串参数，并将其返回结果，这样我可以使用它们。下面是我有这是一个在黑暗中刺，这显然是行不通的。这个例子是我试图填充一个查询结果的列表框;工作表名称是员工和现场/列名。我得到的错误是复杂的数据绑定接受作为数据源，无论是一个IList或IListSource。 ？任何想法

 公共Form1中（）
 {
的InitializeComponent（）; 
 openFileDialog1.ShowDialog（）; 
 openedFile = openFileDialog1.FileName; 
 
 lbxEmployeeNames.DataSource =查询（选择[名] FROM [员工$]）; 
 
 
} 
 
公共对象查询（SQL字符串）
 {
 System.Data.OleDb.OleDbConnection MyConnection的; 
 System.Data.OleDb.OleDbCommand myCommand =新System.Data.OleDb.OleDbCommand（）; 
串connectionPath; 
 
 //建立连接字符串
 connectionPath =供应商= Microsoft.Jet.OLEDB.4.0;数据源='+ openedFile +;扩展属性= Excel的8.0;; 
 
 MyConnection的=新System.Data.OleDb.OleDbConnection（connectionPath）; 
 MyConnection.Open（）; 
 myCommand.Connection = MyConnection的; 
 
 myCommand.CommandText = SQL; 
返回myCommand.ExecuteNonQuery（）; 
 
 
} 
  

解决方案

嘿！试试这个，
。如果你只是想显示所有员工进入一个列表框的名字，这应该工作。
我刚才编辑一些行从您的代码...

  Form1中（）
 {
的InitializeComponent（）; 
 openFileDialog1.ShowDialog（）; 
 openedFile = openFileDialog1.FileName; 
 
 lbxEmployeeNames.DataSource =查询（选择[名] FROM [员工$]）; 
 lbxEmployeeNames.DisplayMember =名; //你想要的列显示在您的列表框。 
} 
 
 //返回字符串的数据表来代替。 
公共数据表查询（SQL字符串）
 {
 System.Data.OleDb.OleDbConnection MyConnection的; 
串connectionPath; 
 
 //建立连接字符串
 connectionPath =供应商= Microsoft.Jet.OLEDB.4.0;数据源='+ openedFile +;扩展属性= Excel的8.0;; 
 
 MyConnection的=新System.Data.OleDb.OleDbConnection（connectionPath）; 
 MyConnection.Open（）; 
 System.Data.OleDb.OleDbDataAdapter myDataAdapter =新System.Data.OleDb.OleDbDataAdapter（SQL，MyConnection的）; 
 DataTable的DT =新的DataTable（）; 
 myDataAdapter.Fill（DT）; 
返回DT; 
} 
  

I'm not sure if im delluded but what I would like to do is create a method that will return the results of a query, so that i can reuse the connection code. As i understand it, a query returns an object but how do i pass that object back? I want to send the query into the method as a string argument, and have it return the results so that I can use them. Here's what i have which was a stab in the dark, it obviously doesn't work. This example is me trying to populate a listbox with the results of a query; the sheet name is Employees and the field/column is name. The error i get is "Complex DataBinding accepts as a data source either an IList or an IListSource.". any ideas?

 public Form1()
        {
            InitializeComponent();
            openFileDialog1.ShowDialog();
            openedFile = openFileDialog1.FileName;

            lbxEmployeeNames.DataSource = Query("Select [name] FROM [Employees$]");


        }

        public object Query(string sql)
        {
            System.Data.OleDb.OleDbConnection MyConnection;
            System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
            string connectionPath;

            //build connection string
            connectionPath = "provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + openedFile + "';Extended Properties=Excel 8.0;";

            MyConnection = new System.Data.OleDb.OleDbConnection(connectionPath);
            MyConnection.Open();
            myCommand.Connection = MyConnection;

            myCommand.CommandText = sql;
            return myCommand.ExecuteNonQuery();


        }

解决方案

Hey! Try this, If you just want to display the names of all employees into a listBox, this should work. I just edited some lines from your code...

Form1()
{
    InitializeComponent();
    openFileDialog1.ShowDialog();
    openedFile = openFileDialog1.FileName;

    lbxEmployeeNames.DataSource = Query("Select [name] FROM [Employees$]");
    lbxEmployeeNames.DisplayMember = "name"; // The column you want to be displayed in your listBox.
}

// Return a DataTable instead of String.
public DataTable Query(string sql)
{
    System.Data.OleDb.OleDbConnection MyConnection;
    string connectionPath;

    //build connection string
    connectionPath = "provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + openedFile + "';Extended Properties=Excel 8.0;";

    MyConnection = new System.Data.OleDb.OleDbConnection(connectionPath);
    MyConnection.Open();
    System.Data.OleDb.OleDbDataAdapter myDataAdapter = new System.Data.OleDb.OleDbDataAdapter(sql, MyConnection);
    DataTable dt = new DataTable();
    myDataAdapter.Fill(dt);
    return dt;
}

这篇关于创建数据库查询方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！