底层sqlHelper
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
namespace WX_Card.CardDAL
{
public class D_Helper
{
#region 日志信息
public static void WriteBarcodesLog(string message)
{
if (string.IsNullOrWhiteSpace(message))
return;
message = "【" + DateTime.Now.ToString("HH:mm:ss") + "】" + message;
try
{
string path = "";
if (path == "")
{
path = "D:/LogFile/DAL_Log/";
}
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
string fileName = DateTime.Now.ToString("yyyyMMdd") + ".txt";
path += fileName;
if (File.Exists(path))
{
System.IO.FileStream fs = new System.IO.FileStream(path, System.IO.FileMode.Append);
System.IO.StreamWriter sw = new System.IO.StreamWriter(fs, System.Text.Encoding.Default);
try
{
sw.WriteLine(message);
sw.Close();
fs.Close();
}
catch
{
sw.Close();
fs.Close();
}
}
else
{
System.IO.FileStream fs = new System.IO.FileStream(path, System.IO.FileMode.Create);
System.IO.StreamWriter sw = new System.IO.StreamWriter(fs, System.Text.Encoding.Default);
try
{
sw.WriteLine(message);
sw.Close();
fs.Close();
}
catch
{
sw.Close();
fs.Close();
}
}
}
catch
{
}
}
public static void WriteException(string msg)
{
WriteBarcodesLog(msg, "exception");
}
public static void WriteBarcodesLog(string message, string fname)
{
if (string.IsNullOrWhiteSpace(message))
return;
message = "【" + DateTime.Now.ToString("HH:mm:ss") + "】" + message;
try
{
string path = "";
if (path == "")
{
path = "D:/LogFile/DAL_Log/";
}
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
string fileName = fname + DateTime.Now.ToString("yyyyMMdd") + ".txt";
path += fileName;
if (File.Exists(path))
{
System.IO.FileStream fs = new System.IO.FileStream(path, System.IO.FileMode.Append);
System.IO.StreamWriter sw = new System.IO.StreamWriter(fs, System.Text.Encoding.Default);
try
{
sw.WriteLine(message);
sw.Close();
fs.Close();
}
catch
{
sw.Close();
fs.Close();
}
}
else
{
System.IO.FileStream fs = new System.IO.FileStream(path, System.IO.FileMode.Create);
System.IO.StreamWriter sw = new System.IO.StreamWriter(fs, System.Text.Encoding.Default);
try
{
sw.WriteLine(message);
sw.Close();
fs.Close();
}
catch
{
sw.Close();
fs.Close();
}
}
}
catch
{
}
}
#endregion
public DataSet runSQLDataSet(string SqlStr)
{
string connstr = WX_Card.CardDAL.Properties.Settings.Default.lswxConnectionString;
SqlCommand cm = new SqlCommand();
cm.Connection = new SqlConnection(connstr);
cm.Connection.Open();
cm.CommandText = SqlStr;
cm.CommandType = CommandType.Text;
DataSet DS = null;
try
{
SqlDataAdapter da = new SqlDataAdapter(cm);
DS = new DataSet();
da.Fill(DS);
da.Dispose();
}
catch (Exception ex)
{
throw ex;
}
finally
{
cm.Connection.Close();
cm.Dispose();
}
return DS;
}
public int GetDataRow(string sql, params SqlParameter[] paras)
{
string connstr = WX_Card.CardDAL.Properties.Settings.Default.lswxConnectionString;
DataTable dt = null;
using (SqlConnection conn = new SqlConnection(connstr))
{
SqlCommand command = new SqlCommand(sql, conn);
command.Parameters.AddRange(paras);
SqlDataAdapter adapter = new SqlDataAdapter(command);
dt = new DataTable();
adapter.Fill(dt);
}
return dt.Rows.Count;
}
public DataTable runSQLDataTable(string SqlStr)
{
string connstr = Properties.Settings.Default.lswxConnectionString;
SqlCommand cm = new SqlCommand();
cm.Connection = new SqlConnection(connstr);
cm.Connection.Open();
cm.CommandText = SqlStr;
cm.CommandType = CommandType.Text;
cm.CommandTimeout = 120;
DataSet DS = null;
try
{
SqlDataAdapter da = new SqlDataAdapter(cm);
DS = new DataSet();
da.Fill(DS);
da.Dispose();
}
catch (Exception ex)
{
throw ex;
}
finally
{
cm.Connection.Close();
cm.Dispose();
}
if (DS != null && DS.Tables.Count > 0)
return DS.Tables[0];
else
return null;
}
public int runSQLNoQuery(string SqlStr)
{
int reint = 0;
string connstr = Properties.Settings.Default.lswxConnectionString;
SqlCommand cm = new SqlCommand();
cm.Connection = new SqlConnection(connstr);
cm.Connection.Open();
cm.CommandText = SqlStr;
cm.CommandType = CommandType.Text;
cm.CommandTimeout = 60;
try
{
reint = cm.ExecuteNonQuery();
}
catch (Exception ex)
{
WriteBarcodesLog(string.Format("执行sql时发生异常[{0}]:{1}", SqlStr, ex.Message), "runSQLNoQuery");
}
finally
{
cm.Connection.Close();
cm.Dispose();
}
return reint;
}
public int runSQLScalar(string SqlStr)
{
int reint = 0;
string connstr = Properties.Settings.Default.lswxConnectionString;
SqlCommand cm = new SqlCommand();
cm.Connection = new SqlConnection(connstr);
cm.Connection.Open();
cm.CommandText = SqlStr;
cm.CommandType = CommandType.Text;
try
{
reint = Convert.ToInt32(cm.ExecuteScalar());
}
catch (Exception ex)
{
WriteBarcodesLog(string.Format("执行sql时发生异常[{0}]:{1}", SqlStr, ex.Message));
}
finally
{
cm.Connection.Close();
cm.Dispose();
}
return reint;
}
public static DataSet runProcedure(string ProcedureName, List<sp_param> sps)
{
string connstr = WX_Card.CardDAL.Properties.Settings.Default.lswxConnectionString;
SqlCommand cm = new SqlCommand();
cm.Connection = new SqlConnection(connstr);
cm.Connection.Open();
cm.CommandText = ProcedureName;
if (sps != null && sps.Count > 0)
{
foreach (var sp in sps)
{
SqlParameter sparam = new SqlParameter(sp.param_name, sp.param_value);
cm.Parameters.Add(sparam);
}
}
cm.CommandType = CommandType.StoredProcedure;
DataSet DS = null;
try
{
SqlDataAdapter da = new SqlDataAdapter(cm);
DS = new DataSet();
da.Fill(DS);
da.Dispose();
}
catch (Exception ex)
{
throw ex;
}
finally
{
cm.Connection.Close();
cm.Dispose();
}
return DS;
}
public static void runProcedure(string ProcedureName)
{
string connstr = WX_Card.CardDAL.Properties.Settings.Default.lswxConnectionString;
SqlCommand cm = new SqlCommand();
cm.Connection = new SqlConnection(connstr);
cm.Connection.Open();
cm.CommandText = ProcedureName;
cm.CommandType = CommandType.StoredProcedure;
DataSet DS = null;
try
{
SqlDataAdapter da = new SqlDataAdapter(cm);
DS = new DataSet();
da.Fill(DS);
da.Dispose();
}
catch (Exception ex)
{
throw ex;
}
finally
{
cm.Connection.Close();
cm.Dispose();
}
}
public static void InsertTable(DataTable dt, string tableName)
{
string connstr = Properties.Settings.Default.lswxConnectionString;
using (SqlConnection conn = new SqlConnection(connstr))
{
Stopwatch sw = new Stopwatch();
SqlBulkCopy bulkCopy = new SqlBulkCopy(conn);
bulkCopy.DestinationTableName = tableName;
bulkCopy.BatchSize = dt.Rows.Count;
conn.Open();
sw.Start();
if (dt != null && dt.Rows.Count > 120)
{
bulkCopy.WriteToServer(dt);
sw.Stop();
}
conn.Close();
}
}
public static DateTime gettime(long timestamp)
{
if (timestamp.Equals(0))
{
return DateTime.Now;
}
var start = new DateTime(1970, 1, 1, 8, 0, 0, DateTimeKind.Utc);
return start.AddSeconds(timestamp);
}
public static long GetTime()
{
DateTime DateStart = new DateTime(1970, 1, 1, 8, 0, 0);
return Convert.ToInt64((DateTime.Now - DateStart).TotalSeconds);
}
public static TOut Trans<TIn, TOut>(TIn tIn)
{
TOut tOut = Activator.CreateInstance<TOut>();
foreach (var outfield in tOut.GetType().GetFields())
{
foreach (var infield in tIn.GetType().GetFields())
{
if (outfield.Name.Equals(infield.Name))
{
try
{
outfield.SetValue(tOut, infield.GetValue(tIn));
}
catch
{
}
break;
}
}
}
foreach (var outProperty in tOut.GetType().GetProperties())
{
foreach (var inProperty in tIn.GetType().GetProperties())
{
if (outProperty.Name.Equals(inProperty.Name))
{
try
{
outProperty.SetValue(tOut, inProperty.GetValue(tIn, null), null);
}
catch
{
}
break;
}
}
}
return tOut;
}
public static void InsertBatchData(DataTable dt, string tableName)
{
string connstr = WX_Card.CardDAL.Properties.Settings.Default.lswxConnectionString;
using (SqlConnection conn = new SqlConnection(connstr))
{
Stopwatch sw = new Stopwatch();
SqlBulkCopy bulkCopy = new SqlBulkCopy(conn);
bulkCopy.DestinationTableName = tableName;
bulkCopy.BatchSize = dt.Rows.Count;
conn.Open();
sw.Start();
if (dt != null && dt.Rows.Count > 120)
{
bulkCopy.WriteToServer(dt);
sw.Stop();
runProcedure("proc_CompareInventory");
}
conn.Close();
}
}
}
public class sp_param
{
public string param_name { get; set; }
public string param_value { get; set; }
}
}