public static void TestMySQL()
{
MySql.Data.MySqlClient.MySqlConnectionStringBuilder csb = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder();
csb.Server = "";
csb.Port = 123;
csb.Database = "";
csb.UserID = "";
csb.Password = "";
csb.PersistSecurityInfo = false;
csb.Pooling = true;
csb.MinimumPoolSize = 1;
csb.MaximumPoolSize = 5;
csb.CharacterSet = System.Text.Encoding.UTF8.WebName;
csb.ConnectionTimeout = 30;
csb.ConnectionReset = true;
csb.ConvertZeroDateTime = false;
csb.SslMode = MySql.Data.MySqlClient.MySqlSslMode.Preferred;
csb.TreatTinyAsBoolean = false;
using (MySql.Data.MySqlClient.MySqlConnection con = new MySql.Data.MySqlClient.MySqlConnection(csb.ConnectionString))
{
string ser = con.ServerVersion;
}
System.Console.WriteLine(csb.ConnectionString);
}
private static void LoadConnections()
{
System.Configuration.ConnectionStringSettings systemConnection = System.Configuration.ConfigurationManager.ConnectionStrings["SystemMapsConnectionString"] ?? null;
System.Data.Common.DbConnectionStringBuilder csb;
if (MySqlAccess.IsMySqlConnectionString(systemConnection.ConnectionString))
{
csb = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder(systemConnection.ConnectionString);
}
else
{
csb = new System.Data.SqlClient.SqlConnectionStringBuilder(systemConnection.ConnectionString);
}
if (!List.ContainsKey(csb["data source"].ToString()))
{
List.Add(csb["data source"].ToString(), systemConnection.ConnectionString);
}
//else
// List[cnnParameter.serverName]=new SqlServersManager(connectionString) ;
AddOnPremissDemoDb();
AddExternalInstances();
isLoaded = true;
}
private void FrmBackup_Load(object sender, EventArgs e)
{
MySql.Data.MySqlClient.MySqlConnectionStringBuilder CnnTemp = Common.RecuperarCnn();
Txt_Servidor.Text = CnnTemp.Server;
Txt_DB.Text = CnnTemp.Database;
Txt_Usuario.Text = CnnTemp.UserID;
Txt_Contraseña.Text = CnnTemp.Password;
}
/// <summary>
/// 创建一个连接MySQL ODBC 3.51驱动所连接数据源的连接字符串
/// </summary>
/// <param name="server">主机名</param>
/// <param name="userID">用户名</param>
/// <param name="password">密码</param>
/// <param name="database">数据库</param>
/// <returns></returns>
public static string MakeConnectionString(string server, string userID, string password, string database)
{
MySql.Data.MySqlClient.MySqlConnectionStringBuilder builder = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder();
builder.Server = server;
builder.UserID = userID;
builder.Password = password;
builder.Database = database;
return(builder.GetConnectionString(true));
}
static Database()
{
MySql.Data.MySqlClient.MySqlConnectionStringBuilder MySqlConnectionStringBuilder = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder {
Server = "memdixyp.mysql.db.hostpoint.ch",
UserID = "memdixyp_user",
Password = "******",
SslMode = MySql.Data.MySqlClient.MySqlSslMode.None
};
connection_string = MySqlConnectionStringBuilder.ToString();
}
public static string ServiceName(this DbConnectionStringBuilder builder)
{
if (builder is MySql.Data.MySqlClient.MySqlConnectionStringBuilder)
{
MySql.Data.MySqlClient.MySqlConnectionStringBuilder temp = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder(builder.ConnectionString);
return temp.Server;
}
SqlConnectionStringBuilder sqltemp = new SqlConnectionStringBuilder(builder.ConnectionString);
return sqltemp.DataSource;
}
public static string ServiceName(this DbConnectionStringBuilder builder)
{
if (builder is MySql.Data.MySqlClient.MySqlConnectionStringBuilder)
{
MySql.Data.MySqlClient.MySqlConnectionStringBuilder temp = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder(builder.ConnectionString);
return(temp.Server);
}
SqlConnectionStringBuilder sqltemp = new SqlConnectionStringBuilder(builder.ConnectionString);
return(sqltemp.DataSource);
}
} // End Constructor
public cMySQL(string strConnectionString)
{
//this.m_DatabaseConfiguration = dbcDBconfig;
this.m_dbtDBtype = DataBaseEngine_t.MySQL;
this.m_providerFactory = this.GetFactory();
this.m_dictScriptTemplates = GetSQLtemplates();
this.m_dblDBversion = 5.5;
this.m_ConnectionString = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder(strConnectionString);
this.m_SqlConnection = new MySql.Data.MySqlClient.MySqlConnection(strConnectionString);
} // End Constructor 2
//**********************************************************************************
//**********************************************************************************
public Logger(string serwer, string nazwaBazy, string hasloDoBazy, string uzytkownik, uint port, PRACOWNIK pracownik)
{
m_DanePolaczenia = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder();
m_DanePolaczenia.Server = serwer;
m_DanePolaczenia.Password = hasloDoBazy;
m_DanePolaczenia.UserID = uzytkownik;
m_DanePolaczenia.Port = port;
m_DanePolaczenia.Database = nazwaBazy;
m_DB = new DB();
}
public static DbConnectionStringBuilder NewInstance(this DbConnectionStringBuilder builder, string database)
{
if (builder is MySql.Data.MySqlClient.MySqlConnectionStringBuilder)
{
MySql.Data.MySqlClient.MySqlConnectionStringBuilder temp = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder(builder.ConnectionString);
temp.Database = database;
return temp;
}
SqlConnectionStringBuilder sqltemp = new SqlConnectionStringBuilder(builder.ConnectionString);
sqltemp.InitialCatalog = database;
return sqltemp;
}
public static DbConnectionStringBuilder NewInstance(this DbConnectionStringBuilder builder, string database)
{
if (builder is MySql.Data.MySqlClient.MySqlConnectionStringBuilder)
{
MySql.Data.MySqlClient.MySqlConnectionStringBuilder temp = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder(builder.ConnectionString);
temp.Database = database;
return(temp);
}
SqlConnectionStringBuilder sqltemp = new SqlConnectionStringBuilder(builder.ConnectionString);
sqltemp.InitialCatalog = database;
return(sqltemp);
}
public static DataTable GetRemoteDataTable(string command, Hashtable connect)
{
MySql.Data.MySqlClient.MySqlConnectionStringBuilder msqConnStr = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder();
msqConnStr.UserID = connect["UserID"].ToString();
msqConnStr.Password = connect["Password"].ToString();
msqConnStr.Database = connect["Database"].ToString();
msqConnStr.Server = connect["Server"].ToString();
msqConnStr.AllowZeroDateTime = true;
MySql.Data.MySqlClient.MySqlConnection msqConn = new MySql.Data.MySqlClient.MySqlConnection(msqConnStr.ToString());
return GetRemoteDataTable(command, msqConn);
}
public bool startup(string ip, string user, string password)
{
var builder = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder();
builder.Server = ip;
builder.UserID = user;
builder.Password = password;
builder.CharacterSet = "utf8";
connection_string = builder.ToString();
connection = new MySql.Data.MySqlClient.MySqlConnection(connection_string);
// no need surround with try-catch, !!! you catch it
connection.Open();
return(true);
}
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
var connectionStringBuilder = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder
{
Server = "localhost",
UserID = "godfather",
Password = "",
Database = "godfather",
ConvertZeroDateTime = true
};
DefaultDbContext.ConnectionString = connectionStringBuilder.ToString();
services.AddDbContext <DefaultDbContext>(options => options.UseMySql(DefaultDbContext.ConnectionString));
services.AddIdentity <Account, IdentityRole>()
.AddEntityFrameworkStores <DefaultDbContext>()
.AddDefaultTokenProviders();
services
.Configure <IdentityOptions>(options =>
{
// Password settings
options.Password.RequireDigit = false;
options.Password.RequiredLength = 6;
options.Password.RequireNonAlphanumeric = false;
options.Password.RequireUppercase = false;
options.Password.RequireLowercase = false;
// Lockout settings
options.Lockout.DefaultLockoutTimeSpan = TimeSpan.FromMinutes(30);
options.Lockout.MaxFailedAccessAttempts = 10;
// Cookie settings
options.Cookies.ApplicationCookie.ExpireTimeSpan = TimeSpan.FromDays(180);
options.Cookies.ApplicationCookie.LoginPath = "/Account/Login";
options.Cookies.ApplicationCookie.LogoutPath = "/Account/Logout";
options.Cookies.ApplicationCookie.AutomaticAuthenticate = true;
options.Cookies.ApplicationCookie.AutomaticChallenge = true;
// User settings
options.User.RequireUniqueEmail = true;
});
// services.AddTransient<IPasswordHasher<Account>, BCryptPasswordHasher>();
// Add framework services.
services.AddMvc();
}
public string Build(Database dbOptions)
{
var builder = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder
{
Server = dbOptions.Server,
Database = dbOptions.Name,
UserID = dbOptions.User,
Password = dbOptions.Password,
PersistSecurityInfo = true,
ConnectionTimeout = 30,
DefaultCommandTimeout = 30
};
_log?.LogInformation($"Building mysql connection string for Server={dbOptions.Server}, Version={dbOptions.Version} Database={dbOptions.Name}, User={dbOptions.User}");
return(builder.ToString());
}
private static MySql.Data.MySqlClient.MySqlConnection GetMySqlConnection(bool open = true,
bool convertZeroDatetime = false, bool allowZeroDatetime = false)
{
string cs = "server=192.168.1.180;port=8066;uid=root;pwd=123456;database=testdb";
var csb = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder(cs);
csb.AllowZeroDateTime = allowZeroDatetime;
csb.ConvertZeroDateTime = convertZeroDatetime;
var conn = new MySql.Data.MySqlClient.MySqlConnection(csb.ConnectionString);
if (open)
{
conn.Open();
}
return(conn);
}
public static MySql.Data.MySqlClient.MySqlConnection GetMySqlConnection(bool open = true,
bool convertZeroDatetime = false, bool allowZeroDatetime = false)
{
string cs = "server=127.0.0.1;port=3306;database=aspidentitymysql;uid=root;pwd=root";
var csb = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder(cs)
{
AllowZeroDateTime = allowZeroDatetime,
ConvertZeroDateTime = convertZeroDatetime
};
var conn = new MySql.Data.MySqlClient.MySqlConnection(csb.ConnectionString);
if (open)
{
conn.Open();
}
return(conn);
}
public static string[] DataBase(this DbConnectionStringBuilder builder)
{
if (builder is MySql.Data.MySqlClient.MySqlConnectionStringBuilder)
{
MySql.Data.MySqlClient.MySqlConnectionStringBuilder temp = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder(builder.ConnectionString);
if (!string.IsNullOrEmpty(temp.Database))
{
return new string[] { temp.Database };
}
}
SqlConnectionStringBuilder sqltemp = new SqlConnectionStringBuilder(builder.ConnectionString);
if (!string.IsNullOrEmpty(sqltemp.InitialCatalog))
{
return new string[] { sqltemp.InitialCatalog };
}
return new string[] { null };
}
} // End Constructor 2
public override string GetConnectionString(string strDb)
{
if (string.IsNullOrEmpty(strDb))
{
return(m_ConnectionString.ConnectionString);
}
string strRetVal = null;
MySql.Data.MySqlClient.MySqlConnectionStringBuilder mcsb = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder();
mcsb.ConnectionString = m_ConnectionString.ConnectionString;
mcsb.Database = strDb;
strRetVal = mcsb.ConnectionString;
mcsb.Clear();
mcsb = null;
return(strRetVal);
} // End Function GetConnectionString
private static MySql.Data.MySqlClient.MySqlConnection GetMySqlConnection(bool open = true,
bool convertZeroDatetime = false, bool allowZeroDatetime = false)
{
string cs = IsAppVeyor
? "Server=localhost;Database=test;Uid=root;Pwd=Password12!;"
: "Server=localhost;Database=tests;Uid=test;Pwd=pass;";
var csb = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder(cs);
csb.AllowZeroDateTime = allowZeroDatetime;
csb.ConvertZeroDateTime = convertZeroDatetime;
var conn = new MySql.Data.MySqlClient.MySqlConnection(csb.ConnectionString);
if (open)
{
conn.Open();
}
return(conn);
}
static void Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
var connectionStringBuilder = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder
{
UserID = "usr_19413097",
Password = "******",
Server = "atlas.dsv.su.se",
Database = "db_19413097",
Port = 3306
};
var guestBook = new GuestBookDbConnector(connectionStringBuilder.ToString());
Application.Run(new Form1(guestBook));
}
public static string[] DataBase(this DbConnectionStringBuilder builder)
{
if (builder is MySql.Data.MySqlClient.MySqlConnectionStringBuilder)
{
MySql.Data.MySqlClient.MySqlConnectionStringBuilder temp = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder(builder.ConnectionString);
if (!string.IsNullOrEmpty(temp.Database))
{
return(new string[] { temp.Database });
}
}
SqlConnectionStringBuilder sqltemp = new SqlConnectionStringBuilder(builder.ConnectionString);
if (!string.IsNullOrEmpty(sqltemp.InitialCatalog))
{
return(new string[] { sqltemp.InitialCatalog });
}
return(new string[] { null });
}
private void Btn_ProbarConexion_Click(object sender, EventArgs e)
{
MySql.Data.MySqlClient.MySqlConnectionStringBuilder MyCnnString = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder();
MyCnnString.Server = Txt_Servidor.Text;
MyCnnString.UserID = Txt_Usuario.Text;
MyCnnString.Database = Txt_DB.Text;
MyCnnString.Password = Txt_Contraseña.Text;
MySql.Data.MySqlClient.MySqlConnection MyCnn = new MySql.Data.MySqlClient.MySqlConnection(MyCnnString.ConnectionString);
try
{
MyCnn.Open();
MyCnn.Close();
MessageBox.Show("Conexión exitosa!");
}
catch (Exception Error)
{
MessageBox.Show("No se puede realizar la coneccion con estos datos.\r\nDetalles:\r\n" + Error.Message);
}
}
public static string LinkedDataSource(string connectionLocal, string connectionLinked)
{
string connStr = string.Empty;
var connStrA = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder(connectionLocal);
var connStrB = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder(connectionLinked);
// 跨服务器
if (connStrA.Server != connStrB.Server)
{
connStr = connStr + "`" + connStrB.Server + "`.";
}
// 跨数据库
if (connStrA.Database != connStrB.Database || connStrA.Server != connStrB.Server)
{
connStr = connStr + "`" + connStrB.Database + "`.";
}
return(connStr);
}
/// <summary>
/// Create the dabase if it does not exist.
/// </summary>
/// <remarks>Mysql/mariadb specific</remarks>
private void CreateIfNotExists()
{
// return new MySql.Data.MySqlClient.MySqlConnection(string.Format("Server={0};Uid={1};Pwd={2};Port={3};CharSet=utf8mb4;",
// server, username, password, port));
var x = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder(strConnectionVpn);
string serverConnectionVpn = $"server={x.Server};user={x.UserID};pwd={x.Password};Port={x.Port};CharSet=utf8mb4;";
using (var cn = new MySql.Data.MySqlClient.MySqlConnection(serverConnectionVpn))
{
var cmd = cn.CreateCommand();
cmd.CommandText = $"CREATE DATABASE IF NOT EXISTS `{x.Database}` CHARACTER SET utf8 COLLATE utf8_unicode_ci;";
cmd.CommandType = System.Data.CommandType.Text;
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
}
// Sessions database
// Alls sessions are store in mysql instead of in process.
// Provides better support for clustering, load balancing and restarting sites after updates
// without users being logged out or loosing data
var y = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder(strConnectionSessions);
string serverConnectionSession = $"server={y.Server};user={y.UserID};pwd={y.Password};Port={y.Port};CharSet=utf8mb4;";
using (var cn2 = new MySql.Data.MySqlClient.MySqlConnection(serverConnectionSession))
{
var cmd2 = cn2.CreateCommand();
cmd2.CommandText = $"CREATE DATABASE IF NOT EXISTS `{y.Database}` CHARACTER SET utf8 COLLATE utf8_unicode_ci;";
cmd2.CommandType = System.Data.CommandType.Text;
cn2.Open();
cmd2.ExecuteNonQuery();
cn2.Close();
}
}
} // End Function GetMsCs
private static string GetMyCS()
{
var csb = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder();
if (System.Environment.OSVersion.Platform == System.PlatformID.Unix)
{
csb.Server = System.Environment.MachineName;
}
else
{
csb.Server = System.Environment.MachineName + @"\SQLEXPRESS";
}
csb.Database = "COR_Basic_Demo_V4";
csb.UserID = TestPlotly.SecretManager.GetSecret <string>("DefaultDbUser");
csb.Password = TestPlotly.SecretManager.GetSecret <string>("DefaultDbPassword");
csb.CharacterSet = System.Text.Encoding.UTF8.WebName;
csb.AllowZeroDateTime = false;
csb.PersistSecurityInfo = false;
csb.ConnectionIdleTimeout = 15;
csb.ConnectionTimeout = 15;
csb.DefaultCommandTimeout = 30;
csb.Pooling = true;
csb.MinimumPoolSize = 1;
csb.MaximumPoolSize = 100;
string cs = csb.ConnectionString;
csb = null;
return(cs);
} // End Function GetMsCs
public DBConnection(string server, string user, string password, string database)
{
MySql.Data.MySqlClient.MySqlConnectionStringBuilder builder = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder();
builder.Server = server;
builder.UserID = user;
builder.Password = password;
builder.Database = database;
connection = new MySql.Data.MySqlClient.MySqlConnection(builder.ConnectionString);
//connection test
try
{
connection.Open();
connection.Close();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
throw ex;
}
//
}
/// <summary>
/// Generate database connection string
/// </summary>
/// <param name="serverName">string: IP address</param>
/// <param name="databaseName">string: Catalog name</param>
/// <param name="providerName">string: ProviderName</param>
/// <param name="databaseUserId">string user id</param>
/// <param name="databasePassword">string: user password</param>
/// <param name="databaseTimeout">string: database timeout</param>
public static string GenerateEntityConnectionString(string serverName, string databaseName, string providerName,
string databaseUserId, string databasePassword, short?databaseTimeout)
{
string conn = string.Empty;
switch (providerName)
{
case "System.Data.SqlClient":
conn = new SqlConnectionStringBuilder()
{
UserID = databaseUserId,
Password = databasePassword,
DataSource = serverName,
IntegratedSecurity = false,
InitialCatalog = databaseName
}.ConnectionString;
break;
case "MySql.Data.MySqlClient":
conn = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder()
{
Server = serverName,
UserID = databaseUserId,
Password = databasePassword,
Database = databaseName,
PersistSecurityInfo = true
}.ConnectionString;
break;
default:
break;
}
return(conn);
}
public static mysql_module setup_sql(string host, string database, string user, string pass, string charset = "utf8")
{
mysql_module dst_obj = new mysql_module();
try
{
var mysql_builder = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder()
{
Server = host,
Database = database,
UserID = user,
Password = pass,
CharacterSet = charset
};
dst_obj._connection_sql_str = mysql_builder.ToString();
}
catch (Exception e)
{
loger_manager.write_log(e.Message, "error");
return(null);
}
return(dst_obj);
}
public static string Sync(DataLayoutCollection ds, Hashtable connect)
{
MySql.Data.MySqlClient.MySqlConnectionStringBuilder msqConnStr = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder();
msqConnStr.UserID = connect["UserID"].ToString();
msqConnStr.Password = connect["Password"].ToString();
msqConnStr.Database = connect["Database"].ToString();
msqConnStr.Server = connect["Server"].ToString();
MySql.Data.MySqlClient.MySqlConnection msqConn = new MySql.Data.MySqlClient.MySqlConnection(msqConnStr.ToString());
DataTable rdt = new DataTable();
List<string> data = new List<string>();
string currentTableName = string.Empty;
List<int> newRowIndexes = new List<int>();
List<int> existedRowIndexes = new List<int>();
int rowIdx = 0;
DataRow[] rows = null;
//data.Add("SET NAMES cp1251;");
// get indexes
Dictionary<string, string> tableIndexesAdd = ApplicationConfiguration.Instance.GetTableDictValueByPath("mappingConfiguration.Indexes");
foreach (excelDataView dtp in ds.GetDataControls())
{
rowIdx = 0;
newRowIndexes.Clear();
existedRowIndexes.Clear();
currentTableName = GetReplacedTableName(dtp.dataLayout.TableName);
// loop by new rows
string exid = tableIndexesAdd[dtp.dataLayout.TableName];
// startup comment
data.Add(Environment.NewLine);
data.Add("-- synchronization for table " + dtp.dataLayout.TableName);
// data.Add(new corelib().getSqlCommandWithDataTable(dtp.Copy()));
rdt = corelib.GetRemoteDataTable(string.Format("SELECT `{0}` FROM {1}", exid, currentTableName), connect);
// looping by received rows
foreach (DataRow cdrw in rdt.Rows)
{
try
{
rows = dtp.IndependentDataLayout.Select(exid + " = '" + cdrw[exid] + "'");
}
catch { }
// in case if new rows - insert them
if (rows != null && rows.Length != 0)
{
// in case if existed rows - update them
//rows[0].Table.Rows.IndexOf(rows[0]);
existedRowIndexes.Add(int.Parse(cdrw[exid].ToString()));
}
}
foreach (DataRow cdrw in dtp.IndependentDataLayout.Rows)
{
if (!existedRowIndexes.Contains(int.Parse(cdrw[exid].ToString())))
newRowIndexes.Add(int.Parse(cdrw[exid].ToString()));
}
/*
foreach (DataRow cdrw in dtp.IndependentDataLayout.Rows)
{
try
{
rows = rdt.Select(exid + " = '" + cdrw[exid] + "'");
}
catch { }
// in case if new rows - insert them
if (rows == null || rows.Length == 0)
{
newRowIndexes.Add(rowIdx);
}
else
{
// in case if existed rows - update them
//rows[0].Table.Rows.IndexOf(rows[0]);
existedRowIndexes.Add(rowIdx);
}
rowIdx++;
}*/
// insert
if (newRowIndexes.Count != 0)
{
data.Add("-- adding new records into table " + dtp.dataLayout.TableName);
dtp.SetSelectedRows(newRowIndexes.ToArray());
data.Add(new corelib().getSqlCommandWithDataTable(dtp, ApplicationConfiguration.Instance.GetValueByPath<string>("commandsConfiguration.insert"), true));
}
// update
data.Add("-- - --");
if (existedRowIndexes.Count != 0)
{
data.Add("-- updating new records into table " + dtp.dataLayout.TableName);
dtp.SetSelectedRows(existedRowIndexes.ToArray());
data.Add(new corelib().getSqlCommandWithDataTable(dtp, ApplicationConfiguration.Instance.GetValueByPath<string>("commandsConfiguration.update"), true));
}
// removal tool
data.Add("-- - --");
data.Add("-- removing records into table " + dtp.dataLayout.TableName);
foreach (DataRow rdrw in rdt.Rows)
{
// if current (dtp) row doesn't contain EXID in remote table
// we'll delete it
if (dtp.dataLayout.Select(exid + " = '" + rdrw[exid] + "'").Length == 0)
data.Add(string.Format("DELETE FROM {0} WHERE {2} = '{1}';", currentTableName, rdrw[exid], exid));
}
data.Add("-- - --");
}
if (msqConn.State != ConnectionState.Closed)
msqConn.Close();
return string.Join(Environment.NewLine, data.ToArray());
}
public static QueryComposer.Adapters.PCORI.PCORIModelAdapter CreatePCORIModelAdapterAdapter(string connectionString, Lpp.Dns.DataMart.Model.Settings.SQLProvider sqlProvider, string schema = null)
{
Dictionary <string, object> adapterSettings;
if (sqlProvider == Settings.SQLProvider.SQLServer)
{
var connectionStringBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder(connectionString);
adapterSettings = new Dictionary <string, object>()
{
{ "Server", connectionStringBuilder.DataSource },
{ "UserID", connectionStringBuilder.UserID },
{ "Password", connectionStringBuilder.Password },
{ "Database", connectionStringBuilder.InitialCatalog },
{ "DataProvider", sqlProvider.ToString() }
};
}
else if (sqlProvider == Settings.SQLProvider.PostgreSQL)
{
var postgresConnectionStringBuilder = new Npgsql.NpgsqlConnectionStringBuilder(connectionString);
adapterSettings = new Dictionary <string, object>()
{
{ "Server", postgresConnectionStringBuilder.Host },
{ "Port", postgresConnectionStringBuilder.Port.ToString() },
{ "UserID", postgresConnectionStringBuilder.Username },
//{"Password", System.Text.Encoding.UTF8.GetString(postgresConnectionStringBuilder.PasswordAsByteArray) },
{ "Password", postgresConnectionStringBuilder.Password },
{ "Database", postgresConnectionStringBuilder.Database },
{ "ConnectionTimeout", postgresConnectionStringBuilder.Timeout.ToString() },
{ "CommandTimeout", postgresConnectionStringBuilder.CommandTimeout.ToString() },
{ "DataProvider", sqlProvider.ToString() }
};
}
else if (sqlProvider == Settings.SQLProvider.Oracle)
{
var oracleConnectionStringBuilder = new Oracle.ManagedDataAccess.Client.OracleConnectionStringBuilder(connectionString);
adapterSettings = new Dictionary <string, object>()
{
{ "Server", "" },
{ "Port", "" },
{ "Database", "" },
{ "UserID", oracleConnectionStringBuilder.UserID },
{ "Password", oracleConnectionStringBuilder.Password },
{ "DataProvider", sqlProvider.ToString() },
{ "DatabaseSchema", schema }
};
//(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={server address})(PORT=1521))(CONNECT_DATA=(SERVICE_NAME={service name})))
System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex(@"\((?:[\w|\=|\.]+)\)");
var matches = regex.Matches(oracleConnectionStringBuilder.DataSource);
foreach (var m in matches)
{
string capture = m.ToString();
string[] split = capture.Substring(1, capture.Length - 2).Split(new[] { '=' });
if (string.Equals("HOST", split[0], StringComparison.OrdinalIgnoreCase))
{
adapterSettings["Server"] = split[1];
}
else if (string.Equals("PORT", split[0], StringComparison.OrdinalIgnoreCase))
{
adapterSettings["Port"] = split[1];
}
else if (string.Equals("SERVICE_NAME", split[0], StringComparison.OrdinalIgnoreCase))
{
adapterSettings["Database"] = split[1];
}
}
}
else if (sqlProvider == Settings.SQLProvider.MySQL)
{
var mysqlConnectionStringBuilder = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder(connectionString);
adapterSettings = new Dictionary <string, object>()
{
{ "Server", mysqlConnectionStringBuilder.Server },
{ "Port", mysqlConnectionStringBuilder.Port.ToString() },
{ "UserID", mysqlConnectionStringBuilder.UserID },
{ "Password", mysqlConnectionStringBuilder.Password },
{ "Database", mysqlConnectionStringBuilder.Database },
{ "ConnectionTimeout", mysqlConnectionStringBuilder.ConnectionTimeout.ToString() },
{ "CommandTimeout", mysqlConnectionStringBuilder.DefaultCommandTimeout.ToString() },
{ "DataProvider", sqlProvider.ToString() }
};
}
else
{
throw new NotImplementedException("Support for parsing configuration string into adapter settings not completed yet.");
}
var adapter = new QueryComposer.Adapters.PCORI.PCORIModelAdapter();
adapter.Initialize(adapterSettings);
return(adapter);
}
public new static void Tests()
{
System.Data.Common.DbConnectionStringBuilder dbcsb = new System.Data.Common.DbConnectionStringBuilder();
System.Data.SqlClient.SqlConnectionStringBuilder sqlcsb =
new System.Data.SqlClient.SqlConnectionStringBuilder();
sqlcsb.NetworkLibrary = "";
//sqlcsb.TransactionBinding
sqlcsb.TypeSystemVersion = "";
sqlcsb.UserInstance = true;
Npgsql.NpgsqlConnectionStringBuilder csb =
new Npgsql.NpgsqlConnectionStringBuilder();
// csb.Protocol
// csb.Compatible
//csb.SslMode = Npgsql.SslMode.Require;
// csb.UseExtendedTypes
System.Data.OracleClient.OracleConnectionStringBuilder ocb = new System.Data.OracleClient.OracleConnectionStringBuilder();
FirebirdSql.Data.FirebirdClient.FbConnectionStringBuilder fbcsb = new FirebirdSql.Data.FirebirdClient.FbConnectionStringBuilder();
fbcsb.ServerType = FirebirdSql.Data.FirebirdClient.FbServerType.Embedded;
Mono.Data.Sqlite.SqliteConnectionStringBuilder litecsb = new Mono.Data.Sqlite.SqliteConnectionStringBuilder();
litecsb.BinaryGUID = true;
litecsb.DateTimeFormat = Mono.Data.Sqlite.SQLiteDateFormats.ISO8601;
litecsb.DefaultIsolationLevel = System.Data.IsolationLevel.ReadCommitted;
litecsb.DefaultTimeout = 123;
litecsb.FailIfMissing = true;
litecsb.JournalMode = Mono.Data.Sqlite.SQLiteJournalModeEnum.Off;
litecsb.LegacyFormat = false;
litecsb.SyncMode = Mono.Data.Sqlite.SynchronizationModes.Full;
litecsb.Uri = "";
litecsb.Version = 123;
MySql.Data.MySqlClient.MySqlConnectionStringBuilder mycb = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder();
mycb.AllowBatch = true;
mycb.CertificateFile = "";
mycb.CertificatePassword = "";
mycb.CertificateStoreLocation = MySql.Data.MySqlClient.MySqlCertificateStoreLocation.LocalMachine;
mycb.CertificateThumbprint = "";
mycb.CharacterSet = "";
mycb.CheckParameters = true;
mycb.ConnectionLifeTime = 123;
mycb.ConnectionProtocol = MySql.Data.MySqlClient.MySqlConnectionProtocol.Tcp;
mycb.ConnectionReset = true;
mycb.ConvertZeroDateTime = false;
mycb.DefaultCommandTimeout = 123;
mycb.FunctionsReturnString = true;
mycb.IgnorePrepare = true;
mycb.InteractiveSession = false;
mycb.Keepalive = 123;
mycb.PipeName = "";
System.Data.Odbc.OdbcConnectionStringBuilder odbc = new System.Data.Odbc.OdbcConnectionStringBuilder();
//System.Data.OleDb.OleDbConnectionStringBuilder oledb = new System.Data.OleDb.OleDbConnectionStringBuilder();
} // End Sub
public MySqlUniversalConnectionStringBuilder()
{
this.m_ConnectionStringBuilder = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder();
}
public static int dbKaydet(string pIstasyon, string pZaman,
float pSicaklik,
float pIletkenlik,
float pPh,
float pOksijen,
float pDebi,
float pPompaHizi,
float pKSicaklik,
float pGirisGerilim,
float pCikisGerilim,
float pCikisFrekans,
float pCikisAkim,
Int16 pBakim,
Int16 pKalibrasyon,
Int16 pGeriYikama,
string pdiDegerleri)
{
MySql.Data.MySqlClient.MySqlConnectionStringBuilder bag = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder();
bag.Server = server;
bag.UserID = userName;
bag.Password = passWord;
bag.Database = dataBase;
MySql.Data.MySqlClient.MySqlConnection baglanti = new MySql.Data.MySqlClient.MySqlConnection(bag.ToString());
MySql.Data.MySqlClient.MySqlCommand sqlCom = new MySql.Data.MySqlClient.MySqlCommand();
sqlCom.Connection = baglanti;
sqlCom.CommandText = "INSERT INTO dsi.arsiv(Istasyon, Zaman, Sicaklik, Iletkenlik, Ph, Oksijen, Debi, PompaHizi, KSicaklik, GirisGerilim, CikisGerilim, CikisFrekans, CikisAkim,Bakim,Kalibrasyon,GeriYikama,diValues) VALUES(?p1,?p2,?p3,?p4,?p5,?p6,?p7,?p8,?p9,?p10,?p11,?p12,?p13,?p14,?p15,?p16,?p17)";
sqlCom.Parameters.Add("?p1", MySql.Data.MySqlClient.MySqlDbType.VarChar, 15).Value = pIstasyon;
sqlCom.Parameters.Add("?p2", MySql.Data.MySqlClient.MySqlDbType.DateTime, 15).Value = modbus.nvl(pZaman,DateTime.Now.ToString());
sqlCom.Parameters.Add("?p3", MySql.Data.MySqlClient.MySqlDbType.Float, 5).Value = modbus.nvl(pSicaklik.ToString(),"0");
sqlCom.Parameters.Add("?p4", MySql.Data.MySqlClient.MySqlDbType.Float, 5).Value = modbus.nvl(pIletkenlik.ToString(), "0");
sqlCom.Parameters.Add("?p5", MySql.Data.MySqlClient.MySqlDbType.Float, 5).Value = modbus.nvl(pPh.ToString(), "0");
sqlCom.Parameters.Add("?p6", MySql.Data.MySqlClient.MySqlDbType.Float, 5).Value = modbus.nvl(pOksijen.ToString(), "0");
if (pDebi > 100000)
{ pDebi = -1; }
sqlCom.Parameters.Add("?p7", MySql.Data.MySqlClient.MySqlDbType.Float, 5).Value = modbus.nvl(pDebi.ToString(), "0");
sqlCom.Parameters.Add("?p8", MySql.Data.MySqlClient.MySqlDbType.Float, 5).Value = modbus.nvl(pPompaHizi.ToString(), "0");
sqlCom.Parameters.Add("?p9", MySql.Data.MySqlClient.MySqlDbType.Float, 5).Value = modbus.nvl(pKSicaklik.ToString(), "0");
sqlCom.Parameters.Add("?p10", MySql.Data.MySqlClient.MySqlDbType.Float, 5).Value = modbus.nvl(pGirisGerilim.ToString(), "0");
sqlCom.Parameters.Add("?p11", MySql.Data.MySqlClient.MySqlDbType.Float, 5).Value = modbus.nvl(pCikisGerilim.ToString(), "0");
sqlCom.Parameters.Add("?p12", MySql.Data.MySqlClient.MySqlDbType.Float, 5).Value = modbus.nvl(pCikisFrekans.ToString(), "0");
sqlCom.Parameters.Add("?p13", MySql.Data.MySqlClient.MySqlDbType.Float, 5).Value = modbus.nvl(pCikisAkim.ToString(), "0");
sqlCom.Parameters.Add("?p14", MySql.Data.MySqlClient.MySqlDbType.Int16, 1).Value = modbus.nvl(pBakim.ToString(), "0");
sqlCom.Parameters.Add("?p15", MySql.Data.MySqlClient.MySqlDbType.Int16, 1).Value = modbus.nvl(pKalibrasyon.ToString(), "0");
sqlCom.Parameters.Add("?p16", MySql.Data.MySqlClient.MySqlDbType.Int16, 1).Value = modbus.nvl(pGeriYikama.ToString(), "0");
sqlCom.Parameters.Add("?p17", MySql.Data.MySqlClient.MySqlDbType.VarChar, 16).Value = pdiDegerleri;
//sqlCom.Parameters.Add("?p4", MySql.Data.MySqlClient.MySqlDbType.VarChar, 51).Value = personel;
////sqlCom.Parameters.Add("?p5", MySql.Data.MySqlClient.MySqlDbType.VarChar, 10).Value = tarih;
////sqlCom.Parameters.Add("?p6", MySql.Data.MySqlClient.MySqlDbType.VarChar, 8).Value = saat;
//sqlCom.Parameters.Add("?p7", MySql.Data.MySqlClient.MySqlDbType.VarChar, 100).Value = adres;
//sqlCom.Parameters.Add("?p10", MySql.Data.MySqlClient.MySqlDbType.Int32).Value = id;
//sqlCom.Parameters.Add("?p8", MySql.Data.MySqlClient.MySqlDbType.VarChar, 8).Value = SlaveId;
//sqlCom.Parameters.Add("?p9", MySql.Data.MySqlClient.MySqlDbType.VarChar, 100).Value = Port;
//sqlCom.Parameters.Add("?p11", MySql.Data.MySqlClient.MySqlDbType.VarChar, 100).Value = SerialNo;
//sqlCom.Parameters.Add("?p12", MySql.Data.MySqlClient.MySqlDbType.VarChar, 25).Value = Email;
////sqlCom.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("?DateParameter", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")));
////sqlCom.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("?IpParameter", cihazIP.ToString()));
try
{
baglanti.Open();
int executed = sqlCom.ExecuteNonQuery();
sqlCom.Parameters.Clear();
baglanti.Close();
return 2;
}
catch (Exception er)
{
throw new Exception("Veritabaný veri kaydedilemedi "+er.Message);
// 3;
}
finally
{
sqlCom.Parameters.Clear();
}
}
public static bool dbsifreUpdate(string yKullanici,string ySifre, string eSifre)
{
MySql.Data.MySqlClient.MySqlConnectionStringBuilder bag = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder();
bag.Server = modbus.server;//örn: csharptr.com
bag.UserID = modbus.userName;
bag.Password = modbus.passWord;
bag.Database = modbus.dataBase;
MySql.Data.MySqlClient.MySqlConnection baglanti = new MySql.Data.MySqlClient.MySqlConnection(bag.ToString());
MySql.Data.MySqlClient.MySqlCommand sqlCom = new MySql.Data.MySqlClient.MySqlCommand();
sqlCom.Connection = baglanti;
sqlCom.CommandText = "UPDATE dsi.yetkilendirme SET sifre = '" + ySifre + "' WHERE sifre = '" + eSifre +"' and kullanici='"+yKullanici+"';";
//MySql.Data.MySqlClient.MySqlDataReader myDataReader;
try
{
baglanti.Open();
sqlCom.ExecuteNonQuery();
//myDataReader = sqlCom.ExecuteReader();
//myDataReader.Read();
/* Tell the Paging System what to Display based on Results of the Search Query */
//if (myDataReader.HasRows)
//{
// /* Matches are found, format and display */
// while (myDataReader.Read() != false)
// {
// dsifre = myDataReader.GetString("sifre");
// }
//}
//myDataReader.Close();
baglanti.Close();
sqlCom.Parameters.Clear();
return true;
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show("MysqlUpdateError" + ex);
return false;
}
}
public static bool dbsifreKontrol(string yKullanici,string ySifre)
{
string dsifre = "";
MySql.Data.MySqlClient.MySqlConnectionStringBuilder bag = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder();
bag.Server = modbus.server;//örn: csharptr.com
bag.UserID = modbus.userName;
bag.Password = modbus.passWord;
bag.Database = modbus.dataBase;
MySql.Data.MySqlClient.MySqlConnection baglanti = new MySql.Data.MySqlClient.MySqlConnection(bag.ToString());
MySql.Data.MySqlClient.MySqlCommand sqlCom = new MySql.Data.MySqlClient.MySqlCommand();
sqlCom.Connection = baglanti;
sqlCom.CommandText = "SELECT sifre FROM dsi.yetkilendirme where kullanici=?p1;";
sqlCom.Parameters.Add("?p1", MySql.Data.MySqlClient.MySqlDbType.VarChar, 15).Value = yKullanici;
MySql.Data.MySqlClient.MySqlDataReader myDataReader;
try
{
baglanti.Open();
myDataReader = sqlCom.ExecuteReader();
//myDataReader.Read();
/* Tell the Paging System what to Display based on Results of the Search Query */
if (myDataReader.HasRows)
{
/* Matches are found, format and display */
while (myDataReader.Read() != false)
{
dsifre = myDataReader.GetString("sifre");
}
}
myDataReader.Close();
baglanti.Close();
sqlCom.Parameters.Clear();
if (dsifre == ySifre) return true;
else return false;
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show("MysqlUpdateError" + ex);
return false;
}
}
public void Run()
{
var Host = string.Empty;
if (!Framework.Get<SettingsComponent>().TryPop<string>("Trebuchet.Database.Host", out Host))
{
Trebuchet.ThrowException("Failed to get the Database Host.");
Trebuchet.ThrowException("Check Setting 'Trebuchet.Database.Host', it must be a valid string.");
return;
}
var Port = default(uint);
if (!Framework.Get<SettingsComponent>().TryPop<uint>("Trebuchet.Database.Port", out Port))
{
Trebuchet.ThrowException("Failed to get the Database Port.");
Trebuchet.ThrowException("Check Setting 'Trebuchet.Database.Port', it must be a valid uinteger.");
return;
}
var Username = string.Empty;
if (!Framework.Get<SettingsComponent>().TryPop<string>("Trebuchet.Database.Username", out Username))
{
Trebuchet.ThrowException("Failed to get the Database Username.");
Trebuchet.ThrowException("Check Setting 'Trebuchet.Database.Username', it must be a valid string.");
return;
}
var Database = string.Empty;
if (!Framework.Get<SettingsComponent>().TryPop<string>("Trebuchet.Database.Database", out Database))
{
Trebuchet.ThrowException("Failed to get the Database Database.");
Trebuchet.ThrowException("Check Setting 'Trebuchet.Database.Database', it must be a valid string.");
return;
}
var Password = string.Empty;
if (!Framework.Get<SettingsComponent>().TryPop<string>("Trebuchet.Database.Password", out Password))
{
Trebuchet.ThrowException("Failed to get the Database Password.");
Trebuchet.ThrowException("Check Setting 'Trebuchet.Database.Password', it must be a valid string.");
return;
}
var Pooling = default(bool);
if (!Framework.Get<SettingsComponent>().TryPop<bool>("Trebuchet.Database.Pooling.Enabled", out Pooling))
{
Trebuchet.ThrowException("Failed to get the Database Pooling.");
Trebuchet.ThrowException("Check Setting 'Trebuchet.Database.Pooling.Enabled', it must be a valid bool.");
return;
}
var MinimumPooling = default(uint);
if (!Framework.Get<SettingsComponent>().TryPop<uint>("Trebuchet.Database.Pooling.Minimal", out MinimumPooling))
{
Trebuchet.ThrowException("Failed to get the Database Minimal Pooling.");
Trebuchet.ThrowException("Check Setting 'Trebuchet.Database.Pooling.Minimal', it must be a valid uinteger.");
return;
}
var MaximalPooling = default(uint);
if (!Framework.Get<SettingsComponent>().TryPop<uint>("Trebuchet.Database.Pooling.Maximal", out MaximalPooling))
{
Trebuchet.ThrowException("Failed to get the Database Maximal Pooling.");
Trebuchet.ThrowException("Check Setting 'Trebuchet.Database.Pooling.Maximal', it must be a valid uinteger.");
return;
}
var Builder = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder();
Builder.Server = Host;
Builder.Port = Port;
Builder.UserID = Username;
Builder.Database = Database;
Builder.Password = Password;
Builder.Pooling = Pooling;
Builder.MinimumPoolSize = MinimumPooling;
Builder.MaximumPoolSize = MaximalPooling;
this.ConnectionString = Builder.ConnectionString;
}
/// <summary>
/// Configura a string de conexão de acordo com o banco de dados definido no arquivo de configuração RegLog.config.
/// </summary>
private static void ConfigConnection()
{
var path = Path.GetDirectoryName(Assembly.GetAssembly(typeof(LogModel)).Location);
var xmlConfig = new XmlTextReader(path + "\\RegLog.config");
while (xmlConfig.Read())
{
if (xmlConfig.NodeType != XmlNodeType.Element) continue;
var elementos = new string[] {"host","port", "database", "databasename", "connectiontimeout","connectionlifetime", "user", "password"};
if (!elementos.Contains(xmlConfig.Name)) continue;
_host = xmlConfig.Name.ToLower().Equals("host") ? xmlConfig.ReadString() : _host;
_port = xmlConfig.Name.ToLower().Equals("port") && !string.IsNullOrWhiteSpace(xmlConfig.ReadString()) ? Convert.ToInt32(xmlConfig.ReadString()) : _port;
_database = xmlConfig.Name.ToLower().Equals("database") ? Convert.ToInt32(xmlConfig.ReadString()) : _database;
_dbName = xmlConfig.Name.ToLower().Equals("databasename") ? xmlConfig.ReadString() : _dbName;
_timeOut = xmlConfig.Name.ToLower().Equals("connectiontimeout") ? Convert.ToInt32(xmlConfig.ReadString()) : _timeOut;
_lifeTime = xmlConfig.Name.ToLower().Equals("connectionlifetime") ? Convert.ToInt32(xmlConfig.ReadString()) : _lifeTime;
_user = xmlConfig.Name.ToLower().Equals("user") ? xmlConfig.ReadString() : _user;
_password = xmlConfig.Name.ToLower().Equals("password") ? xmlConfig.ReadString() : _password;
}
switch (_database)
{
case 0: //SQLITE
var sqlite = new SQLiteConnection(Path.Combine(_host, _dbName));
_connection = sqlite.ConnectionString;
break;
case 1: //MSSQL
case 11:
var mssql = new System.Data.SqlClient.SqlConnectionStringBuilder
{
ConnectTimeout = _timeOut,
DataSource = _host,
Enlist = true,
InitialCatalog = _dbName,
UserID = _user,
Password = _password,
IntegratedSecurity = false,
LoadBalanceTimeout = _lifeTime,
};
_connection = mssql.ConnectionString;
break;
case 19: //MSSQL CE
var sqlce = new SqlCeConnectionStringBuilder
{
DataSource = Path.Combine(_host, _dbName),
Enlist = true,
Password = _password,
FileAccessRetryTimeout = _timeOut,
};
_connection = sqlce.ConnectionString;
break;
case 2: //MYSQL
var mysql = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder
{
Database = _dbName,
Server = _host,
Password = _password,
UserID = _user,
ConnectionLifeTime = (uint)_lifeTime,
ConnectionTimeout = (uint)_timeOut,
IntegratedSecurity = false,
AutoEnlist = true
};
if(_port > 0)
mysql.Port = (uint)_port;
_connection = mysql.ConnectionString;
break;
case 3: //POSTGRESQL
var pgsql = new Npgsql.NpgsqlConnectionStringBuilder
{
Database = _dbName,
Host = _host,
Password = _password,
UserName = _user,
ConnectionLifeTime = _lifeTime,
Timeout = _timeOut,
IntegratedSecurity = false,
Enlist = true
};
if (_port > 0)
pgsql.Port = _port;
_connection = pgsql.ConnectionString;
break;
case 4: //DB2
//var db2 =
// DB2Configuration.Standard.ConnectionString(
// c => c.Database(_dbName).Server(_host).Username(_user).Password(_password));
break;
case 5: //FIREBIRD
var firebird = new FirebirdSql.Data.FirebirdClient.FbConnectionStringBuilder
{
Database = _dbName,
Password = _password,
ConnectionLifeTime = _lifeTime,
Enlist = true,
UserID = _user,
DataSource = _host,
ConnectionTimeout = _timeOut
};
if (_port > 0)
firebird.Port = _port;
_connection = firebird.ConnectionString;
break;
case 6: //ORACLE 9
case 61: //ORACLE 10
var oracle = new Oracle.DataAccess.Client.OracleConnectionStringBuilder
{
Password = _password,
ConnectionLifeTime = _lifeTime,
Enlist = "true",
UserID = _user,
DataSource = _dbName,
ConnectionTimeout = _timeOut
};
oracle["Server"] = _host;
_connection = oracle.ConnectionString;
break;
default:
_connection = "";
break;
}
}
/// <summary>
/// ����һ������MySQL ODBC 3.51��������������Դ�������ַ���
/// </summary>
/// <param name="server">������</param>
/// <param name="userID">�û���</param>
/// <param name="password">����</param>
/// <param name="database">���ݿ�</param>
/// <returns></returns>
public static string MakeConnectionString(string server, string userID, string password, string database)
{
MySql.Data.MySqlClient.MySqlConnectionStringBuilder builder = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder();
builder.Server = server;
builder.UserID = userID;
builder.Password = password;
builder.Database = database;
return builder.GetConnectionString(true);
}
private static string GetCS(DataBase db, System.Data.Common.DbProviderFactory df)
{
if (object.ReferenceEquals(df.GetType(), typeof(System.Data.SqlClient.SqlClientFactory)))
{
System.Data.SqlClient.SqlConnectionStringBuilder csb = new System.Data.SqlClient.SqlConnectionStringBuilder();
csb.DataSource = db.Server;
if (db.Port.HasValue && db.Port.Value > 0)
{
csb.DataSource += "," + db.Port.Value.ToString(System.Globalization.CultureInfo.InvariantCulture);
}
csb.InitialCatalog = db.Database;
if (db.IntegratedSecurity.HasValue)
{
csb.IntegratedSecurity = db.IntegratedSecurity.Value;
}
else
{
csb.IntegratedSecurity = false;
}
if (!csb.IntegratedSecurity)
{
csb.UserID = db.Username;
try
{
csb.Password = new Des3EncryptionService().DeCrypt(db.Password);
}
catch (System.Exception)
{
// Password is unencrypted ...
csb.Password = db.Password;
}
} // End if (!csb.IntegratedSecurity)
csb.PacketSize = 4096;
csb.PersistSecurityInfo = false;
csb.ApplicationName = "LDAP-Service";
csb.MultipleActiveResultSets = false;
csb.WorkstationID = System.Environment.MachineName;
return(csb.ConnectionString);
} // End if (object.ReferenceEquals(this.m_db.ProviderFactory.GetType(), typeof(System.Data.SqlClient.SqlClientFactory)))
if (object.ReferenceEquals(df.GetType(), typeof(Npgsql.NpgsqlFactory)))
{
Npgsql.NpgsqlConnectionStringBuilder csb = new Npgsql.NpgsqlConnectionStringBuilder();
csb.Host = db.Server;
if (db.Port.HasValue && db.Port.Value > 0)
{
csb.Port = db.Port.Value;
}
else
{
csb.Port = 5432;
}
csb.Database = db.Database;
if (db.IntegratedSecurity.HasValue)
{
csb.IntegratedSecurity = db.IntegratedSecurity.Value;
}
else
{
csb.IntegratedSecurity = false;
}
if (!csb.IntegratedSecurity)
{
csb.Username = db.Username;
try
{
csb.Password = new Des3EncryptionService().DeCrypt(db.Password);
}
catch (System.Exception)
{
// Password is unencrypted ...
csb.Password = db.Password;
}
} // End if (!csb.IntegratedSecurity)
csb.PersistSecurityInfo = false;
csb.ApplicationName = "LDAP-Service";
csb.CommandTimeout = 30;
csb.Timeout = 15;
return(csb.ConnectionString);
} // End if (object.ReferenceEquals(this.m_db.ProviderFactory.GetType(), typeof(Npgsql.NpgsqlFactory)))
if (object.ReferenceEquals(df.GetType(), typeof(MySql.Data.MySqlClient.MySqlClientFactory)))
{
MySql.Data.MySqlClient.MySqlConnectionStringBuilder csb =
new MySql.Data.MySqlClient.MySqlConnectionStringBuilder();
csb.Server = db.Server;
if (db.Port.HasValue && db.Port.Value > 0)
{
csb.Port = (uint)db.Port.Value;
}
else
{
csb.Port = 3306u;
}
csb.Database = db.Database;
csb.PersistSecurityInfo = false;
csb.ApplicationName = "LDAP-Service";
csb.DefaultCommandTimeout = 30;
csb.ConnectionTimeout = 15;
return(csb.ConnectionString);
}
System.Data.Common.DbConnectionStringBuilder dbcsb = df.CreateConnectionStringBuilder();
dbcsb.Add("Server", db.Server);
if (db.Port.HasValue && db.Port.Value > 0)
{
dbcsb.Add("Port", db.Port);
}
dbcsb.Add("Database", db.Database);
if (db.IntegratedSecurity.HasValue)
{
dbcsb.Add("IntegratedSecurity", db.IntegratedSecurity);
}
else
{
dbcsb.Add("IntegratedSecurity", false);
}
if (db.IntegratedSecurity.HasValue && !db.IntegratedSecurity.Value)
{
dbcsb.Add("User Id", db.Username);
try
{
dbcsb.Add("Password", new Des3EncryptionService().DeCrypt(db.Password));
}
catch (System.Exception)
{
// Password is unencrypted ...
dbcsb.Add("Password", db.Password);
}
} // End if (!csb.IntegratedSecurity)
return(dbcsb.ConnectionString);
}
public static bool Upload(string data, Hashtable connect, DataLayoutCollection layouts)
{
/*
System.Data.SqlClient.SqlConnectionStringBuilder connString = new System.Data.SqlClient.SqlConnectionStringBuilder();
//connString.DataSource = "localhost";
connString.UserID = "root";
connString.Password = "******";
connString.InitialCatalog = "mpwsdbu_test";
connString.NetworkLibrary = "dbmssocn";
connString.DataSource = "localhost";
connString.Add("server", @".\\MySQL");
System.Data.SqlClient.SqlConnection dataConnect = new System.Data.SqlClient.SqlConnection(connString.ToString());
dataConnect.Open();
System.Data.SqlClient.SqlCommand uploadCmd = new System.Data.SqlClient.SqlCommand(data, dataConnect);
uploadCmd.ExecuteNonQuery();
dataConnect.Close();
*/
if (data == null || data == string.Empty)
return false;
if (ApplicationConfiguration.Instance.GetValueByPath<bool>("formatConfiguration.autoBackup"))
new corelib().Backup(layouts.GetDataTableNames().ToArray(), connect);
bool frez = true;
MySql.Data.MySqlClient.MySqlConnectionStringBuilder msqConnStr = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder();
msqConnStr.UserID = connect["UserID"].ToString();
msqConnStr.Password = connect["Password"].ToString();
msqConnStr.Database = connect["Database"].ToString();
msqConnStr.Server = connect["Server"].ToString();
msqConnStr.CharacterSet = "cp1251";
msqConnStr.AllowZeroDateTime = true;
MySql.Data.MySqlClient.MySqlConnection msqConn = new MySql.Data.MySqlClient.MySqlConnection(msqConnStr.ToString());
try
{
msqConn.Open();
/*
Encoding utf8 = Encoding.GetEncoding(866);
Encoding win1251 = Encoding.Default;
byte[] utf8Bytes = win1251.GetBytes(data);
byte[] win1251Bytes = Encoding.Convert(win1251, Encoding.UTF8, utf8Bytes);
string newdata = win1251.GetString(win1251Bytes);
*/
string[] packages = data.Split(new string[] { "-- - --" }, StringSplitOptions.RemoveEmptyEntries);
foreach (string command in packages)
{
MySql.Data.MySqlClient.MySqlCommand uploaderCmd = new MySql.Data.MySqlClient.MySqlCommand(command, msqConn);
uploaderCmd.ExecuteNonQuery();
}
}
catch (Exception ex) { MessageBox.Show(ex.Message); frez = false; }
if (msqConn.State == System.Data.ConnectionState.Open)
msqConn.Close();
return frez;
}
