/// <summary>
/// 执行查询SQL语句
/// </summary>
/// <param name="StrConn">连接字符串</param>
/// <param name="strConn">连接字符串</param>
/// <param name="TransID">事务ID</param>
/// <param name="SqlText">Sql 文本</param>
/// <param name="SqlParams">Sql 参数，参数值所在位置必须与参数名一致</param>
/// <returns>返回数据集</returns>
public DataTable QueryTable(string TransID, string StrConn, string SqlText, object[] SqlParams)
{
SQLiteConnection oConn = GetSQLiteConnection(TransID, StrConn);
try
{
using (SQLiteCommand oCmd = new SQLiteCommand(SqlText, oConn))
{
CreateSqlParam(oCmd, SqlParams);
using (SQLiteDataReader odr = oCmd.ExecuteReader(CommandBehavior.CloseConnection))
{
using (DataTable dtSchema = odr.GetSchemaTable())
{
DataTable dtResult = BuildDataTable(dtSchema);
object[] aryValues = new object[odr.FieldCount];
dtResult.BeginLoadData();
while (odr.Read())
{
odr.GetValues(aryValues);
dtResult.LoadDataRow(aryValues, true);
}
dtResult.EndLoadData();
aryValues = null;
return(dtResult);
}
}
}
}
finally
{
CloseConnection(TransID, oConn);
}
}
/// <summary>
/// 执行查询SQL语句
/// </summary>
/// <param name="StrConn">连接字符串</param>
/// <param name="strConn">连接字符串</param>
/// <param name="TransID">事务ID</param>
/// <param name="SqlText">Sql 文本</param>
/// <param name="SqlParams">Sql 参数，参数值所在位置必须与参数名一致</param>
/// <returns>返回数据集</returns>
public byte[] Query(string TransID, string StrConn, string SqlText, object[] SqlParams)
{
SQLiteConnection oConn = GetSQLiteConnection(TransID, StrConn);
try
{
using (SQLiteCommand oCmd = new SQLiteCommand(SqlText, oConn))
{
CreateSqlParam(oCmd, SqlParams);
using (SQLiteDataReader odr = oCmd.ExecuteReader(CommandBehavior.CloseConnection))
{
using (DataTable dtSchema = odr.GetSchemaTable())
{
using (CJia.Net.Serialization.SerializationWriter sw = new Net.Serialization.SerializationWriter())
{
SerializationSchema(sw, dtSchema);
object[] aryValues = new object[odr.FieldCount];
while (odr.Read())
{
odr.GetValues(aryValues);
sw.WriteOptimized(aryValues);
}
aryValues = null;
return(sw.ToArray());
}
}
}
}
}
finally
{
CloseConnection(TransID, oConn);
}
}
private void addColumns(IColumnedObject table)
{
string sql = string.Format(sqlColumns, table.Name);
string dbConn = ConnectionString;
var cn = new SQLiteConnection(dbConn);
cn.Open();
var cmd = new SQLiteCommand(sql, cn);
SQLiteDataReader rd = cmd.ExecuteReader(CommandBehavior.SchemaOnly);
DataTable dt = rd.GetSchemaTable();
rd.Close();
foreach (DataRow dr in dt.Rows)
{
string columnName = dr.Field <string>("ColumnName");
bool isKey = dr.Field <bool>("IsKey");
string dataTypeName = dr.Field <string>("DataTypeName");
// string provDataTypeName = dr.Field<string>("ProviderSpecificDataType");
short?numericPrecision = dr.Field <short?>("NumericPrecision");
int id = dr.Field <int>("ColumnOrdinal");
int numPres = -1;
if (numericPrecision.HasValue)
{
numPres = numericPrecision.Value;
}
Sql8rColumn c = new Sql8rColumn(columnName, isKey, dataTypeName, numPres, id);
table.Columns.Add(c.ObjectId, c);
}
}
/// <summary>
/// 获取row信息 Table schema
/// </summary>
static void TestO()
{
string cs = "URI=file:test.db";
using (SQLiteConnection con = new SQLiteConnection(cs))
{
con.Open();
string stm = "SELECT * FROM Cars LIMIT 3";
using (SQLiteCommand cmd = new SQLiteCommand(stm, con))
{
using (SQLiteDataReader rdr = cmd.ExecuteReader())
{
DataTable schemaTable = rdr.GetSchemaTable();
foreach (DataRow row in schemaTable.Rows)
{
foreach (DataColumn col in schemaTable.Columns)
{
Console.WriteLine(col.ColumnName + " = " + row[col]);
}
Console.WriteLine();
}
}
}
con.Close();
}
}
/// <summary>
/// 获取所有的数据列
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
public IList <Column> GetColumns(string tableName)
{
IList <Column> result = new List <Column>();
IList <string> primaryKeys = this.GetPrimaryKeys(tableName);
SQLiteConnection conn = new SQLiteConnection(ConfigurationManager.ConnectionStrings["SQLiteConnectionString"].ConnectionString);
conn.Open();
using (SQLiteCommand cmd = new SQLiteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{1}]", "main", tableName), conn))
using (SQLiteDataReader rd = (SQLiteDataReader)cmd.ExecuteReader(CommandBehavior.SchemaOnly))
using (DataTable columns = rd.GetSchemaTable())
{
foreach (DataRow schemaRow in columns.Rows)
{
Column column = new Column();
column.Name = schemaRow[SchemaTableColumn.ColumnName].ToString();
column.Type = this.GetDataType(schemaRow["DataTypeName"].ToString().ToLower(CultureInfo.InvariantCulture));
column.Description = column.Name;
column.Nullable = (bool)schemaRow[SchemaTableColumn.AllowDBNull];
column.DefaultValue = schemaRow[SchemaTableOptionalColumn.DefaultValue].ToString();
column.Sequence = long.Parse(schemaRow[SchemaTableColumn.ColumnOrdinal].ToString());
column.Type = this.GetCSharpType(column);
column.Primary = primaryKeys.Contains(column.Name);
result.Add(column);
}
if (result.Count > 0)
{
result = new List <Column>(result.OrderBy <Column, string>(c => c.Name));
}
conn.Close();
return(result);
}
}
public DataTable GetSchema2(string name)
{
DataTable schema = null;
try
{
using (SQLiteConnection connection = new SQLiteConnection(_connectionString))
{
connection.Open();
if (connection.State == ConnectionState.Open)
{
using (SQLiteCommand command = new SQLiteCommand("SELECT * FROM " + name, connection))
using (SQLiteDataReader reader = command.ExecuteReader(CommandBehavior.SchemaOnly))
{
schema = reader.GetSchemaTable();
}
connection.Close();
}
}
return(schema);
}
catch (Exception e)
{
_errMsg = e.Message;
_lastException = e;
return(null);
}
return(schema);
}
/// <summary>
/// 查询表信息
/// </summary>
/// <param name="conStr"></param>
/// <param name="sql"></param>
/// <returns></returns>
internal async static Task <DataTable> QueryTableInfo(string conStr, string sql)
{
return(await Task.Run(() =>
{
DataTable dt = new DataTable();
SQLiteHelper.con = SQLiteHelper.NewConnectionMethod(conStr);
SQLiteCommand cmd = new SQLiteCommand(sql, SQLiteHelper.con);
try
{
SQLiteHelper.con.Open();
SQLiteDataReader sdr = cmd.ExecuteReader(CommandBehavior.KeyInfo);
dt = sdr.GetSchemaTable(); //获得表的结构
sdr.Close();
SQLiteHelper.con.Close();
SQLiteHelper.con.Dispose();
}
catch (Exception e)
{
SQLiteHelper.con.Close();
SQLiteHelper.con.Dispose();
throw e;
}
return dt;
}));
}
private static string[] PopulateTableSchema(IFeatureSet fs, string geomColumnName, SQLiteDataReader rdr)
{
var schemaTable = rdr.GetSchemaTable();
var columnNameList = new List <string>();
if (schemaTable != null)
{
foreach (DataRow r in schemaTable.Rows)
{
if (r["ColumnName"].ToString() != geomColumnName)
{
var colName = Convert.ToString(r["ColumnName"]);
var colDataType = Convert.ToString(r["DataType"]);
var t = Type.GetType(colDataType);
if (t != null)
{
fs.DataTable.Columns.Add(colName, t);
columnNameList.Add(colName);
}
}
}
}
return(columnNameList.ToArray());
}
/// <summary>
/// 使用SELECT语句查询表中的字段，返回查询字段的列名和.NET数据类型数据类型
/// </summary>
/// <param name="select">SELECT语句，用于指定表和查询的字段</param>
/// <returns>字段名，.NET数据类型的字典集合</returns>
public Dictionary <string, Type> GetTableSchema(string select)
{
SQLiteDataReader reader = this.conn.GetRecord(select);
try
{
var result = new Dictionary <string, Type>();
DataTable schemaTable = reader.GetSchemaTable();
foreach (DataRow row in schemaTable.Rows)
{
string colName = row["ColumnName"] as string;
Type colType = row["DataType"] as Type;
result.Add(colName, colType);
}
return(result);
}
catch (SQLiteException ex)
{
string errorMessage = "从数据库SELECT数据集发生异常\n"
+ "发生错误的SQL语句：" + select;
throw new Exception(errorMessage, ex);
}
finally
{
reader.Close();
reader.Dispose();
}
}
public static int GetNthOrdinal(this SQLiteDataReader reader, string columnName, int nthOccurrence = 1)
{
DataTable schema = reader.GetSchemaTable();
var occurrences = schema.Rows.Cast <DataRow>().Where(r => string.Equals((string)r["ColumnName"], columnName, StringComparison.Ordinal));
var occurrence = occurrences.Skip(nthOccurrence - 1).First();
object idx = occurrence["ColumnOrdinal"];
return(Convert.ToInt32(idx));
}
public bool HasColumn(SQLiteDataReader reader, string columnName)
{
foreach (DataRow row in reader.GetSchemaTable().Rows)
{
if (row ["ColumnName"].ToString() == columnName)
{
return(true);
}
}
return(false);
}
public ColumnMetaCollection GetTableSchema(string table)
{
ColumnMetaCollection metas = new ColumnMetaCollection();
SQLiteConnection connection = new SQLiteConnection(this.ConnectionString);
connection.Open();
SQLiteCommand cmd = new SQLiteCommand("select * from " + table + " limit 0,1", connection);
using (SQLiteDataReader ddr = cmd.ExecuteReader(CommandBehavior.KeyInfo))
{
if (ddr != null && !ddr.IsClosed)
{
//得元数据
DataTable dt = ddr.GetSchemaTable();
//StringBuilder builder = new StringBuilder();
for (int i = 0, len = dt.Rows.Count; i < len; i++)
{
//for (int offset = 0; offset < dt.Columns.Count; offset++)
//{
// builder.AppendLine(dt.Columns[offset].ColumnName + "=>(index:" + offset + ")=>" + dt.Rows[i][offset]);
//}
ColumnMeta columnMeta = new ColumnMeta();
columnMeta.ColumnName = dt.Rows[i][0].ToString();
columnMeta.ColumnOridinal = TypeConverter.ObjectToInt(dt.Rows[i][1], 0);
columnMeta.ColumnSize = TypeConverter.ObjectToInt(dt.Rows[i][2], 4);
columnMeta.IsUnique = TypeConverter.ObjectToBool(dt.Rows[i][5], false);
columnMeta.IsKey = TypeConverter.ObjectToBool(dt.Rows[i][6], false);
columnMeta.FieldTypeName = ((Type)dt.Rows[i][12]).Name;
columnMeta.FieldType = (Type)dt.Rows[i][12];
columnMeta.AllowDBNull = TypeConverter.ObjectToBool(dt.Rows[i][13], true);
if (dt.Rows[i][0].ToString().IndexOf(" ") > -1)
{
continue;
}
metas.Add(columnMeta);
}
//File.WriteAllText("d:\\struct.txt", builder.ToString());
}
}
return(metas);
}
private bool ExistField(SQLiteDataReader dr, string field)
{
DataTable dt = dr.GetSchemaTable();
foreach (DataRow item in dt.Rows)
{
if (item[0].Equals(field))
{
return(true);
}
}
return(false);
}
public static DataSet ConvertDataReaderToDataSet(SQLiteDataReader reader)
{
DataSet dataSet = new DataSet();
do
{
// Create new data table
DataTable schemaTable = reader.GetSchemaTable();
DataTable dataTable = new DataTable("Query");
if (schemaTable != null)
{
// A query returning records was executed
for (int i = 0; i < schemaTable.Rows.Count; i++)
{
DataRow dataRow = schemaTable.Rows[i];
// Create a column name that is unique in the data table
string columnName = (string)dataRow["ColumnName"];
// Add the column definition to the data table
DataColumn column = new DataColumn(columnName, (Type)dataRow["DataType"]);
dataTable.Columns.Add(column);
}
dataSet.Tables.Add(dataTable);
// Fill the data table we just created
while (reader.Read())
{
DataRow dataRow = dataTable.NewRow();
for (int i = 0; i < reader.FieldCount; i++)
{
dataRow[i] = reader.GetValue(i);
}
dataTable.Rows.Add(dataRow);
}
}
else
{
// No records were returned
DataColumn column = new DataColumn("RowsAffected");
dataTable.Columns.Add(column);
dataSet.Tables.Add(dataTable);
DataRow dataRow = dataTable.NewRow();
dataRow[0] = reader.RecordsAffected;
dataTable.Rows.Add(dataRow);
}
} while(reader.NextResult());
return(dataSet);
}
public List <SQLiteSchema> GetTableSchema(string dbConnection, string tableName)
{
try
{
List <SQLiteSchema> fields = new List <SQLiteSchema>();
string sql = $"SELECT * FROM {tableName} LIMIT 1";
DataTable dt = new DataTable();
using (var c = new SQLiteConnection(dbConnection))
{
c.Open();
using (SQLiteCommand cmd = new SQLiteCommand(sql, c))
{
using (SQLiteDataReader rdr = cmd.ExecuteReader())
{
dt = rdr.GetSchemaTable();
foreach (DataRow row in dt.Rows)
{
SQLiteSchema schema = new SQLiteSchema();
fields.Add(schema);
foreach (DataColumn col in dt.Columns)
{
try
{
object val = row[col];
if (val != DBNull.Value)
{
schema.GetType().GetProperty(col.ColumnName).SetValue(schema, val);
}
}
catch (Exception ee)
{
Console.WriteLine($"{ee.Message}[{tableName}:{col.ColumnName}]");
}
}
}
}
}
c.Close();
}
return(fields);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return(null);
}
}
private string[] PopulateTableSchema(IFeatureSet fs, string geomColumnName, SQLiteDataReader rdr)
{
DataTable schemaTable = rdr.GetSchemaTable();
List <string> columnNameList = new List <string>();
foreach (DataRow r in schemaTable.Rows)
{
if (r["ColumnName"].ToString() != geomColumnName)
{
string colName = Convert.ToString(r["ColumnName"]);
string colDataType = Convert.ToString(r["DataType"]);
fs.DataTable.Columns.Add(colName, Type.GetType(colDataType));
columnNameList.Add(colName);
}
}
return(columnNameList.ToArray());
}
private string getRusName(string table, string engName)
{
SQLiteCommand command = new SQLiteCommand("SELECT rusName FROM RusNamesFields WHERE engName = '" + table + "." + engName + "'", dbConnection);
SQLiteDataReader reader = command.ExecuteReader();
DataTable schema = reader.GetSchemaTable();
string rusName;
if (reader.Read())
{
rusName = reader[0].ToString();
}
else
{
rusName = table + " " + engName;
}
return(rusName);
}
///// <summary>
///// 执行一条计算查询结果语句，返回查询结果（object）。
///// </summary>
///// <param name="SQLString">计算查询结果语句</param>
///// <returns>查询结果（object）</returns>
//public object GetSingle(string SQLString, params SQLiteParameter[] cmdParms)
//{
// using (SQLiteConnection connection = new SQLiteConnection(_ConnectStr))
// {
// using (SQLiteCommand cmd = new SQLiteCommand())
// {
// try
// {
// PrepareCommand(cmd, connection, null, SQLString, cmdParms);
// object obj = cmd.ExecuteScalar();
// cmd.Parameters.Clear();
// if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
// {
// return null;
// }
// else
// {
// return obj;
// }
// }
// catch (System.Data.SQLite.SQLiteException e)
// {
// throw new Exception(e.Message);
// }
// }
// }
//}
public DataTable ExecuteReaderField(string strSQL)
{
using (SQLiteConnection connection = new SQLiteConnection(_ConnectStr))
{
using (SQLiteCommand cmd = new SQLiteCommand(strSQL, connection))
{
try
{
connection.Open();
SQLiteDataReader myReader = cmd.ExecuteReader();
return(myReader.GetSchemaTable());
}
catch (System.Data.SQLite.SQLiteException e)
{
throw new Exception(e.Message);
}
}
}
}
//finds out the geometry column information..
private GeometryColumnInfo FindGeometryColumnInfo(string connString, string sqlQuery)
{
GeometryColumnInfo result = null;
using (SQLiteCommand cmd = CreateCommand(connString, sqlQuery))
{
cmd.Connection.Open();
RunInitialCommands(cmd.Connection);
SpatiaLiteWkbReader wkbr = new SpatiaLiteWkbReader();
SQLiteDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleRow);
DataTable schemaTable = rdr.GetSchemaTable();
foreach (DataRow r in schemaTable.Rows)
{
string colName = Convert.ToString(r["ColumnName"]);
string colDataType = Convert.ToString(r["DataType"]);
//if BLOB, then assume geometry column
if (Type.GetType(colDataType) == typeof(byte[]))
{
result = new GeometryColumnInfo();
result.GeometryColumnName = colName;
break;
}
}
if (result != null && rdr.HasRows)
{
rdr.Read();
byte[] blob = rdr[result.GeometryColumnName] as byte[];
IGeometry geom = wkbr.Read(blob);
result.GeometryType = geom.GeometryType;
}
cmd.Connection.Close();
return(result);
}
}
public DataTable selectData(string query)
{
try
{
SQLiteCommand command = new SQLiteCommand(query, dbConnection);
SQLiteDataReader reader = command.ExecuteReader();
DataTable schema = reader.GetSchemaTable();
DataTable table = new DataTable();
//добавление столбцов
if (schema != null)
{
for (int i = 0; i < schema.Rows.Count; i++)
{
table.Columns.Add();
//table.Columns.Add(getRusName(schema.Rows[i]["BaseTableName"].ToString(), schema.Rows[i][0].ToString()));
//table.Columns.Add(schema.Rows[i]["BaseTableName"].ToString()+" "+ schema.Rows[i][0].ToString());
}
}
//заполнение строк
while (reader.Read())
{
DataRow row = table.NewRow();
for (int i = 0; i < table.Columns.Count; i++)
{
row[i] = reader[i];
}
table.Rows.Add(row);
}
return(table);
}
catch (Exception ex)
{
throw new Exception("Ошибка получения данных \n" + ex.Message);
}
}
public static Dictionary <string, List <string> > ReadTable(string filePath, string table)
{
var result = new Dictionary <string, List <string> >();
string connString = string.Format(ConnectionString, filePath);
using (SQLiteConnection connection = new SQLiteConnection(connString))
{
connection.Open();
string query = $"SELECT * FROM {table}";
SQLiteCommand command = new SQLiteCommand(query, connection);
using (SQLiteDataReader dataReader = command.ExecuteReader())
{
var columnNames = dataReader.GetSchemaTable();
List <List <string> > data = new List <List <string> >();
for (int i = 0; i < columnNames.Rows.Count; i++)
{
data.Add(new List <string>());
}
while (dataReader.Read())
{
for (int i = 0; i < columnNames.Rows.Count; i++)
{
data[i].Add(dataReader[i].ToString());
}
}
for (int i = 0; i < columnNames.Rows.Count; i++)
{
result[columnNames.Rows[i]["ColumnName"].ToString()] = data[i];
}
}
}
return(result);
}
private DataTable Schema_ViewColumns(SQLiteConnection cn)
{
DataTable tbl = new DataTable("ViewColumns");
DataRow row;
string strSql;
int n;
DataRow schemaRow;
DataRow viewRow;
tbl.Locale = CultureInfo.InvariantCulture;
tbl.Columns.Add("VIEW_CATALOG", typeof(string));
tbl.Columns.Add("VIEW_SCHEMA", typeof(string));
tbl.Columns.Add("VIEW_NAME", typeof(string));
tbl.Columns.Add("VIEW_COLUMN_NAME", typeof(String));
tbl.Columns.Add("TABLE_CATALOG", typeof(string));
tbl.Columns.Add("TABLE_SCHEMA", typeof(string));
tbl.Columns.Add("TABLE_NAME", typeof(string));
tbl.Columns.Add("COLUMN_NAME", typeof(string));
tbl.Columns.Add("ORDINAL_POSITION", typeof(int));
tbl.Columns.Add("COLUMN_HASDEFAULT", typeof(bool));
tbl.Columns.Add("COLUMN_DEFAULT", typeof(string));
tbl.Columns.Add("COLUMN_FLAGS", typeof(long));
tbl.Columns.Add("IS_NULLABLE", typeof(bool));
tbl.Columns.Add("DATA_TYPE", typeof(string));
tbl.Columns.Add("CHARACTER_MAXIMUM_LENGTH", typeof(int));
tbl.Columns.Add("NUMERIC_PRECISION", typeof(int));
tbl.Columns.Add("NUMERIC_SCALE", typeof(int));
tbl.Columns.Add("DATETIME_PRECISION", typeof(long));
tbl.Columns.Add("CHARACTER_SET_CATALOG", typeof(string));
tbl.Columns.Add("CHARACTER_SET_SCHEMA", typeof(string));
tbl.Columns.Add("CHARACTER_SET_NAME", typeof(string));
tbl.Columns.Add("COLLATION_CATALOG", typeof(string));
tbl.Columns.Add("COLLATION_SCHEMA", typeof(string));
tbl.Columns.Add("COLLATION_NAME", typeof(string));
tbl.Columns.Add("PRIMARY_KEY", typeof(bool));
tbl.Columns.Add("EDM_TYPE", typeof(string));
tbl.Columns.Add("AUTOINCREMENT", typeof(bool));
tbl.Columns.Add("UNIQUE", typeof(bool));
tbl.BeginLoadData();
using (SQLiteCommand cmdViews = new SQLiteCommand("SELECT * FROM [main].[sqlite_master] WHERE [type] LIKE 'view'", cn))
{
using (SQLiteDataReader rdViews = cmdViews.ExecuteReader())
{
while (rdViews.Read())
{
using (SQLiteCommand cmdViewSelect = new SQLiteCommand(string.Format(CultureInfo.InvariantCulture, "SELECT * FROM [main].[{0}]", rdViews.GetString(2)), cn))
{
strSql = rdViews.GetString(4);
//strSql = rdViews.GetString(4).Replace('\r', ' ').Replace('\n', ' ').Replace('\t', ' ');
n = CultureInfo.InvariantCulture.CompareInfo.IndexOf(strSql, " AS ", CompareOptions.IgnoreCase);
if (n < 0)
{
continue;
}
strSql = strSql.Substring(n + 4);
using (SQLiteCommand cmd = new SQLiteCommand(strSql, cn))
using (SQLiteDataReader rdViewSelect = cmdViewSelect.ExecuteReader(CommandBehavior.SchemaOnly))
using (SQLiteDataReader rd = cmd.ExecuteReader(CommandBehavior.SchemaOnly))
using (DataTable tblSchemaView = rdViewSelect.GetSchemaTable())
using (DataTable tblSchema = rd.GetSchemaTable())
{
for (n = 0; n < tblSchema.Rows.Count; n++)
{
viewRow = tblSchemaView.Rows[n];
schemaRow = tblSchema.Rows[n];
row = tbl.NewRow();
row["VIEW_CATALOG"] = "main";
row["VIEW_NAME"] = rdViews.GetString(2);
row["TABLE_CATALOG"] = "main";
row["TABLE_SCHEMA"] = schemaRow[SchemaTableColumn.BaseSchemaName];
row["TABLE_NAME"] = schemaRow[SchemaTableColumn.BaseTableName];
row["COLUMN_NAME"] = schemaRow[SchemaTableColumn.BaseColumnName];
row["VIEW_COLUMN_NAME"] = viewRow[SchemaTableColumn.ColumnName];
row["COLUMN_HASDEFAULT"] = (viewRow[SchemaTableOptionalColumn.DefaultValue] != DBNull.Value);
row["COLUMN_DEFAULT"] = viewRow[SchemaTableOptionalColumn.DefaultValue];
row["ORDINAL_POSITION"] = viewRow[SchemaTableColumn.ColumnOrdinal];
row["IS_NULLABLE"] = viewRow[SchemaTableColumn.AllowDBNull];
row["DATA_TYPE"] = viewRow["DataTypeName"];
row["CHARACTER_MAXIMUM_LENGTH"] = viewRow[SchemaTableColumn.ColumnSize];
row["TABLE_SCHEMA"] = viewRow[SchemaTableColumn.BaseSchemaName];
row["PRIMARY_KEY"] = viewRow[SchemaTableColumn.IsKey];
row["AUTOINCREMENT"] = viewRow[SchemaTableOptionalColumn.IsAutoIncrement];
row["COLLATION_NAME"] = viewRow["CollationType"];
row["UNIQUE"] = viewRow[SchemaTableColumn.IsUnique];
if (!string.IsNullOrEmpty(schemaRow[SchemaTableColumn.BaseColumnName].ToString()))
{
tbl.Rows.Add(row);
}
}
}
}
}
}
}
tbl.EndLoadData();
tbl.AcceptChanges();
return(tbl);
}
/// <summary>
/// 生成实体类
/// </summary>
/// <param name="nameSpace"></param>
/// <param name="baseClass">父类</param>
/// <returns></returns>
public int GenerateEntities(string nameSpace, Type baseClass = null)
{
using (var conn = new SQLiteConnection(connectionString))
{
SQLiteCommand command = new SQLiteCommand();
command.CommandTimeout = 120;
command.Connection = conn;
conn.Open();
DataTable dt = conn.GetSchema("Tables");
foreach (DataRow row in dt.Rows)
{
var tableName = row["TABLE_NAME"];
string generatePath = System.AppDomain.CurrentDomain.BaseDirectory + "bin\\generate\\" + tableName + ".cs";
if (!Directory.Exists(System.AppDomain.CurrentDomain.BaseDirectory + "bin\\generate\\"))
{
Directory.CreateDirectory(System.AppDomain.CurrentDomain.BaseDirectory + "bin\\generate\\");
}
if (File.Exists(generatePath))
{
File.Delete(generatePath);
}
FileStream fs1 = new FileStream(generatePath, FileMode.Create, FileAccess.Write);//创建写入文件
StreamWriter sw = new StreamWriter(fs1);
if (baseClass != null)
{
sw.Write("using " + baseClass.Namespace + ";\r\n"); //开始写入值
}
sw.Write("using System;\r\nusing System.Collections.Generic;\r\nnamespace " + nameSpace + "\r\n{\r\n public class " + tableName); //开始写入值
List <string> baseClassProperties = new List <string>();
if (baseClass != null)
{
sw.Write(" : " + baseClass.Name);
baseClassProperties = baseClass.GetProperties().Select(m => m.Name).ToList();
}
sw.Write("\r\n {\r\n");
command.CommandText = string.Format("SELECT * FROM `{0}`", tableName);
SQLiteDataReader dr = command.ExecuteReader(CommandBehavior.KeyInfo);
DataTable Schema = dr.GetSchemaTable();
foreach (DataRow field in Schema.Rows)
{
var coms = field.Table.Columns;
string columnName = field[coms.IndexOf("ColumnName")].ToString();
bool isKey = Convert.ToBoolean(field[coms.IndexOf("IsKey")]);
bool isAutoIncrement = Convert.ToBoolean(field[coms.IndexOf("IsAutoIncrement")]);
if (!baseClassProperties.Contains(columnName))
{
var columnType = field[coms.IndexOf("DataType")];
string typeBrief = string.Empty;
switch (columnType.ToString())
{
case "System.Int16":
case "System.UInt16":
typeBrief = "short?";
break;
case "System.Int32":
case "System.UInt32":
typeBrief = "int?";
break;
case "System.UInt64":
case "System.Int64":
typeBrief = "long?";
break;
case "System.Boolean":
typeBrief = "bool?";
break;
case "System.String":
typeBrief = "string";
break;
case "System.DateTime":
typeBrief = "DateTime?";
break;
case "System.SByte":
case "System.Byte":
typeBrief = "byte?";
break;
case "System.Decimal":
typeBrief = "decimal?";
break;
case "System.Single":
typeBrief = "float?";
break;
case "System.Double":
typeBrief = "double?";
break;
case "System.Byte[]":
typeBrief = "byte[]";
break;
default:
break;
}
if (isKey)
{
if (isAutoIncrement)
{
sw.WriteLine(" [Key(IsAutoGenerated = true)]");
}
else
{
sw.WriteLine(" [Key]");
}
}
sw.WriteLine(" public " + typeBrief + " " + columnName + " { get; set; }");
}
}
sw.WriteLine(" }");
sw.WriteLine("}\r\n");
sw.Close();
fs1.Close();
dr.Close();
}
conn.Close();
return(1);
}
}
public DataTable GetSchemaTable()
{
return(Reader.GetSchemaTable());
}
static void Main(string[] args)
{
try
{
Console.WriteLine("Conectando-se ao SQLite para demonstrar as operações de CRUD");
//Construindo a string de conexão
SQLiteConnectionStringBuilder sb = new SQLiteConnectionStringBuilder();
//Data source
sb.DataSource = @"C:\SQLite\sqlite-tools-win32-x86-3310100\senac.db";
//Conectando-se ao SQLite
Console.WriteLine("Conectando-se ao SQLite...");
using (SQLiteConnection con = new SQLiteConnection(sb.ConnectionString))
{
//Criando uma tabela e inserindo alguns registros
Console.WriteLine("Criando tabela, pressione qualquer tecla para continuar...");
//Aguardando ação do usuário
Console.ReadKey(true);
StringBuilder builder = new StringBuilder();
//builder.Append("USE senac.db;");
builder.Append("CREATE TABLE endereco ( ");
builder.Append(" Codigo INTEGER PRIMARY KEY AUTOINCREMENT, ");
builder.Append(" Logradouro VARCHAR(50) NOT NULL, ");
builder.Append(" Numero VARCHAR(9) NOT NULL ");
builder.Append("); ");
builder.Append("INSERT INTO endereco (Logradouro, Numero) VALUES ");
builder.Append("('Rua Alberto Andaló', '5689'), ");
builder.Append("('Rua Tereza P. Padovez', '77'), ");
builder.Append("('Rua Treze de Maio', '666'); ");
string sql = builder.ToString();
using (SQLiteCommand cmd = new SQLiteCommand(sql, con))
{
//Abrindo a conexão
con.Open();
Console.WriteLine("Conexão aberta!");
//Executando o SQL
cmd.ExecuteNonQuery();
Console.WriteLine("Tabela criada!");
//Fechando a conexão
con.Close();
Console.WriteLine("Conexão fechada!");
}
//Demonstração de insert
Console.WriteLine("Inserindo uma nova linha na tabela, pressione qualquer tecla para continuar...");
//Aguardando ação do usuário
Console.ReadKey(true);
builder.Clear();
builder.Append("INSERT INTO endereco (logradouro, numero) ");
builder.Append("VALUES (@logradouro, @numero);");
sql = builder.ToString();
using (SQLiteCommand cmd = new SQLiteCommand(sql, con))
{
//Parâmetros
cmd.Parameters.AddWithValue("@logradouro", "Rua Bady Bassit");
cmd.Parameters.AddWithValue("@numero", "657");
//Abrindo a conexão
con.Open();
Console.WriteLine("Conexão aberta!");
//Número de linhas afetadas pelo SQL
int linhas = cmd.ExecuteNonQuery();
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine(linhas + " linha(s) afetada(s)");
Console.ResetColor();
//Fechando a conexão
con.Close();
Console.WriteLine("Conexão fechada!");
}
string numero = "666";
//Demonstração de Update
Console.WriteLine("Atualizando o logradouro do número " + numero + ", pressione qualquer tecla para continuar...");
Console.ReadKey(true);
builder.Clear();
builder.Append("UPDATE Endereco SET logradouro = 'Av. Indio Tibiriçá' WHERE numero = @numero;");
sql = builder.ToString();
using (SQLiteCommand cmd = new SQLiteCommand(sql, con))
{
cmd.Parameters.AddWithValue("@numero", numero);
//Abrindo a conexão
con.Open();
Console.WriteLine("Conexão aberta!");
int linhas = cmd.ExecuteNonQuery();
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine(linhas + " linha(s) afetada(s)");
Console.ResetColor();
//Fechando a conexão
con.Close();
Console.WriteLine("Conexão fechada!");
}
//Demonstração de Delete
numero = "77";
Console.WriteLine("Excluindo o logradouro do número " + numero + ", pressione qualquer tecla para continuar");
Console.ReadKey(true);
builder.Clear();
builder.Append("DELETE FROM Endereco WHERE numero = @numero;");
sql = builder.ToString();
using (SQLiteCommand cmd = new SQLiteCommand(sql, con))
{
cmd.Parameters.AddWithValue("@numero", numero);
//Abrindo a conexão
con.Open();
Console.WriteLine("Conexão aberta!");
int linhas = cmd.ExecuteNonQuery();
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine(linhas + " linha(s) afetada(s)");
Console.ResetColor();
//Fechando a conexão
con.Close();
Console.WriteLine("Conexão fechada!");
}
//Demonstração select
Console.WriteLine("Lendo todos os registros da tabela, pressione qualquer tecla para continuar...");
Console.ReadKey(true);
sql = @"SELECT * FROM Endereco;";
using (SQLiteCommand cmd = new SQLiteCommand(sql, con))
{
//Abrindo a conexão
con.Open();
Console.WriteLine("Conexão aberta!");
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
DataTable schemaTable = reader.GetSchemaTable();
while (reader.Read())
{
Console.ForegroundColor = ConsoleColor.Yellow;
Console.WriteLine("{0} {1} {2}", reader.GetInt32(0), reader.GetString(1), reader.GetString(2));
Console.ResetColor();
}
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine(reader.VisibleFieldCount + " registro(s) no banco de dados");
Console.ResetColor();
}
//Fechando a conexão
con.Close();
Console.WriteLine("Conexão fechada!");
}
}
}
catch (SQLiteException e)
{
Console.WriteLine("Erro: " + e.ToString());
}
}
/// <summary>
/// 根据数据读取返回制定数据实体列表
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="reader"></param>
/// <returns></returns>
public List <T> GetModelList <T>(SQLiteDataReader reader)
{
List <T> tList = new List <T>();
if (reader != null && reader.HasRows)
{
T t = default(T);
int index = -1;
try
{
while (reader.Read())
{
t = Activator.CreateInstance <T>();
Type type = t.GetType();
PropertyInfo[] pinfos = type.GetProperties();
DataTable table = reader.GetSchemaTable();
if (table != null && table.Rows.Count > 0)
{
foreach (DataRow dr in table.Rows)
{
index = GetPropertyIndex(pinfos, dr["ColumnName"].ToString());
if (index >= 0)
{
PropertyInfo pi = pinfos[index];
String fieldName = pi.Name;
switch (pi.PropertyType.Name.ToLower())
{
case "int":
case "int32":
pi.SetValue(t, ExtendUtility.Instance.ParseToInt32(reader[pi.Name.ToLower()]), null);
break;
case "decimal":
pi.SetValue(t, ExtendUtility.Instance.ParseToDecimal(reader[pi.Name.ToLower()]), null);
break;
case "datetime":
pi.SetValue(t, ExtendUtility.Instance.ParseToDateTime(reader[pi.Name.ToLower()]).ToString("s"), null);
break;
default:
pi.SetValue(t, ExtendUtility.Instance.ParseToString(reader[pi.Name.ToLower()]), null);
break;
} //switch (pi.PropertyType.Name.ToLower())
} //if (index > 0)
index = -1;
} //foreach (DataColumn dc in table.Columns)
} //if (table != null && table.Rows.Count > 0)
tList.Add(t);
} //while (reader.Read())
}
catch (Exception)
{
}
finally
{
if (reader != null)
{
reader.Dispose();
reader.Close();
}
}
}
return(tList);
}
internal SQLiteKeyReader(SQLiteConnection cnn, SQLiteDataReader reader, SQLiteStatement stmt)
{
List <string> item;
Dictionary <string, int> strs = new Dictionary <string, int>();
Dictionary <string, List <string> > strs1 = new Dictionary <string, List <string> >();
List <SQLiteKeyReader.KeyInfo> keyInfos = new List <SQLiteKeyReader.KeyInfo>();
List <SQLiteKeyReader.RowIdInfo> rowIdInfos = new List <SQLiteKeyReader.RowIdInfo>();
this._stmt = stmt;
using (DataTable schema = cnn.GetSchema("Catalogs"))
{
foreach (DataRow row in schema.Rows)
{
strs.Add((string)row["CATALOG_NAME"], Convert.ToInt32(row["ID"], CultureInfo.InvariantCulture));
}
}
using (DataTable schemaTable = reader.GetSchemaTable(false, false))
{
foreach (DataRow dataRow in schemaTable.Rows)
{
if (dataRow[SchemaTableOptionalColumn.BaseCatalogName] == DBNull.Value)
{
continue;
}
string str = (string)dataRow[SchemaTableOptionalColumn.BaseCatalogName];
string item1 = (string)dataRow[SchemaTableColumn.BaseTableName];
if (strs1.ContainsKey(str))
{
item = strs1[str];
}
else
{
item = new List <string>();
strs1.Add(str, item);
}
if (item.Contains(item1))
{
continue;
}
item.Add(item1);
}
foreach (KeyValuePair <string, List <string> > keyValuePair in strs1)
{
for (int i = 0; i < keyValuePair.Value.Count; i++)
{
string str1 = keyValuePair.Value[i];
DataRow dataRow1 = null;
string[] key = new string[] { keyValuePair.Key, null, str1 };
using (DataTable dataTable = cnn.GetSchema("Indexes", key))
{
for (int j = 0; j < 2 && dataRow1 == null; j++)
{
foreach (DataRow row1 in dataTable.Rows)
{
if (j != 0 || !(bool)row1["PRIMARY_KEY"])
{
if (j != 1 || !(bool)row1["UNIQUE"])
{
continue;
}
dataRow1 = row1;
break;
}
else
{
dataRow1 = row1;
break;
}
}
}
if (dataRow1 != null)
{
string[] strArrays = new string[] { keyValuePair.Key, null, str1 };
using (DataTable schema1 = cnn.GetSchema("Tables", strArrays))
{
int num = strs[keyValuePair.Key];
int num1 = Convert.ToInt32(schema1.Rows[0]["TABLE_ROOTPAGE"], CultureInfo.InvariantCulture);
int cursorForTable = stmt._sql.GetCursorForTable(stmt, num, num1);
string[] key1 = new string[] { keyValuePair.Key, null, str1, (string)dataRow1["INDEX_NAME"] };
using (DataTable dataTable1 = cnn.GetSchema("IndexColumns", key1))
{
bool flag = (string)dataRow1["INDEX_NAME"] == string.Concat("sqlite_master_PK_", str1);
SQLiteKeyReader.KeyQuery keyQuery = null;
List <string> strs2 = new List <string>();
for (int k = 0; k < dataTable1.Rows.Count; k++)
{
string stringOrNull = SQLiteConvert.GetStringOrNull(dataTable1.Rows[k]["COLUMN_NAME"]);
bool flag1 = true;
foreach (DataRow row2 in schemaTable.Rows)
{
if (row2.IsNull(SchemaTableColumn.BaseColumnName) || !((string)row2[SchemaTableColumn.BaseColumnName] == stringOrNull) || !((string)row2[SchemaTableColumn.BaseTableName] == str1) || !((string)row2[SchemaTableOptionalColumn.BaseCatalogName] == keyValuePair.Key))
{
continue;
}
if (flag)
{
SQLiteKeyReader.RowIdInfo rowIdInfo = new SQLiteKeyReader.RowIdInfo()
{
databaseName = keyValuePair.Key,
tableName = str1,
column = (int)row2[SchemaTableColumn.ColumnOrdinal]
};
rowIdInfos.Add(rowIdInfo);
}
dataTable1.Rows.RemoveAt(k);
k--;
flag1 = false;
break;
}
if (flag1)
{
strs2.Add(stringOrNull);
}
}
if (!flag && strs2.Count > 0)
{
string[] strArrays1 = new string[strs2.Count];
strs2.CopyTo(strArrays1);
keyQuery = new SQLiteKeyReader.KeyQuery(cnn, keyValuePair.Key, str1, strArrays1);
}
for (int l = 0; l < dataTable1.Rows.Count; l++)
{
string stringOrNull1 = SQLiteConvert.GetStringOrNull(dataTable1.Rows[l]["COLUMN_NAME"]);
SQLiteKeyReader.KeyInfo keyInfo = new SQLiteKeyReader.KeyInfo()
{
rootPage = num1,
cursor = cursorForTable,
database = num,
databaseName = keyValuePair.Key,
tableName = str1,
columnName = stringOrNull1,
query = keyQuery,
column = l
};
keyInfos.Add(keyInfo);
}
}
}
}
else
{
keyValuePair.Value.RemoveAt(i);
i--;
}
}
}
}
}
this._keyInfo = new SQLiteKeyReader.KeyInfo[keyInfos.Count];
keyInfos.CopyTo(this._keyInfo);
this._rowIdInfo = new SQLiteKeyReader.RowIdInfo[rowIdInfos.Count];
rowIdInfos.CopyTo(this._rowIdInfo);
}
/// <summary>
/// This function does all the nasty work at determining what keys need to be returned for
/// a given statement.
/// </summary>
/// <param name="cnn"></param>
/// <param name="reader"></param>
/// <param name="stmt"></param>
internal SQLiteKeyReader(SQLiteConnection cnn, SQLiteDataReader reader, SQLiteStatement stmt)
{
Dictionary <string, int> catalogs = new Dictionary <string, int>();
Dictionary <string, List <string> > tables = new Dictionary <string, List <string> >();
List <string> list;
List <KeyInfo> keys = new List <KeyInfo>();
// Record the statement so we can use it later for sync'ing
_stmt = stmt;
// Fetch all the attached databases on this connection
using (DataTable tbl = cnn.GetSchema("Catalogs"))
{
foreach (DataRow row in tbl.Rows)
{
catalogs.Add((string)row["CATALOG_NAME"], Convert.ToInt32(row["ID"], CultureInfo.InvariantCulture));
}
}
// Fetch all the unique tables and catalogs used by the current statement
using (DataTable schema = reader.GetSchemaTable(false, false))
{
foreach (DataRow row in schema.Rows)
{
// Check if column is backed to a table
if (row[SchemaTableOptionalColumn.BaseCatalogName] == DBNull.Value)
{
continue;
}
// Record the unique table so we can look up its keys
string catalog = (string)row[SchemaTableOptionalColumn.BaseCatalogName];
string table = (string)row[SchemaTableColumn.BaseTableName];
if (tables.ContainsKey(catalog) == false)
{
list = new List <string>();
tables.Add(catalog, list);
}
else
{
list = tables[catalog];
}
if (list.Contains(table) == false)
{
list.Add(table);
}
}
// For each catalog and each table, query the indexes for the table.
// Find a primary key index if there is one. If not, find a unique index instead
foreach (KeyValuePair <string, List <string> > pair in tables)
{
for (int i = 0; i < pair.Value.Count; i++)
{
string table = pair.Value[i];
DataRow preferredRow = null;
using (DataTable tbl = cnn.GetSchema("Indexes", new string[] { pair.Key, null, table }))
{
// Loop twice. The first time looking for a primary key index,
// the second time looking for a unique index
for (int n = 0; n < 2 && preferredRow == null; n++)
{
foreach (DataRow row in tbl.Rows)
{
if (n == 0 && (bool)row["PRIMARY_KEY"] == true)
{
preferredRow = row;
break;
}
else if (n == 1 && (bool)row["UNIQUE"] == true)
{
preferredRow = row;
break;
}
}
}
if (preferredRow == null) // Unable to find any suitable index for this table so remove it
{
pair.Value.RemoveAt(i);
i--;
}
else // We found a usable index, so fetch the necessary table details
{
using (DataTable tblTables = cnn.GetSchema("Tables", new string[] { pair.Key, null, table }))
{
// Find the root page of the table in the current statement and get the cursor that's iterating it
int database = catalogs[pair.Key];
int rootPage = Convert.ToInt32(tblTables.Rows[0]["TABLE_ROOTPAGE"], CultureInfo.InvariantCulture);
int cursor = stmt._sql.GetCursorForTable(stmt, database, rootPage);
// Now enumerate the members of the index we're going to use
using (DataTable indexColumns = cnn.GetSchema("IndexColumns", new string[] { pair.Key, null, table, (string)preferredRow["INDEX_NAME"] }))
{
KeyQuery query = null;
List <string> cols = new List <string>();
for (int x = 0; x < indexColumns.Rows.Count; x++)
{
bool addKey = true;
// If the column in the index already appears in the query, skip it
foreach (DataRow row in schema.Rows)
{
if (row.IsNull(SchemaTableColumn.BaseColumnName))
{
continue;
}
if ((string)row[SchemaTableColumn.BaseColumnName] == (string)indexColumns.Rows[x]["COLUMN_NAME"] &&
(string)row[SchemaTableColumn.BaseTableName] == table &&
(string)row[SchemaTableOptionalColumn.BaseCatalogName] == pair.Key)
{
indexColumns.Rows.RemoveAt(x);
x--;
addKey = false;
break;
}
}
if (addKey == true)
{
cols.Add((string)indexColumns.Rows[x]["COLUMN_NAME"]);
}
}
// If the index is not a rowid alias, record all the columns
// needed to make up the unique index and construct a SQL query for it
if ((string)preferredRow["INDEX_NAME"] != "sqlite_master_PK_" + table)
{
// Whatever remains of the columns we need that make up the index that are not
// already in the query need to be queried separately, so construct a subquery
if (cols.Count > 0)
{
string[] querycols = new string[cols.Count];
cols.CopyTo(querycols);
query = new KeyQuery(cnn, pair.Key, table, querycols);
}
}
// Create a KeyInfo struct for each column of the index
for (int x = 0; x < indexColumns.Rows.Count; x++)
{
string columnName = (string)indexColumns.Rows[x]["COLUMN_NAME"];
KeyInfo key = new KeyInfo();
key.rootPage = rootPage;
key.cursor = cursor;
key.database = database;
key.databaseName = pair.Key;
key.tableName = table;
key.columnName = columnName;
key.query = query;
key.column = x;
keys.Add(key);
}
}
}
}
}
}
}
}
// Now we have all the additional columns we have to return in order to support
// CommandBehavior.KeyInfo
_keyInfo = new KeyInfo[keys.Count];
keys.CopyTo(_keyInfo);
}
//Вывод на экран всех продуктов и блюд
private void TakeIngredientsFromBD()
{
if (File.Exists(basePersonalData))
{
try
{
ConnectBd();
DataTable dataTable = new DataTable();
commandDish.CommandText = "SELECT name FROM product";
SQLiteDataReader readerForName = commandDish.ExecuteReader();
String sqlCom1 = "SELECT name FROM product";
SQLiteDataAdapter adapter = new SQLiteDataAdapter(sqlCom1, connectBD);
adapter.Fill(dataTable);
int countOfRows = dataTable.Rows.Count;
string strName;
DataTable dt = readerForName.GetSchemaTable();
if (countOfRows > 0)
{
int i = 0;
while (readerForName.Read())
{
strName = Convert.ToString(readerForName.GetString(readerForName.GetOrdinal("name")));
Label labelNameOfProduct = new Label();
labelNameOfProduct.Content = strName;
labelNameOfProduct.Foreground = new SolidColorBrush(Colors.White);
labelNameOfProduct.FontSize = 14;
CheckBox checkBoxForIngredient = new CheckBox();
checkBoxForIngredient.HorizontalAlignment = HorizontalAlignment.Left;
checkBoxForIngredient.HorizontalContentAlignment = HorizontalAlignment.Left;
// checkBoxForIngredient.IsEnabledChanged += new EventHandler(this.ChoiceCheckBox);
//checkBoxForIngredient.IsEnabledChanged += (s, e) => MessageBox.Show("Clicked checkbox " + i);
gridForIngredients.RowDefinitions.Add(new RowDefinition());
gridForIngredients.ShowGridLines = true;
Grid.SetColumn(labelNameOfProduct, 0);
Grid.SetRow(labelNameOfProduct, i);
Grid.SetColumn(checkBoxForIngredient, 1);
Grid.SetRow(checkBoxForIngredient, i);
gridForIngredients.Children.Add(labelNameOfProduct);
gridForIngredients.Children.Add(checkBoxForIngredient);
i++;
}
readerForName.Close();
}
else
{
Label labelAmpty = new Label();
labelAmpty.Content = " ";
stackPanelIngredient.Children.Add(labelAmpty);
}
}
catch (SQLiteException)
{
MessageBox.Show("Нет соединения!", "Окно CreatingDishByIngredient");
}
catch (Exception)
{
MessageBox.Show("Ошибка!", "Предупреждение");
}
}
}
private void tv_connections_NodeMouseClick(object sender, TreeNodeMouseClickEventArgs e)
{
if (e.Button == MouseButtons.Right)
{
if (e.Node.Name == "PATH")
{
e.Node.ContextMenuStrip = menu_connect;
curr_node = e.Node;
//menu_connect.Show();
}
else if (e.Node.Name == "CONN")
{
e.Node.ContextMenuStrip = menu_connection;
curr_node = e.Node;
}
else if (e.Node.Name == "TABLE")
{
e.Node.ContextMenuStrip = menu_table;
curr_node = e.Node;
}
}
txt_status.Text = e.Node.Name;
if (e.Node.Name == "TABLE" || e.Node.Name == "VIEW")
{
var connId = Convert.ToInt32(e.Node.Tag);
txt_query.Text = "";
dg_data.DataSource = null;
tbl = obj_sql.Execute_DataTable("SELECT sql FROM sqlite_master WHERE tbl_name='"
+ e.Node.Text + "'", connId);
if (tbl.Rows.Count != 0)
{
txt_query.Text = tbl.Rows[0][0].ToString();
}
else
{
txt_status.Text = obj_sql.WorkerResult.Message;
return;
}
// MessageBox.Show(e.Node.Tag.ToString());
tbl = obj_sql.Execute_DataTable("SELECT * from " + e.Node.Text + " limit 100"
, connId);
if (tbl.Rows.Count != 0)
{
dg_data.DataSource = tbl;
}
else
{
txt_status.Text = obj_sql.WorkerResult.Message;
}
using (SQLiteDataReader dr = obj_sql.Execute_DataReader($"SELECT * FROM {e.Node.Text} WHERE 1=2",
connId))
{
dg_struct.DataSource = dr.GetSchemaTable().AsEnumerable()
.Select(x => new
{
ColumnOrdinal = x.Field <int>("ColumnOrdinal"),
ColumnName = x.Field <string>("ColumnName"),
DataTypeName = x.Field <string>("DataTypeName"),
AllowDBNull = x.Field <bool>("AllowDBNull"),
IsKey = x.Field <bool>("IsKey"),
IsAutoIncrement = x.Field <bool>("IsAutoIncrement")
}).ToList();
}
}
}
/// <summary>
/// This function does all the nasty work at determining what keys need to be returned for
/// a given statement.
/// </summary>
/// <param name="cnn"></param>
/// <param name="reader"></param>
/// <param name="stmt"></param>
internal SQLiteKeyReader(SQLiteConnection cnn, SQLiteDataReader reader, SQLiteStatement stmt)
{
Dictionary<string, int> catalogs = new Dictionary<string, int>();
Dictionary<string, List<string>> tables = new Dictionary<string, List<string>>();
List<string> list;
List<KeyInfo> keys = new List<KeyInfo>();
// Record the statement so we can use it later for sync'ing
_stmt = stmt;
// Fetch all the attached databases on this connection
using (DataTable tbl = cnn.GetSchema("Catalogs"))
{
foreach (DataRow row in tbl.Rows)
{
catalogs.Add((string)row["CATALOG_NAME"], Convert.ToInt32(row["ID"], CultureInfo.InvariantCulture));
}
}
// Fetch all the unique tables and catalogs used by the current statement
using (DataTable schema = reader.GetSchemaTable(false, false))
{
foreach (DataRow row in schema.Rows)
{
// Check if column is backed to a table
if (row[SchemaTableOptionalColumn.BaseCatalogName] == DBNull.Value)
continue;
// Record the unique table so we can look up its keys
string catalog = (string)row[SchemaTableOptionalColumn.BaseCatalogName];
string table = (string)row[SchemaTableColumn.BaseTableName];
if (tables.ContainsKey(catalog) == false)
{
list = new List<string>();
tables.Add(catalog, list);
}
else
list = tables[catalog];
if (list.Contains(table) == false)
list.Add(table);
}
// For each catalog and each table, query the indexes for the table.
// Find a primary key index if there is one. If not, find a unique index instead
foreach (KeyValuePair<string, List<string>> pair in tables)
{
for (int i = 0; i < pair.Value.Count; i++)
{
string table = pair.Value[i];
DataRow preferredRow = null;
using (DataTable tbl = cnn.GetSchema("Indexes", new string[] { pair.Key, null, table }))
{
// Loop twice. The first time looking for a primary key index,
// the second time looking for a unique index
for (int n = 0; n < 2 && preferredRow == null; n++)
{
foreach (DataRow row in tbl.Rows)
{
if (n == 0 && (bool)row["PRIMARY_KEY"] == true)
{
preferredRow = row;
break;
}
else if (n == 1 && (bool)row["UNIQUE"] == true)
{
preferredRow = row;
break;
}
}
}
if (preferredRow == null) // Unable to find any suitable index for this table so remove it
{
pair.Value.RemoveAt(i);
i--;
}
else // We found a usable index, so fetch the necessary table details
{
using (DataTable tblTables = cnn.GetSchema("Tables", new string[] { pair.Key, null, table }))
{
// Find the root page of the table in the current statement and get the cursor that's iterating it
int database = catalogs[pair.Key];
int rootPage = Convert.ToInt32(tblTables.Rows[0]["TABLE_ROOTPAGE"], CultureInfo.InvariantCulture);
int cursor = stmt._sql.GetCursorForTable(stmt, database, rootPage);
// Now enumerate the members of the index we're going to use
using (DataTable indexColumns = cnn.GetSchema("IndexColumns", new string[] { pair.Key, null, table, (string)preferredRow["INDEX_NAME"] }))
{
KeyQuery query = null;
List<string> cols = new List<string>();
for (int x = 0; x < indexColumns.Rows.Count; x++)
{
bool addKey = true;
// If the column in the index already appears in the query, skip it
foreach (DataRow row in schema.Rows)
{
if (row.IsNull(SchemaTableColumn.BaseColumnName))
continue;
if ((string)row[SchemaTableColumn.BaseColumnName] == (string)indexColumns.Rows[x]["COLUMN_NAME"] &&
(string)row[SchemaTableColumn.BaseTableName] == table &&
(string)row[SchemaTableOptionalColumn.BaseCatalogName] == pair.Key)
{
indexColumns.Rows.RemoveAt(x);
x--;
addKey = false;
break;
}
}
if (addKey == true)
cols.Add((string)indexColumns.Rows[x]["COLUMN_NAME"]);
}
// If the index is not a rowid alias, record all the columns
// needed to make up the unique index and construct a SQL query for it
if ((string)preferredRow["INDEX_NAME"] != "sqlite_master_PK_" + table)
{
// Whatever remains of the columns we need that make up the index that are not
// already in the query need to be queried separately, so construct a subquery
if (cols.Count > 0)
{
string[] querycols = new string[cols.Count];
cols.CopyTo(querycols);
query = new KeyQuery(cnn, pair.Key, table, querycols);
}
}
// Create a KeyInfo struct for each column of the index
for (int x = 0; x < indexColumns.Rows.Count; x++)
{
string columnName = (string)indexColumns.Rows[x]["COLUMN_NAME"];
KeyInfo key = new KeyInfo();
key.rootPage = rootPage;
key.cursor = cursor;
key.database = database;
key.databaseName = pair.Key;
key.tableName = table;
key.columnName = columnName;
key.query = query;
key.column = x;
keys.Add(key);
}
}
}
}
}
}
}
}
// Now we have all the additional columns we have to return in order to support
// CommandBehavior.KeyInfo
_keyInfo = new KeyInfo[keys.Count];
keys.CopyTo(_keyInfo);
}
