using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace DBComm
{
static class DBCommand
{
public class DBParameters
{
private SqlCommand m_owner = null;
public DBParameters(SqlCommand owner)
{
m_owner = owner;
}
public SqlParameterCollection P()
{
return m_owner.Parameters;
}
};
public static bool BulkToDB(string tabname, DataTable dt, params string[] destColumnNames)
{
bool bRet = false;
do
{
if (dt == null)
break;
if (dt.Rows.Count == 0)
break;
using (SqlConnection conn = DBConn.GetConn())
{
if (conn == null)
break;
SqlBulkCopy bulkcopy = new SqlBulkCopy(conn);
if (bulkcopy == null)
break;
bulkcopy.DestinationTableName = tabname;
bulkcopy.BulkCopyTimeout = 30;
if (destColumnNames.Length == 0)
{
foreach (DataColumn col in dt.Columns)
bulkcopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
}
else
{
if (destColumnNames.Length == dt.Columns.Count)
{
for (int i = 0; i < destColumnNames.Length; ++i)
{
bulkcopy.ColumnMappings.Add(dt.Columns[i].ColumnName, destColumnNames[i]);
}
}
}
bulkcopy.BatchSize = dt.Rows.Count;
try
{
bulkcopy.WriteToServer(dt);
}
catch (System.Exception e)
{
string err = e.Message;
break;
}
finally
{
bulkcopy.Close();
}
}
bRet = true;
} while (false);
return bRet;
}
public static DBParameters ExecProcNonQuery(string proc_name, object[] paraValues)
{
using (SqlConnection conn = DBConn.GetConn())
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = proc_name;
AddInParaValues(cmd, paraValues);
cmd.ExecuteNonQuery();
return new DBParameters(cmd);
}
}
public delegate T[] FillValues(SqlDataReader reader);
public static T[] QuerySomes(string sql, FillValues fill)
{
using (SqlConnection conn = DBConn.GetConn())
{
T[] result = null;
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
SqlDataReader reader = null;
lock (reader = cmd.ExecuteReader())
{
try
{
result = fill(reader);
}
catch (Exception e)
{
throw new Exception(e.StackTrace);
}
finally
{
reader.Close();
}
}
return result;
}
}
public delegate object FillValue(SqlDataReader reader);
public static object QuerySome(string sql, FillValue fill)
{
using (SqlConnection conn = DBConn.GetConn())
{
object result = null;
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
SqlDataReader reader = null;
lock (reader = cmd.ExecuteReader())
{
try
{
result = fill(reader);
}
catch (Exception e)
{
throw new Exception(e.StackTrace);
}
finally
{
reader.Close();
}
}
return result;
}
}
public static object FillResultValue(SqlDataReader reader)
{
object o = null;
if (reader.Read())
{
o = reader.GetValue(0);
}
return o;
}
public static bool QueryBoolean(string sql)
{
return Convert.ToBoolean(QuerySome(sql, new FillValue(FillResultValue)));
}
public static byte[] QueryBytes(string sql)
{
return (byte[])(QuerySome(sql, new FillValue(FillResultValue)));
}
public static int QueryInteger(string sql)
{
return Convert.ToInt32(QuerySome(sql, new FillValue(FillResultValue)));
}
public static string QueryStr(string sql)
{
return QuerySome(sql, new FillValue(FillResultValue)) as string;
}
private static string[] FillStrsValue(SqlDataReader reader)
{
List
lststr = new List();
while (reader.Read())
{
lststr.Add(reader.GetString(0));
}
return lststr.ToArray();
}
public static string[] QueryStrs(string sql)
{
return QuerySomes(sql, new FillValues(FillStrsValue));
}
private static bool[] FillBooleansValue(SqlDataReader reader)
{
List lstbool = new List();
while (reader.Read())
{
lstbool.Add(reader.GetBoolean(0));
}
return lstbool.ToArray();
}
public static bool[] QueryBooleans(string sql)
{
return QuerySomes(sql, new FillValues(FillBooleansValue));
}
public static void ExecCmd(string sql)
{
using (SqlConnection conn = DBConn.GetConn())
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText =