public async Task <InvokeResult> ValidateConnectionAsync(DataStream stream)
{
var result = new InvokeResult();
var builder = new System.Data.SqlClient.SqlConnectionStringBuilder();
builder.Add("Data Source", stream.DbURL);
builder.Add("Initial Catalog", stream.DbName);
builder.Add("User Id", stream.DbUserName);
builder.Add("Password", stream.DbPassword);
_connectionString = builder.ConnectionString;
/* be careful when updating the SQL below, the rdr uses field indexes,
* if this wasn't so small and self contained, I probably wouldn't be so lazy,
* buf for one field...well...moving on.*/
var sql = $@"
select
b.name as ColumnName,
type_name(b.xusertype) ColumnType,
b.IsNullable,
columnproperty(a.id, b.name, 'isIdentity') IsIdentity,
sm.text AS DefaultValue
from sysobjects a
inner join syscolumns b on a.id = b.id
LEFT JOIN sys.syscomments sm ON sm.id = b.cdefault
WHERE a.xtype = 'U' and a.name = @tableName";
var fields = new List <SQLFieldMetaData>();
using (var cn = new System.Data.SqlClient.SqlConnection(_connectionString))
using (var cmd = new System.Data.SqlClient.SqlCommand(sql, cn))
{
cmd.Parameters.AddWithValue("@tableName", stream.DbTableName);
try
{
await cn.OpenAsync();
using (var rdr = await cmd.ExecuteReaderAsync())
{
while (await rdr.ReadAsync())
{
fields.Add(new SQLFieldMetaData()
{
ColumnName = rdr["ColumnName"].ToString(),
IsRequired = !Convert.ToBoolean(rdr["IsNullable"]),
DataType = rdr["ColumnType"].ToString(),
IsIdentity = Convert.ToBoolean(rdr["IsIdentity"]),
DefaultValue = Convert.ToString(rdr["DefaultValue"])
});
}
}
}
catch (Exception ex)
{
result.AddUserError($"Could not access SQL Server: {ex.Message}");
return(result);
}
}
if (fields.Count == 0)
{
result.AddUserError($"Table [{stream.DbTableName}] name not found on SQL Server database [{stream.DbName}] on server [{stream.DbURL}.");
}
else
{
result.Concat(stream.ValidateSQLSeverMetaData(fields));
}
return(result);
}