public static void DeleteTuopan(SendDetail entity, SendRecord parent)
{
string[] sql = new string[1];
sql[0] = "delete from senddetail where tph='" +
entity.Tph + "'";
SqliteDbFactory.GetSqliteDbOperator().BatchExecute(sql);
}
public void SaveAsnMain(string RollBack)
{
if (RollBack == string.Empty)
{
string sqlForSelect = string.Format(@"select * from receiverecord where pnno='{0}'", this.pnno);
DataTable dt = SqliteDbFactory.GetSqliteDbOperator().SelectFromSql(sqlForSelect);
if (dt.Rows.Count == 0)
{
string sqlInsert = string.Format(@"insert into receiverecord (receiveno,factory,factoryid,storage,storageid,carno,receivetype,qufen,pnno,
tno,hold,sl,scaner,scantime,status) values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}',{11},'{12}','{13}',{14})",
this.receiveno, this.factory, this.factoryid, this.storage, this.storageid, this.carno, this.receivetype, this.qufen, this.pnno, this.tno, this.hold,
this.sl, this.scanner, this.scantime, this.status);
SqliteDbFactory.GetSqliteDbOperator().ExecuteNonQuery(sqlInsert);
}
else
{
string sqlUpdate = string.Format(@"update receiverecord set sl=sl+" + this.sl + " where pnno='{0}'", this.pnno);
SqliteDbFactory.GetSqliteDbOperator().ExecuteNonQuery(sqlUpdate);
}
}
else
{
string sqlRollBack = string.Format(@"update receiverecord set sl+sl-" + this.sl + " where pnno='{0}'", this.pnno);
SqliteDbFactory.GetSqliteDbOperator().ExecuteNonQuery(sqlRollBack);
}
}
public static bool IsExists(string kuweicode)
{
string sql = "select * from kuweiinfo where kuweicode='" + kuweicode + "'";
DataTable dt = SqliteDbFactory.GetSqliteDbOperator().SelectFromSql(sql);
return(dt != null && dt.Rows.Count == 1);
}
public static void Delete(ZuTuo entity)
{
string sql = string.Empty;
sql = "delete from zutuo where sn='" + entity.Sn + "'";
SqliteDbFactory.GetSqliteDbOperator().ExecuteNonQuery(sql);
}
public void MigrateToLatest()
{
var announcer = new TextWriterAnnouncer(s => SharpStarLogger.DefaultLogger.Debug(s));
var assembly = Assembly.GetExecutingAssembly();
var migrationContext = new RunnerContext(announcer)
{
Namespace = "SharpStar.Database.Migrations"
};
var options = new MigrationOptions {
PreviewOnly = false, Timeout = 60
};
ReflectionBasedDbFactory factory;
if (MonoHelper.IsRunningOnMono())
{
factory = new MonoSQLiteDbFactory();
}
else
{
factory = new SqliteDbFactory();
}
var connection = factory.CreateConnection(_config.GetProperty(NHibernate.Cfg.Environment.ConnectionString));
var processor = new SqliteProcessor(connection, new SqliteGenerator(), announcer, options, factory);
var runner = new MigrationRunner(assembly, migrationContext, processor);
runner.MigrateUp(true);
}
public static void Delete(AnTuoYiKu entity)
{
string sql = string.Empty;
sql = "delete from antuoyiku where tph='" + entity.Tph + "'";
SqliteDbFactory.GetSqliteDbOperator().ExecuteNonQuery(sql);
}
private void btn_OK_Click(object sender, EventArgs e)
{
if (cb_UpdateUserData.Checked)
{
if (!UpdateUserData())
{
return;
}
}
string sql = string.Format("select rightstr from myUsers where name='{0}' and pwd = '{1}'", txt_User.Text.Trim(), txt_Pwd.Text.Trim());
DataTable dt = SqliteDbFactory.GetSqliteDbOperator().SelectFromSql(sql);
if (dt.Rows.Count == 0)
{
MessageBox.Show("登陆失败，请检查用户名和密码！");
return;
}
string[] functions = dt.Rows[0]["rightstr"].ToString().Split(",".ToCharArray());
Program.UserID = this.txt_User.Text;
Function_List fl = new Function_List(functions);
fl.Show();
fl.Closed += new EventHandler(fl_Closed);
this.Hide();
}
private bool UpdateUserData()
{
DataSet ds = new DB().GetUserAndFunction();
if (ds == null || ds.Tables.Count < 2)
{
MessageBox.Show("获取数据失败，请检查网络！");
return(false);
}
ds.Tables["User"].Columns.Add("rightstr", typeof(string));
foreach (DataRow dr in ds.Tables["Function"].Rows)
{
DataRow[] drs = ds.Tables["User"].Select(string.Format("USER_CODE = '{0}'", dr["USER_CODE"]));
if (drs.Length == 0)
{
continue;
}
drs[0]["rightstr"] = drs[0]["rightstr"] is DBNull ? dr["FUNCTION_CODE"].ToString() : drs[0]["rightstr"].ToString() + "," + dr["FUNCTION_CODE"].ToString();
}
StringBuilder sql = new StringBuilder();
sql.Append("delete from myUsers;");
sql.Append("insert into myUsers(name,pwd,rightstr) ");
foreach (DataRow dr in ds.Tables["User"].Rows)
{
sql.AppendFormat(System.Globalization.CultureInfo.CurrentCulture, "select '{0}','{1}','{2}' union all ", dr["USER_CODE"], dr["USER_PWD"], dr["rightstr"]);
}
sql.Length = sql.Length - "union all ".Length;
SqliteDbFactory.GetSqliteDbOperator().BatchExecute(sql.ToString().Split(";".ToCharArray()));
return(true);
}
public static DataTable GetUserData(string uid)
{
string sql = "select so as 单号,pnno as 产品类别,carno as 车牌号"
+ " from sendrecord where scaner='" + uid + "'";
return(SqliteDbFactory.GetSqliteDbOperator().SelectFromSql(sql));
}
public static void Delete(ZtPinTuo entity)
{
string sql = string.Empty;
sql = "delete from ztpintuo where tph='" +
entity.Tph + "'";
SqliteDbFactory.GetSqliteDbOperator().ExecuteNonQuery(sql);
}
public static bool CheckExists(ZuTuo entity)
{
string sql = string.Empty;
sql = "select * from zutuo where sn='" + entity.Sn + "'";
DataTable dt = SqliteDbFactory.GetSqliteDbOperator().SelectFromSql(sql);
return(dt != null && dt.Rows.Count == 1);
}
public static void Delete(JieTuo entity)
{
string sql = string.Empty;
sql = "delete from jietuo where tph='" +
entity.Tph + "' and sn='" + entity.Sn + "' and iswhole=" +
entity.IsWhole + " and scaner='" + entity.Scaner + "'";
SqliteDbFactory.GetSqliteDbOperator().ExecuteNonQuery(sql);
}
public static bool CheckExists(AnHuoYiKu entity)
{
string sql = string.Empty;
sql = "select * from anhuoyiku where cp='" + entity.Cp + "'";
DataTable dt = SqliteDbFactory.GetSqliteDbOperator().SelectFromSql(sql);
return(dt != null && dt.Rows.Count == 1);
}
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
services.AddControllers();
using var dbFactory = new SqliteDbFactory(Configuration.GetConnectionString("DotNetCoreDatabase"));
services.AddTransient <IPersonService>(f => new PersonService(new UnitOfWork(dbFactory)));
services.AddTransient <IPlaceService>(f => new PlaceService(new UnitOfWork(dbFactory)));
services.AddTransient <IEventService>(f => new EventService(new UnitOfWork(dbFactory)));
services.AddTransient <IEventTypeService>(f => new EventTypeService(new UnitOfWork(dbFactory)));
}
/*
* create table antuoyiku(id integer primary key autoincrement,
* tph varchar(20),mdkw varchar(20),scaner varchar(30),
* scantime datetime);
*/
public static void Save(AnTuoYiKu entity)
{
string sql = string.Empty;
sql = "insert into antuoyiku(tph,mdkw,scaner,scantime) values('" +
entity.Tph + "','" + entity.Mdkw + "','" + entity.Scaner + "','" +
System.DateTime.Now.ToString("s")
+ "')";
SqliteDbFactory.GetSqliteDbOperator().ExecuteNonQuery(sql);
}
public static bool CheckExistsTuopan(SendDetail entity)
{
string sql = string.Empty;
sql = "select * from senddetail where tph='" +
entity.Tph + "'";
DataTable dt = SqliteDbFactory.GetSqliteDbOperator().SelectFromSql(sql);
return(dt != null && dt.Rows.Count == 1);
}
/*
* create table pintuo(id integer primary key autoincrement,
* tph varchar(20),sn varchar(20),wz varchar(30),
* xxjh varchar(20),scaner varchar(30),scantime datetime);
*/
public static void Save(PinTuo entity)
{
string sql = string.Empty;
sql = "insert into pintuo(tph,sn,xxjh,wz,scaner,scantime) values('" +
entity.Tph + "','" + entity.Sn + "','" + entity.Xxjh + "','" +
entity.Wz + "','" + entity.Scaner + "','" +
System.DateTime.Now.ToString("s")
+ "')";
SqliteDbFactory.GetSqliteDbOperator().ExecuteNonQuery(sql);
}
public static bool CheckExists(JieTuo entity)
{
string sql = string.Empty;
sql = "select * from jietuo where tph='" +
entity.Tph + "' and sn='" + entity.Sn + "' and iswhole=" +
entity.IsWhole + " and scaner='" + entity.Scaner + "'";
DataTable dt = SqliteDbFactory.GetSqliteDbOperator().SelectFromSql(sql);
return(dt != null && dt.Rows.Count == 1);
}
/*
* create table zutuo(id integer primary key autoincrement
* ,tph varchar(20),sn varchar(20),xxjh varchar(20),
* scaner varchar(30),scantime datetime);
*/
public static void Save(ZuTuo entity)
{
string sql = string.Empty;
sql = "insert into zutuo(tph,sn,xxjh,scaner,scantime) values('" +
entity.Tph + "','" + entity.Sn + "','" +
entity.Xxjh + "','" + entity.Scaner + "','" +
entity.date.ToString("s")
+ "')";
SqliteDbFactory.GetSqliteDbOperator().ExecuteNonQuery(sql);
}
public override IMigrationProcessor Create(String connectionString, IAnnouncer announcer, IMigrationProcessorOptions options)
{
var factory = new SqliteDbFactory();
var connection = factory.CreateConnection(connectionString);
var generator = new SqliteGenerator()
{
compatabilityMode = CompatabilityMode.STRICT
};
return(new NzbDroneSqliteProcessor(connection, generator, announcer, options, factory));
}
public static void Delete(SendDetail entity, SendRecord parent)
{
string[] sql = new string[2];
sql[0] = "delete from senddetail where sn='" +
entity.Sn + "'";
if (entity.Sn != null && entity.Sn.Length > 0)
{
sql[1] = "update sendrecord set sl=sl-1 where so='" + parent.So + "' and pnno='" + entity.Sn.Substring(2, 10) + "'";
}
SqliteDbFactory.GetSqliteDbOperator().BatchExecute(sql);
}
/*
* create table jietuo(id integer primary key autoincrement,
* tph varchar(20),sn varchar(20),iswhole integer,
* scaner varchar(30),scantime datetime);
*/
public static void Save(JieTuo entity)
{
string sql = string.Empty;
sql = "insert into jietuo(tph,sn,iswhole,scaner,scantime) values('" +
entity.Tph + "','" + entity.Sn + "'," + entity.IsWhole + ",'" +
entity.Scaner + "','" +
System.DateTime.Now.ToString("s")
+ "')";
//MessageBox.Show(sql);
SqliteDbFactory.GetSqliteDbOperator().ExecuteNonQuery(sql);
}
private void btn_UpdataBaseData_Click(object sender, EventArgs e)
{
DataSet ds = new DB().GetFactoryAndStorage();
if (ds == null || ds.Tables.Count < 2)
{
MessageBox.Show("获取数据失败，请检查网络！");
return;
}
StringBuilder sql = new StringBuilder();
sql.Append("delete from mydicts;");
sql.Append("insert into mydicts(dicttype,dictvalue,dicttext) ");
foreach (DataRow dr in ds.Tables["Factory"].Rows)
{
sql.AppendFormat(System.Globalization.CultureInfo.CurrentCulture, "select '{0}','{1}','{2}' union all ", 1, dr["id"], dr["name"]);
}
foreach (DataRow dr in ds.Tables["Storage"].Rows)
{
sql.AppendFormat(System.Globalization.CultureInfo.CurrentCulture, "select '{0}','{1}','{2}' union all ", 2, dr["id"], dr["name"]);
}
if (sql.Length == 0)
{
return;
}
sql.Length = sql.Length - "union all ".Length;
if (SqliteDbFactory.GetSqliteDbOperator().BatchExecute(sql.ToString().Split(";".ToCharArray())) == 0)
{
MessageBox.Show("同步基础数据失败，请检查网络！");
return;
}
ds = new DB().GetLoc();
sql.Length = 0;
sql.Append("delete from kuweiinfo;");
sql.Append("insert into kuweiinfo(kuweicode) ");
foreach (DataRow dr in ds.Tables[0].Rows)
{
sql.AppendFormat(System.Globalization.CultureInfo.CurrentCulture, "select '{0}' union all ", dr["loc"]);
}
if (sql.Length == 0)
{
return;
}
sql.Length = sql.Length - "union all ".Length;
if (SqliteDbFactory.GetSqliteDbOperator().BatchExecute(sql.ToString().Split(";".ToCharArray())) == 0)
{
MessageBox.Show("同步基础数据失败，请检查网络！");
return;
}
MessageBox.Show("同步基础数据成功！");
}
public static void Delete(SendRecord entity)
{
string sql = string.Empty;
sql = "delete from sendrecord where so='" +
entity.So + "' and otherso='" + entity.OtherSo + "' and qufen='" +
entity.QuFen + "' and pnno='" +
entity.PnNo + "' and cpqufen='" +
entity.CpQuFen + "' and carno='" +
entity.CarNo + "' and status=''"
+ " and scaner='" + entity.Scaner + "'";
SqliteDbFactory.GetSqliteDbOperator().ExecuteNonQuery(sql);
}
/*
* create table sendrecord(id integer primary key autoincrement,
* so varchar(20),otherso varchar(200),qufen varchar(20),
* pnno varchar(20),cpqufen varchar(20),sl integer,
* carno varchar(30),status integer,scaner varchar(30),
* scantime datetime);
*/
public static void Save(SendRecord entity)
{
string sql = string.Empty;
sql = "insert into sendrecord(so,otherso,qufen,pnno,cpqufen,carno,status,scaner,scantime) values('" +
entity.So + "','" + entity.OtherSo + "','" +
entity.QuFen + "','" + entity.PnNo + "','" +
entity.CpQuFen + "','" + entity.CarNo + "'," + entity.Status + ",'" +
entity.Scaner + "','" +
System.DateTime.Now.ToString("s")
+ "')";
//MessageBox.Show(sql);
SqliteDbFactory.GetSqliteDbOperator().ExecuteNonQuery(sql);
}
private void tabControl1_SelectedIndexChanged(object sender, EventArgs e)
{
if (tabControl1.SelectedIndex == 1)
{
string sql = string.Format(@"select receiveno AS 收货单,pnno AS 产品,tno AS 托盘,cuno AS SN from receivedetail where receiveno='{0}' and scaner='{1}'", asnMain.Receiveno, Program.UserID);
this.dg_Resume.DataSource = SqliteDbFactory.GetSqliteDbOperator().SelectFromSql(sql);
new DB().SetDataGridCloumnWidth(dg_Resume);
}
else if (tabControl1.SelectedIndex == 2)
{
string sql = string.Format(@"select receiveno AS 收货单,pnno AS 产品,sl AS 数量 from receiverecord where receiveno='{0}'", asnMain.Receiveno);
this.dg_Summarizing.DataSource = SqliteDbFactory.GetSqliteDbOperator().SelectFromSql(sql);
new DB().SetDataGridCloumnWidth(dg_Summarizing);
}
}
public static bool CheckExists(SendRecord entity)
{
string sql = string.Empty;
sql = "select * from sendrecord where so='" +
entity.So + "' and otherso='" + entity.OtherSo + "' and qufen='" +
entity.QuFen + "' and pnno='" +
entity.PnNo + "' and cpqufen='" +
entity.CpQuFen + "' and carno='" +
entity.CarNo + "' and status=" + entity.Status + ""
+ " and scaner='" + entity.Scaner + "'";
//MessageBox.Show(sql);
DataTable dt = SqliteDbFactory.GetSqliteDbOperator().SelectFromSql(sql);
return(dt != null && dt.Rows.Count == 1);
}
private void tabControl1_SelectedIndexChanged_1(object sender, EventArgs e)
{
if (tabControl1.SelectedIndex == 1)
{
string sql = string.Format(@"select sn as SN,tph as 托盘,kuwei as 库位 from pandiandetail where scaner='{0}'", Program.UserID);
DataTable dt = SqliteDbFactory.GetSqliteDbOperator().SelectFromSql(sql);
this.dg_Resume.DataSource = dt;
new DB().SetDataGridCloumnWidth(dg_Resume);
}
if (tabControl1.SelectedIndex == 2)
{
string sql = string.Format(@"select pnno as 产品 ,sl as 数量 from pandianrecord");
DataTable dt = SqliteDbFactory.GetSqliteDbOperator().SelectFromSql(sql);
this.dg_Summarizing.DataSource = dt;
new DB().SetDataGridCloumnWidth(dg_Summarizing);
}
}
protected static void ExecuteWithSqlite(Action <IMigrationProcessor> test, IntegrationTestOptions.DatabaseServerOptions serverOptions)
{
if (!serverOptions.IsEnabled)
{
return;
}
var announcer = new TextWriterAnnouncer(System.Console.Out);
announcer.Heading("Testing Migration against SQLite");
var factory = new SqliteDbFactory();
using (var connection = factory.CreateConnection(serverOptions.ConnectionString))
{
var processor = new SqliteProcessor(connection, new SqliteGenerator(), announcer, new ProcessorOptions(), factory);
test(processor);
}
}
public void SetUp()
{
// This connection used in the tests
var factory = new SqliteDbFactory();
connection = factory.CreateConnection("Data Source=:memory:;Version=3;New=True;");
connection.Open();
command = connection.CreateCommand();
// SUT
processor = new SqliteProcessor(connection, new SqliteGenerator(), new TextWriterAnnouncer(System.Console.Out), new ProcessorOptions(), factory);
column = new Mock <ColumnDefinition>();
tableName = "NewTable";
tableNameThanMustBeEscaped = "123NewTable";
columnName = "ColumnName";
column.SetupGet(c => c.Name).Returns(columnName);
column.SetupGet(c => c.IsNullable).Returns(true);
column.SetupGet(c => c.Type).Returns(DbType.Int32);
}
