/// <summary>
/// 获取表的所有字段名及字段类型
/// </summary>
public List <Dictionary <string, string> > GetAllColumns(string tableName)
{
SQLiteHelper sqliteHelper = new SQLiteHelper();
DataTable dt = sqliteHelper.Query("PRAGMA table_info('" + tableName + "')");
List <Dictionary <string, string> > result = new List <Dictionary <string, string> >();
foreach (DataRow dr in dt.Rows)
{
Dictionary <string, string> dic = new Dictionary <string, string>();
dic.Add("columns_name", dr["name"].ToString());
dic.Add("notnull", dr["notnull"].ToString() == "1" ? "1" : "0");
dic.Add("comments", "");
dic.Add("data_type", "string");
dic.Add("data_scale", "");
dic.Add("data_precision", "");
if (dr["pk"].ToString() == "1")
{
dic.Add("constraint_type", "P");
}
else
{
dic.Add("constraint_type", "");
}
result.Add(dic);
}
return(result);
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select TID,BarCode,MaterialCode,MaterialName,ScannerID ");
strSql.Append(" FROM stockinfo ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(SQLiteHelper.Query(strSql.ToString()));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * ");
strSql.Append(" FROM frontaxleresult ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(SQLiteHelper.Query(strSql.ToString()));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select TID,UserID,UserName,UserSex,UserPwd,PhoneNum,Department,RoleName,CreateTime ");
strSql.Append(" FROM userinfo ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(SQLiteHelper.Query(strSql.ToString()));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select tid,barcode,productcode,userid,stationid,pedalassycode,accelpedalcode,cluthhandlebatchno,boltbatchno,nutbatchno,torque1,torque2,torque3,completed,createtime,completetime ");
strSql.Append(" FROM pedalresult ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(SQLiteHelper.Query(strSql.ToString()));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select TID,ProductType,ProductCode,ProductName,MaterialCode,MaterialName,MaterialNum,FeatureIndex,FeatureCode,BatchNum,ScannerID,TraceType,`Desc` ");
strSql.Append(" FROM productbominfo ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(SQLiteHelper.Query(strSql.ToString()));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select TID,DeviceType,DeviceID,DeviceName,DeviceIP,ProductType,DeviceState,StationID,`Desc` ");
strSql.Append(" FROM deviceinfo ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(SQLiteHelper.Query(strSql.ToString()));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select tid,barcode,productcode,userid,stationid,radiatorcode,condensercode,fanassemblycode,intercoolercode,torque1,angle1,torque2,angle2,torque3,angle3,torque4,angle4,torque5,angle5,torque6,angle6,completed,createtime,completetime ");
strSql.Append(" FROM radiatorresult ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(SQLiteHelper.Query(strSql.ToString()));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select TID,ProductType,ProductCode,ProductName,FeatureIndex,FeatureCode,BarCodeCount,HaveSub,`Desc` ");
strSql.Append(" FROM ProductInfo ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(SQLiteHelper.Query(strSql.ToString()));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select productcode,sortnum ");
strSql.Append(" FROM productsort ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(SQLiteHelper.Query(strSql.ToString()));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select TID,MaterialCode,MaterialName,TableName,FieldName,`Desc` ");
strSql.Append(" FROM materialfield ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(SQLiteHelper.Query(strSql.ToString()));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select TID,BarCode,ProductType,MaterialCode,MaterialName,BatchNo,BatchNum,StockNum,Supplier,CreateTime");
strSql.Append(" FROM BatchNo");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(SQLiteHelper.Query(strSql.ToString()));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select tid,barcode,productcode,userid,stationid,brakepumpcode,gasketbatchno,hexagonalnutbatchno,pressuresensorbatchno,silencerbatchno,connectingpipe,boosterbrakepumpbracket,completed,createtime,completetime");
strSql.Append(" from brakepumpresult");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(SQLiteHelper.Query(strSql.ToString()));
}
/// <summary>
/// 获取数据库名
/// </summary>
public List <Dictionary <string, string> > GetAllTables()
{
SQLiteHelper sqliteHelper = new SQLiteHelper();
DataTable dt = sqliteHelper.Query("select tbl_name from sqlite_master where type='table'");
List <Dictionary <string, string> > result = new List <Dictionary <string, string> >();
foreach (DataRow dr in dt.Rows)
{
Dictionary <string, string> dic = new Dictionary <string, string>();
dic.Add("table_name", dr["tbl_name"].ToString());
dic.Add("comments", "");
result.Add(dic);
}
return(result);
}
///<summary>
/// 得到一个对象实体
/// </summary>
public MDL.BrakepumpResultMDL GetModel(string where)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select tid,barcode,productcode,userid,stationid,brakepumpcode,gasketbatchno,hexagonalnutbatchno,pressuresensorbatchno,silencerbatchno,connectingpipe,boosterbrakepumpbracket,completed,createtime,completetime from brakepumpresult ");
if (!string.IsNullOrEmpty(where))
{
strSql.Append(" where " + where);
}
MDL.BrakepumpResultMDL model = new MDL.BrakepumpResultMDL();
DataSet ds = SQLiteHelper.Query(strSql.ToString());
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public MDL.PedalResultMDL GetModel(string where)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select tid,barcode,productcode,userid,stationid,pedalassycode,accelpedalcode,cluthhandlebatchno,boltbatchno,nutbatchno,torque1,torque2,torque3,completed,createtime,completetime from pedalresult ");
if (!string.IsNullOrEmpty(where))
{
strSql.Append(" where " + where);
}
MDL.PedalResultMDL model = new MDL.PedalResultMDL();
DataSet ds = SQLiteHelper.Query(strSql.ToString());
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public MDL.MaterialFieldMDL GetModel(long TID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select TID,MaterialCode,MaterialName,TableName,FieldName,`Desc` from materialfield ");
strSql.Append(" where TID=@TID");
SQLiteParameter[] parameters =
{
new SQLiteParameter("@TID", TID)
};
MDL.MaterialFieldMDL model = new MDL.MaterialFieldMDL();
DataSet ds = SQLiteHelper.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public MDL.PedalResultMDL GetModel(long tid)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select tid,barcode,productcode,userid,stationid,pedalassycode,accelpedalcode,cluthhandlebatchno,boltbatchno,nutbatchno,torque1,torque2,torque3,completed,createtime,completetime from pedalresult ");
strSql.Append(" where tid=@tid");
SQLiteParameter[] parameters =
{
new SQLiteParameter("@tid", tid)
};
MDL.PedalResultMDL model = new MDL.PedalResultMDL();
DataSet ds = SQLiteHelper.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public MDL.DeviceInfoMDL GetModel(string str)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select TID,DeviceType,DeviceID,DeviceName,DeviceIP,ProductType,DeviceState,StationID,`Desc` from deviceinfo ");
if (!string.IsNullOrEmpty(str))
{
strSql.Append("where " + str);
}
MDL.DeviceInfoMDL model = new MDL.DeviceInfoMDL();
DataSet ds = SQLiteHelper.Query(strSql.ToString());
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public MDL.ProductInfoMDL GetModel(string where)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select TID,ProductType,ProductCode,ProductName,FeatureIndex,FeatureCode,BarCodeCount,HaveSub,`Desc` from ProductInfo ");
if (!string.IsNullOrEmpty(where))
{
strSql.Append(" where " + where);
}
MDL.ProductInfoMDL model = new MDL.ProductInfoMDL();
DataSet ds = SQLiteHelper.Query(strSql.ToString());
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public MDL.MaterialFieldMDL GetModel(string where)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select TID,MaterialCode,MaterialName,TableName,FieldName,`Desc` from materialfield ");
if (!string.IsNullOrEmpty(where))
{
strSql.Append(" where " + where);
}
strSql.Append(" LIMIT 1");
MDL.MaterialFieldMDL model = new MDL.MaterialFieldMDL();
DataSet ds = SQLiteHelper.Query(strSql.ToString());
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public MDL.StockInfoMDL GetModel(string where)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select TID,BarCode,MaterialCode,MaterialName,ScannerID from stockinfo ");
if (!string.IsNullOrEmpty(where))
{
strSql.Append(" where " + where);
}
MDL.StockInfoMDL model = new MDL.StockInfoMDL();
DataSet ds = SQLiteHelper.Query(strSql.ToString());
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public BatchNoMDL GetModel(string where)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select TID,BarCode,ProductType,MaterialCode,MaterialName,BatchNo,BatchNum,StockNum,Supplier,CreateTime from BatchNo ");
if (!string.IsNullOrEmpty(where))
{
strSql.Append(" where " + where);
}
BatchNoMDL model = new BatchNoMDL();
DataSet ds = SQLiteHelper.Query(strSql.ToString());
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public MDL.RadiatorResultMDL GetModel(string where)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select tid,barcode,productcode,userid,stationid,radiatorcode,condensercode,fanassemblycode,intercoolercode,torque1,angle1,torque2,angle2,torque3,angle3,torque4,angle4,torque5,angle5,torque6,angle6,completed,createtime,completetime from radiatorresult ");
if (!string.IsNullOrEmpty(where))
{
strSql.Append(" where " + where);
}
strSql.Append(" limit 1");
MDL.RadiatorResultMDL model = new MDL.RadiatorResultMDL();
DataSet ds = SQLiteHelper.Query(strSql.ToString());
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public MDL.ProductInfoMDL GetModel(long TID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select TID,ProductType,ProductCode,ProductName,FeatureIndex,FeatureCode,BarCodeCount,HaveSub,`Desc` from ProductInfo ");
strSql.Append(" where TID=@TID");
SQLiteParameter[] parameters =
{
new SQLiteParameter("@TID", TID)
};
MDL.ProductInfoMDL model = new MDL.ProductInfoMDL();
DataSet ds = SQLiteHelper.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public FrontAxleResultMDL GetModel(long tid)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select tid, leftfrontcaliperbatchno, rightfrontcaliperbatchno, caliperboltbatchno, lowerballpinbatchno, completed, createtime, completetime, repairstate, barcode, productcode, userid, stationid, leftsteeringbatchno, rightsteeringbatchno, bearingbatchno, frontbrakediscbatchno ");
strSql.Append(" from frontaxleresult ");
strSql.Append(" where tid=@tid");
SQLiteParameter[] parameters =
{
new SQLiteParameter("@tid", tid)
};
DataSet ds = SQLiteHelper.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 分页获取数据列表
/// </summary>
public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT * FROM ( ");
strSql.Append(" SELECT ROW_NUMBER() OVER (");
if (!string.IsNullOrEmpty(orderby.Trim()))
{
strSql.Append("order by T." + orderby);
}
else
{
strSql.Append("order by T.tid desc");
}
strSql.Append(")AS Row, T.* from radiatorresult T ");
if (!string.IsNullOrEmpty(strWhere.Trim()))
{
strSql.Append(" WHERE " + strWhere);
}
strSql.Append(" ) TT");
strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
return(SQLiteHelper.Query(strSql.ToString()));
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public MDL.UserInfoMDL GetModel(string UserID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select TID,UserID,UserName,UserSex,UserPwd,PhoneNum,Department,RoleName,CreateTime from userinfo ");
strSql.Append(" where UserID=@UserID");
SQLiteParameter[] parameters =
{
new SQLiteParameter("@UserID", UserID)
};
MDL.UserInfoMDL model = new MDL.UserInfoMDL();
DataSet ds = SQLiteHelper.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public MDL.RadiatorResultMDL GetModel(int tid)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select tid,barcode,productcode,userid,stationid,radiatorcode,condensercode,fanassemblycode,intercoolercode,torque1,angle1,torque2,angle2,torque3,angle3,torque4,angle4,torque5,angle5,torque6,angle6,completed,createtime,completetime from radiatorresult ");
strSql.Append(" where tid=@tid");
SQLiteParameter[] parameters =
{
new SQLiteParameter("@tid", tid)
};
MDL.RadiatorResultMDL model = new MDL.RadiatorResultMDL();
DataSet ds = SQLiteHelper.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public MDL.StockInfoMDL GetModel(int TID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select TID,BarCode,MaterialCode,MaterialName,ScannerID from stockinfo ");
strSql.Append(" where TID=@TID");
SQLiteParameter[] parameters =
{
new SQLiteParameter("@TID", TID)
};
MDL.StockInfoMDL model = new MDL.StockInfoMDL();
DataSet ds = SQLiteHelper.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}