本文整理汇总了C#中SqlDataProvider.ExecuteSql方法的典型用法代码示例。如果您正苦于以下问题：C# SqlDataProvider.ExecuteSql方法的具体用法？C# SqlDataProvider.ExecuteSql怎么用？C# SqlDataProvider.ExecuteSql使用的例子？那么恭喜您, 这里精选的方法代码示例或许可以为您提供帮助。您也可以进一步了解该方法所在类SqlDataProvider的用法示例。
在下文中一共展示了SqlDataProvider.ExecuteSql方法的3个代码示例，这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞，您的评价将有助于系统推荐出更棒的C#代码示例。
示例1: CreateOrUpdateTable
public override void CreateOrUpdateTable(OrmSchema schema, bool recreateTable, bool recreateIndexes, SqlDataProvider dataProvider)
{
const string longTextType = "TEXT";
var columnMappings = new[]
{
new {Flags = TypeFlags.Boolean, ColumnType = "TINYINT"},
new {Flags = TypeFlags.Integer, ColumnType = "INTEGER"},
new {Flags = TypeFlags.Decimal, ColumnType = "DECIMAL({0},{1})"},
new {Flags = TypeFlags.FloatingPoint, ColumnType = "REAL"},
new {Flags = TypeFlags.String, ColumnType = "VARCHAR({0})"},
new {Flags = TypeFlags.Array | TypeFlags.Byte, ColumnType = "LONGBLOB"},
new {Flags = TypeFlags.DateTime, ColumnType = "DATETIME"}
};
if (recreateTable)
recreateIndexes = true;
HashSet<string> tableNames = new HashSet<string>(dataProvider.ExecuteSqlReader("SELECT name FROM sqlite_master WHERE type='table'", null).Select(rec => rec["name"].ToString()),StringComparer.OrdinalIgnoreCase);
if (tableNames.Contains(schema.MappedName) && recreateTable)
{
dataProvider.ExecuteSql("DROP TABLE " + QuoteTable(schema.MappedName), null);
}
var existingColumns = dataProvider.ExecuteSqlReader("pragma table_info(" + QuoteTable(schema.MappedName) + ")", null).ToLookup(rec => rec["name"].ToString());
var parts = new List<string>();
bool createNew = true;
foreach (var field in schema.Fields)
{
var columnMapping = columnMappings.FirstOrDefault(mapping => field.FieldInfo.TypeInspector.Is(mapping.Flags));
if (columnMapping == null)
continue;
if (existingColumns.Contains(field.MappedName) && !recreateTable)
{
createNew = false;
continue;
}
if (columnMapping.Flags == TypeFlags.String && field.ColumnSize == int.MaxValue)
columnMapping = new { columnMapping.Flags, ColumnType = longTextType };
var part = string.Format("{0} {1}", QuoteField(field.MappedName), string.Format(columnMapping.ColumnType, field.ColumnSize, field.ColumnScale));
if (!field.ColumnNullable || field.PrimaryKey)
part += " NOT";
part += " NULL";
if (field.PrimaryKey && schema.PrimaryKeys.Length == 1)
{
part += " PRIMARY KEY";
if (field.AutoIncrement)
part += " AUTOINCREMENT";
}
parts.Add(part);
}
if (parts.Any() && schema.PrimaryKeys.Length > 1)
{
parts.Add("PRIMARY KEY (" + string.Join(",", schema.PrimaryKeys.Select(pk => QuoteField(pk.MappedName))) + ")");
}
if (parts.Any())
{
if (createNew)
{
dataProvider.ExecuteSql("CREATE TABLE " + QuoteTable(schema.MappedName) + " (" + string.Join(",", parts) + ")", null);
}
else
{
foreach (var part in parts)
{
dataProvider.ExecuteSql("ALTER TABLE " + QuoteTable(schema.MappedName) + " ADD COLUMN " + part + ";", null);
}
}
}
var existingIndexes = dataProvider.ExecuteSqlReader("PRAGMA INDEX_LIST(' " + schema.MappedName + "')", null).ToLookup(rec => rec["name"].ToString());
foreach (var index in schema.Indexes)
{
if (existingIndexes[index.Name].Any())
{
if (recreateIndexes)
dataProvider.ExecuteSql("DROP INDEX " + QuoteTable(index.Name) + " ON " + QuoteTable(schema.MappedName), null);
else
continue;
}
string createIndexSql = "CREATE INDEX " + QuoteTable(index.Name) + " ON " + QuoteTable(schema.MappedName) + " (";
//.........这里部分代码省略.........
示例2: CreateOrUpdateTable
public override void CreateOrUpdateTable(OrmSchema schema, bool recreateTable, bool recreateIndexes, SqlDataProvider dataProvider)
{
const string longTextType = "TEXT";
var columnMappings = new[]
{
new {Flags = TypeFlags.Boolean, ColumnType = "BIT"},
new {Flags = TypeFlags.Integer8, ColumnType = "TINYINT"},
new {Flags = TypeFlags.Integer16, ColumnType = "SMALLINT"},
new {Flags = TypeFlags.Integer32, ColumnType = "INT"},
new {Flags = TypeFlags.Integer64, ColumnType = "BIGINT"},
new {Flags = TypeFlags.Decimal, ColumnType = "DECIMAL({0},{1})"},
new {Flags = TypeFlags.Double, ColumnType = "FLOAT"},
new {Flags = TypeFlags.Single, ColumnType = "REAL"},
new {Flags = TypeFlags.String, ColumnType = "VARCHAR({0})"},
new {Flags = TypeFlags.Array | TypeFlags.Byte, ColumnType = "IMAGE"},
new {Flags = TypeFlags.DateTime, ColumnType = "DATETIME"}
};
string[] tableNameParts = schema.MappedName.Split('.');
string tableSchemaName = tableNameParts.Length == 1 ? "dbo" : tableNameParts[0];
string tableName = tableNameParts.Length == 1 ? tableNameParts[0] : tableNameParts[1];
var existingColumns = dataProvider.ExecuteSqlReader("select * from INFORMATION_SCHEMA.COLUMNS where [email protected] and [email protected]",
new QueryParameterCollection(new { schema = tableSchemaName, name = tableName })).ToLookup(rec => rec["COLUMN_NAME"].ToString());
var tableExists = dataProvider.ExecuteSqlReader("select count(*) from INFORMATION_SCHEMA.TABLES where [email protected] and [email protected]",
new QueryParameterCollection(new {schema = tableSchemaName, name = tableName})).Select(rec => rec.First().Value.Convert<int>()).First() == 1;
var parts = new List<string>();
bool createNew = true;
foreach (var field in schema.Fields)
{
var columnMapping = columnMappings.FirstOrDefault(mapping => field.FieldInfo.TypeInspector.Is(mapping.Flags));
if (columnMapping == null)
continue;
if (existingColumns.Contains(field.MappedName) && !recreateTable)
{
createNew = false;
continue;
}
if (columnMapping.Flags == TypeFlags.String && field.ColumnSize == int.MaxValue)
columnMapping = new { columnMapping.Flags, ColumnType = longTextType };
var part = string.Format("{0} {1}", QuoteField(field.MappedName), string.Format(columnMapping.ColumnType, field.ColumnSize, field.ColumnScale));
if (!field.ColumnNullable || field.PrimaryKey)
part += " NOT";
part += " NULL";
if (field.AutoIncrement)
part += " IDENTITY(1,1)";
parts.Add(part);
}
if (parts.Any() && schema.PrimaryKeys.Length > 0)
{
parts.Add("PRIMARY KEY (" + string.Join(",", schema.PrimaryKeys.Select(pk => QuoteField(pk.MappedName))) + ")");
}
if (recreateTable && tableExists)
dataProvider.ExecuteSql("DROP TABLE " + QuoteTable(schema.MappedName), null);
if (parts.Any())
{
string sql = (createNew ? "CREATE TABLE " : "ALTER TABLE ") + QuoteTable(schema.MappedName);
sql += createNew ? " (" : " ADD ";
sql += string.Join(",", parts);
if (createNew)
sql += ")";
dataProvider.ExecuteSql(sql, null);
}
var existingIndexes = dataProvider.ExecuteSqlReader("SELECT ind.name as IndexName FROM sys.indexes ind INNER JOIN sys.tables t ON ind.object_id = t.object_id WHERE ind.name is not null and ind.is_primary_key = 0 AND t.is_ms_shipped = 0 AND [email protected]",
new QueryParameterCollection(new { tableName })).ToLookup(rec => rec["IndexName"].ToString());
foreach (var index in schema.Indexes)
{
if (existingIndexes["IX_" + index.Name].Any())
{
if (recreateIndexes || recreateTable)
dataProvider.ExecuteSql($"DROP INDEX {QuoteTable("IX_" + index.Name)} ON {QuoteTable(schema.MappedName)}", null);
else
continue;
}
string createIndexSql = $"CREATE INDEX {QuoteTable("IX_" + index.Name)} ON {QuoteTable(schema.MappedName)} (";
createIndexSql += string.Join(",", index.FieldsWithOrder.Select(field => QuoteField(field.Item1.MappedName) + " " + (field.Item2 == SortOrder.Ascending ? "ASC" : "DESC")));
//.........这里部分代码省略.........
示例3: CreateOrUpdateTable
public override void CreateOrUpdateTable(OrmSchema schema, bool recreateTable, bool recreateIndexes, SqlDataProvider datProvider)
{
const string longTextType = "LONGTEXT";
var columnMappings = new[]
{
new {Flags = TypeFlags.Boolean, ColumnType = "BOOLEAN"},
new {Flags = TypeFlags.Byte, ColumnType = "TINYINT UNSIGNED"},
new {Flags = TypeFlags.SByte, ColumnType = "TINYINT"},
new {Flags = TypeFlags.Int16, ColumnType = "SMALLINT"},
new {Flags = TypeFlags.UInt16, ColumnType = "SMALLINT UNSIGNED"},
new {Flags = TypeFlags.Int32, ColumnType = "INT"},
new {Flags = TypeFlags.UInt32, ColumnType = "INT UNSIGNED"},
new {Flags = TypeFlags.Int64, ColumnType = "BIGINT"},
new {Flags = TypeFlags.UInt64, ColumnType = "BIGINT UNSIGNED"},
new {Flags = TypeFlags.Decimal, ColumnType = "DECIMAL({0},{1})"},
new {Flags = TypeFlags.Double, ColumnType = "DOUBLE"},
new {Flags = TypeFlags.Single, ColumnType = "FLOAT"},
new {Flags = TypeFlags.String, ColumnType = "VARCHAR({0})"},
new {Flags = TypeFlags.Array | TypeFlags.Byte, ColumnType = "LONGBLOB"},
new {Flags = TypeFlags.DateTime, ColumnType = "DATETIME"}
};
if (recreateTable)
recreateIndexes = true;
var existingColumns = datProvider.ExecuteSqlReader("select * from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA=DATABASE() and [email protected]", new QueryParameterCollection(new { name = schema.MappedName })).ToLookup(rec => rec["COLUMN_NAME"].ToString());
var parts = new List<string>();
bool createNew = true;
foreach (var field in schema.Fields)
{
var columnMapping = columnMappings.FirstOrDefault(mapping => field.FieldInfo.TypeInspector.Is(mapping.Flags));
if (columnMapping == null)
continue;
if (existingColumns.Contains(field.MappedName)/* && !recreateTable*/)
{
createNew = false;
continue;
}
if (columnMapping.Flags == TypeFlags.String && field.ColumnSize == int.MaxValue)
columnMapping = new { columnMapping.Flags, ColumnType = longTextType };
var part = string.Format("{0} {1}", QuoteField(field.MappedName), string.Format(columnMapping.ColumnType, field.ColumnSize, field.ColumnScale));
if (!field.ColumnNullable)
part += " NOT";
part += " NULL";
// if (field.PrimaryKey)
// part += " PRIMARY KEY";
if (field.AutoIncrement)
part += " AUTO_INCREMENT";
parts.Add(part);
}
if (parts.Any() && schema.PrimaryKeys.Length > 0 && createNew)
{
parts.Add("PRIMARY KEY (" + string.Join(",", schema.PrimaryKeys.Select(pk => QuoteField(pk.MappedName))) + ")");
}
if (!parts.Any())
return;
string sql = (createNew ? "CREATE TABLE " : "ALTER TABLE ") + QuoteTable(schema.MappedName);
if (createNew)
sql += " (";
if (createNew)
sql += string.Join(",", parts);
else
sql += string.Join(",", parts.Select(s => "ADD COLUMN " + s));
if (createNew)
sql += ")";
datProvider.ExecuteSql(sql, null);
}