cfquery type interface for C# asp.net

Question

Hello,

I really liked the cfquery in coldfusion and I'm wondering if anyone has attempted to write something like it for asp.net/C#. Or, does anyone know how they do it in cf with Java? I'd like an interface to my database that is similar

QueryResult myObject = ObjectDatabase.Query("SELECT XXXXX","DataSource");
this.Var = myObject.VariableOne;

Something like above where I can query the database and it creates my variables almost on the fly.

Answer 1

Normally these days in the dot net world we use an ORM or similar tool (NHibernate, Linq-to-Sql, SubSonic, Entity Framework, etc).

But if you really want to, something like the following should get you started. You will need to reference the appropriate assemblies of and add using statements for at least

using System.Data;
using System.Data.SqlClient;

Then you can use this code to get you started (untested and uncompiled, but close enough)

// connection string will be like "Server=(local);DataBase=Northwind;Integrated Security=SSPI"

// instantiate and open connection
using( var conn =  new SqlConnection(connectionString) )
{
    conn.Open();

    var cmd = new SqlCommand("select * from Customers where city = @City", conn);

    // define parameters used in command object
    cmd.Parameters.Add( new SqlParameter{ ParameterName = "@City", Value = inputCity });

    using( var reader = command.ExecuteReader() )
    {
        // write each record
        while(reader.Read())
        {
            Console.WriteLine("{0}, {1}", reader["CompanyName"], reader["ContactName"]);
        }
    }
}

Answer 2

Sounds like you want LINQ to SQL.