private IList<ScalarFunction> queryForScalarFunctions(Schema schema)
{
var sql = new Sql(@"SELECT
J.[name] AS FunctionName
, J.object_id AS FunctionId
, EP.value AS [Description]
, R.DATA_TYPE AS ReturnDataType
, R.CHARACTER_MAXIMUM_LENGTH AS ReturnTypeMaximumLength
, CONVERT(INT, R.NUMERIC_PRECISION) AS ReturnTypePrecision
, CONVERT(INT, R.NUMERIC_SCALE) AS ReturnTypeScale
FROM sys.objects AS J
INNER JOIN sys.schemas AS S
ON ( J.schema_id = S.schema_id )
INNER JOIN INFORMATION_SCHEMA.ROUTINES AS R
ON ( R.SPECIFIC_SCHEMA = S.[name] AND R.SPECIFIC_NAME = J.[name] )
LEFT OUTER JOIN sys.extended_properties AS EP
ON ( EP.class = 1 AND EP.name = 'MS_Description' AND EP.major_id = J.object_id AND EP.minor_id = 0 )
WHERE J.schema_id = @0
AND J.[type] = 'FN'
ORDER BY J.[name];", schema.SchemaId);
return this.peta.Fetch<ScalarFunction>(sql);
}
public void SyncData()
{
try
{
//查询最后一次同步时间
channelhistoryarchivesync syncTime = channelhistoryarchivesync.SingleOrDefault("where id=1");
if (syncTime == null)
{
syncTime = new channelhistoryarchivesync();
syncTime.date = DateTime.Now.AddYears(-1);
syncTime.date_created = DateTime.Now;
syncTime.Insert();
}
DateTime syncDate = DateTime.Now.Date;
//查询之前没有同步的数据
Sql channelhistorySql = new Sql();
channelhistorySql.Append(
"select channelNo,DATE_FORMAT(date_created,'%Y-%m-%d') as datestr,count(id) as visitcount")
.Append("from channelHistory")
.Append("WHERE date_created > @0", syncTime.date)
.Append("WHERE date_created <= @0", syncDate)
.Append("group by datestr,channelNo");
IEnumerable<channelhistory> channelhistoryDayList = videoContextDB.GetInstance().Query<channelhistory>(channelhistorySql);
foreach (var channelhistory in channelhistoryDayList)
{
bool isExists = channelhistoryarchive.Exists(
"where [email protected] and [email protected]",
channelhistory.channelNo,
channelhistory.datestr);
if (!isExists)
{
channelhistoryarchive channelhistoryarchiveModel = new channelhistoryarchive();
channelhistoryarchiveModel.channelNo = channelhistory.channelNo;
channelhistoryarchiveModel.date = channelhistory.datestr;
channelhistoryarchiveModel.realcount = channelhistory.visitcount;
channelhistoryarchiveModel.date_created = DateTime.Now;
channelhistoryarchiveModel.paymentstate = false;
channelhistoryarchiveModel.Insert();
}
}
//同步的日期
syncTime.date = syncDate;
syncTime.Update();
this.OutPutDialogString(ResponseStatus.Success, "同步成功", "ChannelInfoArchiveList", string.Empty, CallbackType.forward, string.Empty);
}
catch (Exception ex)
{
this.OutPutDialogString(ResponseStatus.Fail, "同步失败", "ChannelInfoArchiveList", string.Empty, CallbackType.forward, string.Empty);
}
}
public void should_return_Sql_object_from_constructor_with_two_parameters()
{
string sqlStatemet = "SELECT * FROM User WHERE UserId = @0 AND UserName = @1";
object[] arguments = new object[2]{ 1, "UserName"};
Sql sql = new Sql(sqlStatemet, arguments);
Assert.IsTrue(sql.SQL == sqlStatemet);
Assert.IsTrue(sql.Arguments[0].Equals(1));
Assert.IsTrue(sql.Arguments[1].Equals("UserName"));
}
public void single_arg()
{
var sql = new Sql();
sql.Append("arg @0", "a1");
Assert.AreEqual(sql.SQL, "arg @0");
Assert.AreEqual(sql.Arguments.Length, 1);
Assert.AreEqual(sql.Arguments[0], "a1");
}
public Sql Append(Sql sql)
{
if (_rhs != null)
_rhs.Append(sql);
else
_rhs = sql;
return this;
}
public void simple_append()
{
var sql = new Sql();
sql.Append("LINE 1");
sql.Append("LINE 2");
sql.Append("LINE 3");
Assert.AreEqual(sql.SQL, "LINE 1\nLINE 2\nLINE 3");
Assert.AreEqual(sql.Arguments.Length, 0);
}
public void multiple_args()
{
var sql = new Sql();
sql.Append("arg @0 @1", "a1", "a2");
Assert.AreEqual(sql.SQL, "arg @0 @1");
Assert.AreEqual(sql.Arguments.Length, 2);
Assert.AreEqual(sql.Arguments[0], "a1");
Assert.AreEqual(sql.Arguments[1], "a2");
}
public void unordered_args()
{
var sql = new Sql();
sql.Append("arg @2 @1", "a1", "a2", "a3");
Assert.AreEqual(sql.SQL, "arg @0 @1");
Assert.AreEqual(sql.Arguments.Length, 2);
Assert.AreEqual(sql.Arguments[0], "a3");
Assert.AreEqual(sql.Arguments[1], "a2");
}
public void ConsecutiveOrderBy()
{
var sql = new Sql()
.Append("SELECT * FROM blah");
sql.Append("ORDER BY x");
sql.Append("ORDER BY y");
Expect(sql.SQL, Is.EqualTo("SELECT * FROM blah\nORDER BY x\n, y"));
}
public Sql Append(Sql sql)
{
if (this._rhs != null)
{
this._rhs.Append(sql);
}
else
{
this._rhs = sql;
}
return this;
}
public void repeated_args()
{
var sql = new Sql();
sql.Append("arg @0 @1 @0 @1", "a1", "a2");
Assert.AreEqual(sql.SQL, "arg @0 @1 @2 @3");
Assert.AreEqual(sql.Arguments.Length, 4);
Assert.AreEqual(sql.Arguments[0], "a1");
Assert.AreEqual(sql.Arguments[1], "a2");
Assert.AreEqual(sql.Arguments[2], "a1");
Assert.AreEqual(sql.Arguments[3], "a2");
}
public void append_with_args2()
{
var sql = new Sql();
sql.Append("l1");
sql.Append("l2 @0 @1", "a1", "a2");
sql.Append("l3 @0", "a3");
Expect(sql.SQL, Is.EqualTo("l1\nl2 @0 @1\nl3 @2"));
Expect(sql.Arguments.Length, Is.EqualTo(3));
Expect(sql.Arguments[0], Is.EqualTo("a1"));
Expect(sql.Arguments[1], Is.EqualTo("a2"));
Expect(sql.Arguments[2], Is.EqualTo("a3"));
}
public void append_instances()
{
var sql = new Sql("l0 @0", "a0");
var sql1 = new Sql("l1 @0", "a1");
var sql2 = new Sql("l2 @0", "a2");
Expect(sql.Append(sql1), Is.SameAs(sql));
Expect(sql.Append(sql2), Is.SameAs(sql));
Expect(sql.SQL, Is.EqualTo("l0 @0\nl1 @1\nl2 @2"));
Expect(sql.Arguments.Length, Is.EqualTo(3));
Expect(sql.Arguments[0], Is.EqualTo("a0"));
Expect(sql.Arguments[1], Is.EqualTo("a1"));
Expect(sql.Arguments[2], Is.EqualTo("a2"));
}
private IList<Table> queryForTables(Schema schema)
{
var sql = new Sql(@"SELECT T.name AS TableName
, COALESCE(EP.value, '') AS [Description]
, T.object_id AS TableId
FROM sys.tables AS T
LEFT OUTER JOIN sys.extended_properties AS EP
ON ( EP.major_id = T.object_id AND EP.minor_id = 0 AND EP.name = 'MS_Description' )
WHERE T.schema_id = @0
ORDER BY T.name", schema.SchemaId);
return this.peta.Fetch<Table>(sql);
}
private IList<View> getViewsForSchema(Schema schema)
{
var sql = new Sql(@"SELECT
V.object_id AS ViewId
, V.[name] AS ViewName
, EP.value AS [Description]
FROM sys.views AS V
LEFT OUTER JOIN sys.extended_properties AS EP
ON ( V.object_id = EP.major_id AND EP.class = 1 AND EP.minor_id = 0 AND EP.[name] = 'MS_Description' )
WHERE schema_id = @0
ORDER BY V.[name];", schema.SchemaId);
return this.peta.Fetch<View>(sql);
}
private IList<StoredProcedure> queryForStoredProcedures(Schema schema)
{
var sql = new Sql(@"SELECT
SP.[name] AS ProcedureName
, SP.object_id AS ProcedureId
, EP.value AS [Description]
FROM sys.procedures AS SP
LEFT OUTER JOIN sys.extended_properties AS EP
ON ( EP.class = 1 AND EP.name = 'MS_Description' AND EP.major_id = SP.object_id AND EP.minor_id = 0 )
WHERE SP.schema_id = @0
ORDER BY SP.[name];", schema.SchemaId);
return this.peta.Fetch<StoredProcedure>(sql);
}
private IList<string> getIndexColumnNames(Index index, IDbObject parent)
{
var sql = new Sql(@"SELECT C.[name]
FROM sys.indexes AS I
INNER JOIN sys.index_columns AS IC
ON ( I.index_id = IC.index_id AND I.object_id = IC.object_id )
INNER JOIN sys.columns AS C
ON ( C.object_id = IC.object_id AND C.column_id = IC.column_id )
WHERE I.object_id = @0
AND I.index_id = @1
ORDER BY IC.key_ordinal, C.[name];", parent.ObjectId, index.IndexId);
return this.peta.Fetch<string>(sql);
}
private List<Schema> queryForSchemas()
{
//TODO: add description metadata from extended properties
var sql = new Sql(@"SELECT S.[name] AS SchemaName
, S.schema_id AS SchemaId
, EP.value AS [Description]
FROM sys.schemas AS S
LEFT OUTER JOIN sys.extended_properties AS EP
ON ( S.schema_id = EP.major_id AND EP.name = 'MS_Description' )
WHERE S.[name] NOT LIKE 'db__%'
AND S.[name] NOT IN ( 'sys', 'INFORMATION_SCHEMA' )
ORDER BY S.[name];");
return this.peta.Fetch<Schema>(sql);
}
private IList<Index> getTableOrViewIndexes(IDbObject parent)
{
var sql = new Sql(@"SELECT I.[name] AS IndexName
, I.index_id AS IndexId
, EP.value AS [Description]
, I.is_unique AS IsUnique
, I.is_primary_key AS IsPrimaryKey
, I.type_desc AS IndexTypeDescription
FROM sys.indexes AS I
LEFT OUTER JOIN sys.extended_properties AS EP
ON ( EP.class = 7 AND EP.name = 'MS_Description' AND EP.major_id = I.object_id AND EP.minor_id = I.index_id )
WHERE object_id = @0
AND I.index_id != 0
ORDER BY I.[name];", parent.ObjectId);
return this.peta.Fetch<Index>(sql);
}
public static PetaPoco.Page<PetapocoOrm.MonitorLog.FileWatch> GetList(int pageSize, int pageIndex, int serverId, string filename)
{
PetaPoco.Sql sb = new Sql();
sb.Append("select * from FileWatch where 1=1");
if (serverId >= 1)
{
sb.Append(" and ServerName in ( select ServerName from ServerList where id=" + serverId + ") ", serverId);
}
if (!string.IsNullOrEmpty(filename))
{
sb.Append(" and filename like '%" + WXQ.Common.StringPlus.SQLSafe(filename) + "%'");
}
sb.Append(" order by filename asc , adddate desc ");
PetaPoco.Page<PetapocoOrm.MonitorLog.FileWatch> result = PetapocoOrm.MonitorLog.Db.GetInstance().Page<PetapocoOrm.MonitorLog.FileWatch>(pageIndex, pageSize, sb.ToString());
return result;
}
private IList<Column> queryForFunctionColumns(TableFunction func)
{
var sql = new Sql(@"SELECT
C.name AS ColumnName
, Y.[name] AS BaseDataTypeName
, C.is_nullable AS AllowNull
FROM sys.objects AS T
INNER JOIN sys.schemas AS S
ON ( T.schema_id = S.schema_id )
INNER JOIN sys.columns AS C
ON ( T.object_id = C.object_id )
INNER JOIN sys.types AS Y
ON ( Y.user_type_id = C.user_type_id )
WHERE T.object_id = @0
AND T.type = 'TF'
ORDER BY C.column_id, C.name;", func.FunctionId);
return this.peta.Fetch<Column>(sql);
}
private IList<TableFunction> queryForTableFunctions(Schema schema)
{
var sql = new Sql(@"SELECT
J.[name] AS FunctionName
, J.object_id AS FunctionId
, EP.value AS [Description]
FROM sys.objects AS J
INNER JOIN sys.schemas AS S
ON ( J.schema_id = S.schema_id )
INNER JOIN INFORMATION_SCHEMA.ROUTINES AS R
ON ( R.SPECIFIC_SCHEMA = S.[name] AND R.SPECIFIC_NAME = J.[name] )
LEFT OUTER JOIN sys.extended_properties AS EP
ON ( EP.class = 1 AND EP.name = 'MS_Description' AND EP.major_id = J.object_id AND EP.minor_id = 0 )
WHERE J.schema_id = @0
AND J.[type] IN ( 'TF', 'IF' )
ORDER BY J.[name];", schema.SchemaId);
return this.peta.Fetch<TableFunction>(sql);
}
public void mysql_user_vars()
{
var sql = new Sql();
sql.Append("arg @@user1 @2 @1 @@@system1", "a1", "a2", "a3");
Assert.AreEqual(sql.SQL, "arg @@user1 @0 @1 @@@system1");
Assert.AreEqual(sql.Arguments.Length, 2);
Assert.AreEqual(sql.Arguments[0], "a3");
Assert.AreEqual(sql.Arguments[1], "a2");
}
public void ConsecutiveOrderBy()
{
var sql = new Sql()
.Append("SELECT * FROM blah");
sql.Append("ORDER BY x");
sql.Append("ORDER BY y");
Assert.AreEqual(sql.SQL, "SELECT * FROM blah\nORDER BY x\n, y");
}
public void ConsecutiveWhere()
{
var sql = new Sql()
.Append("SELECT * FROM blah");
sql.Append("WHERE x");
sql.Append("WHERE y");
Assert.AreEqual(sql.SQL, "SELECT * FROM blah\nWHERE x\nAND y");
}
public void append_instances()
{
var sql = new Sql("l0 @0", "a0");
var sql1 = new Sql("l1 @0", "a1");
var sql2 = new Sql("l2 @0", "a2");
Assert.AreSame(sql.Append(sql1), sql);
Assert.AreSame(sql.Append(sql2), sql);
Assert.AreEqual(sql.SQL, "l0 @0\nl1 @1\nl2 @2");
Assert.AreEqual(sql.Arguments.Length, 3);
Assert.AreEqual(sql.Arguments[0], "a0");
Assert.AreEqual(sql.Arguments[1], "a1");
Assert.AreEqual(sql.Arguments[2], "a2");
}
public void invalid_arg_name()
{
Assert.Throws<ArgumentException>(() =>
{
var sql = new Sql();
sql.Append("arg @name1 @name2", new { x = 1, y = 2 });
Assert.AreEqual(sql.SQL, "arg @0 @1");
});
}
public void invalid_arg_index()
{
Assert.Throws<ArgumentOutOfRangeException>(()=>{
var sql = new Sql();
sql.Append("arg @0 @1", "a0");
Assert.AreEqual(sql.SQL, "arg @0 @1");
});
}
public void append_with_args2()
{
var sql = new Sql();
sql.Append("l1");
sql.Append("l2 @0 @1", "a1", "a2");
sql.Append("l3 @0", "a3");
Assert.AreEqual(sql.SQL, "l1\nl2 @0 @1\nl3 @2");
Assert.AreEqual(sql.Arguments.Length, 3);
Assert.AreEqual(sql.Arguments[0], "a1");
Assert.AreEqual(sql.Arguments[1], "a2");
Assert.AreEqual(sql.Arguments[2], "a3");
}
public void mixed_named_and_numbered_args()
{
var sql = new Sql();
sql.Append("arg @0 @name @1 @password @2", "a1", "a2", "a3", new { name = "n", password = "p" });
Assert.AreEqual(sql.SQL, "arg @0 @1 @2 @3 @4");
Assert.AreEqual(sql.Arguments.Length, 5);
Assert.AreEqual(sql.Arguments[0], "a1");
Assert.AreEqual(sql.Arguments[1], "n");
Assert.AreEqual(sql.Arguments[2], "a2");
Assert.AreEqual(sql.Arguments[3], "p");
Assert.AreEqual(sql.Arguments[4], "a3");
}
