/// <summary> /// 执行带一个存储过程参数的的SQL语句。 /// </summary> /// <param name="SQLString">SQL语句</param> /// <param name="content">参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加</param> /// <returns>影响的记录数</returns> public static int ExecuteSql(string SQLString, string content) { using (OracleConnection connection = new OracleConnection(connectionString)) { OracleCommand cmd = new OracleCommand(SQLString, connection); System.Data.OracleClient.OracleParameter myParameter = new System.Data.OracleClient.OracleParameter("@content", OracleDbType.NVarchar2); myParameter.Value = content; cmd.Parameters.Add(myParameter); try { connection.Open(); int rows = cmd.ExecuteNonQuery(); return(rows); } catch (System.Data.OracleClient.OracleException E) { throw new Exception(E.Message); } finally { cmd.Dispose(); connection.Close(); } } }
private string updateDbByID() { System.Data.OracleClient.OracleParameter[] ps = new System.Data.OracleClient.OracleParameter[7]; System.Data.OracleClient.OracleParameter p1 = new System.Data.OracleClient.OracleParameter("I_EnTest_Work_Id", this.taskInfo.id); System.Data.OracleClient.OracleParameter p2 = new System.Data.OracleClient.OracleParameter("I_EnTest_Times", System.Data.OracleClient.OracleType.Int32); p2.Value = this.times; System.Data.OracleClient.OracleParameter p3 = new System.Data.OracleClient.OracleParameter("I_Fk_Time_Name", "COLUMN0"); System.Data.OracleClient.OracleParameter p4 = new System.Data.OracleClient.OracleParameter("I_Object_Table_Name", this.dataScriptRule.DesTable); System.Data.OracleClient.OracleParameter p5 = new System.Data.OracleClient.OracleParameter("I_Source_Table_Name", tableName); System.Data.OracleClient.OracleParameter p6 = new System.Data.OracleClient.OracleParameter("O_Return_Int", System.Data.OracleClient.OracleType.Int32); p6.Direction = ParameterDirection.Output; System.Data.OracleClient.OracleParameter p7 = new System.Data.OracleClient.OracleParameter("O_Return_String", System.Data.OracleClient.OracleType.VarChar, 100); p7.Direction = ParameterDirection.Output; ps[0] = p1; ps[1] = p2; ps[2] = p3; ps[3] = p4; ps[4] = p5; ps[5] = p6; ps[6] = p7; log.Info(string.Format("BetchLogic > updateDbByID > 更新数据:{0} -> {1}", tableName, this.dataScriptRule.DesTable)); SendMessageEvent(string.Format("更新数据:{0} -> {1} , {2}", tableName, this.dataScriptRule.DesTable, DateTime.Now.ToString("HH:mm:ss"))); object[] prout = OracleHelper.ExcuteProc("Process_Date_Server.Process_Temp_Into_Oragin", 2, ps); log.Info(string.Format("BetchLogic > updateDbByID > 更新结束:{0} : {1} {2} {3}", this.dataScriptRule.DesTable, DateTime.Now.ToString("HH:mm:ss"), prout[0], prout[1])); SendMessageEvent(string.Format("更新结束:{0} : {1}", this.dataScriptRule.DesTable, DateTime.Now.ToString("HH:mm:ss"))); return(prout[1].ToString()); SendCompleteEvent(prout[1].ToString()); //TableDAL.DropTable(tableName); }
/// <summary> /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例) /// </summary> /// <param name="strSQL">SQL语句</param> /// <param name="fs">图像字节,数据库的字段类型为image的情况</param> /// <returns>影响的记录数</returns> public static int ExecuteSqlInsertImg(string strSQL, byte[] fs) { using (OracleConnection connection = new OracleConnection(connectionString)) { OracleCommand cmd = new OracleCommand(strSQL, connection); System.Data.OracleClient.OracleParameter myParameter = new System.Data.OracleClient.OracleParameter("@fs", OracleDbType.LongRaw); myParameter.Value = fs; cmd.Parameters.Add(myParameter); try { connection.Open(); int rows = cmd.ExecuteNonQuery(); return(rows); } catch (System.Data.OracleClient.OracleException E) { throw new Exception(E.Message); } finally { cmd.Dispose(); connection.Close(); } } }
public void sss1() { try { OraDbHelper _OraHelper = new OraDbHelper(null); _OraHelper.ConnectionString = "Data Source=KS_PRD_AY;Persist Security Info=True;User ID=FA;Password=FA;Unicode=True"; string sSql = ""; sSql = " INSERT INTO FA.JOBLOG_HIS (SP_NAME, TIMESTAMP, ERRORCODE, ERRORDESC, STATUS) "; sSql += " VALUES('OLP_STATUS_MAIL', '20190520 172605', 'sss1',?pb, 'START');"; System.Data.OracleClient.OracleParameter[] param = new System.Data.OracleClient.OracleParameter[] { new System.Data.OracleClient.OracleParameter("?pb", OracleDbType.Varchar2) }; param[0].Value = "TEST"; _OraHelper.ExecuteNonQuery(sSql, CommandType.Text, param); } catch (Exception ex) { MessageBox.Show(ex.Message.ToString()); } }
public void OnTableChange(Object sender, OracleNotificationEventArgs args) { //Get data Changed before......................... if (args.Type == OracleNotificationType.Change) { int start = 0; if (args.Info.ToString() == "Update") { start = 1; } for (int i = start; i < args.Details.Rows.Count; i++) { DataRow detailRow = args.Details.Rows[i]; string rowid = detailRow["Rowid"].ToString(); DataSet ds = new DataSet(); using (System.Data.OracleClient.OracleDataAdapter da = new System.Data.OracleClient.OracleDataAdapter(NotificationNameAdapter, constr)) { try { da.SelectCommand.CommandType = CommandType.StoredProcedure; da.SelectCommand.Parameters.Add("MY_ID", System.Data.OracleClient.OracleType.VarChar).Value = rowid; da.SelectCommand.Parameters.Add("Info", System.Data.OracleClient.OracleType.VarChar).Value = args.Info.ToString(); System.Data.OracleClient.OracleParameter ret = new System.Data.OracleClient.OracleParameter("my_cursor", System.Data.OracleClient.OracleType.Cursor); ret.Direction = ParameterDirection.ReturnValue; da.SelectCommand.Parameters.Add(ret); da.Fill(ds); } catch (Exception ex) { } } string data = JsonConvert.SerializeObject(ds); try { Message messageObject = new Message(); for (int j = 0; j < ds.Tables[0].Rows.Count; j++) { messageObject.message = ds.Tables[0].Rows[j]["MESSAGE"].ToString(); messageObject.url = ds.Tables[0].Rows[j]["URL"].ToString();; messageObject.type = ds.Tables[0].Rows[j]["TYPE"].ToString(); var jsonMessage = JsonConvert.SerializeObject(messageObject); //after get two paramters of notification fire it... string successMessage = sendNotification(ds.Tables[0].Rows[j]["NID"].ToString(), jsonMessage); } } catch (Exception e) { } } } }
public static System.Data.OracleClient.OracleParameter GetCursorParameter(string name) { System.Data.OracleClient.OracleParameter result = new System.Data.OracleClient.OracleParameter(); result.OracleType = System.Data.OracleClient.OracleType.Cursor; result.Direction = System.Data.ParameterDirection.Output; result.ParameterName = name; result.Value = DBNull.Value; return(result); }
/// <summary> /// 转换为数据库的对应参数类型 /// </summary> /// <param name="dbType"></param> /// <returns></returns> public System.Data.Common.DbParameter[] ConvertToDbParameter(eDBType dbType) { if (_dicParamNameValue == null | _dicParamNameValue.Count == 0) { return(new System.Data.Common.DbParameter[0]); } System.Data.Common.DbParameter[] pras = new System.Data.Common.DbParameter[_dicParamNameValue.Count]; switch (dbType) { case eDBType.Oracle: System.Data.OracleClient.OracleParameter[] pOras = new System.Data.OracleClient.OracleParameter[_dicParamNameValue.Count]; int iOra = 0; foreach (string name in _dicParamNameValue.Keys) { pOras[iOra++] = new System.Data.OracleClient.OracleParameter(name, _dicParamNameValue[name]); } return(pOras); case eDBType.SqlServer: System.Data.SqlClient.SqlParameter[] pSqls = new System.Data.SqlClient.SqlParameter[_dicParamNameValue.Count]; int iSql = 0; foreach (string name in _dicParamNameValue.Keys) { pSqls[iSql++] = new System.Data.SqlClient.SqlParameter(name, _dicParamNameValue[name]); } return(pSqls); case eDBType.Access: System.Data.OleDb.OleDbParameter[] oSqls = new System.Data.OleDb.OleDbParameter[_dicParamNameValue.Count]; int iOledb = 0; foreach (string name in _dicParamNameValue.Keys) { oSqls[iOledb++] = new System.Data.OleDb.OleDbParameter(name, _dicParamNameValue[name]); } return(oSqls); case eDBType.MySql: case eDBType.DB2: case eDBType.Sybase: default: break; } return(new System.Data.Common.DbParameter[0]); }
/// <summary> /// 转换为数据库的对应参数类型 /// </summary> /// <param name="dbType"></param> /// <returns></returns> public System.Data.Common.DbParameter[] ConvertToDbParameter(eDBType dbType) { if (_dicParamNameValue == null | _dicParamNameValue.Count == 0) return new System.Data.Common.DbParameter[0]; System.Data.Common.DbParameter[] pras = new System.Data.Common.DbParameter[_dicParamNameValue.Count]; switch (dbType) { case eDBType.Oracle: System.Data.OracleClient.OracleParameter[] pOras = new System.Data.OracleClient.OracleParameter[_dicParamNameValue.Count]; int iOra = 0; foreach (string name in _dicParamNameValue.Keys) { pOras[iOra++] = new System.Data.OracleClient.OracleParameter(name, _dicParamNameValue[name]); } return pOras; case eDBType.SqlServer: System.Data.SqlClient.SqlParameter[] pSqls = new System.Data.SqlClient.SqlParameter[_dicParamNameValue.Count]; int iSql = 0; foreach (string name in _dicParamNameValue.Keys) { pSqls[iSql++] = new System.Data.SqlClient.SqlParameter(name, _dicParamNameValue[name]); } return pSqls; case eDBType.Access: System.Data.OleDb.OleDbParameter[] oSqls = new System.Data.OleDb.OleDbParameter[_dicParamNameValue.Count]; int iOledb = 0; foreach (string name in _dicParamNameValue.Keys) { oSqls[iOledb++] = new System.Data.OleDb.OleDbParameter(name, _dicParamNameValue[name]); } return oSqls; case eDBType.MySql: case eDBType.DB2: case eDBType.Sybase: default: break; } return new System.Data.Common.DbParameter[0]; }
private void DisplayDataForQuery() { dataGridView1.DataSource = null; if (queryBuilder.MetadataProvider != null && queryBuilder.MetadataProvider.Connected) { if (queryBuilder.MetadataProvider is OracleMetadataProvider) { System.Data.OracleClient.OracleCommand command = (System.Data.OracleClient.OracleCommand)queryBuilder.MetadataProvider.Connection.CreateCommand(); command.CommandText = queryBuilder.SQL; // handle the query parameters if (queryBuilder.Parameters.Count > 0) { for (int i = 0; i < queryBuilder.Parameters.Count; i++) { if (!command.Parameters.Contains(queryBuilder.Parameters[i].FullName)) { System.Data.OracleClient.OracleParameter parameter = new System.Data.OracleClient.OracleParameter(); parameter.ParameterName = queryBuilder.Parameters[i].FullName; parameter.DbType = queryBuilder.Parameters[i].DataType; command.Parameters.Add(parameter); } } using (QueryParametersForm qpf = new QueryParametersForm(command)) { qpf.ShowDialog(); } } System.Data.OracleClient.OracleDataAdapter adapter = new System.Data.OracleClient.OracleDataAdapter(command); DataSet dataset = new DataSet(); try { adapter.Fill(dataset, "QueryResult"); dataGridView1.DataSource = dataset.Tables["QueryResult"]; } catch (Exception ex) { MessageBox.Show(ex.Message, "SQL query error"); } } //end oracle (ms) } //end check if connected } //end method
// =========================================== // ==用hashTable对数据库进行insert,update,del操作,注意此时只能用默认的数据库连接"connstr"== // ============================================ #region 根据表名及哈稀表自动插入数据库 用法:Insert("test",ht) public int Insert(string TableName, Hashtable ht) { OracleParameter[] Parms = new OracleParameter[ht.Count]; IDictionaryEnumerator et = ht.GetEnumerator(); DataTable dt = GetTabType(TableName); System.Data.OracleClient.OracleType otype; int size = 0; int i = 0; while (et.MoveNext()) // 作哈希表循环 { GetoType(et.Key.ToString().ToUpper(), dt, out otype, out size); System.Data.OracleClient.OracleParameter op = MakeParam(":" + et.Key.ToString(), otype, size, et.Value.ToString()); Parms[i] = op; // 添加SqlParameter对象 i = i + 1; } string str_Sql = GetInsertSqlbyHt(TableName, ht); // 获得插入sql语句 int val = ExecuteNonQuery(str_Sql, Parms); return(val); }
internal static IDataParameter AddRefCursor(IDbCommand command) { #if ODP Oracle.DataAccess.Client.OracleParameter parameter = new Oracle.DataAccess.Client.OracleParameter(); parameter.ParameterName = "p_cursor"; parameter.OracleDbType = Oracle.DataAccess.Client.OracleDbType.RefCursor; parameter.Direction = ParameterDirection.Output; ((Oracle.DataAccess.Client.OracleCommand)command).Parameters.Add(parameter); return(parameter); #elif OracleClient System.Data.OracleClient.OracleParameter parameter = new System.Data.OracleClient.OracleParameter(); parameter.ParameterName = "p_cursor"; parameter.DbType = (DbType)System.Data.OracleClient.OracleType.Cursor; parameter.Direction = ParameterDirection.Output; ((System.Data.OracleClient.OracleCommand)command).Parameters.Add(parameter); return(parameter); #else throw new NotImplementedException("AddRefCursor is only implemented for Oracle database access."); #endif }
private void updateDbByID() { System.Data.OracleClient.OracleParameter[] ps = new System.Data.OracleClient.OracleParameter[7]; System.Data.OracleClient.OracleParameter p1 = new System.Data.OracleClient.OracleParameter("I_EnTest_Work_Id", this.taskInfo.id); System.Data.OracleClient.OracleParameter p2 = new System.Data.OracleClient.OracleParameter("I_EnTest_Times", System.Data.OracleClient.OracleType.Int32); p2.Value = this.times; System.Data.OracleClient.OracleParameter p3 = new System.Data.OracleClient.OracleParameter("I_Fk_Time_Name", this.dataScriptRule.DesBusinessPk); System.Data.OracleClient.OracleParameter p4 = new System.Data.OracleClient.OracleParameter("I_Object_Table_Name", this.dataScriptRule.DesTable); System.Data.OracleClient.OracleParameter p5 = new System.Data.OracleClient.OracleParameter("I_Source_Table_Name", tableName); System.Data.OracleClient.OracleParameter p6 = new System.Data.OracleClient.OracleParameter("O_Return_Int", System.Data.OracleClient.OracleType.Int32); p6.Direction = ParameterDirection.Output; System.Data.OracleClient.OracleParameter p7 = new System.Data.OracleClient.OracleParameter("O_Return_String", System.Data.OracleClient.OracleType.VarChar, 100); p7.Direction = ParameterDirection.Output; ps[0] = p1; ps[1] = p2; ps[2] = p3; ps[3] = p4; ps[4] = p5; ps[5] = p6; ps[6] = p7; object[] prout = OracleHelper.ExcuteProc("Process_Date_Server.Process_Temp_Into_Oragin", 2, ps); }
/// <summary> /// 初始化 com.individual.helper.ComDbParameter 类的新实例 /// </summary> /// <param name="DataBaseType">数据库类型</param> /// <param name="ParameterName">参数名称</param> /// <param name="DbType">参数数据类型</param> /// <param name="Value">参数值</param> /// <param name="Direction">该值指示参数是只可输入、只可输出、双向还是存储过程返回值参数,System.Data.ParameterDirection 值之一</param> public static DbParameter CreateDbParameter(DataBaseType DataBaseType, string ParameterName, DbParameterType DbType, object Value, ParameterDirection Direction) { if (DataBaseType == helper.DataBaseType.Access) { System.Data.OleDb.OleDbParameter param = new System.Data.OleDb.OleDbParameter(ParameterName, GetOleDbType(DbType)); param.Direction = Direction; param.Value = Value; param.Size = 8000; return param; } else if (DataBaseType == helper.DataBaseType.MySql) { MySql.Data.MySqlClient.MySqlParameter param = new MySql.Data.MySqlClient.MySqlParameter(ParameterName, GetMySqlDbType(DbType)); param.Direction = Direction; param.Value = Value; param.Size = 8000; return param; } else if (DataBaseType == helper.DataBaseType.Oracle) { System.Data.OracleClient.OracleParameter param = new System.Data.OracleClient.OracleParameter(ParameterName, GetOracleType(DbType)); param.Direction = Direction; param.Value = Value; param.Size = 8000; return param; } else { System.Data.SqlClient.SqlParameter param = new System.Data.SqlClient.SqlParameter(ParameterName, GetSqlDbType(DbType)); param.Direction = Direction; param.Value = Value; param.Size = 8000; return param; } }
public static DbParameter[] GetSelectParamArray <T>(T t, MyDBType mydbtype) { string tStr = string.Empty; if (t == null) { return(null); } System.Reflection.PropertyInfo[] properties = t.GetType().GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public); if (properties.Length <= 0) { return(null); } System.Collections.Generic.List <DbParameter> list = new System.Collections.Generic.List <DbParameter>(); DbParameter[] paramArray = null; int lenght = 0; for (int i = 0; i < properties.Length; i++) { string name = properties[i].Name; //���� object value = properties[i].GetValue(t, null); //ֵ if (value != null) { if (CanAdd(name, t.GetType().Name)) { lenght++; } } } switch (mydbtype) { case MyDBType.Access: paramArray = new System.Data.OleDb.OleDbParameter[lenght]; break; case MyDBType.Sql: paramArray = new System.Data.SqlClient.SqlParameter[lenght]; break; case MyDBType.Oracle: paramArray = new System.Data.OracleClient.OracleParameter[lenght]; break; } int index = 0; for (int i = 0; i < properties.Length; i++) { string name = properties[i].Name; //���� object value = properties[i].GetValue(t, null); //ֵ string type = properties[i].PropertyType.Name; if (value != null) { if (CanAdd(name, t.GetType().Name)) { switch (mydbtype) { case MyDBType.Access: paramArray[index] = new System.Data.OleDb.OleDbParameter("@" + name, value); break; case MyDBType.Sql: paramArray[index] = new System.Data.SqlClient.SqlParameter("@" + name, value); break; case MyDBType.Oracle: paramArray[index] = new System.Data.OracleClient.OracleParameter(":" + name, value); break; } index++; } } } return(paramArray); }
protected override void PrepareParameters() { // Prepare parameters SQLParameter parameter; for (int index = 0; index < _parameterIndexes.Length; index++) { parameter = Parameters[_parameterIndexes[index]]; System.Data.OracleClient.OracleParameter oracleParameter = (System.Data.OracleClient.OracleParameter)_command.CreateParameter(); oracleParameter.ParameterName = String.Format(":{0}", parameter.Name); oracleParameter.IsNullable = true; switch (parameter.Direction) { case SQLDirection.Out: oracleParameter.Direction = System.Data.ParameterDirection.Output; break; case SQLDirection.InOut: oracleParameter.Direction = System.Data.ParameterDirection.InputOutput; break; case SQLDirection.Result: oracleParameter.Direction = System.Data.ParameterDirection.ReturnValue; break; default: oracleParameter.Direction = System.Data.ParameterDirection.Input; break; } if (parameter.Type is SQLStringType) { oracleParameter.OracleType = System.Data.OracleClient.OracleType.VarChar; oracleParameter.Size = ((SQLStringType)parameter.Type).Length; } else if (parameter.Type is SQLBooleanType) { oracleParameter.OracleType = System.Data.OracleClient.OracleType.Int32; } else if (parameter.Type is SQLIntegerType) { switch (((SQLIntegerType)parameter.Type).ByteCount) { case 1: oracleParameter.OracleType = System.Data.OracleClient.OracleType.Byte; break; case 2: oracleParameter.OracleType = System.Data.OracleClient.OracleType.Int16; break; case 8: oracleParameter.OracleType = System.Data.OracleClient.OracleType.Number; oracleParameter.Precision = 20; oracleParameter.Scale = 0; break; default: oracleParameter.OracleType = System.Data.OracleClient.OracleType.Int32; break; } } else if (parameter.Type is SQLNumericType) { SQLNumericType type = (SQLNumericType)parameter.Type; oracleParameter.OracleType = System.Data.OracleClient.OracleType.Number; oracleParameter.Precision = type.Precision; oracleParameter.Scale = type.Scale; } else if (parameter.Type is SQLBinaryType) { oracleParameter.OracleType = System.Data.OracleClient.OracleType.Blob; } else if (parameter.Type is SQLTextType) { oracleParameter.OracleType = System.Data.OracleClient.OracleType.Clob; } else if (parameter.Type is SQLDateTimeType) { oracleParameter.OracleType = System.Data.OracleClient.OracleType.DateTime; } else if (parameter.Type is SQLDateType) { oracleParameter.OracleType = System.Data.OracleClient.OracleType.DateTime; } else if (parameter.Type is SQLTimeType) { oracleParameter.OracleType = System.Data.OracleClient.OracleType.DateTime; } else if (parameter.Type is SQLGuidType) { oracleParameter.OracleType = System.Data.OracleClient.OracleType.Char; oracleParameter.Size = 24; } else if (parameter.Type is SQLMoneyType) { oracleParameter.OracleType = System.Data.OracleClient.OracleType.Number; oracleParameter.Precision = 28; oracleParameter.Scale = 8; } else { throw new ConnectionException(ConnectionException.Codes.UnknownSQLDataType, parameter.Type.GetType().Name); } _command.Parameters.Add(oracleParameter); } }
/// <summary> /// 执行带一个存储过程参数的的SQL语句。 /// </summary> /// <param name="SQLString">SQL语句</param> /// <param name="content">参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加</param> /// <returns>影响的记录数</returns> public static int ExecuteSql(string SQLString,string content) { using (OracleConnection connection = new OracleConnection(connectionString)) { OracleCommand cmd = new OracleCommand(SQLString,connection); System.Data.OracleClient.OracleParameter myParameter = new System.Data.OracleClient.OracleParameter("@content", OracleDbType.NVarchar2); myParameter.Value = content ; cmd.Parameters.Add(myParameter); try { connection.Open(); int rows=cmd.ExecuteNonQuery(); return rows; } catch(System.Data.OracleClient.OracleException E) { throw new Exception(E.Message); } finally { cmd.Dispose(); connection.Close(); } } }
/// <summary> /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例) /// </summary> /// <param name="strSQL">SQL语句</param> /// <param name="fs">图像字节,数据库的字段类型为image的情况</param> /// <returns>影响的记录数</returns> public static int ExecuteSqlInsertImg(string strSQL,byte[] fs) { using (OracleConnection connection = new OracleConnection(connectionString)) { OracleCommand cmd = new OracleCommand(strSQL,connection); System.Data.OracleClient.OracleParameter myParameter = new System.Data.OracleClient.OracleParameter("@fs", OracleDbType.LongRaw); myParameter.Value = fs ; cmd.Parameters.Add(myParameter); try { connection.Open(); int rows=cmd.ExecuteNonQuery(); return rows; } catch(System.Data.OracleClient.OracleException E) { throw new Exception(E.Message); } finally { cmd.Dispose(); connection.Close(); } } }
protected override DbParameter GetOneDbParameter(string name, object value) { System.Data.OracleClient.OracleParameter cur = new System.Data.OracleClient.OracleParameter(name, value); return(cur); }
private void DisplayDataForQuery() { //AppGlobals.AppMessages.DisplayAlertMessage("DisplayDataForQuery"); dataGridView1.DataSource = null; if (queryBuilder.MetadataProvider != null && queryBuilder.MetadataProvider.Connected) { if (queryBuilder.MetadataProvider is MSSQLMetadataProvider) { SqlCommand command = (SqlCommand)queryBuilder.MetadataProvider.Connection.CreateCommand(); command.CommandText = queryBuilder.SQL; // handle the query parameters if (queryBuilder.Parameters.Count > 0) { for (int i = 0; i < queryBuilder.Parameters.Count; i++) { if (!command.Parameters.Contains(queryBuilder.Parameters[i].FullName)) { SqlParameter parameter = new SqlParameter(); parameter.ParameterName = queryBuilder.Parameters[i].FullName; parameter.DbType = queryBuilder.Parameters[i].DataType; command.Parameters.Add(parameter); } } using (QueryParametersForm qpf = new QueryParametersForm(command)) { qpf.ShowDialog(); } } SqlDataAdapter adapter = new SqlDataAdapter(command); DataSet dataset = new DataSet(); try { adapter.Fill(dataset, "QueryResult"); dataGridView1.DataSource = dataset.Tables["QueryResult"]; } catch (Exception ex) { MessageBox.Show(ex.Message, "SQL query error"); } }//end sql server //if (queryBuilder.MetadataProvider is MSSQLCEMetadataProvider) //{ // SqlCeCommand command = (SqlCeCommand)queryBuilder.MetadataProvider.Connection.CreateCommand(); // command.CommandText = queryBuilder.SQL; // // handle the query parameters // if (queryBuilder.Parameters.Count > 0) // { // for (int i = 0; i < queryBuilder.Parameters.Count; i++) // { // if (!command.Parameters.Contains(queryBuilder.Parameters[i].FullName)) // { // SqlCeParameter parameter = new SqlCeParameter(); // parameter.ParameterName = queryBuilder.Parameters[i].FullName; // parameter.DbType = queryBuilder.Parameters[i].DataType; // command.Parameters.Add(parameter); // } // } // using (QueryParametersForm qpf = new QueryParametersForm(command)) // { // qpf.ShowDialog(); // } // } // SqlCeDataAdapter adapter = new SqlCeDataAdapter(command); // DataSet dataset = new DataSet(); // try // { // adapter.Fill(dataset, "QueryResult"); // dataGridView1.DataSource = dataset.Tables["QueryResult"]; // } // catch (Exception ex) // { // MessageBox.Show(ex.Message, "SQL query error"); // } //}//end sql server ce if (queryBuilder.MetadataProvider is OLEDBMetadataProvider) { //AppGlobals.AppMessages.DisplayAlertMessage("for OLEDBMetadataProvider"); OleDbCommand command = (OleDbCommand)queryBuilder.MetadataProvider.Connection.CreateCommand(); command.CommandText = queryBuilder.SQL; // handle the query parameters if (queryBuilder.Parameters.Count > 0) { for (int i = 0; i < queryBuilder.Parameters.Count; i++) { if (!command.Parameters.Contains(queryBuilder.Parameters[i].FullName)) { OleDbParameter parameter = new OleDbParameter(); parameter.ParameterName = queryBuilder.Parameters[i].FullName; parameter.DbType = queryBuilder.Parameters[i].DataType; command.Parameters.Add(parameter); } } using (QueryParametersForm qpf = new QueryParametersForm(command)) { qpf.ShowDialog(); } } OleDbDataAdapter adapter = new OleDbDataAdapter(command); DataSet dataset = new DataSet(); try { adapter.Fill(dataset, "QueryResult"); dataGridView1.DataSource = dataset.Tables["QueryResult"]; } catch (Exception ex) { MessageBox.Show(ex.Message, "SQL query error"); } //AppGlobals.AppMessages.DisplayAlertMessage("end for OLEDBMetadataProvider"); }//end ole schemaRoot if (queryBuilder.MetadataProvider is ODBCMetadataProvider) { OdbcCommand command = (OdbcCommand)queryBuilder.MetadataProvider.Connection.CreateCommand(); command.CommandText = queryBuilder.SQL; // handle the query parameters if (queryBuilder.Parameters.Count > 0) { for (int i = 0; i < queryBuilder.Parameters.Count; i++) { if (!command.Parameters.Contains(queryBuilder.Parameters[i].FullName)) { OdbcParameter parameter = new OdbcParameter(); parameter.ParameterName = queryBuilder.Parameters[i].FullName; parameter.DbType = queryBuilder.Parameters[i].DataType; command.Parameters.Add(parameter); } } using (QueryParametersForm qpf = new QueryParametersForm(command)) { qpf.ShowDialog(); } } OdbcDataAdapter adapter = new OdbcDataAdapter(command); DataSet dataset = new DataSet(); try { adapter.Fill(dataset, "QueryResult"); dataGridView1.DataSource = dataset.Tables["QueryResult"]; } catch (Exception ex) { MessageBox.Show(ex.Message, "SQL query error"); } }//end odbc if (queryBuilder.MetadataProvider is OracleMetadataProvider) { System.Data.OracleClient.OracleCommand command = (System.Data.OracleClient.OracleCommand)queryBuilder.MetadataProvider.Connection.CreateCommand(); command.CommandText = queryBuilder.SQL; // handle the query parameters if (queryBuilder.Parameters.Count > 0) { for (int i = 0; i < queryBuilder.Parameters.Count; i++) { if (!command.Parameters.Contains(queryBuilder.Parameters[i].FullName)) { System.Data.OracleClient.OracleParameter parameter = new System.Data.OracleClient.OracleParameter(); parameter.ParameterName = queryBuilder.Parameters[i].FullName; parameter.DbType = queryBuilder.Parameters[i].DataType; command.Parameters.Add(parameter); } } using (QueryParametersForm qpf = new QueryParametersForm(command)) { qpf.ShowDialog(); } } System.Data.OracleClient.OracleDataAdapter adapter = new System.Data.OracleClient.OracleDataAdapter(command); DataSet dataset = new DataSet(); try { adapter.Fill(dataset, "QueryResult"); dataGridView1.DataSource = dataset.Tables["QueryResult"]; } catch (Exception ex) { MessageBox.Show(ex.Message, "SQL query error"); } } //end oracle (ms) } //end check if connected } //end method
/// <summary> /// 生成参数 /// </summary> /// <param name="ParamName"></param> /// <param name="DbType"></param> /// <param name="Size"></param> /// <returns></returns> public DbParameter MakeParam(string ParamName, DbType DbType, int Size) { System.Data.SqlClient.SqlParameter sparam; System.Data.OracleClient.OracleParameter oparam; switch (SDBType) { case "SqlServer": if (Size > 0) sparam = new System.Data.SqlClient.SqlParameter(ParamName, (SqlDbType)DbType, Size); else sparam = new System.Data.SqlClient.SqlParameter(ParamName, (SqlDbType)DbType); return sparam; case "Oracle": if (Size > 0) oparam = new System.Data.OracleClient.OracleParameter(ParamName, (System.Data.OracleClient.OracleType)DbType, Size); else oparam = new System.Data.OracleClient.OracleParameter(ParamName, (System.Data.OracleClient.OracleType)DbType); return oparam; default: if (Size > 0) sparam = new System.Data.SqlClient.SqlParameter(ParamName, (SqlDbType)DbType, Size); else sparam = new System.Data.SqlClient.SqlParameter(ParamName, (SqlDbType)DbType); return sparam; } }
private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e) { Neusoft.FrameWork.WinForms.Classes.Function.ShowWaitForm("ÕýÔÚ¼ìË÷"); Application.DoEvents(); Neusoft.FrameWork.Management.Connection.Instance = new System.Data.OracleClient.OracleConnection(Neusoft.FrameWork.Management.Connection.Instance.ConnectionString); Neusoft.FrameWork.Management.Connection.Instance.Open(); Neusoft.FrameWork.Management.DataBaseManger m = new Neusoft.FrameWork.Management.DataBaseManger(); DataSet ds = new DataSet(); if (m.ExecQuery("select id,name from com_sql", ref ds) == -1) { Neusoft.FrameWork.WinForms.Classes.Function.HideWaitForm(); MessageBox.Show(m.Err); return; } //Neusoft.FrameWork.Management.Connection.Instance.Close(); //Neusoft.FrameWork.Management.Connection.Instance = new Oracle.DataAccess.Client.OracleConnection(Neusoft.FrameWork.Management.Connection.Instance.ConnectionString); //Neusoft.FrameWork.Management.Connection.Instance.Open(); Neusoft.FrameWork.Management.PublicTrans.BeginTransaction(); m.SetTrans(Neusoft.FrameWork.Management.PublicTrans.Trans); //Neusoft.FrameWork.Management.Transaction t = new Neusoft.FrameWork.Management.Transaction(Neusoft.FrameWork.Management.Connection.Instance); //t.BeginTransaction(); //m.SetTrans(t.Trans); foreach (DataRow dr in ds.Tables[0].Rows) { string s = "update com_sql set name1 = :a where id='" + dr[0].ToString() + "'"; System.Data.OracleClient.OracleCommand command = new System.Data.OracleClient.OracleCommand(s); command.Connection = Neusoft.FrameWork.Management.Connection.Instance as System.Data.OracleClient.OracleConnection; command.CommandText = s; command.CommandType = System.Data.CommandType.Text; command.Transaction = Neusoft.FrameWork.Management.PublicTrans.Trans as System.Data.OracleClient.OracleTransaction; System.Data.OracleClient.OracleParameter param = command.Parameters.Add("a", System.Data.OracleClient.OracleType.Clob); param.Direction = System.Data.ParameterDirection.Input; // Assign Byte Array to Oracle Parameter param.Value = dr[1].ToString(); // Step 5 // Execute the Anonymous PL/SQL Block try { if (command.ExecuteNonQuery() == -1) { Neusoft.FrameWork.Management.PublicTrans.RollBack(); Neusoft.FrameWork.WinForms.Classes.Function.HideWaitForm(); MessageBox.Show(m.Err); return; } } catch (Exception ex) { Neusoft.FrameWork.Management.PublicTrans.RollBack(); Neusoft.FrameWork.WinForms.Classes.Function.HideWaitForm(); MessageBox.Show(ex.Message); return; } //if (m.InputBlob(s, b)== -1) //{ // t.RollBack(); // Neusoft.FrameWork.WinForms.Classes.Function.HideWaitForm(); // MessageBox.Show(m.Err); // return; //} Neusoft.FrameWork.WinForms.Classes.Function.ShowWaitForm(dr[0].ToString() + "\n" + dr[1].ToString()); Application.DoEvents(); } Neusoft.FrameWork.Management.PublicTrans.Commit(); Neusoft.FrameWork.WinForms.Classes.Function.HideWaitForm(); MessageBox.Show("³É¹¦£¡"); }
public static DbParameter[] GetParamArray <T>(T t, MyDBType mydbtype) { string tStr = string.Empty; if (t == null) { return(null); } System.Reflection.PropertyInfo[] properties = t.GetType().GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public); if (properties.Length <= 0) { return(null); } System.Collections.Generic.List <DbParameter> list = new System.Collections.Generic.List <DbParameter>(); DbParameter[] paramArray = null; switch (mydbtype) { case MyDBType.Access: paramArray = new System.Data.OleDb.OleDbParameter[properties.Length]; break; case MyDBType.Sql: paramArray = new System.Data.SqlClient.SqlParameter[properties.Length]; break; case MyDBType.Oracle: paramArray = new System.Data.OracleClient.OracleParameter[properties.Length]; break; } for (int i = 0; i < properties.Length; i++) { string name = properties[i].Name; //���� object value = properties[i].GetValue(t, null); //ֵ string type = properties[i].PropertyType.Name; switch (mydbtype) { case MyDBType.Access: if (name.ToLower() == "LIFECYCLE".ToLower() && value.ToString() == "0") { paramArray[i] = new System.Data.OleDb.OleDbParameter("@" + name, DBNull.Value); } else { paramArray[i] = new System.Data.OleDb.OleDbParameter("@" + name, value); } break; case MyDBType.Sql: if (name.ToLower() == "LIFECYCLE".ToLower() && value.ToString() == "0") { paramArray[i] = new System.Data.SqlClient.SqlParameter("@" + name, DBNull.Value); } else { paramArray[i] = new System.Data.SqlClient.SqlParameter("@" + name, value); } break; case MyDBType.Oracle: if (name.ToLower() == "LIFECYCLE".ToLower() && value.ToString() == "0") { paramArray[i] = new System.Data.OracleClient.OracleParameter(":" + name, DBNull.Value); } else { paramArray[i] = new System.Data.OracleClient.OracleParameter(":" + name, value); } break; } } return(paramArray); }