private void InitParameters(Parse headerCells) { Dictionary <String, DbParameterAccessor> allParams = dbEnvironment.GetAllProcedureParameters(procedureName); accessors = new DbParameterAccessor[headerCells.Size]; for (int i = 0; headerCells != null; i++, headerCells = headerCells.More) { String paramName = NameNormaliser.NormaliseName(headerCells.Text); try { accessors[i] = DbParameterAccessor.CloneWithSameParameter(allParams[paramName]); } catch (System.Collections.Generic.KeyNotFoundException) { Wrong(headerCells); throw new ApplicationException("Cannot find parameter " + paramName); } accessors[i].IsBoundToCheckOperation = checkIsImpliedByRegex.IsMatch(headerCells.Text); // sql server quirk. if output parameter is used in an input column, then // the param should be remapped to IN/OUT if ((!accessors[i].IsBoundToCheckOperation) && accessors[i].DbParameter.Direction == ParameterDirection.Output) { accessors[i].DbParameter.Direction = ParameterDirection.InputOutput; } } }
private void InitParameters(Parse headerCells) { Dictionary <String, DbParameterAccessor> allParams = dbEnvironment.GetAllProcedureParameters(procedureName); columnAccessors = new ColumnAccessors(); for (int i = 0; headerCells != null; i++, headerCells = headerCells.More) { String paramName = NameNormaliser.NormaliseName(headerCells.Text); try { DbParameterAccessor accessor = DbParameterAccessor.CloneWithSameParameter(allParams[paramName]); accessor.IsBoundToCheckOperation = BindingFactory.CheckIsImpliedBy(headerCells.Text); // sql server quirk. if output parameter is used in an input column, then // the param should be remapped to IN/OUT if ((!accessor.IsBoundToCheckOperation) && accessor.DbParameter.Direction == ParameterDirection.Output) { accessor.DbParameter.Direction = ParameterDirection.InputOutput; } columnAccessors.Assign(paramName, accessor); accessors.Add(accessor); } catch (KeyNotFoundException) { Wrong(headerCells); throw new ApplicationException("Cannot find parameter " + paramName); } } }
public override Dictionary <string, DbParameterAccessor> GetAllColumns(string tableOrViewName) { String[] qualifiers = NameNormaliser.NormaliseName(tableOrViewName).Split('.'); SchemaObjectName name = SchemaObjectName.Parse(qualifiers); string qry = GetAllColumnsSql(name); return(ReadIntoParams(name, qry)); }
public void CheckNormaliseName() { Assert.AreEqual("dbtest", NameNormaliser.NormaliseName("dbtest?")); Assert.AreEqual("dbtest", NameNormaliser.NormaliseName("db test")); Assert.AreEqual("dbtest", NameNormaliser.NormaliseName("db test?")); Assert.AreEqual("db.test", NameNormaliser.NormaliseName("db.test")); Assert.AreEqual("db_test", NameNormaliser.NormaliseName("db_test")); Assert.AreEqual("dbtest", NameNormaliser.NormaliseName("DbTeSt")); }
private Dictionary <string, DbParameterAccessor> ProcedureReadIntoParams(String[] queryParameters, String query) //done { DbDataReader reader = ExecuteParameterQuery(queryParameters, query); Dictionary <String, DbParameterAccessor> allParams = new Dictionary <string, DbParameterAccessor>(); reader.Read(); String procType = (reader.IsDBNull(0)) ? null : reader.GetString(0); String paramList = multispaces.Replace(reader.GetString(1), " "); String returns = reader.GetString(2); reader.Close(); int position = 0; foreach (string param in paramList.Split(',')) { string[] tokens = param.Trim().ToLower().Split(new char[] { ' ', '(', ')' }); int i = 0; string direction = ""; string paramName = ""; string dataType = ""; int length = 0; if (tokens[i].Equals("in") || tokens[i].Equals("out") || tokens[i].Equals("inout")) { direction = tokens[i]; i++; } else { direction = "in"; } paramName = tokens[i]; i++; dataType = tokens[i]; i++; if (i <= tokens.Length - 1 && !Int32.TryParse(tokens[i], out length)) { length = 0; } MySqlParameter dp = BuildMySqlParameter(direction, paramName, dataType, length); allParams[NameNormaliser.NormaliseName(paramName)] = new DbParameterAccessor(dp, GetDotNetType(dataType), position++, dataType); } if (procType.Equals("FUNCTION")) { string[] tokens = returns.Trim().ToLower().Split(new char[] { ' ', '(', ')' }); string paramName = "?"; string dataType = tokens[0]; MySqlParameter dp = BuildMySqlParameter("return", paramName, dataType, -1); allParams[NameNormaliser.NormaliseName(paramName)] = new DbParameterAccessor(dp, GetDotNetType(dataType), position++, dataType); } return(allParams); }
private void ReadColumnNames(Parse headerCells) { accessors = new DataColumnAccessor[headerCells.Size]; for (int i = 0; headerCells != null; i++, headerCells = headerCells.More) { String columnName = NameNormaliser.NormaliseName(headerCells.Text); accessors[i] = new DataColumnAccessor(GetColumn(columnName), !checkIsImpliedByRegex.IsMatch(headerCells.Text)); } }
private DataColumn GetColumn(String name) { foreach (DataColumn c in dataTable.Columns) { if (name.Equals(NameNormaliser.NormaliseName(c.ColumnName))) { return(c); } } throw new ColumnMissingException(name); }
private Dictionary <string, DbParameterAccessor> ReadIntoParams(String[] queryParameters, String query) { var cnx = CurrentConnection; var dc = cnx.CreateCommand(); dc.Transaction = (DbTransaction)CurrentTransaction; dc.CommandText = query; dc.CommandType = CommandType.Text; for (int i = 0; i < queryParameters.Length; i++) { AddInput(dc, ":" + i, queryParameters[i].ToUpper()); } var reader = dc.ExecuteReader(); Dictionary <String, DbParameterAccessor> allParams = new Dictionary <string, DbParameterAccessor>(); int position = 0; while (reader.Read()) { String paramName = (reader.IsDBNull(0)) ? null : reader.GetString(0); String dataType = reader.GetString(1); int length = (reader.IsDBNull(2)) ? 0 : reader.GetInt32(2); String direction = reader.GetString(3); OracleParameter dp = new OracleParameter(); dp.Direction = GetParameterDirection(direction); if (paramName != null) { dp.ParameterName = paramName; dp.SourceColumn = paramName; } else { dp.Direction = ParameterDirection.ReturnValue; } dp.OracleType = GetDBType(dataType); if (length > 0) { dp.Size = length; } else { if (!ParameterDirection.Input.Equals(dp.Direction) || typeof(String).Equals(GetDotNetType(dataType))) { dp.Size = 4000; } } allParams[NameNormaliser.NormaliseName(paramName)] = new DbParameterAccessor(dp, GetDotNetType(dataType), position++, dataType); } return(allParams); }
// this method will initialise accessors array from the parameters that // really go into the insert command and columnAccessors for all columns private void InitParameters(Parse headerCells) { Dictionary <String, DbParameterAccessor> allParams = dbEnvironment.GetAllColumns(parameterTableType); columnAccessors = new ColumnAccessors(); isOutputColumn = new bool[headerCells.Size]; var paramAccessors = new List <DbParameterAccessor>(); for (int i = 0; headerCells != null; i++, headerCells = headerCells.More) { String paramName = NameNormaliser.NormaliseName(headerCells.Text); DbParameterAccessor currentColumn; try { currentColumn = allParams[paramName]; } catch (KeyNotFoundException) { Wrong(headerCells); throw new ApplicationException("Cannot find column " + paramName); } isOutputColumn[i] = BindingFactory.CheckIsImpliedBy(headerCells.Text); currentColumn.IsBoundToCheckOperation = isOutputColumn[i]; columnAccessors.Assign(paramName, currentColumn); if (isOutputColumn[i]) { if (dbEnvironment.SupportsReturnOnInsert) { currentColumn.DbParameter.Direction = ParameterDirection.Output; paramAccessors.Add(currentColumn); } else // don't add to paramAccessors { //columnAccessors.Assign(paramName, new IdRetrievalAccessor(dbEnvironment, currentColumn.DotNetType)); columnAccessors.Assign(paramName, new IdRetrievalAccessor(dbEnvironment, currentColumn.DotNetType, parameterTableType)); } } else // not output { currentColumn.DbParameter.Direction = ParameterDirection.Input; paramAccessors.Add(currentColumn); } table.Columns.Add(currentColumn.DbFieldName, currentColumn.DotNetType); } accessors = paramAccessors.ToArray(); }
public override Dictionary <String, DbParameterAccessor> GetAllProcedureParameters(String procName) { String[] qualifiers = NameNormaliser.NormaliseName(procName).Split('.'); String cols = " argument_name, data_type, data_length, IN_OUT, sequence "; String qry = @" select " + cols + " from all_arguments where data_level=0 and "; if (qualifiers.Length == 3) { qry += " owner=:0 and package_name=:1 and object_name=:2 "; } else if (qualifiers.Length == 2) { qry += @" ((owner=:0 and package_name is null and object_name=:1) or (owner=user and package_name=:0 and object_name=:1))" ; } else { qry += @" (owner=user and package_name is null and object_name=:0)" ; } // map to public synonyms also if (qualifiers.Length < 3) { qry += @" union all select " + cols + @" from all_arguments, all_synonyms where data_level=0 and all_synonyms.owner='PUBLIC' and all_arguments.owner=table_owner and " ; if (qualifiers.Length == 2) // package { qry += " package_name=table_name and synonym_name=:0 and object_name=:1 "; } else { qry += " package_name is null and object_name=table_name and synonym_name=:0 "; } } qry += " order by sequence "; //Console.WriteLine(qry); Dictionary <String, DbParameterAccessor> res = ReadIntoParams(qualifiers, qry); if (res.Count == 0) { throw new ApplicationException("Cannot read list of parameters for " + procName + " - check spelling and access privileges"); } return(res); }
// this method will initialise accessors array from the parameters that // really go into the insert command and columnAccessors for all columns private void InitParameters(Parse headerCells) { Dictionary <String, DbParameterAccessor> allParams = dbEnvironment.GetAllColumns(tableName); columnAccessors = new Accessor[headerCells.Size]; isOutputColumn = new bool[headerCells.Size]; List <DbParameterAccessor> paramAccessors = new List <DbParameterAccessor>(); for (int i = 0; headerCells != null; i++, headerCells = headerCells.More) { String paramName = NameNormaliser.NormaliseName(headerCells.Text); DbParameterAccessor currentColumn; try { currentColumn = allParams[paramName]; } catch (System.Collections.Generic.KeyNotFoundException) { Wrong(headerCells); throw new ApplicationException("Cannot find column " + paramName); } isOutputColumn[i] = checkIsImpliedByRegex.IsMatch(headerCells.Text); currentColumn.IsBoundToCheckOperation = isOutputColumn[i]; columnAccessors[i] = currentColumn; if (isOutputColumn[i]) { if (dbEnvironment.SupportsReturnOnInsert) { currentColumn.DbParameter.Direction = ParameterDirection.Output; paramAccessors.Add(currentColumn); } else // don't add to paramAccessors { columnAccessors[i] = new dbfit.util.IdRetrievalAccessor(dbEnvironment, currentColumn.DotNetType); } } else // not output { currentColumn.DbParameter.Direction = ParameterDirection.Input; paramAccessors.Add(currentColumn); } } accessors = paramAccessors.ToArray(); }
private void InitParameters(Parse headerCells) { Dictionary <String, DbParameterAccessor> allParams = dbEnvironment.GetAllColumns(tableName); columnAccessors = new ColumnAccessors(); IList <DbParameterAccessor> selectAccList = new List <DbParameterAccessor>(); IList <DbParameterAccessor> updateAccList = new List <DbParameterAccessor>(); for (int i = 0; headerCells != null; i++, headerCells = headerCells.More) { String paramName = NameNormaliser.NormaliseName(headerCells.Text); try { DbParameterAccessor acc = allParams[paramName]; acc.DbParameter.Direction = ParameterDirection.Input; // allow same column to be used in both sides: // remap update parameters to u_paramname and select to s_paramname acc = DbParameterAccessor.Clone(acc, dbEnvironment); if (headerCells.Text.EndsWith("=")) { acc.DbParameter.ParameterName = acc.DbParameter.ParameterName + "_u"; updateAccList.Add(acc); columnAccessors.Assign(paramName + "=", acc); } else { acc.DbParameter.ParameterName = acc.DbParameter.ParameterName + "_s"; selectAccList.Add(acc); columnAccessors.Assign(paramName, acc); } } catch (KeyNotFoundException) { Wrong(headerCells); throw new ApplicationException("Cannot find column for " + paramName); } } selectAccessors = new DbParameterAccessor[selectAccList.Count]; selectAccList.CopyTo(selectAccessors, 0); updateAccessors = new DbParameterAccessor[updateAccList.Count]; updateAccList.CopyTo(updateAccessors, 0); }
private Dictionary <string, DbParameterAccessor> ReadIntoParams(String[] queryParameters, String query) //done { DbDataReader reader = ExecuteParameterQuery(queryParameters, query); Dictionary <String, DbParameterAccessor> allParams = new Dictionary <string, DbParameterAccessor>(); int position = 0; while (reader.Read()) { String paramName = (reader.IsDBNull(0)) ? null : reader.GetString(0); String dataType = reader.GetString(1); int length = (reader.IsDBNull(2)) ? 0 : reader.GetInt32(2); String direction = reader.GetString(3); MySqlParameter dp = BuildMySqlParameter(direction, paramName, dataType, length); allParams[NameNormaliser.NormaliseName(paramName)] = new DbParameterAccessor(dp, GetDotNetType(dataType), position++, dataType); } reader.Close(); return(allParams); }
public override Dictionary <String, DbParameterAccessor> GetAllProcedureParameters(String procName) //done { String[] qualifiers = NameNormaliser.NormaliseName(procName).Split('.'); String qry = " select type,param_list,returns from mysql.proc where "; if (qualifiers.Length == 2) { qry += " lower(db)=@schema and lower(name)=@objname "; } else { qry += " (db=database() and lower(name)=@objname)"; } //Console.WriteLine(qry); Dictionary <String, DbParameterAccessor> res = ProcedureReadIntoParams(qualifiers, qry); if (res.Count == 0) { throw new ApplicationException("Cannot read list of parameters for " + procName + " - check spelling and access privileges"); } return(res); }
public override Dictionary <String, DbParameterAccessor> GetAllProcedureParameters(String procName) { String[] qualifiers = NameNormaliser.NormaliseName(procName).Split('.'); String qry = " select parmname as column_name, typename as data_type, length, " + " rowtype as direction, ordinal from SYSIBM.SYSroutinePARMS where "; if (qualifiers.Length == 2) { qry += " lower(routineschema)=@0 and lower(routinename)=@1 "; } else { qry += " (lower(routinename)=@0)"; } qry += " order by ordinal"; Dictionary <String, DbParameterAccessor> res = ReadIntoParams(qualifiers, qry, ""); if (res.Count == 0) { throw new ApplicationException("Cannot read list of parameters for " + procName + " - check spelling and access privileges"); } return(res); }
public override Dictionary <String, DbParameterAccessor> GetAllColumns(String tableOrViewName) { String[] qualifiers = NameNormaliser.NormaliseName(tableOrViewName).Split('.'); String qry = " select colname as column_name, typename as data_type, length, " + " 'P' as direction from syscat.columns where "; if (qualifiers.Length == 2) { qry += " lower(tabschema)=@0 and lower(tabname)=@1 "; } else { qry += " (lower(tabname)=@0)"; } qry += " order by colname"; Dictionary <String, DbParameterAccessor> res = ReadIntoParams(qualifiers, qry, "@"); if (res.Count == 0) { throw new ApplicationException("Cannot read list of parameters for " + tableOrViewName + " - check spelling and access privileges"); } return(res); }
public override Dictionary <String, DbParameterAccessor> GetAllColumns(String tableOrViewName) //done { String[] qualifiers = NameNormaliser.NormaliseName(tableOrViewName).Split('.'); String qry = @" select column_name, data_type, character_maximum_length, 'IN' as direction from information_schema.columns where "; if (qualifiers.Length == 2) { qry += " lower(table_schema)=@schema and lower(table_name)=@objname "; } else { qry += @" (table_schema=database() and lower(table_name)=@objname)"; } qry += " order by ordinal_position "; //Console.WriteLine(qry); Dictionary <String, DbParameterAccessor> res = ReadIntoParams(qualifiers, qry); if (res.Count == 0) { throw new ApplicationException("Cannot read list of columns for " + tableOrViewName + " - check spelling and access privileges"); } return(res); }
public override Dictionary <String, DbParameterAccessor> GetAllColumns(String tableOrViewName) { String[] qualifiers = NameNormaliser.NormaliseName(tableOrViewName).Split('.'); String qry = @" select column_name, data_type, data_length, 'IN' as direction, column_id from all_tab_columns where " ; if (qualifiers.Length == 2) { qry += " owner=:0 and table_name=:1 "; } else { qry += @" (owner=user and table_name=:0)" ; } qry += " order by column_id "; Dictionary <String, DbParameterAccessor> res = ReadIntoParams(qualifiers, qry); if (res.Count == 0) { throw new ApplicationException("Cannot read list of columns for " + tableOrViewName + " - check spelling and access privileges"); } return(res); }
private void LoadRowStructure(Parse headerRow) { Parse headerCell = headerRow.Parts; int colNum = headerRow.Parts.Size; columnNames = new String[colNum]; keyProperties = new bool[colNum]; for (int i = 0; i < colNum; i++) { String currentName = headerCell.Text; if (currentName == null) { throw new ApplicationException("Column " + i + " does not have a name"); } currentName = currentName.Trim(); if (currentName.Length == 0) { throw new ApplicationException("Column " + i + " does not have a name"); } columnNames[i] = NameNormaliser.NormaliseName(currentName); keyProperties[i] = !currentName.EndsWith("?"); headerCell = headerCell.More; } }
private Dictionary <string, DbParameterAccessor> ReadIntoParams(String query, String objname, String schemaname) { objname = NameNormaliser.NormaliseName(objname); var cnx = CurrentConnection; var dc = cnx.CreateCommand(); dc.Transaction = (DbTransaction)CurrentTransaction; dc.CommandText = query; dc.CommandType = CommandType.Text; AddInput(dc, "@objname", objname); if (schemaname != null) { AddInput(dc, "@schemaname", NameNormaliser.NormaliseName(schemaname)); } var reader = dc.ExecuteReader(); Dictionary <String, DbParameterAccessor> allParams = new Dictionary <string, DbParameterAccessor>(); int position = 0; while (reader.Read()) { String paramName = (reader.IsDBNull(0)) ? null : reader.GetString(0); String dataType = reader.GetString(1); int length = (reader.IsDBNull(2)) ? 0 : System.Convert.ToInt32(reader[2]); String direction = (reader.IsDBNull(3)) ? "IN" : reader.GetString(3); byte precision = 0; byte scale = 0; if (!reader.IsDBNull(4)) { scale = System.Convert.ToByte(reader[4]); } if (!reader.IsDBNull(5)) { precision = System.Convert.ToByte(reader[5]); } SqlParameter dp = new SqlParameter(); dp.Direction = GetParameterDirection(direction); if (!String.IsNullOrEmpty(paramName)) { dp.ParameterName = paramName; dp.SourceColumn = paramName; } else { dp.Direction = ParameterDirection.ReturnValue; } dp.SqlDbType = GetDBType(dataType); if (precision > 0) { dp.Precision = precision; } if (scale > 0) { dp.Scale = scale; } if (length > 0) { dp.Size = System.Convert.ToInt32(length); } else { if (!ParameterDirection.Input.Equals(dp.Direction) || typeof(String).Equals(GetDotNetType(dataType))) { dp.Size = 4000; } } allParams[NameNormaliser.NormaliseName(paramName)] = new DbParameterAccessor(dp, GetDotNetType(dataType), position++, dataType); } reader.Close(); if (allParams.Count == 0) { throw new ApplicationException("Cannot read columns/parameters for object " + objname + " - check spelling or access privileges "); } return(allParams); }
private Dictionary <string, DbParameterAccessor> ReadIntoParams(String objname, String query) { if (objname.Contains(".")) { // The object name is multi-part an will not be amended } else { objname = "[" + NameNormaliser.NormaliseName(objname) + "]"; } DbCommand dc = CurrentConnection.CreateCommand(); dc.Transaction = CurrentTransaction; dc.CommandText = query; dc.CommandType = CommandType.Text; AddInput(dc, "@objname", objname); DbDataReader reader = dc.ExecuteReader(); var allParams = new Dictionary <string, DbParameterAccessor>(); int position = 0; while (reader.Read()) { String paramName = (reader.IsDBNull(0)) ? null : reader.GetString(0); String dataType = reader.GetString(1); int length = (reader.IsDBNull(2)) ? 0 : Convert.ToInt32(reader[2]); int isOutput = (reader.IsDBNull(3)) ? 0 : Convert.ToInt32(reader[3]); byte precision = Convert.ToByte(reader[5]); byte scale = Convert.ToByte(reader[6]); var dp = new SqlParameter { Direction = GetParameterDirection(isOutput) }; if (!String.IsNullOrEmpty(paramName)) { dp.ParameterName = paramName; dp.SourceColumn = paramName; } else { dp.Direction = ParameterDirection.ReturnValue; } dp.SqlDbType = GetDBType(dataType); String typeName = NormaliseTypeName(dataType); if (precision > 0) { dp.Precision = precision; } if (scale > 0) { dp.Scale = scale; } if ("NTEXT".Equals(typeName) || ("TEXT".Equals(typeName))) { dp.Size = MAX_STRING_SIZE; } else if ("NVARCHAR".Equals(typeName) || ("NCHAR".Equals(typeName))) { dp.Size = System.Convert.ToInt32(length) / 2; } else if (length > 0) { dp.Size = Convert.ToInt32(length); } else { if (!ParameterDirection.Input.Equals(dp.Direction) || typeof(String).Equals(GetDotNetType(dataType))) { dp.Size = MAX_STRING_SIZE; } } allParams[NameNormaliser.NormaliseName(paramName)] = new DbParameterAccessor(dp, GetDotNetType(dataType), position++, dataType); } reader.Close(); if (allParams.Count == 0) { throw new ApplicationException("Cannot read columns/parameters for object " + objname + " - check spelling or access privileges "); } return(allParams); }
private Dictionary <string, DbParameterAccessor> ReadIntoParams(String objname, String query) { if (objname.Contains(".")) { String[] schemaAndName = objname.Split(new char[] { '.' }, 2); objname = schemaAndName[0] + ".." + schemaAndName[1]; } else { objname = NameNormaliser.NormaliseName(objname); } var cnx = CurrentConnection; var dc = cnx.CreateCommand(); dc.Transaction = (DbTransaction)CurrentTransaction; dc.CommandText = query; dc.CommandType = CommandType.Text; AddInput(dc, "@objname", objname); var reader = dc.ExecuteReader(); Dictionary <String, DbParameterAccessor> allParams = new Dictionary <string, DbParameterAccessor>(); int position = 0; while (reader.Read()) { String paramName = (reader.IsDBNull(0)) ? null : reader.GetString(0); String dataType = reader.GetString(1); int length = (reader.IsDBNull(2)) ? 0 : System.Convert.ToInt32(reader[2]); int isOutput = (reader.IsDBNull(3)) ? 0 : System.Convert.ToInt32(reader[3]); byte precision = (reader.IsDBNull(4)) ? Convert.ToByte(0) : System.Convert.ToByte(reader[4]); byte scale = (reader.IsDBNull(5)) ? Convert.ToByte(0) : System.Convert.ToByte(reader[5]); AseParameter dp = new AseParameter(); dp.Direction = GetParameterDirection(isOutput); if (!String.IsNullOrEmpty(paramName)) { if (!paramName.StartsWith(ParameterPrefix)) { dp.ParameterName = ParameterPrefix + paramName; } else { dp.ParameterName = paramName; } dp.SourceColumn = paramName; } else { dp.Direction = ParameterDirection.ReturnValue; } dp.AseDbType = GetDBType(dataType); String typeName = NormaliseTypeName(dataType); if (precision > 0) { dp.Precision = precision; } if (scale > 0) { dp.Scale = scale; } if ("NTEXT".Equals(typeName) || ("TEXT".Equals(typeName))) { dp.Size = MAX_STRING_SIZE; } else if (length > 0) { dp.Size = System.Convert.ToInt32(length); } else { if (!ParameterDirection.Input.Equals(dp.Direction) || typeof(String).Equals(GetDotNetType(dataType))) { dp.Size = MAX_STRING_SIZE; } } allParams[NameNormaliser.NormaliseName(paramName)] = new DbParameterAccessor(dp, GetDotNetType(dataType), position++, dataType); } reader.Close(); return(allParams); }