/// <summary>
/// 根据指定Sql返回一个DataReader
/// </summary>
/// <param name="sql">要执行的sql语句</param>
/// <param name="sp">参数数组</param>
/// <returns>IDataReader</returns>
public virtual async Task <IDataReader> ExecReaderAsync(string sql, params IDataParameter[] sp)
{
var conn = StartConn();
DbCommand sc = DBComm.CreateCommand(conn);
sc.CommandText = sql;
// we use a try/catch here because if the method throws an exception we want to
// close the connection throw code, because no datareader will exist, hence the
// commandBehaviour.CloseConnection will not work
try
{
PrepareCommand(sc, sp);
IDataReader rdr = await sc.ExecuteReaderAsync(
InTrans?CommandBehavior.Default :
CommandBehavior.CloseConnection);
sc.Parameters.Clear();
return(rdr);
}
catch
{
EndConn(conn);
throw;
}
}
/// <summary>
/// 得到单一对象
/// </summary>
/// <param name="sql">要执行的sql语句</param>
/// <param name="sp">参数数组</param>
/// <returns>返回的单个值</returns>
public virtual object ExecGetObject(String sql, params IDataParameter[] sp)
{
object o = 0;
DbConnection conn = StartConn();
DbCommand sc = DBComm.CreateCommand(conn);
sc.CommandText = sql;
PrepareCommand(sc, sp);
o = sc.ExecuteScalar();
sc.Parameters.Clear();
EndConn(conn);
return(o);
}
/// <summary>
/// 用指定连接串执行非查询语句
/// </summary>
/// <param name="sql">非查询语句</param>
/// <param name="sp">可选参数数组</param>
/// <returns>影响的行数</returns>
public virtual int ExecNonQuery(string sql, params IDataParameter[] sp)
{
DbConnection conn = StartConn();
DbCommand sc = DBComm.CreateCommand(conn);
sc.CommandText = sql;
PrepareCommand(sc, sp);
int r = sc.ExecuteNonQuery();
sc.Parameters.Clear();
EndConn(conn);
return(r);
}
/// <summary>
/// 执行存储过程修改数据
/// </summary>
/// <param name="procname">存储过程名称</param>
/// <param name="sp">可选参数数组</param>
/// <returns>影响的行数</returns>
public virtual async Task <int> RunProcedureAsync(string procname, params IDataParameter[] sp)
{
var conn = StartConn();
DbCommand sc = DBComm.CreateCommand(conn);
sc.CommandText = procname;
sc.CommandType = CommandType.StoredProcedure;
PrepareCommand(sc, sp);
int r = await sc.ExecuteNonQueryAsync();
sc.Parameters.Clear();
EndConn(conn);
return(r);
}
/// <summary>
/// 得到单一对象
/// </summary>
/// <param name="sql">要执行的sql语句</param>
/// <param name="sp">参数数组</param>
/// <returns>返回的单个值</returns>
public virtual async Task <object> ExecGetObjectAsync(String sql, params IDataParameter[] sp)
{
object o = 0;
var conn = StartConn();
DbCommand sc = DBComm.CreateCommand(conn);
sc.CommandText = sql;
PrepareCommand(sc, sp);
o = await sc.ExecuteScalarAsync();
sc.Parameters.Clear();
EndConn(conn);
return(o);
}
/// <summary>
/// 执行SQL查询语句，返回DataSet
/// </summary>
/// <param name="sql">要执行的Sql语句</param>
/// <param name="sp">参数数组</param>
/// <returns>DataSet</returns>
public virtual DataSet ExecDataSet(string sql, params IDataParameter[] sp)
{
DbConnection conn = StartConn();
DbCommand sc = DBComm.CreateCommand(conn);
sc.CommandText = sql;
PrepareCommand(sc, sp);
DbDataAdapter da = DBComm.CreateDataAdapter(sc);
DataSet ds = new DataSet();
da.Fill(ds);
sc.Parameters.Clear();
EndConn(conn);
return(ds);
}
/// <summary>
/// 用指定连接串执行非查询语句异步版本
/// </summary>
/// <param name="sql">非查询语句</param>
/// <param name="sp">可选参数数组</param>
/// <returns>影响的行数</returns>
public virtual async Task <int> ExecNonQueryAsync(string sql, params IDataParameter[] sp)
{
var conn = StartConn();
DbCommand sc = DBComm.CreateCommand(conn);
sc.CommandText = sql;
PrepareCommand(sc, sp);
if (!sql.Contains(' '))
{
sc.CommandType = CommandType.StoredProcedure;
}
int r = await sc.ExecuteNonQueryAsync();
sc.Parameters.Clear();
EndConn(conn);
return(r);
}
/// <summary>
/// 导入DataTable到数据库
/// </summary>
/// <param name="dt">内存中的数据表</param>
/// <param name="tableName">表名,如果为空，则以传入的DataTable的TableName作为表名</param>
/// <param name="buckCopy">是否使用批量导入, 对应的DBComm必须实现ISupportBuckCopy的接口</param>
/// <param name="notifyAfter">发生提示时导入的行数</param>
/// <param name="onRowsCopied">发生提示时执行的委托</param>
/// <returns>成功导入的行数</returns>
public virtual int Import(DataTable dt, string tableName = null, bool buckCopy = true, int notifyAfter = 10, Action <int> onRowsCopied = null)
{
if (dt == null)
{
throw new ArgumentNullException("dt");
}
if (onRowsCopied == null)
{
onRowsCopied = r => { }
}
;
if (tableName.IsEmpty())
{
tableName = dt.TableName;
}
if (notifyAfter <= 0)
{
throw new ArgumentException("notifyAfter<=0");
}
int rowsCopied = 0;
int rowCount = dt.Rows.Count;
//如果目标表不存在则创建
if (!DBComm.TableExists(tableName))
{
DBComm.CreateTable(dt, tableName);
}
//用bcp导入数据
if (buckCopy && DBComm is ISupportBuckCopy)
{
return(((ISupportBuckCopy)DBComm).BuckCopy(dt, tableName, notifyAfter, onRowsCopied));
}
else //用Sql Insert 导入数据
{
BeginTrans();
string sqlFields = "";
string sqlValues = "";
foreach (DataColumn f in dt.Columns)
{
if (!f.AutoIncrement)
{
sqlFields += String.Format(",{1}{0}{2}", f.ColumnName, DBComm.FieldPrefix, DBComm.FieldSuffix);
sqlValues += String.Format(",@{0}", f.ColumnName);
}
}
sqlFields = sqlFields.Substring(1);
sqlValues = sqlValues.Substring(1);
for (int i = 0; i < dt.Rows.Count; i++)
{
int j = 0;
IDataParameter[] sp = new IDataParameter[dt.Columns.Count];
foreach (DataColumn f in dt.Columns)
{
if (!f.AutoIncrement)
{
IDataParameter p = CreateParameter(f.ColumnName, CommOp.TestNull(dt.Rows[i][j]));
sp[j] = p;
}
j++;
}
string sql = String.Format("INSERT INTO {3}{0}{4}({1}) VALUES({2})", tableName, sqlFields, sqlValues, DBComm.FieldPrefix, DBComm.FieldSuffix);
try
{
ExecNonQuery(sql, sp);
}
catch (Exception ex)
{
RollbackTrans();
throw new TableImportException(ex, i + 1, 0);
}
if (i % notifyAfter == 0 || i == rowCount)
{
onRowsCopied(i);
}
}
EndTrans();
}
rowsCopied = dt.Rows.Count;
return(rowsCopied);
}
/// <summary>
/// 将value中的值赋给对象的属性
/// </summary>
/// <param name="obj">对象</param>
/// <param name="pi">对象的属性信息</param>
/// <param name="value">值</param>
void SetValue(object obj, PropertyInfo pi, object value)
{
pi.SetValue(obj, CommOp.HackType(value, pi.PropertyType), null);
}
/// <summary>
/// 压缩数据库
/// </summary>
public virtual void ShrinkDB()
{
DBComm.ShrinkDB();
}
/// <summary>
/// 获取数据库大小
/// </summary>
/// <returns>数据库大小</returns>
public virtual double GetDBSize()
{
return(DBComm.GetDBSize());
}
/// <summary>
/// 生成参数
/// </summary>
/// <param name="parameterName">参数名称</param>
/// <param name="value">参数值</param>
/// <returns>参数对象</returns>
public virtual IDataParameter CreateParameter(string parameterName, object value)
{
return(DBComm.CreateParameter(parameterName, CommOp.TestNull(value)));
}
public Trans(string connectionString)
{
conn = DBComm.CreateConnection(connectionString);
conn.Open();
dbTrans = conn.BeginTransaction();
}
public Trans()
{
conn = DBComm.CreateConnection();
conn.Open();
dbTrans = conn.BeginTransaction();
}
public void ProcessRequest(HttpContext context)
{
try
{
//System.Diagnostics.Debugger.Launch();
int test = Convert.ToInt32(ConfigurationManager.AppSettings["ModuleSize"]);
var data = new DBComm();
JsonSerializer serializer = new JsonSerializer();
string requestName = context.Request.Url.LocalPath;
switch (requestName)
{
case "/Barcode/newSerial":
string tipo = context.Request.QueryString["Tipo"];
string idUser = context.Request.QueryString["IdUser"];
string idClasse = context.Request.QueryString["IdClasse"];
string label = context.Request.QueryString["Label"];
data.WriteOnDb(tipo, Convert.ToInt32(idUser), Convert.ToInt32(idClasse), label);
var queryResult = data.ReadFromDb(tipo, Convert.ToInt32(idUser), Convert.ToInt32(idClasse), label);
string composedSerial = ComposeSerial(queryResult, tipo);
var jsoncontent = new
{
StatusCode = context.Response.StatusCode,
StatusMessage = "OK",
Serial = composedSerial
};
context.Response.StatusCode = 200;
context.Response.ContentType = "application/json";
context.Response.Write(JsonConvert.SerializeObject(jsoncontent));
break;
case "/Barcode/GetDM":
string serial = context.Request.QueryString["Serial"];
string encoding = context.Request.QueryString["Encoding"];
var row = data.ReadFromDb(Convert.ToInt32(serial));
byte[] labelBytes;
if (encoding == "ASCII")
{
labelBytes = Encoding.ASCII.GetBytes(row.Item1.PadRight(150));
}
else
{
labelBytes = Encoding.BigEndianUnicode.GetBytes(row.Item1.PadRight(150));
}
byte[] corsoBytes = BitConverter.GetBytes(IPAddress.HostToNetworkOrder(row.Item2));
byte[] userBytes = BitConverter.GetBytes(IPAddress.HostToNetworkOrder(row.Item3));
byte[] timeBytes = BitConverter.GetBytes(IPAddress.HostToNetworkOrder(row.Item4));
byte[] serialBytes = BitConverter.GetBytes(IPAddress.HostToNetworkOrder(row.Item5));
byte[] typeBytes = Encoding.BigEndianUnicode.GetBytes(row.Item6.PadRight(150));
byte[] content = MergeArrays(labelBytes, corsoBytes, userBytes, timeBytes, serialBytes, typeBytes);
var dmImage = FinalDm(content);
context.Response.ContentType = "image/png";
dmImage.Save(context.Response.OutputStream, ImageFormat.Png);
context.Response.Write(content);
break;
case "/Barcode/ReportData":
string serialRd = context.Request.QueryString["Serial"];
var rowRd = data.ReadFromDb(Convert.ToInt32(serialRd));
var jsonContentRd = new
{
label = rowRd.Item1,
corso = rowRd.Item2,
user = rowRd.Item3,
time = rowRd.Item4,
serial = rowRd.Item5,
type = rowRd.Item6
};
context.Response.ContentType = "application/json";
context.Response.Write(JsonConvert.SerializeObject(jsonContentRd));
break;
default:
if (requestName != "/Barcode/GetDM" || requestName != "/Barcode/GetDM" || requestName != "/Barcode/ReportData")
{
context.Response.StatusCode = 500;
context.Response.Write("Errore, metodi richiamabili: <br>");
context.Response.Write("/Barcode/newSerial?Tipo=stringa(nome del tipo)&IdUser=intero&IdClasse=intero&Label=stringa <br>");
context.Response.Write("/Barcode/GetDM?Serial=intero&Encoding=stringa(es. UTF8) <br>");
context.Response.Write("/Barcode/ReportData?Serial=intero");
}
break;
}
}
catch (Exception)
{
context.Response.StatusCode = 500;
context.Response.Write("Qualcose è andato storto, Codice: " + context.Response.StatusCode);
}
}
public string MainFlow()
{
string instkif = HttpUtility.UrlDecode(StockInfo);
//修改库存中tickect
JsonStockInfo Stockinfo = JsonHelper.DeserializeJsonToObject <JsonStockInfo>(instkif);
if (Stockinfo == null)
{
return("Failed! Message format is not correct!");
}
object obj = DBComm.ExecuteScalar("SELECT TOP 1 [Ticket] FROM [MergeData].[dbo].[YJS_Ticket]");
if (obj == null)
{
//进行登录
DataTable dtser = DBComm.ExecuteDataTable("SELECT TOP 1 [s],[d] FROM [MergeData].[dbo].[YSJ_SerNum]");
string s = dtser.Rows[0]["s"].ToString();
string d = dtser.Rows[0]["d"].ToString();
JsonLoginBack LoginBack = Login(s, d);
if (!IsLogin)
{
if (LoginBack != null)
{
Ticket = LoginBack.header.ticket[0];
IsLogin = true;
}
else
{
return("");
}
}
else
{
return("");
}
}
else
{
Ticket = obj.ToString();
}
Stockinfo.header.serailNumber = Ticket;
string Inid = "'";
for (int ctbdy = 0; ctbdy < Stockinfo.body.Count; ctbdy++)
{
Inid = Inid + Stockinfo.body[ctbdy].logisticsCode + "','";
//Stockinfo.body[ctbdy].stockRemark = "共享库存";
}
//实体序列化和反序列化
string jsonData = "jsonData=" + JsonHelper.SerializeObject(Stockinfo);
string url = "http://wl.yao1.cn:8899/scm/do/dispatcher/erpStock/submitErpStock";
string LoginRst = JsonHelper.HttpGet(url, jsonData);
JsonLoginBack StockinfoBack = JsonHelper.DeserializeJsonToObject <JsonLoginBack>(LoginRst);
if (StockinfoBack != null)
{
if (StockinfoBack.header.ticket == null)
{
DBComm.ExecuteNonQuery_Simple("DELETE FROM [MergeData].[dbo].[YJS_Ticket] WHERE TICKET='" + Ticket + "'");
return("Failed! ID=" + Inid);
}
else
{
string NewTicket = StockinfoBack.header.ticket[0] == null ? "" : StockinfoBack.header.ticket[0];
DBComm.ExecuteNonQuery_Simple("insert into [MergeData].[dbo].[YJS_Ticket] (Ticket,CreateDate)values('" +
NewTicket + "','" + DateTime.Now.ToString("yyyy-MM-dd") + "')");
DBComm.ExecuteNonQuery_Simple("DELETE FROM [MergeData].[dbo].[YJS_Ticket] WHERE TICKET='" + Ticket + "'");
return("Success! ID=" + Inid);
}
}
else
{
return("Failed! ID=" + Inid);
}
}