Click here to Skip to main content
Click here to Skip to main content
Alternative Tip

An Easy Way To Query a Database

, 23 Feb 2011
Rate this:
Please Sign up or sign in to vote.
Nice,How about adding a couple of other methodspublic static void Parameter(IDbCommand command, string naam, object value){ IDbDataParameter par = command.CreateParameter(); par.ParameterName = naam; par.Value = value ?? DBNull.Value; ...
Nice,
How about adding a couple of other methods
public static void Parameter(IDbCommand command, string naam, object value)
{
    IDbDataParameter par = command.CreateParameter();
    par.ParameterName = naam;
    par.Value = value ?? DBNull.Value;
    command.Parameters.Add(par);
}
public static void Fill(IDbCommand command, DataSet dataset, string tableName)
{
    if (!dataset.Tables.Contains(tableName))
        dataset.Tables.Add(new DataTable(tableName));
    Fill(command, dataset.Tables[tableName]);
 }
public static void Fill(IDbCommand command, DataTable table)
{
    try
    {
        if (command.Connection.State != ConnectionState.Open)
            command.Connection.Open();
        table.Load(command.ExecuteReader(CommandBehavior.SingleResult), LoadOption.OverwriteChanges);
    }
    finally
    {
        command.Connection.Close();
    }
}
public static object FieldByName(IDbCommand command, string naam)
{
    try
    {
        if (command.Connection.State != ConnectionState.Open)
            command.Connection.Open();
        IDataReader reader = command.ExecuteReader();
        if (reader.Read())
            return reader[naam];
    }
    finally
    {
        command.Connection.Close();
    }
    return null;
}
public static object FieldByName(DataTable table, string naam)
{
    if ((table.Rows.Count <= 0) || (!table.Columns.Contains(naam)))
        return null;
    return table.Rows[0][naam];
}
// and my favs :) 
public static IList ReadAsList(System.Data.IDbCommand command, Type targetType)
{
    Type tList = typeof(List<>).MakeGenericType(targetType);
    IList list = (IList)Activator.CreateInstance(tList);
    try
    {
        if (command.Connection.State != System.Data.ConnectionState.Open)
            command.Connection.Open();
        IDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            object item = Activator.CreateInstance(targetType);
            for (int i = 0; i < reader.FieldCount; i++)
            {
                foreach (PropertyInfo property in targetType.GetProperties(Flags))
                {
                    if ((property.Name == reader.GetName(i)) && (property.CanWrite))
                        property.SetValue(item, reader.GetValue(i), null);
                }
            }
            list.Add(item);
        }
    }
    finally
    {
        command.Dispose();
    }
    return list;
}
public static object ReadAsObject(System.Data.IDbCommand command, Type targetType)
{    try
    {
        if (command.Connection.State != System.Data.ConnectionState.Open)
            command.Connection.Open();
        IDataReader reader = command.ExecuteReader();
        if (reader.Read())
        {
            object item = Activator.CreateInstance(targetType);
            for (int i = 0; i < reader.FieldCount; i++)
            {
                foreach (PropertyInfo property in targetType.GetProperties(Flags))
                {
                    if ((property.Name == reader.GetName(i)) && (property.CanWrite))
                        property.SetValue(item, reader.GetValue(i), null);
                }
            }
            return item;
        }
    }
    finally
    {
        command.Dispose();
    }
    return null;
}
 
Or even how about using DataContext.
That would be easy enough.
using(var database = new DataContext(connectionString)){
   // executing a command
   int result = database.ExecuteCommand(query, parameters);
   // Execute a query and translate the result to an Object
   ResultObject result = database.ExecuteQuery<resultobject>(query, parameters).FirstOrDefault();
}</resultobject>

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

PrimeCoder
Engineer
Netherlands Netherlands
Hi, people call me Prime(Coder), the name PrimeCoder stuck because I started as coder (nga); so it always said Coded by Prime or Code: Prime...
 
Thus Prime became PrimeCoder and still Prime sounds good enough.
Follow on   Twitter

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web03 | 2.8.140721.1 | Last Updated 24 Feb 2011
Article Copyright 2011 by PrimeCoder
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid