/// <summary>コンストラクタ</summary>
/// <param name="dbms">DBMSの種類</param>
/// <param name="convertString">文字列変換方法（空の場合は既定値を使用）</param>
/// <param name="dateTimeFormatString">日付を文字列化する際に使用するFormatString</param>
public SQLUtility(DbEnum.DBMSType dbms, string convertString, string dateTimeFormatString)
{
this._dbms = dbms;
switch (this._dbms)
{
case DbEnum.DBMSType.SQLServer:
// convertString
if (string.IsNullOrEmpty(convertString))
{
// 既定値
this._convertString = "nvarchar";
}
else
{
// 指定の値
this._convertString = convertString;
}
// dateTimeFormatString
if (string.IsNullOrEmpty(dateTimeFormatString))
{
// 既定値
this._dateTimeFormatString = "yyyy/MM/dd HH:mm:ss.fff";
}
else
{
// 指定の値
this._dateTimeFormatString = dateTimeFormatString;
}
break;
case DbEnum.DBMSType.PstGrS:
// convertString
if (string.IsNullOrEmpty(convertString))
{
// 既定値
this._convertString = "text";
}
else
{
// 指定の値
this._convertString = convertString;
}
// dateTimeFormatString
if (string.IsNullOrEmpty(dateTimeFormatString))
{
// 既定値
this._dateTimeFormatString = "yyyy/MM/dd HH:mm:ss.fff";
}
else
{
// 指定の値
this._dateTimeFormatString = dateTimeFormatString;
}
break;
case DbEnum.DBMSType.Oracle:
// convertString
if (string.IsNullOrEmpty(convertString))
{
// 既定値
this._convertString = "varchar2";
}
else
{
// 指定の値
this._convertString = convertString;
}
// dateTimeFormatString
if (string.IsNullOrEmpty(dateTimeFormatString))
{
// 既定値
this._dateTimeFormatString = "dd-MMM-yyyy hh:mm:ss tt";
}
else
{
// 指定の値
this._dateTimeFormatString = dateTimeFormatString;
}
break;
case DbEnum.DBMSType.MySQL:
// convertString
if (string.IsNullOrEmpty(convertString))
{
// 既定値
this._convertString = "char";
}
else
{
// 指定の値
this._convertString = convertString;
}
// dateTimeFormatString
if (string.IsNullOrEmpty(dateTimeFormatString))
{
// 既定値
this._dateTimeFormatString = "yyyy/MM/dd HH:mm:ss.fff";
}
else
{
// 指定の値
this._dateTimeFormatString = dateTimeFormatString;
}
break;
case DbEnum.DBMSType.DB2:
// convertString
if (string.IsNullOrEmpty(convertString))
{
// 既定値
this._convertString = "varchar";
}
else
{
// 指定の値
this._convertString = convertString;
}
// dateTimeFormatString
if (string.IsNullOrEmpty(dateTimeFormatString))
{
// 既定値
this._dateTimeFormatString = "yyyy-MM-dd-HH.mm.ss.fff";
}
else
{
// 指定の値
this._dateTimeFormatString = dateTimeFormatString;
}
break;
default:
throw new NotImplementedException(PublicExceptionMessage.NOT_IMPLEMENTED);
}
}
/// <summary>コンストラクタ</summary>
/// <param name="dbms">DBMSの種類</param>
/// <param name="convertString">文字列変換方法（空の場合は既定値を使用）</param>
/// <param name="dateTimeFormatString">日付を文字列化する際に使用するFormatString</param>
public SQLUtility(DbEnum.DBMSType dbms, string convertString, string dateTimeFormatString)
{
this._dbms = dbms;
switch (this._dbms)
{
case DbEnum.DBMSType.SQLServer:
// convertString
if (string.IsNullOrEmpty(convertString))
{
// 既定値
this._convertString = "nvarchar";
}
else
{
// 指定の値
this._convertString = convertString;
}
// dateTimeFormatString
if (string.IsNullOrEmpty(dateTimeFormatString))
{
// 既定値
this._dateTimeFormatString = "yyyy/MM/dd HH:mm:ss.fff";
}
else
{
// 指定の値
this._dateTimeFormatString = dateTimeFormatString;
}
break;
case DbEnum.DBMSType.PstGrS:
// convertString
if (string.IsNullOrEmpty(convertString))
{
// 既定値
this._convertString = "text";
}
else
{
// 指定の値
this._convertString = convertString;
}
// dateTimeFormatString
if (string.IsNullOrEmpty(dateTimeFormatString))
{
// 既定値
this._dateTimeFormatString = "yyyy/MM/dd HH:mm:ss.fff";
}
else
{
// 指定の値
this._dateTimeFormatString = dateTimeFormatString;
}
break;
case DbEnum.DBMSType.Oracle:
// convertString
if (string.IsNullOrEmpty(convertString))
{
// 既定値
this._convertString = "varchar2";
}
else
{
// 指定の値
this._convertString = convertString;
}
// dateTimeFormatString
if (string.IsNullOrEmpty(dateTimeFormatString))
{
// 既定値
this._dateTimeFormatString = "dd-MMM-yyyy hh:mm:ss tt";
}
else
{
// 指定の値
this._dateTimeFormatString = dateTimeFormatString;
}
break;
case DbEnum.DBMSType.MySQL:
// convertString
if (string.IsNullOrEmpty(convertString))
{
// 既定値
this._convertString = "char";
}
else
{
// 指定の値
this._convertString = convertString;
}
// dateTimeFormatString
if (string.IsNullOrEmpty(dateTimeFormatString))
{
// 既定値
this._dateTimeFormatString = "yyyy/MM/dd HH:mm:ss.fff";
}
else
{
// 指定の値
this._dateTimeFormatString = dateTimeFormatString;
}
break;
case DbEnum.DBMSType.DB2:
// convertString
if (string.IsNullOrEmpty(convertString))
{
// 既定値
this._convertString = "varchar";
}
else
{
// 指定の値
this._convertString = convertString;
}
// dateTimeFormatString
if (string.IsNullOrEmpty(dateTimeFormatString))
{
// 既定値
this._dateTimeFormatString = "yyyy-MM-dd-HH.mm.ss.fff";
}
else
{
// 指定の値
this._dateTimeFormatString = dateTimeFormatString;
}
break;
default:
throw new NotImplementedException(PublicExceptionMessage.NOT_IMPLEMENTED);
}
}
/// <summary>コンストラクタ</summary>
/// <param name="dbms">DBMSの種類</param>
/// <param name="convertString">文字列変換方法（空の場合は既定値を使用）</param>
public SQLUtility(DbEnum.DBMSType dbms, string convertString)
: this(dbms, convertString, "")
{
}
/// <summary>コンストラクタ</summary>
/// <param name="dbms">DBMSの種類</param>
public SQLUtility(DbEnum.DBMSType dbms)
: this(dbms, "", "")
{
}
/// <summary>Main</summary>
static void Main(string[] args)
{
//// Database connection using IDbCommand
//IDbConnection dbConnection = null;
//IDbCommand dbCommand = null;
Dictionary <string, string> dic = null;
List <string> list = null;
PubCmnFunction.GetCommandArgs('/', out dic, out list);
// Get the type of DB
if (dic.ContainsKey("/DBMS"))
{
switch (dic["/DBMS"])
{
case "ODP":
Program._dbms = DbEnum.DBMSType.Oracle;
//dbConnection = new OracleConnection(GetConfigParameter.GetConfigValue("Oracle"));
break;
case "DB2":
Program._dbms = DbEnum.DBMSType.DB2;
//dbConnection = new DB2Connection(GetConfigParameter.GetConfigValue("DB2Conn"));
break;
case "MCN":
Program._dbms = DbEnum.DBMSType.MySQL;
//dbConnection = new MySqlConnection(GetConfigParameter.GetConfigValue("MySQLConn"));
break;
case "NPS":
Program._dbms = DbEnum.DBMSType.PstGrS;
//dbConnection = new NpgsqlConnection(GetConfigParameter.GetConfigValue("PostgreSQLConn"));
break;
default:
Program._dbms = DbEnum.DBMSType.SQLServer;
//dbConnection = new SqlConnection(GetConfigParameter.GetConfigValue("SQLServerConn"));
break;
}
}
DataTable dt = null;
DataRow dr = null;
try
{
#region Datatable creation
dt = new DataTable("table1");
dt.Columns.Add(new DataColumn("column1", typeof(string)));
dt.Columns.Add(new DataColumn("column2", typeof(Int32)));
dt.Columns.Add(new DataColumn("column3", typeof(byte[])));
dt.Columns.Add(new DataColumn("column4", typeof(DateTime)));
dt.Columns.Add(new DataColumn("column5", typeof(char)));
dt.Columns.Add(new DataColumn("column6", typeof(string)));
dr = dt.NewRow();
dr["column1"] = "test1";
dr["column2"] = 1111;
dr["column3"] = new byte[] { 1, 2, 3, 4, 5, 6 };
dr["column4"] = DateTime.Now;
dr["column5"] = DBNull.Value;
dr["column6"] = string.Empty;
dt.Rows.Add(dr);
dr = dt.NewRow();
dr["column1"] = "test2";
dr["column2"] = 22222;
dt.Rows.Add(dr);
dr = dt.NewRow();
dr["column1"] = "test3";
dr["column2"] = 3;
dr["column3"] = DBNull.Value;
dr["column4"] = DBNull.Value;
dr["column5"] = 'D';
dr["column6"] = "Test's";
dt.Rows.Add(dr);
#endregion
#region Generate SQL for batch process
string[] strs = null;
StringBuilder sb = null;
string collist = "";
string temp = "";
string query = "";
// Second and third argument optional.
SQLUtility su = new SQLUtility(Program._dbms);
#region Assembly of parts of SQL insert.
// Forming insert query.
strs = su.GetInsertSQLParts(dt);
Console.WriteLine("<InsertSQLParts>");
foreach (string str in strs)
{
Console.WriteLine(str);
}
Console.WriteLine("\r\n");
sb = new StringBuilder();
// INSERT
switch (Program._dbms)
{
case DbEnum.DBMSType.Oracle:
foreach (string str in strs)
{
if (string.IsNullOrEmpty(collist))
{
collist = str;
// for oracle: remove double quotes from the "column" name
collist = collist.Replace("\"", string.Empty);
}
else
{
sb.Append(str + ";");
}
}
// 最後のカンマを削る。
temp = sb.ToString();
temp = temp.Substring(0, temp.Length - 1);
string[] temp2 = temp.Split(';');
StringBuilder sbOracle = new StringBuilder();
// Prepare Oracle Insert Statement to execute
sbOracle.Append("INSERT ALL ");
foreach (string str in temp2)
{
sbOracle.Append(string.Format(
" INTO "
+ su.OpeningBracket + dt.TableName + su.ClosingBracket + " {0} "
+ " VALUES{1} \r\n", collist, str));
}
sbOracle.Append(" SELECT * FROM DUAL");
query = sbOracle.ToString();
break;
case DbEnum.DBMSType.DB2:
//・・・
foreach (string str in strs)
{
if (string.IsNullOrEmpty(collist))
{
collist = str;
collist = collist.Replace("\"", string.Empty);
}
else
{
sb.Append(str + ",\r\n");
}
}
// Sharpen the last comma.
temp = sb.ToString();
temp = temp.Substring(0, temp.Length - 3);
query =
@"INSERT INTO "
+ su.OpeningBracket + dt.TableName + su.ClosingBracket + collist
+ "VALUES \r\n" + temp;
break;
default:
// PostGreSQL
// MySQL
// SQLServer
// Preparing Insert query insert values.
foreach (string str in strs)
{
if (string.IsNullOrEmpty(collist))
{
collist = str;
}
else
{
sb.Append(str + ",\r\n");
}
}
// Sharpen the last comma.
temp = sb.ToString();
temp = temp.Substring(0, temp.Length - 3);
query =
@"INSERT INTO "
+ su.OpeningBracket + dt.TableName + su.ClosingBracket + collist
+ "VALUES \r\n" + temp;
break;
}
Console.WriteLine("<INSERT>");
Console.WriteLine(query);
Console.WriteLine("\r\n");
#endregion
#region Assembly of parts of SQL update.
// Forming update query
strs = su.GetUpdateSQLParts(dt, new string[] { "column1", "column2" });
Console.WriteLine("<UpdateSQLParts>");
foreach (string str in strs)
{
Console.WriteLine(str);
}
Console.WriteLine("\r\n");
sb = new StringBuilder();
// UPDATE
switch (Program._dbms)
{
case DbEnum.DBMSType.Oracle:
// Oracle
// To Run multiple sql statements in oracle from application we need to prepend "BEGIN" to the Query
sb.Append("BEGIN ");
foreach (string s in strs)
{
string ss = s.Replace("\"", string.Empty);
//Add ";" at the end of each update statement
sb.Append(@"UPDATE " + su.OpeningBracket + dt.TableName + su.ClosingBracket + " " + ss + ";\r\n");
}
// To Run multiple sql statements in oracle from application we need to append "END;" to the Query
sb.Append("END;");
query = sb.ToString();
break;
case DbEnum.DBMSType.DB2:
// To Run multiple sql statements in DB2 from an application we need to prepend "BEGIN" to the Query
sb.Append("BEGIN ");
foreach (string s in strs)
{
//Add ";" at the end of each update statement to execute multiple update statements in DB2 datbase from .net
sb.Append(@"UPDATE " + su.OpeningBracket + dt.TableName + su.ClosingBracket + " " + s + ";\r\n");
}
// To Run multiple sql statements in DB2 from .net we need to append "END;" to the Query
sb.Append("END;");
query = sb.ToString();
query = query.Replace("\"", string.Empty);
break;
default:
// PostGreSQL
// MySQL
// SQLServer
foreach (string s in strs)
{
sb.Append(@"UPDATE " + su.OpeningBracket + dt.TableName + su.ClosingBracket + " " + s + "\r\n");
}
query = sb.ToString();
break;
}
Console.WriteLine("<UPDATE>");
Console.WriteLine(query);
Console.WriteLine("\r\n");
#endregion
#endregion
////Executing queries using ADO.NET Icommand connection and command.
//dbConnection.Open();
//dbCommand = dbConnection.CreateCommand();
//dbCommand.CommandText = query;
//dbCommand.ExecuteNonQuery();
Console.WriteLine("Query executed successfully");
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
/// <summary>Main</summary>
static void Main(string[] args)
{
//// Database connection using IDbCommand
//IDbConnection dbConnection = null;
//IDbCommand dbCommand = null;
Dictionary<string, string> dic = null;
List<string> list = null;
PubCmnFunction.GetCommandArgs('/', out dic, out list);
// Get the type of DB
if (dic.ContainsKey("/DBMS"))
{
switch (dic["/DBMS"])
{
case "ODP":
Program._dbms = DbEnum.DBMSType.Oracle;
//dbConnection = new OracleConnection(GetConfigParameter.GetConfigValue("Oracle"));
break;
case "DB2":
Program._dbms = DbEnum.DBMSType.DB2;
//dbConnection = new DB2Connection(GetConfigParameter.GetConfigValue("DB2Conn"));
break;
case "MCN":
Program._dbms = DbEnum.DBMSType.MySQL;
//dbConnection = new MySqlConnection(GetConfigParameter.GetConfigValue("MySQLConn"));
break;
case "NPS":
Program._dbms = DbEnum.DBMSType.PstGrS;
//dbConnection = new NpgsqlConnection(GetConfigParameter.GetConfigValue("PostgreSQLConn"));
break;
default:
Program._dbms = DbEnum.DBMSType.SQLServer;
//dbConnection = new SqlConnection(GetConfigParameter.GetConfigValue("SQLServerConn"));
break;
}
}
DataTable dt = null;
DataRow dr = null;
try
{
#region Datatable creation
dt = new DataTable("table1");
dt.Columns.Add(new DataColumn("column1", typeof(string)));
dt.Columns.Add(new DataColumn("column2", typeof(Int32)));
dt.Columns.Add(new DataColumn("column3", typeof(byte[])));
dt.Columns.Add(new DataColumn("column4", typeof(DateTime)));
dt.Columns.Add(new DataColumn("column5", typeof(char)));
dt.Columns.Add(new DataColumn("column6", typeof(string)));
dr = dt.NewRow();
dr["column1"] = "test1";
dr["column2"] = 1111;
dr["column3"] = new byte[] { 1, 2, 3, 4, 5, 6 };
dr["column4"] = DateTime.Now;
dr["column5"] = DBNull.Value;
dr["column6"] = string.Empty;
dt.Rows.Add(dr);
dr = dt.NewRow();
dr["column1"] = "test2";
dr["column2"] = 22222;
dt.Rows.Add(dr);
dr = dt.NewRow();
dr["column1"] = "test3";
dr["column2"] = 3;
dr["column3"] = DBNull.Value;
dr["column4"] = DBNull.Value;
dr["column5"] = 'D';
dr["column6"] = "Test's";
dt.Rows.Add(dr);
#endregion
#region Generate SQL for batch process
string[] strs = null;
StringBuilder sb = null;
string collist = "";
string temp = "";
string query = "";
// Second and third argument optional.
SQLUtility su = new SQLUtility(Program._dbms);
#region Assembly of parts of SQL insert.
// Forming insert query.
strs = su.GetInsertSQLParts(dt);
Console.WriteLine("<InsertSQLParts>");
foreach (string str in strs)
{
Console.WriteLine(str);
}
Console.WriteLine("\r\n");
sb = new StringBuilder();
// INSERT
switch (Program._dbms)
{
case DbEnum.DBMSType.Oracle:
foreach (string str in strs)
{
if (string.IsNullOrEmpty(collist))
{
collist = str;
// for oracle: remove double quotes from the "column" name
collist = collist.Replace("\"", string.Empty);
}
else
{
sb.Append(str + ";");
}
}
// 最後のカンマを削る。
temp = sb.ToString();
temp = temp.Substring(0, temp.Length - 1);
string[] temp2 = temp.Split(';');
StringBuilder sbOracle = new StringBuilder();
// Prepare Oracle Insert Statement to execute
sbOracle.Append("INSERT ALL ");
foreach (string str in temp2)
{
sbOracle.Append(string.Format(
" INTO "
+ su.OpeningBracket + dt.TableName + su.ClosingBracket + " {0} "
+ " VALUES{1} \r\n", collist, str));
}
sbOracle.Append(" SELECT * FROM DUAL");
query = sbOracle.ToString();
break;
case DbEnum.DBMSType.DB2:
//・・・
foreach (string str in strs)
{
if (string.IsNullOrEmpty(collist))
{
collist = str;
collist = collist.Replace("\"", string.Empty);
}
else
{
sb.Append(str + ",\r\n");
}
}
// Sharpen the last comma.
temp = sb.ToString();
temp = temp.Substring(0, temp.Length - 3);
query =
@"INSERT INTO "
+ su.OpeningBracket + dt.TableName + su.ClosingBracket + collist
+ "VALUES \r\n" + temp;
break;
default:
// PostGreSQL
// MySQL
// SQLServer
// Preparing Insert query insert values.
foreach (string str in strs)
{
if (string.IsNullOrEmpty(collist))
{
collist = str;
}
else
{
sb.Append(str + ",\r\n");
}
}
// Sharpen the last comma.
temp = sb.ToString();
temp = temp.Substring(0, temp.Length - 3);
query =
@"INSERT INTO "
+ su.OpeningBracket + dt.TableName + su.ClosingBracket + collist
+ "VALUES \r\n" + temp;
break;
}
Console.WriteLine("<INSERT>");
Console.WriteLine(query);
Console.WriteLine("\r\n");
#endregion
#region Assembly of parts of SQL update.
// Forming update query
strs = su.GetUpdateSQLParts(dt, new string[] { "column1", "column2" });
Console.WriteLine("<UpdateSQLParts>");
foreach (string str in strs)
{
Console.WriteLine(str);
}
Console.WriteLine("\r\n");
sb = new StringBuilder();
// UPDATE
switch (Program._dbms)
{
case DbEnum.DBMSType.Oracle:
// Oracle
// To Run multiple sql statements in oracle from application we need to prepend "BEGIN" to the Query
sb.Append("BEGIN ");
foreach (string s in strs)
{
string ss = s.Replace("\"", string.Empty);
//Add ";" at the end of each update statement
sb.Append(@"UPDATE " + su.OpeningBracket + dt.TableName + su.ClosingBracket + " " + ss + ";\r\n");
}
// To Run multiple sql statements in oracle from application we need to append "END;" to the Query
sb.Append("END;");
query = sb.ToString();
break;
case DbEnum.DBMSType.DB2:
// To Run multiple sql statements in DB2 from an application we need to prepend "BEGIN" to the Query
sb.Append("BEGIN ");
foreach (string s in strs)
{
//Add ";" at the end of each update statement to execute multiple update statements in DB2 datbase from .net
sb.Append(@"UPDATE " + su.OpeningBracket + dt.TableName + su.ClosingBracket + " " + s + ";\r\n");
}
// To Run multiple sql statements in DB2 from .net we need to append "END;" to the Query
sb.Append("END;");
query = sb.ToString();
query = query.Replace("\"", string.Empty);
break;
default:
// PostGreSQL
// MySQL
// SQLServer
foreach (string s in strs)
{
sb.Append(@"UPDATE " + su.OpeningBracket + dt.TableName + su.ClosingBracket + " " + s + "\r\n");
}
query = sb.ToString();
break;
}
Console.WriteLine("<UPDATE>");
Console.WriteLine(query);
Console.WriteLine("\r\n");
#endregion
#endregion
////Executing queries using ADO.NET Icommand connection and command.
//dbConnection.Open();
//dbCommand = dbConnection.CreateCommand();
//dbCommand.CommandText = query;
//dbCommand.ExecuteNonQuery();
Console.WriteLine("Query executed successfully");
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
