/// <summary>
/// 加载、初始化数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void SQLiteTools_Load(object sender, EventArgs e)
{
List <TextValue> listType = new List <TextValue>();
listType.Add(new TextValue("INTEGER", SQLiteDataType.INTEGER));
listType.Add(new TextValue("REAL", SQLiteDataType.REAL));
listType.Add(new TextValue("TEXT", SQLiteDataType.TEXT));
listType.Add(new TextValue("BLOB", SQLiteDataType.BLOB));
this.DataType.DisplayMember = "Text";
this.DataType.ValueMember = "Value";
this.DataType.DataSource = listType;
this.DataType.DisplayStyle = DataGridViewComboBoxDisplayStyle.ComboBox;
this.tbDataSource.Text = SQLiteHelper.DataSource;
listSchema = new List <SQLiteTableSchema>();
SQLiteTableSchema info = new SQLiteTableSchema();
info.FieldName = "ID";
info.DataType = SQLiteDataType.INTEGER;
info.Primary = true;
info.AotoIncrement = true;
info.Null = false;
listSchema.Add(info);
this.dgvTableSchema.DataSource = listSchema;
}
/// <summary>
/// 添加行
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnAddRow_Click(object sender, EventArgs e)
{
SQLiteTableSchema info = new SQLiteTableSchema();
info.FieldName = "";
info.DataType = SQLiteDataType.INTEGER;
info.Primary = false;
info.AotoIncrement = false;
info.Null = false;
info.DefaultValue = "";
listSchema.Add(info);
this.dgvTableSchema.DataSource = null;
this.dgvTableSchema.DataSource = listSchema;
}
public void DoNonQueryBatch(EbDataTable Table)
{
try
{
using SqliteConnection con = new SqliteConnection("Data Source=" + this.DbPath);
con.Open();
string query = "INSERT INTO {0} ({1}) VALUES ({2});";
List <string> _cols = new List <string>();
List <string> _vals = new List <string>();
using (SqliteCommand cmd = con.CreateCommand())
{
using SqliteTransaction transaction = con.BeginTransaction();
for (int k = 0; k < Table.Rows.Count; k++)
{
cmd.Parameters.Clear();
for (int i = 0; i < Table.Rows[k].Count; i++)
{
EbDataColumn column = Table.Columns.Find(item => item.ColumnIndex == i);
if (k == 0)
{
_cols.Add(column.ColumnName);
_vals.Add("@" + column.ColumnName);
}
cmd.Parameters.Add(new SqliteParameter {
ParameterName = "@" + column.ColumnName, Value = SQLiteTableSchema.SQLiteTypeValue(column.Type, Table.Rows[k][i])
});
}
cmd.CommandText = string.Format(query, Table.TableName, string.Join(",", _cols.ToArray()), string.Join(",", _vals.ToArray()));
int rowAffected = cmd.ExecuteNonQuery();
}
transaction.Commit();
}
con.Close();
}
catch (Exception e)
{
EbLog.Error(e.Message);
}
}
public void ImportData(EbDataSet dataSet)
{
EbLog.Info("Importing Data to local DB...");
if (dataSet?.Tables.Count > 0)
{
EbLog.Info($"Importing {dataSet?.Tables.Count} Tables");
foreach (EbDataTable dt in dataSet.Tables)
{
EbLog.Info($"Importing Tables {dt.TableName} with {dt.Rows.Count} records");
List <SQLiteColumSchema> ColSchema = new List <SQLiteColumSchema>();
foreach (EbDataColumn col in dt.Columns)
{
ColSchema.Add(new SQLiteColumSchema
{
ColumnName = col.ColumnName,
ColumnType = SQLiteTableSchema.SQLiteType(col.Type)
});
}
DropTable(dt.TableName);
EbLog.Info($"{dt.TableName} droped.");
CreateTable(dt.TableName, ColSchema);
EbLog.Info($"{dt.TableName} created.");
App.DataDB.DoNonQueryBatch(dt);
EbLog.Info($"Importing Tables {dt.TableName} complete.");
}
}
}
public void CreateTableSchema()
{
try
{
SQLiteTableSchemaList schemas = new SQLiteTableSchemaList();
this.ControlDictionary = this.ChildControls.ToControlDictionary();
SQLiteTableSchema masterSchema = new SQLiteTableSchema()
{
TableName = this.TableName
};
schemas.Add(masterSchema);
foreach (var pair in this.ControlDictionary)
{
if (pair.Value is INonPersistControl)
{
continue;
}
if (pair.Value is ILinesEnabled)
{
SQLiteTableSchema linesSchema = new SQLiteTableSchema()
{
TableName = (pair.Value as ILinesEnabled).TableName
};
foreach (var ctrl in (pair.Value as ILinesEnabled).ChildControls)
{
linesSchema.Columns.Add(new SQLiteColumSchema
{
ColumnName = ctrl.Name,
ColumnType = ctrl.SQLiteType
});
}
linesSchema.AppendDefault();
linesSchema.Columns.Add(new SQLiteColumSchema
{
ColumnName = this.TableName + "_id",
ColumnType = "INT"
});
schemas.Add(linesSchema);
}
else
{
masterSchema.Columns.Add(new SQLiteColumSchema
{
ColumnName = pair.Value.Name,
ColumnType = pair.Value.SQLiteType
});
}
}
masterSchema.AppendDefault();
DBService.Current.CreateTables(schemas);
}
catch (Exception ex)
{
EbLog.Error(ex.Message);
}
}
