ADOHelper

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

namespace Core.Tests {

    public class ADOHelper {

        public static void SetTableToAllowNulls(DataTable t) {
            foreach(DataColumn col in t.Columns)
                col.AllowDBNull = true;
        }

        public static bool IsDifferent(DataRow fromRow,DataRow toRow) {
            DataColumnCollection col = fromRow.Table.Columns;
            foreach(DataColumn dc in toRow.Table.Columns) {
                if(col.Contains(dc.ColumnName)) {
                    if(!toRow[dc.ColumnName].ToString().Equals(fromRow[dc.ColumnName].ToString()))
                        return true;
                }
            }
            return false;
        }
        public static bool IsDataInTable(DataTable table) {
            if(table == null)
                return false;
            if(table.Rows.Count == 0)
                return false;
            return true;
        }
        public static bool TryScalarResultInt(object ret,out int val) {
            val = 0;
            if(ret == null)
                return false;
            return int.TryParse(ret.ToString(),out val);
        }
        public static T GetFirstItem<T>(Array results) {
            if(HasAnyRecords(results) == false)
                return default(T);
            return (T)results.GetValue(0);
        }
        public static bool HasAnyRecords(Array results) {
            if(results == null)
                return false;
            if(results.Length == 0)
                return false;
            return true;
        }
        public static DataRow CopyRow(DataRow row) {
            DataRow newRow = row.Table.NewRow();
            newRow.ItemArray = row.ItemArray;
            return newRow;
        }
        public static void MapRow(DataRow fromRow,DataRow toRow) {
            DataColumnCollection col = fromRow.Table.Columns;
            foreach(DataColumn dc in toRow.Table.Columns) {
                if(col.Contains(dc.ColumnName))
                    toRow[dc.ColumnName] = fromRow[dc.ColumnName];
            }
        }
        public static int ExceuteCommandText(string sql) {
            return ExceuteCommandText(null,sql);
        }
        public static int ExceuteCommandText(string dbName,string sql) {
            using(SqlConnection cn = (SqlConnection)CreateConnection(dbName)) {
                cn.Open();
                using(SqlTransaction tx = cn.BeginTransaction()) {
                    try {
                        using(SqlCommand cmd = new SqlCommand(sql,cn,tx)) {
                            int ret = cmd.ExecuteNonQuery();
                            tx.Commit();
                            return ret;
                        }
                    }
                    catch {
                        tx.Rollback();
                        throw;
                    }
                }
            }
        }
        public static IDbCommand CreateCommand(string dbName,string sql) {
            SqlConnection cn = (SqlConnection)CreateConnection(dbName);
            cn.Open();
            SqlCommand cmd = new SqlCommand(sql,cn);
            return cmd;
        }
        public static DataTable FetchDataTable(string sql) {
            return FetchDataTable(null,sql);
        }
        public static DataTable FetchDataTable(string sql,int startRecord,int maxRecords) {
            return FetchDataTable(null,sql,startRecord,maxRecords);
        }
        public static DataTable FetchDataTable(string dbName,string sql) {
            using(SqlConnection cn = (SqlConnection)CreateConnection(dbName)) {
                cn.Open();
                DataTable table = new DataTable();
                using(SqlDataAdapter da = new SqlDataAdapter(sql,cn)) {
                    da.Fill(table);
                    return table;
                }
            }
        }
        public static DataTable FetchDataTable(string dbName,string sql,int startRecord,int maxRecords) {
            using(SqlConnection cn = (SqlConnection)CreateConnection(dbName)) {
                cn.Open();
                DataTable table = new DataTable();
                using(SqlDataAdapter da = new SqlDataAdapter(sql,cn)) {
                    da.Fill(startRecord,maxRecords,table);
                    return table;
                }
            }
        }
        public static SqlConnection CreateConnection(string dbName) {
            if(dbName.IsNull())
                return CreateConnection();
            ConnectionStringSettings setting = ConfigurationManager.ConnectionStrings[dbName];
            if(setting == null)
                throw new ArgumentNullException("dbName","DataTableHelper.CreateDatabase " + dbName);
            return new SqlConnection(setting.ConnectionString);
        }
        public static SqlConnection CreateConnection() {
            if(ConfigurationManager.ConnectionStrings.Count == 0)
                throw new ArgumentException("SqlConnection CreateDatabase - No ConfigurationManager.ConnectionStrings");
            ConnectionStringSettings setting = ConfigurationManager.ConnectionStrings[0];
            return new SqlConnection(setting.ConnectionString);
        }
    }
}