Creating a query to the METHOD database

Question

I'm not sure if I don’t like it, but what I would like to do is create a method that will return the results of the request so that I can reuse the connection code. As I understand it, the request returns an object, but how to pass this object back? I want to send the request to the method as a string argument and return the results to it so that I can use them. Here is what I have, which was a blow in the dark, this clearly does not work. In this example, I am trying to populate a list with query results; the sheet name is Employees, and the field / column is the name. The error I get is "Complex DataBinding accepts either IList or IListSource as the data source." 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();


        }

Answer 1

! , listBox, . ...

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

Answer 2

, < : . SQL- , - . - , , , , , , . , , , , , .

, , - , : ( ). , , . , , . , , . , :

public class DataLayer
{
   private DbConnection GetConnection()
   {
        //This could also be a connection for OleDb, ODBC, Oracle, MySQL, 
        // or whatever kind of database you have.
        //We could also use this place (or the constructor) to load the 
        // connection string from an external source, like a
        // (possibly-encrypted) config file
        return new SqlConnection("connection string here");
   }
}

. , , - , . . , , . , , , , , :

private DataTable Query(string sql)
{
     var result = new DataTable();
     using (var connection = GetConnection())
     using (var command = new SqlCommand(sql, connection)
     {
         connection.Open();
         result.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
     }
     return result;
}

( //). , . , . , , - . , , .

, , - using. - .Net #. using . . , . -, , ; - . , , . , , try/catch. , IDisposable .Net. IDisposable : , (think: inherit) IDisposable .

using . , :

SqlConnection connection;
try
{
   connection = new SqlConnection("connection string here");
   SqlCommand command = new SqlCommand("sql query here", connetion);

   connection.Open();
   SqlDataReader reader = command.ExecuteReader(); 
   //do something with the data reader here
}
finally
{
    connection.Close();
}

. finally, , . using - , , . , connection.Close(), , , , . sql try/finally , .Close() , , . , .

- : -, . , sql, , . Employee:

public DataTable GetEmployeeData()
{
    return Query("SELECT * FROM Employees");
}

, ... , , . -. , : , . , - , , . , "Fred":

public DataTable GetFredsEmployeeData()
{
     return Query("SELECT * FROM Employees WHERE Firstname='Fred'");
}

, , . . - :

public DataTable GetEmployeeData(string FirstName)
{
    return Query("SELECT * FROM Employees WHERE FirstName='" + FirstName + "'");
}

, . . , , , - ';Drop table employees;-- ( ) FirstName . - , , , , sql.

, Query. , , , , , sql. , . , sql . SqlParameter, / . , , .

, , , , , ( ) #. , , #: /lambdas (: , ). , , . , . , Query(), :

private DataTable Query(string sql, Action<SqlParameterCollection> addParameters)
{
    var result = new DataTable();
    using (var connection = GetConnection())
    using (var command = new SqlCommand(sql, connection)
    {
        //addParameters is a function we can call that was as an argument
        addParameters(command.Parameters);

        connection.Open(); 
        result.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
    }
    return result;
}

Action<SqlParameterCollection>. < >. , , . , ( , SqlParameterCollection ) . GetEmployeeData():

public DataTable GetEmployeeData(string firstName)
{
    return Query("SELECT * FROM Employees WHERE FirstName= @Firstname", 
    p => 
    {
        p.Add("@FirstName", SqlDbType.VarChar, 50).Value = firstName;
    });
}  

, Query() firstName, GetEmployeeData(), @FirstName sql. , ADO.Net sql. , , SQL-. , . , , . , , , , () .

(!) . - :

public DataTable GetAllEmployees()
{
    return Query("SELECT * FROM Employees", p => {});
}

Query(), , , , .

-, , , . , . , datatable, datareader . , , . , , , , , , , , , , , . , , , .

, : finally . , , .

Answer 3

ExecuteReader. , :

OleDbDataReader myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
while(myReader.Read()) 
{
   Console.WriteLine(myReader.GetString(0));
}

Answer 4

, , , . , SQL-. , sql.

( , ) ADO.

, , , - , :
, , SQL Server

, ADO , DbDataReader. , IEnumerable <IDataRecord> , linq , Action <SqlCommand> , . , :

private static IEnumerable<IDataRecord> Retrieve(string sql, Action<SqlParameterCollection> addParameters)
{
    using (var cn = new SqlConnection(ConnectionString))
    using (var cmd = new SqlCommand(sql, cn))
    {
        addParameters(cmd.Parameters);

        cn.Open();
        using (var rdr = cmd.ExecuteReader())
        {
            while (rdr.Read())
                yield return rdr;
            rdr.Close();
        }
    }
}

, :

public IEnumerable<IDataRecord> GetSomeDataById(int MyId)
{
    return Retrieve(
        "SELECT * FROM [MyTable] WHERE ID= @MyID", 
       p =>
       {
          p.Add("@MyID", SqlDbType.Int).Value = MyId;
       }
     );
}

, , , ( ) .