public static string ParseParameter(
string connectionstring_in,
eDBServerTypes dbServerTypes_in,
eParameter parameter_in
) {
switch (parameter_in) {
case eParameter.Database: {
return ParseParameter(connectionstring_in, dbServerTypes_in, "database");
}
case eParameter.Server: {
return ParseParameter(connectionstring_in, dbServerTypes_in, "server");
}
case eParameter.User: {
switch (dbServerTypes_in) {
#if PostgreSQL
case eDBServerTypes.PostgreSQL: {
return ParseParameter(connectionstring_in, dbServerTypes_in, "User ID");
}
#endif
#if MySQL
case eDBServerTypes.MySQL:
#endif
case eDBServerTypes.SQLServer: {
return ParseParameter(connectionstring_in, dbServerTypes_in, "uid");
}
}
break;
}
}
throw new Exception(
string.Format(
"{0}.{1}.ParseParameter(): - error parsing db connectionstring: '{2}'",
typeof(Connectionstring).Namespace,
typeof(Connectionstring).Name,
connectionstring_in
)
);
}
public static string ParseParameter(
string connectionstring_in,
eDBServerTypes dbServerTypes_in,
string parameter_in
) {
System.Text.RegularExpressions.Regex fields_reg = new System.Text.RegularExpressions.Regex(
string.Format("^(?<before>.*){0}=(?<param>.*);(?<after>.*)", parameter_in),
System.Text.RegularExpressions.RegexOptions.IgnoreCase
);
System.Text.RegularExpressions.Match matchingfields = fields_reg.Match(connectionstring_in);
if (!matchingfields.Success) {
throw new Exception(
string.Format(
"{0}.{1}.ParseParameter(): - error parsing db connectionstring: '{2}'",
typeof(Connectionstring).Namespace,
typeof(Connectionstring).Name,
connectionstring_in
)
);
} else {
return matchingfields.Groups["param"].Value;
}
}
// <summary>
// Aimed for code generator, hence returning straight string.
// </summary>
public static string DBType2DBEmptyValue(DbType dbType_in, eDBServerTypes dbServerType_in) {
switch (dbType_in) {
case DbType.DateTime:
case DbType.Date:
case DbType.Time: {
return object2SQLobject(
new DateTime(1900, 1, 1),
dbServerType_in
);
}
case DbType.Int16:
case DbType.Int32:
case DbType.Int64:
case DbType.UInt16:
case DbType.UInt32:
case DbType.UInt64:
case DbType.Byte:
case DbType.Single:
case DbType.Double:
case DbType.Decimal:
case DbType.Boolean: {
return "0";
}
case DbType.String: {
return "''";
}
case DbType.Binary: {
return "new Byte[] {}";
}
// ToDos: here!
}
throw new Exception(string.Format(
"undefined variable type: {0}",
dbType_in.ToString() // dbType_in.GetType().ToString()
));
}
/// <summary>
/// Converts any .net framework object value to a string that can be used to buil an SQL Query.
/// </summary>
/// <param name="object_in">.net framework object value to be converted</param>
/// <param name="dbServerType_in">DataBase Server Type</param>
/// <returns>a string that can be used to buil an SQL Query</returns>
public static string object2SQLobject(object object_in, eDBServerTypes dbServerType_in) {
if (object_in == null) {
switch (dbServerType_in) {
#if PostgreSQL
case eDBServerTypes.PostgreSQL:
#endif
#if MySQL
case eDBServerTypes.MySQL:
#endif
case eDBServerTypes.SQLServer: {
return "null";
}
}
} else {
switch (object_in.GetType().ToString()) {
case "System.Char":
case "System.String": {
switch (dbServerType_in) {
//case eDBServerTypes.PostgreSQL: {
// // ToDos: here! check if changes made are correct (I need test units for this)
// // return string.Format("''{0}''", object_in.ToString ().Replace("'", "''"));
// return string.Format("'{0}'", object_in.ToString ().Replace("'", "''"));
//}
case eDBServerTypes.SQLServer: {
return string.Format("\'{0}\'", object_in.ToString ().Replace("\'", "\'\'"));
}
}
break;
}
case "System.DateTime": {
DateTime _datetime = ((DateTime)object_in);
if (DateTime.Compare(_datetime, DateTime.MinValue) == 0) {
return object2SQLobject(null, dbServerType_in);
} else {
switch (dbServerType_in) {
#if MySQL
case eDBServerTypes.MySQL:
#endif
#if PostgreSQL
case eDBServerTypes.PostgreSQL:
// ToDos: here! check if changes made are correct (I need test units for this)
// return string.Format("timestamp ''{0}''", _datetime.ToString("yyyy-MM-dd HH:mm:ss"));
return string.Format("timestamp '{0}'", _datetime.ToString("yyyy-MM-dd HH:mm:ss"));
#endif
case eDBServerTypes.SQLServer:
return string.Format("CONVERT(DATETIME, \'{0}\', 120)", _datetime.ToString("yyyy-MM-dd HH:mm:ss"));
}
}
break;
}
case "System.Boolean": {
switch (dbServerType_in) {
#if MySQL
case eDBServerTypes.MySQL:
#endif
#if PostgreSQL
case eDBServerTypes.PostgreSQL:
return (((bool)object_in) ? "true" : "false");
#endif
case eDBServerTypes.SQLServer:
return (((bool)object_in) ? "1" : "0");
}
break;
}
case "System.Int16":
case "System.Int32":
case "System.Int64":
case "System.Double":
case "System.Decimal":
case "System.Single": {
switch (dbServerType_in) {
//case eDBServerTypes.PostgreSQL: {
// return object_in.ToString().Replace(",", ".");
//}
case eDBServerTypes.SQLServer: {
// ToDos: here! this will likely change accordingly with regional
// settings configurations, I need to come up with a better
// approach to this:
return object_in.ToString().Replace(",", ".");
}
}
break;
}
case "System.DBNull": {
return object2SQLobject(null, dbServerType_in);
}
}
}
throw new Exception (string.Format(
"not implemented for: {0}",
object_in.GetType().ToString()
));
}
private static string sqlview_delete(string name_in, eDBServerTypes dbServerType_in) {
switch (dbServerType_in) {
case eDBServerTypes.SQLServer:
return string.Format(
"DROP VIEW {0}",
name_in
);
#if MySQL
case eDBServerTypes.MySQL:
return string.Format(
"DROP VIEW `{0}`",
name_in
);
#endif
#if PostgreSQL
case eDBServerTypes.PostgreSQL:
// ToDos: here! not implemented, needed if droping,
// no need when replacing, you can use:
// CREATE OR REPLACE VIEW "some_view"
#endif
default: {
throw new Exception(
string.Format(
"{0}.{1}.sqlview_delete(): - not implemented!",
typeof(cDBConnection).Namespace,
typeof(cDBConnection).Name
)
);
}
}
}
//---
#region public void Parameters(...);
/// <summary>
/// Uses a range of parameters to build the ConnectionString.
/// </summary>
/// <param name="serverName_in">database server name/ip</param>
/// <param name="userName_in">database user</param>
/// <param name="userPassword_in">database user password</param>
/// <param name="dataBaseName_in">database name</param>
/// <param name="dbServerTypes_in">database type</param>
/// <returns>ConnectionString</returns>
public static string Parameters(
string serverName_in,
string userName_in,
string userPassword_in,
string dataBaseName_in,
eDBServerTypes dbServerTypes_in
) {
switch (dbServerTypes_in) {
case eDBServerTypes.SQLServer:
return string.Format(
"server={0};uid={1};pwd={2};database={3};",
serverName_in,
userName_in,
userPassword_in,
dataBaseName_in
);
#if PostgreSQL
case eDBServerTypes.PostgreSQL:
return string.Format(
"Server={0};User ID={1};Password={2};Database={3};",
serverName_in,
userName_in,
userPassword_in,
dataBaseName_in
);
#endif
#if MySQL
case eDBServerTypes.MySQL:
return string.Format(
"Server={0};Uid={1};Pwd={2};Database={3};",
serverName_in,
userName_in,
userPassword_in,
dataBaseName_in
);
#endif
case eDBServerTypes.ODBC:
case eDBServerTypes.Access:
case eDBServerTypes.Excel:
default:
throw new Exception("invalid DBServerType");
}
}
private static string sqlstoredprocedure_delete(string name_in, eDBServerTypes dbServerType_in) {
switch (dbServerType_in) {
case eDBServerTypes.SQLServer: {
return string.Format(
"DROP PROCEDURE {0}",
name_in
);
}
#if MySQL
case eDBServerTypes.MySQL: {
return string.Format(
"DROP PROCEDURE `{0}`",
name_in
);
}
#endif
#if PostgreSQL
case eDBServerTypes.PostgreSQL:
// ToDos: here! not implemented
// NOTES: It's not as easy as it is for SQLServer and MySQL. PostgreSQL
// allows you to create diferent signatures for the same procedure, so in
// order to drop a procedure we need to know the parameters for such
// procedure.
// To overcome such probleme, remember that in PostgreSQL you can use:
// CREATE OR REPLACE PROCEDURE "some_procedure"
// PostgreSQL supports Stored Procedures.
#endif
default: {
throw new Exception(
string.Format(
"{0}.{1}.sqlstoredprocedure_delete(): - not implemented!",
typeof(cDBConnection).Namespace,
typeof(cDBConnection).Name
)
);
}
}
}
private /*static*/ string sqlview_exists(string name_in, eDBServerTypes dbServerType_in) {
switch (dbServerType_in) {
#if PostgreSQL
case eDBServerTypes.PostgreSQL:
#endif
case eDBServerTypes.SQLServer:
return string.Format(
#region "SELECT ...",
@"
SELECT null
FROM INFORMATION_SCHEMA.TABLES
WHERE
(TABLE_TYPE = 'VIEW')
AND
(TABLE_NAME = '{0}')
",
#endregion
name_in
);
#if MySQL
case eDBServerTypes.MySQL:
string _database = Connectionstring_database();
return string.Format(
#region "SELECT ...",
@"
SELECT null
FROM INFORMATION_SCHEMA.TABLES
WHERE
(TABLE_TYPE = 'VIEW')
AND
(TABLE_NAME = '{0}')
AND
(TABLE_SCHEMA = '{1}')
",
#endregion
name_in,
_database
);
#endif
default:
throw new Exception(string.Format(
"{0}.{1}.sqlview_exists(): - not implemented!",
typeof(cDBConnection).Namespace,
typeof(cDBConnection).Name
));
}
}
private /*static*/ string sqlstoredprocedure_exists(string name_in, eDBServerTypes dbServerType_in) {
switch (dbServerType_in) {
#if PostgreSQL
case eDBServerTypes.PostgreSQL:
#endif
case eDBServerTypes.SQLServer:
return string.Format(
#region "SELECT ...",
@"
SELECT null
FROM INFORMATION_SCHEMA.ROUTINES
WHERE
(routine_type = 'PROCEDURE')
AND
(routine_name = '{0}')
",
#endregion
name_in
);
#if MySQL
case eDBServerTypes.MySQL:
string _database = Connectionstring_database();
return string.Format(
#region "SELECT ...",
@"
SELECT null
FROM INFORMATION_SCHEMA.ROUTINES
WHERE
(routine_type = 'PROCEDURE')
AND
(routine_name = '{0}')
AND
(routine_schema = '{1}')
",
#endregion
name_in,
_database
);
#endif
default: {
throw new Exception("not implemented");
}
}
}
private static string sqlfunction_delete(string name_in, eDBServerTypes dbServerType_in) {
switch (dbServerType_in) {
case eDBServerTypes.SQLServer: {
return string.Format(
"DROP FUNCTION {0}",
name_in
);
}
#if MySQL
case eDBServerTypes.MySQL: {
return string.Format(
"DROP FUNCTION `{0}`",
name_in
);
}
#endif
#if PostgreSQL
case eDBServerTypes.PostgreSQL:
// ToDos: here! not implemented
// NOTES: It's not as easy as it is for SQLServer and MySQL. PostgreSQL
// allows you to create diferent signatures for the same function, so in
// order to drop a function we need to know the parameters for such
// function.
// To overcome such probleme, remember that in PostgreSQL you can use:
// CREATE OR REPLACE FUNCTION "some_function"
#endif
default: {
throw new Exception(
string.Format(
"{0}.{1}.sqlfunction_delete(): - not implemented!",
typeof(cDBConnection).Namespace,
typeof(cDBConnection).Name
)
);
}
}
}
private /*static*/ string sqlfunction_exists(string name_in, eDBServerTypes dbServerType_in) {
switch (dbServerType_in) {
#if PostgreSQL
case eDBServerTypes.PostgreSQL:
#endif
case eDBServerTypes.SQLServer:
return string.Format(
#region "SELECT ...",
@"
SELECT null
FROM INFORMATION_SCHEMA.ROUTINES
WHERE
(routine_type = 'FUNCTION')
AND
(routine_name = '{0}')
",
#endregion
name_in
);
#if MySQL
case eDBServerTypes.MySQL:
string _database = Connectionstring_database();
return string.Format(
#region "SELECT ...",
@"
SELECT null
FROM INFORMATION_SCHEMA.ROUTINES
WHERE
(routine_type = 'FUNCTION')
AND
(routine_name = '{0}')
AND
(routine_schema = '{1}')
",
#endregion
name_in,
_database
);
#endif
default:
throw new Exception(string.Format(
"{0}.{1}.sqlfunction_exists(): - not implemented!",
typeof(cDBConnection).Namespace,
typeof(cDBConnection).Name
));
}
}
/// <param name="dbServerType_in">DataBase Server Type</param>
/// <param name="connectionstring_in">Connection String</param>
/// <param name="Logfile_in">Log File</param>
public cDBConnection(
eDBServerTypes dbServerType_in,
string connectionstring_in,
string Logfile_in
) : this (
dbServerType_in,
connectionstring_in
) {
if (
(Logfile_in == null)
||
(Logfile_in == string.Empty)
||
(!File.Exists(Logfile_in))
) {
Logenabled = false;
Logfile = null;
} else {
Logenabled = true;
Logfile = Logfile_in;
}
}
// #region public cDBConnection(...);
/// <param name="dbServerType_in">DataBase Server Type</param>
/// <param name="connectionstring_in">Connection String</param>
public cDBConnection(
eDBServerTypes dbServerType_in,
string connectionstring_in
) {
Logenabled = false;
Logfile = null;
dbservertype_ = dbServerType_in;
isopen_ = false;
connectionstring_ = connectionstring_in;
}
