public async Task TryConnectAsync(SqlServerConnectionInfo connectionString, CancellationToken ct)
{
using (SqlConnection sqlConnection = new SqlConnection())
{
sqlConnection.ConnectionString = connectionString.ToConnectionString();
await sqlConnection.OpenAsync(ct);
}
}
public void TryConnect(SqlServerConnectionInfo connectionString, string databaseName)
{
using (SqlConnection sqlConnection = new SqlConnection())
{
sqlConnection.ConnectionString = connectionString.ToConnectionString();
sqlConnection.Open();
sqlConnection.ChangeDatabase(databaseName);
}
}
// https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-schema-collections?view=netframework-4.8
public List <string> GetDatabases(SqlServerConnectionInfo connectionInfo)
{
using (SqlConnection sqlConnection = new SqlConnection())
{
sqlConnection.ConnectionString = connectionInfo.ToConnectionString();
sqlConnection.Open();
var databasesNames = sqlConnection.GetSchema("Databases").AsEnumerable().Select(s => s[0].ToString()).ToList();
return(databasesNames);
}
}
public List <(string schema, string name)> GetViews(SqlServerConnectionInfo connectionInfo, string database)
{
using (SqlConnection sqlConnection = new SqlConnection())
{
sqlConnection.ConnectionString = connectionInfo.ToConnectionString();
sqlConnection.Open();
sqlConnection.ChangeDatabase(database);
var views = sqlConnection.GetSchema("Tables")
.AsEnumerable()
.Where(x => x["TABLE_TYPE"]?.ToString() == "VIEW")
.Select(t => (t["TABLE_SCHEMA"]?.ToString(), t["TABLE_NAME"]?.ToString()))
.ToList();
return(views);
}
}
public IEnumerable <object> Run(SqlServerConnectionInfo connectionString, string text, Action <Type> returnTypeCreated = null)
{
using (var sqlConnection = new SqlConnection())
{
sqlConnection.ConnectionString = connectionString.ToConnectionString();
sqlConnection.Open();
//sqlConnection.ChangeDatabase(databaseName);
using (var sqlCommand = new SqlCommand(text, sqlConnection))
{
using (var sqlReader = sqlCommand.ExecuteReader())
{
List <(string name, Type type)> fields = new List <(string name, Type type)>();
for (int i = 0; i < sqlReader.FieldCount; i++)
{
var name = sqlReader.GetName(i);
var type = sqlReader.GetFieldType(i);
fields.Add((name, type));
}
Type returnType = new ExpressionHelper().CreateAnonymousType(fields);
returnTypeCreated?.Invoke(returnType);
while (sqlReader.Read())
{
var item = Activator.CreateInstance(returnType);
for (int i = 0; i < sqlReader.FieldCount; i++)
{
var name = sqlReader.GetName(i);
var field = returnType.GetField(name);
var value = sqlReader.GetValue(i);
if (value is DBNull)
{
value = GetDefaultValue(field.FieldType);
}
field.SetValue(item, value);
}
yield return(item);
}
}
}
}
}