?? sqliteconnection.cs
字號:
// The only way to figure it out is to check if the index was an autoindex and if we have a non-rowid
// primary key, and all the columns in the given index match the primary key columns
if (primaryKeys.Count > 0 && rd.GetString(1).StartsWith("sqlite_autoindex_" + rdTables.GetString(2), StringComparison.InvariantCultureIgnoreCase) == true)
{
using (SQLiteCommand cmdDetails = new SQLiteCommand(String.Format(CultureInfo.InvariantCulture, "PRAGMA [{0}].index_info([{1}])", strCatalog, rd.GetString(1)), this))
using (SQLiteDataReader rdDetails = cmdDetails.ExecuteReader())
{
int nMatches = 0;
while (rdDetails.Read())
{
if (primaryKeys.Contains(rdDetails.GetInt32(1)) == false)
{
nMatches = 0;
break;
}
nMatches++;
}
if (nMatches == primaryKeys.Count)
{
row["PRIMARY_KEY"] = true;
primaryKeys.Clear();
}
}
}
tbl.Rows.Add(row);
}
}
}
}
}
}
tbl.AcceptChanges();
tbl.EndLoadData();
return tbl;
}
/// <summary>
/// Retrieves table schema information for the database and catalog
/// </summary>
/// <param name="strCatalog">The catalog (attached database) to retrieve tables on</param>
/// <param name="strTable">The table to retrieve, can be null</param>
/// <param name="strType">The table type, can be null</param>
/// <returns>DataTable</returns>
private DataTable Schema_Tables(string strCatalog, string strTable, string strType)
{
DataTable tbl = new DataTable("Tables");
DataRow row;
string strItem;
tbl.Locale = CultureInfo.InvariantCulture;
tbl.Columns.Add("TABLE_CATALOG", typeof(string));
tbl.Columns.Add("TABLE_SCHEMA", typeof(string));
tbl.Columns.Add("TABLE_NAME", typeof(string));
tbl.Columns.Add("TABLE_TYPE", typeof(string));
tbl.Columns.Add("TABLE_ID", typeof(long));
tbl.Columns.Add("TABLE_ROOTPAGE", typeof(int));
tbl.BeginLoadData();
if (String.IsNullOrEmpty(strCatalog)) strCatalog = "main";
using (SQLiteCommand cmd = new SQLiteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT [type], [name], [tbl_name], [rootpage], [sql], [rowid] FROM [{0}].[sqlite_master] WHERE [type] LIKE 'table'", strCatalog), this))
using (SQLiteDataReader rd = (SQLiteDataReader)cmd.ExecuteReader())
{
while (rd.Read())
{
strItem = rd.GetString(0);
if (String.Compare(rd.GetString(2), 0, "SQLITE_", 0, 7, true, CultureInfo.InvariantCulture) == 0)
strItem = "SYSTEM_TABLE";
if (String.Compare(strType, strItem, true, CultureInfo.InvariantCulture) == 0
|| strType == null)
{
if (String.Compare(rd.GetString(2), strTable, true, CultureInfo.InvariantCulture) == 0
|| strTable == null)
{
row = tbl.NewRow();
row["TABLE_CATALOG"] = strCatalog;
row["TABLE_NAME"] = rd.GetString(2);
row["TABLE_TYPE"] = strItem;
row["TABLE_ID"] = rd.GetInt64(5);
row["TABLE_ROOTPAGE"] = rd.GetInt32(3);
tbl.Rows.Add(row);
}
}
}
}
tbl.AcceptChanges();
tbl.EndLoadData();
return tbl;
}
/// <summary>
/// Retrieves view schema information for the database
/// </summary>
/// <param name="strCatalog">The catalog (attached database) to retrieve views on</param>
/// <param name="strView">The view name, can be null</param>
/// <returns>DataTable</returns>
private DataTable Schema_Views(string strCatalog, string strView)
{
DataTable tbl = new DataTable("Views");
DataRow row;
string strItem;
int nPos;
tbl.Locale = CultureInfo.InvariantCulture;
tbl.Columns.Add("TABLE_CATALOG", typeof(string));
tbl.Columns.Add("TABLE_SCHEMA", typeof(string));
tbl.Columns.Add("TABLE_NAME", typeof(string));
tbl.Columns.Add("VIEW_DEFINITION", typeof(string));
tbl.Columns.Add("CHECK_OPTION", typeof(bool));
tbl.Columns.Add("IS_UPDATABLE", typeof(bool));
tbl.Columns.Add("DESCRIPTION", typeof(string));
tbl.Columns.Add("DATE_CREATED", typeof(DateTime));
tbl.Columns.Add("DATE_MODIFIED", typeof(DateTime));
tbl.BeginLoadData();
if (String.IsNullOrEmpty(strCatalog)) strCatalog = "main";
using (SQLiteCommand cmd = new SQLiteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[sqlite_master] WHERE [type] LIKE 'view'", strCatalog), this))
using (SQLiteDataReader rd = (SQLiteDataReader)cmd.ExecuteReader())
{
while (rd.Read())
{
if (String.Compare(rd.GetString(1), strView, true, CultureInfo.InvariantCulture) == 0
|| String.IsNullOrEmpty(strView))
{
strItem = rd.GetString(4).Replace('\r', ' ').Replace('\n', ' ').Replace('\t', ' ');
nPos = Globalization.CultureInfo.InvariantCulture.CompareInfo.IndexOf(strItem, " AS ", CompareOptions.IgnoreCase);
if (nPos > -1)
{
strItem = strItem.Substring(nPos + 4).Trim();
row = tbl.NewRow();
row["TABLE_CATALOG"] = strCatalog;
row["TABLE_NAME"] = rd.GetString(2);
row["IS_UPDATABLE"] = false;
row["VIEW_DEFINITION"] = strItem;
tbl.Rows.Add(row);
}
}
}
}
tbl.AcceptChanges();
tbl.EndLoadData();
return tbl;
}
/// <summary>
/// Retrieves catalog (attached databases) schema information for the database
/// </summary>
/// <param name="strCatalog">The catalog to retrieve, can be null</param>
/// <returns>DataTable</returns>
private DataTable Schema_Catalogs(string strCatalog)
{
DataTable tbl = new DataTable("Catalogs");
DataRow row;
tbl.Locale = CultureInfo.InvariantCulture;
tbl.Columns.Add("CATALOG_NAME", typeof(string));
tbl.Columns.Add("DESCRIPTION", typeof(string));
tbl.Columns.Add("ID", typeof(long));
tbl.BeginLoadData();
using (SQLiteCommand cmd = new SQLiteCommand("PRAGMA database_list", this))
using (SQLiteDataReader rd = (SQLiteDataReader)cmd.ExecuteReader())
{
while (rd.Read())
{
if (String.Compare(rd.GetString(1), strCatalog, true, CultureInfo.InvariantCulture) == 0
|| strCatalog == null)
{
row = tbl.NewRow();
row["CATALOG_NAME"] = rd.GetString(1);
row["DESCRIPTION"] = rd.GetString(2);
row["ID"] = rd.GetInt64(0);
tbl.Rows.Add(row);
}
}
}
tbl.AcceptChanges();
tbl.EndLoadData();
return tbl;
}
private DataTable Schema_DataTypes()
{
DataTable tbl = new DataTable("DataTypes");
tbl.Locale = CultureInfo.InvariantCulture;
tbl.Columns.Add("TypeName", typeof(String));
tbl.Columns.Add("ProviderDbType", typeof(int));
tbl.Columns.Add("ColumnSize", typeof(long));
tbl.Columns.Add("CreateFormat", typeof(String));
tbl.Columns.Add("CreateParameters", typeof(String));
tbl.Columns.Add("DataType", typeof(String));
tbl.Columns.Add("IsAutoIncrementable", typeof(bool));
tbl.Columns.Add("IsBestMatch", typeof(bool));
tbl.Columns.Add("IsCaseSensitive", typeof(bool));
tbl.Columns.Add("IsFixedLength", typeof(bool));
tbl.Columns.Add("IsFixedPrecisionScale", typeof(bool));
tbl.Columns.Add("IsLong", typeof(bool));
tbl.Columns.Add("IsNullable", typeof(bool));
tbl.Columns.Add("IsSearchable", typeof(bool));
tbl.Columns.Add("IsSearchableWithLike", typeof(bool));
tbl.Columns.Add("IsLiteralSupported", typeof(bool));
tbl.Columns.Add("LiteralPrefix", typeof(String));
tbl.Columns.Add("LiteralSuffix", typeof(String));
tbl.Columns.Add("IsUnsigned", typeof(bool));
tbl.Columns.Add("MaximumScale", typeof(short));
tbl.Columns.Add("MinimumScale", typeof(short));
tbl.Columns.Add("IsConcurrencyType", typeof(bool));
tbl.BeginLoadData();
IO.StringReader reader = new IO.StringReader(SR.DataTypes);
tbl.ReadXml(reader);
reader.Close();
tbl.AcceptChanges();
tbl.EndLoadData();
return tbl;
}
/// <summary>
/// Returns the base column information for indexes in a database
/// </summary>
/// <param name="strCatalog">The catalog to retrieve indexes for (can be null)</param>
/// <param name="strTable">The table to restrict index information by (can be null)</param>
/// <param name="strIndex">The index to restrict index information by (can be null)</param>
/// <param name="strColumn">The source column to restrict index information by (can be null)</param>
/// <returns>A DataTable containing the results</returns>
private DataTable Schema_IndexColumns(string strCatalog, string strTable, string strIndex, string strColumn)
{
DataTable tbl = new DataTable("IndexColumns");
DataRow row;
List<KeyValuePair<int, string>> primaryKeys = new List<KeyValuePair<int, string>>();
bool maybeRowId;
tbl.Locale = CultureInfo.InvariantCulture;
tbl.Columns.Add("CONSTRAINT_CATALOG", typeof(string));
tbl.Columns.Add("CONSTRAINT_SCHEMA", typeof(string));
tbl.Columns.Add("CONSTRAINT_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("INDEX_NAME", typeof(string));
if (String.IsNullOrEmpty(strCatalog)) strCatalog = "main";
tbl.BeginLoadData();
using (SQLiteCommand cmdTables = new SQLiteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[sqlite_master] WHERE [type] LIKE 'table'", strCatalog), this))
using (SQLiteDataReader rdTables = cmdTables.ExecuteReader())
{
while (rdTables.Read())
{
maybeRowId = false;
primaryKeys.Clear();
if (String.IsNullOrEmpty(strTable) || String.Compare(rdTables.GetString(2), strTable, true, CultureInfo.InvariantCulture) == 0)
{
using (SQLiteCommand cmdTable = new SQLiteCommand(String.Format(CultureInfo.InvariantCulture, "PRAGMA [{0}].table_info([{1}])", strCatalog, rdTables.GetString(2)), this))
using (SQLiteDataReader rdTable = cmdTable.ExecuteReader())
{
while (rdTable.Read())
{
if (rdTable.GetInt32(5) == 1) // is a primary key
{
primaryKeys.Add(new KeyValuePair<int, string>(rdTable.GetInt32(0), rdTable.GetString(1)));
// Is an integer -- could be a rowid if no other primary keys exist in the table
if (String.Compare(rdTable.GetString(2), "INTEGER", true, CultureInfo.InvariantCulture) == 0)
maybeRowId = true;
}
}
}
if (primaryKeys.Count == 1 && maybeRowId == true)
{
row = tbl.NewRow();
row["CONSTRAINT_CATALOG"] = strCatalog;
row["CONSTRAINT_NAME"] = String.Format(CultureInfo.InvariantCulture, "sqlite_master_PK_{0}", rdTables.GetString(2));
row["TABLE_CATALOG"] = strCatalog;
row["TABLE_NAME"] = rdTables.GetString(2);
row["COLUMN_NAME"] = primaryKeys[0].Value;
row["INDEX_NAME"] = row["CONSTRAINT_NAME"];
row["ORDINAL_POSITION"] = primaryKeys[0].Key;
if (String.IsNullOrEmpty(strIndex) || String.Compare(strIndex, (string)row["INDEX_NAME"], true, CultureInfo.InvariantCulture) == 0)
tbl.Rows.Add(row);
}
using (SQLiteCommand cmdIndexes = new SQLiteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[sqlite_master] WHERE [type] LIKE 'index' AND [tbl_name] LIKE '{1}'", strCatalog, rdTables.GetString(2).Replace("'", "''")), this))
using (SQLiteDataReader rdIndexes = cmdIndexes.ExecuteReader())
{
while (rdIndexes.Read())
{
if (String.IsNullOrEmpty(strIndex) || String.Compare(strIndex, rdIndexes.GetString(1), true, CultureInfo.InvariantCulture) == 0)
{
using (SQLiteCommand cmdIndex = new SQLiteCommand(String.Format(CultureInfo.InvariantCulture, "PRAGMA [{0}].index_info([{1}])", strCatalog, rdIndexes.GetString(1)), this))
using (SQLiteDataReader rdIndex = cmdIndex.ExecuteReader())
{
while (rdIndex.Read())
{
row = tbl.NewRow();
row["CONSTRAINT_CATALOG"] = strCatalog;
row["CONSTRAINT_NAME"] = rdIndexes.GetString(1);
row["TABLE_CATALOG"] = strCatalog;
row["TABLE_NAME"] = rdIndexes.GetString(2);
row["COLUMN_NAME"] = rdIndex.GetString(2);
row["INDEX_NAME"] = rdIndexes.GetString(1);
row["ORDINAL_POSITION"] = rdIndex.GetInt32(1);
if (String.IsNullOrEmpty(?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -