/// <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); }