static void CacheServer()
{
SQLDatabaseConnection cnn = new SQLDatabaseConnection();
cnn.Server = "192.168.0.10";
cnn.Port = 5000;
cnn.Username = "******";
cnn.Password = "******";
cnn.Open();
if (cnn.State == ConnectionState.Open)
{
SQLDatabaseCacheServer cs = new SQLDatabaseCacheServer();
cs.Connection = cnn;
// In Cache server collections are automatically created if one does not exist.
//Add remove raw bytes with Cache Id of 101 and collection name System.String
//if trying to exchange strings or data with other programing languages use raw
cs.AddRaw("System.String", Encoding.UTF8.GetBytes("Example Text for Cache Server"), "101");
string c101 = Encoding.UTF8.GetString((byte[])cs.Get("System.String", "101")).ToString();
cs.Remove("System.String", "101");
cs.Add <string>("Example Text for Cache Server", "101");
c101 = cs.Get <string>("101");
cs.Remove <string>("101");
ApplicationUser u = new ApplicationUser();
u.Id = 1;
u.Name = "SQLUser";
u.Job = "SQL Developer";
string id = cs.Add <ApplicationUser>(u);
ApplicationUser user = cs.Get <ApplicationUser>(id);
Console.WriteLine("Id \t {0} ", user.Id);
Console.WriteLine("Name \t {0} ", user.Name);
Console.WriteLine("Job \t {0} ", user.Job);
List <string> collectionList = cs.CollectionList();
foreach (string collectionName in collectionList)
{
Console.WriteLine("Collection : {0}", collectionName);
}
cs.DropCollection("System.String");
cs.DropCollection <ApplicationUser>();
}
cnn.Close();
cnn.Dispose();
Console.WriteLine("CacheServer() Completed");
}
static void OpenCloseConnection()
{
SQLDatabaseConnection cnn = new SQLDatabaseConnection();
cnn.Server = "192.168.0.10";
cnn.Port = 5000;
cnn.Username = "******";
cnn.Password = "******";
cnn.Open();
Console.WriteLine(cnn.State);
cnn.Close();
cnn.Dispose();
Console.WriteLine("OpenCloseConnection() Completed");
}
static void CreateDropDatabase()
{
SQLDatabaseConnection cnn = new SQLDatabaseConnection();
cnn.Server = "192.168.0.10";
cnn.Port = 5000;
cnn.Username = "******";
cnn.Password = "******";
cnn.Open();
if (cnn.State == ConnectionState.Open)
{
SQLDatabaseCommand cmd = new SQLDatabaseCommand(cnn);
SQLDatabaseUtility u = new SQLDatabaseUtility();
u.Command = cmd;
u.CreateDatabase("TestDatabase");
u.DropDatabase("TestDatabase");
}
cnn.Close();
cnn.Dispose();
Console.WriteLine("CreateDropDatabase() Completed");
}
protected EmptyResult checkBlockerOfLockTimeoutException(string connectionString)
{
try
{
string sqlTextCommand = @"
;WITH SESSOES(SESSAO_BLOQUEADORA, LOGIN_APP, LAST_REQUEST_START_TIME, LAST_REQUEST_END_TIME, BLOQUEADORA, APP, APP_BLOQUEADO, CMD_BLOQUEADO) AS(
SELECT SESSION_ID, '','', '', BLOCKING_SESSION_ID, '', '', ''
FROM SYS.DM_EXEC_REQUESTS AS R JOIN SYS.SYSPROCESSES P ON P.SPID = R.SESSION_ID
WHERE BLOCKING_SESSION_ID > 0
UNION ALL
SELECT SESSION_ID, S.LOGIN_NAME , S.LAST_REQUEST_START_TIME, S.LAST_REQUEST_END_TIME , CAST(0 AS SMALLINT), S.PROGRAM_NAME, '', ''
FROM SYS.DM_EXEC_SESSIONS AS S
WHERE EXISTS (
SELECT* FROM SYS.DM_EXEC_REQUESTS AS R
WHERE S.SESSION_ID = R.BLOCKING_SESSION_ID)
AND NOT EXISTS(
SELECT* FROM SYS.DM_EXEC_REQUESTS AS R
WHERE S.SESSION_ID = R.SESSION_ID)
),
BLOQUEIOS AS(
SELECT
SESSAO_BLOQUEADORA, BLOQUEADORA, SESSAO_BLOQUEADORA AS REF, 1 AS NIVEL
FROM SESSOES
UNION ALL
SELECT S.SESSAO_BLOQUEADORA, B.SESSAO_BLOQUEADORA, B.REF, NIVEL + 1
FROM BLOQUEIOS AS B
INNER JOIN SESSOES AS S ON B.SESSAO_BLOQUEADORA = S.BLOQUEADORA)
--INSERT INTO DBDBA..TB_MON_LOCKS
SELECT REF AS SESSAO_BLOQUEADORA,
(SELECT LOGIN_NAME+ '_' + HOST_NAME FROM SYS.DM_EXEC_SESSIONS
WHERE SESSION_ID = B.REF) AS LOGIN_APP_HOSTNAME, --BLOQUEADOR
(SELECT LAST_REQUEST_START_TIME FROM SYS.DM_EXEC_SESSIONS
WHERE SESSION_ID = B.REF) AS LAST_REQUEST_START_TIME, --BLOQUEADOR
COUNT(DISTINCT R.SESSION_ID) AS 'BLOQUEIOSDIRETOS', --BLOQUEADOR
COUNT(DISTINCT B.SESSAO_BLOQUEADORA) - 1 AS BLOQUEIOSTOTAL, --BLOQUEADOR
COUNT(DISTINCT B.SESSAO_BLOQUEADORA) - COUNT(DISTINCT R.SESSION_ID) - 1 AS BLOQUEIOSINDIRETOS, --BLOQUEADOR
(SELECT TEXT FROM SYS.DM_EXEC_SQL_TEXT(
(SELECT MOST_RECENT_SQL_HANDLE FROM SYS.DM_EXEC_CONNECTIONS
WHERE SESSION_ID = B.REF))) AS COMANDO, --BLOQUEADOR
(SELECT PROGRAM_NAME FROM SYS.DM_EXEC_SESSIONS
WHERE SESSION_ID = B.REF) AS APP, --BLOQUEADOR
P.SPID AS SESSAO_BLOQUEADA,
P.PROGRAM_NAME AS APP_BLOQUEADO,
S.TEXT AS CMD_BLOQUEADO,
P.HOSTNAME AS HOSTNAME_BLOQUEADO
FROM BLOQUEIOS AS B
INNER JOIN SYS.DM_EXEC_REQUESTS AS R ON B.REF = R.BLOCKING_SESSION_ID
INNER JOIN SYS.SYSPROCESSES AS P ON P.SPID = R.SESSION_ID
CROSS APPLY SYS.DM_EXEC_SQL_TEXT (P.SQL_HANDLE ) AS S
GROUP BY REF, R.WAIT_RESOURCE, P.PROGRAM_NAME, S.TEXT, P.HOSTNAME, P.SPID, DATEDIFF(SECOND, R.START_TIME, GETDATE()), R.START_TIME
HAVING COUNT(DISTINCT R.SESSION_ID) = 1 ";
var t = new Thread(new ParameterizedThreadStart((connStringObj) =>
{
try
{
Task.Delay(2000);
using (var conn = new SQLDatabaseConnection(connStringObj?.ToString()))
{
using (var reader = conn.GetDataReader(sqlTextCommand, 3))
{
if (reader == null || reader.IsClosed)
{
return;
}
var schema = reader.GetSchemaTable();
if (schema == null)
{
return;
}
StringBuilder sb = new StringBuilder("* Locking details:");
sb.AppendLine();
bool lockerFound = false;
while (reader.Read())
{
for (int ic = 0; ic < schema.Rows.Count; ic++)
{
string columnName = schema.Rows[ic]["ColumnName"]?.ToString();
sb.Append($"{(ic == 0 ? "-" : " /")} {columnName.ToUpper()}: {reader[columnName]?.ToString()?.Trim() ?? "NULL"}");
}
sb.AppendLine();
lockerFound = true;
}
if (lockerFound)
{
_factory._logger.LogError(new Exception($"Lock Timeout Exception detected.{sb.ToString()}"));
}
conn.Close();
}
}
}
catch
{//empty catch}
}
}));
t.Start(connectionString);
return(new EmptyResult());
}
catch (Exception ex)
{
return(new EmptyResult()
{
Exception = ex
});
}
}
static void CreateTable()
{
SQLDatabaseConnection cnn = new SQLDatabaseConnection();
cnn.Server = "192.168.0.10";
cnn.Port = 5000;
cnn.Username = "******";
cnn.Password = "******";
cnn.Open();
if (cnn.State == ConnectionState.Open)
{
SQLDatabaseResultSet[] rs;
SQLDatabaseCommand cmd = new SQLDatabaseCommand(cnn);
SQLDatabaseUtility u = new SQLDatabaseUtility();
u.Command = cmd;
u.CreateDatabase("testdb");
cnn.DatabaseName = "testdb";
cnn.MultipleActiveResultSets = true;
cmd.CommandText = "Create table if not exists testtable (id integer, textvalue text);";
rs = cmd.ExecuteNonQuery();
cmd.CommandText = "Insert Into testtable VALUES (1, 'example 1');";
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT * FROM testtable;";
rs = cmd.ExecuteReader();
foreach (SQLDatabaseResultSet drs in rs)
{
if (drs != null)
{
if (!string.IsNullOrWhiteSpace(drs.ErrorMessage))
{
Console.WriteLine(drs.ErrorMessage);
}
else
{
for (int r = 0; r < drs.RowCount; r++)
{
for (int c = 0; c < drs.ColumnCount; c++)
{
Console.Write(drs.Columns[c] + "(" + drs.DataTypes[c] + ")");
Console.Write("\t");
}
Console.WriteLine("");
for (int c = 0; c < drs.ColumnCount; c++)
{
Console.Write(drs.Rows[r][c]);
Console.Write("\t");
}
}
Console.WriteLine("");
}
}
}
cmd.CommandText = "DROP TABLE testtable;";
cmd.ExecuteNonQuery();
u.DropDatabase("testdb");
}
cnn.Close();
cnn.Dispose();
Console.WriteLine("CreateTable() Completed");
}
static void ORMClient()
{
SQLDatabaseConnection cnn = new SQLDatabaseConnection();
cnn.Server = "192.168.0.10";
cnn.Port = 5000;
cnn.Username = "******";
cnn.Password = "******";
cnn.Open();
if (cnn.State == ConnectionState.Open)
{
SQLDatabaseCommand cmd = new SQLDatabaseCommand(cnn);
SQLDatabaseUtility u = new SQLDatabaseUtility();
u.Command = cmd;
u.CreateDatabase("ormtestdb");
cnn.DatabaseName = "ormtestdb";
ApplicationUser e = new ApplicationUser();
SQLDatabaseOrmClient <ApplicationUser> orm = new SQLDatabaseOrmClient <ApplicationUser>();
orm.Connection = cnn;
orm.CreateTable(e);
e.Id = 1;
e.Name = "SQLUser";
e.Job = "SQL Developer";
orm.Add(e); // add
ApplicationUser user = orm.GetById(1); //get one by id
Console.WriteLine("Id \t {0} ", user.Id);
Console.WriteLine("Name \t {0} ", user.Name);
Console.WriteLine("Job \t {0} ", user.Job);
user.Job = "New Job";
orm.Update(user);
// Get all
IList <ApplicationUser> userList = orm.GetAll();
//Filter example;
SQLDatabaseOrmClient <ApplicationUser> .Filter <ApplicationUser> f = new SQLDatabaseOrmClient <ApplicationUser> .Filter <ApplicationUser>();
f.Add(x => x.Id, 1);//get user with id of 1
//methods for order by and contains including limiting number of returned rows.
//f.Add(x => x.Name, "SQLUser");
//f.OrderBy(x => x.Name, "DESC");
//f.Contains(x => x.Name, "u");
//f.Limit(10, 10);
//to find use following
IList <ApplicationUser> foundUsers = orm.Find(f).ToList();
//to remove use following
orm.Remove(f);
//remove or drop entire entity
orm.DropTable(user);
u.DropDatabase("ormtestdb");
}
cnn.Close();
cnn.Dispose();
Console.WriteLine("ORMClient() Completed");
}