public async Task <InvokeResult> InitAsync(DataStream stream)
{
_stream = stream;
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;
if (stream.DBValidateSchema)
{
var result = await ValidationConnection(stream);
if (!result.Successful)
{
_instanceLogger.AddError("SQLServerConnecction", "Could not validate SQL Connection", result.Errors.First().Message.ToKVP("firstError"));
return(result.ToInvokeResult());
}
}
return(InvokeResult.Success);
}
static SqlConnection CreateConnection(string server, string db, string uname, string pass)
{
SqlConnectionStringBuilder connSB = new SqlConnectionStringBuilder();
connSB.Add("user id", uname);
connSB.Add("password", pass);
connSB.Add("server", server);
connSB.Add("database", db);
//Console.WriteLine(connSB.ConnectionString);
return new SqlConnection(connSB.ConnectionString);
}
public static string RetornaCadenaConexionMasterDatabase()
{
//Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password = myPassword;
var connectionBuilder = new SqlConnectionStringBuilder();
connectionBuilder.Add("Server", Server);
connectionBuilder.Add("Database", "Master");
connectionBuilder.Add("User Id", User);
connectionBuilder.Add("Password", Password);
return connectionBuilder.ToString();
}
/// <summary>
/// 获取可用数据库名
/// </summary>
/// <param name="Server">连接到的服务器</param>
/// <param name="UserName">连接用户名</param>
/// <param name="PassWord">连接密码</param>
/// <returns>可用数据库名称列表或者本地化错误信息</returns>
public static String[] GetDBNames(String Server, String UserName = null, String PassWord = null)
{
List<String> names = new List<String>();
names.Add(LocalString.NoDBNames);
try
{
SqlConnectionStringBuilder sqlCSB = new SqlConnectionStringBuilder();
sqlCSB.Add("server", "tcp:" + Server);
if (UserName == null || PassWord == null)
{
sqlCSB.Add("Integrated Security", true);
}
else
{
sqlCSB.Add("uid", UserName);
sqlCSB.Add("pwd", PassWord);
}
sqlCSB.Add("Connection Timeout", TimeOut);
SqlConnection sqlConnection = new SqlConnection(sqlCSB.ConnectionString);
sqlConnection.Open();
String SQL = @"SELECT name FROM sys.databases WHERE "
+ "name NOT IN ('master', 'tempdb', 'model', 'msdb','ReportServer','ReportServerTempDB');";
SqlCommand cmd = new SqlCommand(SQL, sqlConnection);
SqlDataReader data = cmd.ExecuteReader();
if (data.HasRows)
{
names.RemoveAll((a) => { return true; });
}
while (data.Read())
{
names.Add(data["name"].ToString());
}
data.Close();
cmd.Dispose();
sqlConnection.Close();
}
catch (Exception ex)
{
Debuger.PrintException(ex);
}
return names.ToArray();
}
/// <summary>
/// 打开连接
/// </summary>
public void Open()
{
SqlConnectionStringBuilder sqlCSB = new SqlConnectionStringBuilder();
sqlCSB.Add("server", "tcp:" + Server);
sqlCSB.Add("database", DBName);
if (this.Integrated)
{
sqlCSB.Add("Integrated Security", true);
}
else
{
sqlCSB.Add("uid", User);
sqlCSB.Add("pwd", Pass);
}
sqlCSB.Add("Connection Timeout", TimeOut);
//启用多个活动结果集支持(MARS)仅SQL Server 2008及更新版本
//sqlCSB.Add("MultipleActiveResultSets", "true");
conn = new SqlConnection(sqlCSB.ConnectionString);
conn.Open();
}
/// <summary>
/// 进行测试连接
/// </summary>
/// <param name="Server">数据库服务器地址</param>
/// <param name="DataBaseName">数据库名</param>
/// <param name="UserName">数据库用户名</param>
/// <param name="PassWord">数据库密码</param>
/// <returns>是否可以连接到数据库</returns>
public static bool TestDBConnction(String Server, String DataBaseName, String UserName, String PassWord,out String Error)
{
try
{
SqlConnectionStringBuilder sqlCSB = new SqlConnectionStringBuilder();
sqlCSB.Add("server", "tcp:" + Server);
sqlCSB.Add("database", DataBaseName);
sqlCSB.Add("uid", UserName);
sqlCSB.Add("pwd", PassWord);
sqlCSB.Add("Connection Timeout", TimeOut);
SqlConnection sqlConnection = new SqlConnection(sqlCSB.ConnectionString);
sqlConnection.Open();
sqlConnection.Close();
Error = "";
return true;
}
catch (SqlException ex)
{
if (ex.Class < 17)
{
Debuger.PrintException(ex);
Error = ex.Message;
return false;
}
throw ex;
}
}
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);
}