/// <summary>静的SQLを生成する</summary> /// <param name="sqlUtil">SQLUtility</param> /// <returns>SQL文</returns> /// <remarks> /// Commandでの実行はしない。 /// 派生のDamXXXでオーバーライドする。 /// </remarks> public abstract string ExecGenerateSQL(SQLUtility sqlUtil);
/// <summary>静的SQLを生成する</summary> /// <param name="sqlUtil">SQLUtility</param> /// <returns>SQL文</returns> /// <remarks> /// Commandでの実行はしない。 /// 通常、Dao経由で利用する。 /// </remarks> public override string ExecGenerateSQL(SQLUtility sqlUtil) { throw new NotImplementedException(PublicExceptionMessage.NOT_IMPLEMENTED); }
/// <summary>静的SQLを生成する。</summary> /// <param name="fileName">ファイル名</param> /// <param name="sqlUtil">SQLユーティリティ</param> /// <returns>生成した静的SQL</returns> public string ExecGenerateSQL(string fileName, SQLUtility sqlUtil) { // ファイルからSQLを設定する。 this.SetSqlByFile2(fileName); // パラメタの設定 this.SetParametersFromHt(); return base.ExecGenerateSQL(sqlUtil); }
/// <summary>静的SQLを生成する</summary> /// <param name="sqlUtil">SQLUtility</param> /// <returns>SQL文</returns> /// <remarks> /// Commandでの実行はしない。 /// 派生のデータアクセス クラスから利用する。 /// </remarks> protected string ExecGenerateSQL(SQLUtility sqlUtil) { // 委譲(UOC_PreQuery、UOC_AfterQueryは実行しない) return this._dam.ExecGenerateSQL(sqlUtil); }
/// <summary>静的SQLを生成する</summary> /// <param name="sqlUtil">SQLUtility</param> /// <returns>SQL文</returns> /// <remarks> /// Commandでの実行はしない。 /// 通常、Dao経由で利用する。 /// </remarks> public override string ExecGenerateSQL(SQLUtility sqlUtil) { // SQL実行前の、 // ・通常のパラメタライズド クエリ // ・動的パラメタライズド クエリ // 制御用メソッド this.PreExecQuery(); // --- // パラメタ名 string paramName = ""; // CommandTextを退避 string tmpCommandText = this._cmd.CommandText; // 名前バインド パラメタをSQL化する。 while (true) { // パラメタ記号の位置(インデックス) int paramSignIndex; // Command.CommandTextから、パラメタ名を取得する。 paramName = this.GetParamByText(tmpCommandText, DamOraClient._paramSign, out paramSignIndex); if (paramSignIndex == -1) { // パラメタが検出されなかった場合 // 置換処理を終了する break; } // 名前バインド パラメタをSQLに置換。 tmpCommandText = tmpCommandText.Substring(0, paramSignIndex) + sqlUtil.ConvertParameterToSQL(this._cmd.Parameters[paramName].Value) + tmpCommandText.Substring(paramSignIndex + paramName.Length + 1); //+1はパラメタの先頭記号分 } // Command.CommandTextをクリア this._cmd.CommandText = ""; return tmpCommandText; }
/// <summary>ExecGenerateSQLで静的SQLを生成する</summary> /// <param name="sqlUtil">SQLUtility</param> /// <returns>SQL文</returns> /// <remarks>自由に(拡張して)利用できる。</remarks> public new string ExecGenerateSQL(SQLUtility sqlUtil) { // SQLの設定 this.SetSQL(); // パラメタの一括設定 this.SetParameters(); // 静的SQLを生成する。 // (★ ベースのメソッドを呼ぶ) return base.ExecGenerateSQL(sqlUtil); }
/// <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); } }