protected override void InitializeParameter(IDbDataParameter dbParam, string name, SqlTypes.SqlType sqlType)
{
base.InitializeParameter(dbParam, name, sqlType);
if (sqlType.DbType == DbType.Currency)
dbParam.DbType = DbType.Decimal;
}
/// <summary>
/// Adds a new <see cref="Column"/> with the specified <paramref name="name"/>
/// this this <see cref="Table"/>.
/// </summary>
/// <param name="name">The name.</param>
/// <param name="type">The data type.</param>
/// <param name="length">The data type's length.</param>
/// <returns>The column.</returns>
public Column AddColumn(string name, SqlTypes type, int length)
{
Column c = new Column(this, name, Modifier.Add).Type(type, length);
Columns.Add(c);
return(c);
}
public static SqlParameter GetParameter(object obj, PropertyInfo propertyInfo, string parameterName = null)
{
parameterName = parameterName ?? propertyInfo.Name;
var sqlParameter = new SqlParameter(parameterName, propertyInfo.GetValue(obj, null));
var typeOfParameter = GetParameterType(propertyInfo);
if (!SqlTypes.IsClrTypeASqlSystemType(typeOfParameter))
{
LoggerHelper.Log($"*****UDT Type: {obj.GetType()} - {propertyInfo} - {propertyInfo.Name} ");
sqlParameter.UdtTypeName = propertyInfo.PropertyType.Name;
}
if (propertyInfo.PropertyType == typeof(XElement))
{
sqlParameter.DbType = DbType.Xml;
var xElement = (XElement)propertyInfo.GetValue(obj, null);
sqlParameter.Value = new SqlXml(xElement.CreateReader());
}
if (propertyInfo.PropertyType.IsEnum)
{
sqlParameter.SqlValue = (int)propertyInfo.GetValue(obj, null);
}
else if (propertyInfo.PropertyType == typeof(DateTime))
{
sqlParameter.DbType = DbType.DateTime2;
}
if (sqlParameter.Value == null)
{
sqlParameter.Value = DBNull.Value;
}
return(sqlParameter);
}
/// <summary>
/// Adds a new <see cref="Column"/> with the specified <paramref name="name"/>
/// this this <see cref="Table"/>.
/// </summary>
/// <param name="name">The name.</param>
/// <param name="type">The data type.</param>
/// <param name="scale">The scale.</param>
/// <param name="precision">The precision.</param>
/// <returns>The column.</returns>
public Column AddColumn(string name, SqlTypes type, int scale, int precision)
{
Column c = new Column(this, name, Modifier.Add).Type(type, scale, precision);
Columns.Add(c);
return(c);
}
public static string GetSqlTypeName(SqlTypes typ)
{
var typeName = typ switch
{
SqlTypes.INTEGER => "integer",
SqlTypes.INTEGER_ARRAY => "integer[]",
SqlTypes.SMALLINT => "smallint",
SqlTypes.SMALLINT_ARRAY => "smallint[]",
SqlTypes.BIGINT => "bigint",
SqlTypes.BIGINT_ARRAY => "bigint[]",
SqlTypes.VARCHAR => "text",
SqlTypes.REAL => "real",
SqlTypes.DOUBLE_PRECISION => "double precision",
SqlTypes.CHARACTER => "char",
SqlTypes.BLOB => "bytea",
SqlTypes.VECTOR2 => "vector2",
SqlTypes.VECTOR2_ARRAY => "vector2[]",
SqlTypes.VECTOR3 => "vector3",
SqlTypes.VECTOR3_ARRAY => "vector3[]",
SqlTypes.VECTOR4 => "vector4",
SqlTypes.VECTOR4_ARRAY => "vector4[]",
SqlTypes.MATRIX4_X4 => "matrix4x4",
SqlTypes.HALFMATRIX4_X3 => "halfMatrix4x3",
SqlTypes.BOX3 => "box3",
_ => "bytea"
};
return(typeName);
}
protected override void InitializeParameter(IDbDataParameter dbParam, string name, SqlTypes.SqlType sqlType)
{
base.InitializeParameter(dbParam, name, sqlType);
// Since the .NET currency type has 4 decimal places, we use a decimal type in PostgreSQL instead of its native 2 decimal currency type.
if (sqlType.DbType == DbType.Currency)
dbParam.DbType = DbType.Decimal;
}
protected override void InitializeParameter(IDbDataParameter dbParam, string name, SqlTypes.SqlType sqlType)
{
base.InitializeParameter(dbParam, name, sqlType);
if (sqlType.DbType == DbType.Time)
{
((SqlParameter) dbParam).SqlDbType = SqlDbType.Time;
}
}
private string getQueryDbChanger(string query, SqlTypes type)
{
try
{
string nQuery = query;
if (type == SqlTypes.INSERT | type == SqlTypes.INSERT_CUSTOM | type == SqlTypes.INSERT_RETURN_ID)
{
if (query.ToUpper().StartsWith("INSERT INTO "))
{
nQuery = query.Replace(query.Split(' ')[2], "[" + database + "].[dbo].[" + query.Split(' ')[2] + "]");
//while (nQuery.Contains("~" + query.Split(' ')[2]))
//nQuery = nQuery.Replace("~" + query.Split(' ')[2], "[" + database + "].[dbo].[" + query.Split(' ')[2] + "]");
}
}
else if (type == SqlTypes.SELECT)
{
if (query.ToUpper().StartsWith("SELECT "))
{
int fromStartIndex = query.ToUpper().IndexOf("FROM ");
nQuery = query.Insert(fromStartIndex + 5, "[" + database + "].[dbo].[");
int endOfTableName = -1;
for (int i = fromStartIndex + 5 + ("[" + database + "].[dbo].[").Length; i < nQuery.Length; i++)
{
if (nQuery.ToCharArray()[i] == ' ')
{
endOfTableName = i;
}
if (endOfTableName != -1)
{
i = nQuery.Length;
}
}
if (endOfTableName == -1)
{
nQuery = nQuery + "]";
}
else
{
nQuery = nQuery.Insert(endOfTableName, "]");
}
}
}
else if (type == SqlTypes.UPDATE)
{
if (query.ToUpper().StartsWith("UPDATE "))
{
nQuery = query.Replace(query.Split(' ')[1], "[" + database + "].[dbo].[" + query.Split(' ')[1] + "]");
}
}
return(nQuery);
}
catch (Exception ee)
{
OnError(ee);
}
return(query);
}
public string GetSqlType(SqlTypes c)
{
string sql = null;
if (sqlTypeLookup.TryGetValue(c, out sql) == false)
{
return(string.Empty);
}
return(sql);
}
public void Where8([IncludeDataSources(true, TestProvName.AllSqlServer2008Plus)] string context)
{
using (var db = GetDataContext(context))
{
var hid = SqlHierarchyId.Parse("/1/");
AreEqual(
SqlTypes.Data(context)
.Where(t => IsDescendantOf(hid, t.HID)),
db.GetTable <SqlTypes>()
.Where(t => IsDescendantOf(hid, t.HID) == true));
}
}
public void Where5([SqlServerDataContext] string context)
{
using (var db = GetDataContext(context))
{
var hid = SqlHierarchyId.Parse("/1/");
AreEqual(
SqlTypes.Data(context)
.Where(t => hid.IsDescendantOf(t.HID).IsTrue),
db.GetTable <SqlTypes>()
.Where(t => hid.IsDescendantOf(t.HID).IsTrue));
}
}
public void Where6([IncludeDataSources(true, TestProvName.AllSqlServer2008Plus)] string context)
{
using (var db = GetDataContext(context))
{
var hid = SqlHierarchyId.Parse("/1/");
AreEqual(
SqlTypes.Data(context)
.Where(t => (bool)(hid.IsDescendantOf(t.HID) == SqlBoolean.True) && t.ID != 1)
.OrderBy(c => c.HID),
db.GetTable <SqlTypes>()
.Where(t => (bool)(hid.IsDescendantOf(t.HID) == SqlBoolean.True) && t.ID != 1)
.OrderBy(c => c.HID));
}
}
public void Where6([SqlServerDataContext] string context)
{
using (var db = GetDataContext(context))
{
var hid = SqlHierarchyId.Parse("/1/");
AreEqual(
SqlTypes.Data(context)
.Where(t => (bool)(hid.IsDescendantOf(t.HID) == SqlBoolean.True) && t.ID != 1)
.OrderBy(c => c.HID),
db.GetTable <SqlTypes>()
.Where(t => (bool)(hid.IsDescendantOf(t.HID) == SqlBoolean.True) && t.ID != 1)
.OrderBy(c => c.HID));
}
}
private void BuildSQLGen(SqlTypes sqlType, string baseTableName, SqlTable[] optionalTables = null, bool distinct = false)
{
SqlTypeString = GetSqlType(sqlType);
SqlTable basetable = new SqlTable();
basetable.Name = baseTableName.ToUpper();
BaseTable = baseTableName.ToUpper();
if (BaseTable.Contains("."))
{
basetable.DoNotBracket = true;
}
Tables.Add(basetable);
if (optionalTables != null)
{
Tables.AddRange(optionalTables);
}
}
public static NpgsqlDbType GetNgpSqlTypeName(SqlTypes typ)
{
var ngpsqlType = typ switch
{
SqlTypes.INTEGER => NpgsqlDbType.Integer,
SqlTypes.INTEGER_ARRAY => NpgsqlDbType.Integer | NpgsqlDbType.Array,
SqlTypes.SMALLINT => NpgsqlDbType.Smallint,
SqlTypes.BIGINT => NpgsqlDbType.Bigint,
SqlTypes.VARCHAR => NpgsqlDbType.Text,
SqlTypes.REAL => NpgsqlDbType.Real,
SqlTypes.DOUBLE_PRECISION => NpgsqlDbType.Double,
SqlTypes.CHARACTER => NpgsqlDbType.Char,
SqlTypes.BLOB => NpgsqlDbType.Bytea,
_ => NpgsqlDbType.Bytea
};
return(ngpsqlType);
}
public override string GetTypeName(SqlTypes.SqlType sqlType)
{
switch (sqlType.DbType)
{
case System.Data.DbType.Binary:
case System.Data.DbType.Boolean:
case System.Data.DbType.DateTime:
case System.Data.DbType.Double:
case System.Data.DbType.Guid:
case System.Data.DbType.Int32:
case System.Data.DbType.Int64:
case System.Data.DbType.String:
return base.GetTypeName(sqlType);
}
throw new NotSupportedException("Azure table storage does not support the type: " + sqlType.DbType.ToString());
}
public static CSharpProperty ToCSharpProperty(SqlColumn sqlColumn)
{
var cSharpProperty = new CSharpProperty();
var propertyName = AutomaticPropertyNameRewrites.GetNameWithRewriting(sqlColumn.Name);
cSharpProperty.Name = propertyName;
cSharpProperty.ClrAccessModifier = ClrAccessModifier.Public;
cSharpProperty.IsNullable = sqlColumn.IsNullable;
cSharpProperty.DataType = SqlTypes.IsSystemType(sqlColumn.SqlDataType.TypeName)
? SqlToClrTypeConverter.GetClrTypeName(sqlColumn.SqlDataType.TypeName)
: sqlColumn.SqlDataType.TypeName;
cSharpProperty.DataAnnotationDefinitionBases.AddRange(DataAnnotationFactory.CreateDataAnnotations(sqlColumn));
return(cSharpProperty);
}
public SQLCommand(string query, DbConnection connection, Profiler profiler = null, params object[] arguments)
{
CommandProfiler = profiler;
Query = string.Format(query, arguments);
// All logic assumes MySQL is the "edge case", and the fallback is postgre.
if (connection is MySqlConnection)
{
Query = Query.Replace(":", "?").Replace("at time zone 'UTC'", "");
Query = cast_replace.Replace(Query, "$1");
SqlType = new SqlTypes(MySqlDbType.Int32, MySqlDbType.Text, MySqlDbType.Double, MySqlDbType.Bit, MySqlDbType.VarChar);
}
else
{
SqlType = new SqlTypes(NpgsqlDbType.Integer, NpgsqlDbType.Text, NpgsqlDbType.Double, NpgsqlDbType.Boolean, NpgsqlDbType.Varchar);
}
Command = DatabaseConnectionProvider.GetCommand(Query, connection);
}
private object sqlExecuter(object o, SqlTypes type)
{
openDBConnection();
if (objConn.State == ConnectionState.Closed)
{
objConn.Open();
}
SqlCommand cmd;
object returnVal = null;
switch (type)
{
case SqlTypes.DataReader:
cmd = (SqlCommand)o;
cmd.Connection = objConn;
reader = cmd.ExecuteReader();
break;
case SqlTypes.VoidQuery:
cmd = (SqlCommand)o;
cmd.Connection = objConn;
returnVal = cmd.ExecuteNonQuery();
objConn.Close();
break;
default:
object[] adapterAndDataSet = (object[])o;
SqlDataAdapter objAdapter = (SqlDataAdapter)adapterAndDataSet[0];
objAdapter.SelectCommand.Connection = objConn;
DataSet objDataSet = (DataSet)adapterAndDataSet[1];
objAdapter.Fill(objDataSet);
returnVal = objDataSet;
objConn.Close();
break;
}
return(returnVal);
}
public string ToStringObsolete()
{
if (SqlTypes.IsCharType(TypeName))
{
var charLength = MaximumCharLength.HasValue && MaximumCharLength.Value > 0
? MaximumCharLength.Value.ToString()
: "MAX";
return($"{TypeName}({charLength})");
}
if (SqlTypes.IsApproximateNumeric(TypeName))
{
return(TypeName + (Mantissa.HasValue ? $"({Mantissa.Value})" : string.Empty));
}
if (SqlTypes.IsExactNumeric(TypeName))
{
if (NumericPrecision.HasValue && NumericScale.HasValue)
{
return($"{TypeName}({NumericPrecision},{NumericScale})");
}
if (NumericPrecision.HasValue)
{
return($"{TypeName}({NumericPrecision})");
}
return(TypeName);
}
if (SqlTypes.IsDateTime(TypeName))
{
return(FractionalSecondsPrecision.HasValue && SqlTypes.IsDateTimeWithPrecision(TypeName)
? $"{TypeName}({FractionalSecondsPrecision})"
: TypeName);
}
return(TypeName);
throw new ArgumentException($"Conversion for the type {TypeName} is not defined.");
}
public static SqlType FromInformationSchemaColumn(SISColumn col)
{
if (SqlTypes.IsDateTime(col.DATA_TYPE))
{
return(SqlType.DateTime(col.DATA_TYPE, col.DATETIME_PRECISION));
}
if (SqlTypes.IsApproximateNumeric(col.DATA_TYPE))
{
if (col.DATA_TYPE == "float")
{
return(SqlTypeFactory.Float(col.NUMERIC_PRECISION.Value));
}
if (col.DATA_TYPE == "real")
{
return(SqlTypeFactory.Real());
}
}
if (SqlTypes.IsExactNumeric(col.DATA_TYPE))
{
return(SqlType.ExactNumericType(col.DATA_TYPE, col.NUMERIC_PRECISION, col.NUMERIC_PRECISION_RADIX));
}
if (SqlTypes.IsCharType(col.DATA_TYPE))
{
return(SqlType.TextType(col.DATA_TYPE, col.CHARACTER_MAXIMUM_LENGTH));
}
if (col.DATA_TYPE.Equals("uniqueidentifier", StringComparison.InvariantCultureIgnoreCase))
{
return(SqlType.Type(col.DATA_TYPE));
}
if (col.DATA_TYPE.Equals("varbinary", StringComparison.InvariantCultureIgnoreCase))
{
return(SqlType.Type(col.DATA_TYPE));
}
throw new NotImplementedException($"Unknown type {col.DATA_TYPE}");
}
private string getQueryDbChanger(string query, SqlTypes type)
{
try
{
string nQuery = query;
if (type == SqlTypes.INSERT | type == SqlTypes.INSERT_CUSTOM | type == SqlTypes.INSERT_RETURN_ID)
{
if (query.ToUpper().StartsWith("INSERT INTO "))
{
nQuery = query.Replace(query.Split(' ')[2], "[" + database + "].[dbo].[" + query.Split(' ')[2] + "]");
//while (nQuery.Contains("~" + query.Split(' ')[2]))
//nQuery = nQuery.Replace("~" + query.Split(' ')[2], "[" + database + "].[dbo].[" + query.Split(' ')[2] + "]");
}
}
else if (type == SqlTypes.SELECT)
{
if (query.ToUpper().StartsWith("SELECT "))
{
int fromStartIndex = query.ToUpper().IndexOf("FROM ");
nQuery = query.Insert(fromStartIndex + 5, "[" + database + "].[dbo].[");
int endOfTableName = -1;
for (int i = fromStartIndex + 5 + ("[" + database + "].[dbo].[").Length; i < nQuery.Length; i++)
{
if (nQuery.ToCharArray()[i] == ' ')
endOfTableName = i;
if (endOfTableName != -1)
i = nQuery.Length;
}
if (endOfTableName == -1)
nQuery = nQuery + "]";
else
{
nQuery = nQuery.Insert(endOfTableName, "]");
}
}
}
else if (type == SqlTypes.UPDATE)
{
if (query.ToUpper().StartsWith("UPDATE "))
{
nQuery = query.Replace(query.Split(' ')[1], "[" + database + "].[dbo].[" + query.Split(' ')[1] + "]");
}
}
return nQuery;
}
catch (Exception ee)
{
OnError(ee);
}
return query;
}
/// <summary>
/// Sets the data type for this <see cref="Column"/>.
/// </summary>
/// <param name="type">The data type.</param>
/// <param name="scale">The scale.</param>
/// <param name="precision">The precision.</param>
/// <returns>The column.</returns>
public Column Type(SqlTypes type, int scale, int precision)
{
DataType = type;
Scale = scale;
Precision = precision;
return this;
}
/// <summary>
/// Sets the data type for this <see cref="Column"/>.
/// </summary>
/// <param name="type">The data type.</param>
/// <param name="length">The length of the data type.</param>
/// <returns>The column.</returns>
public Column Type(SqlTypes type, int? length)
{
DataType = type;
Length = length;
return this;
}
/// <summary>
/// Sets the data type for this <see cref="Column"/>.
/// </summary>
/// <param name="type">The data type.</param>
/// <returns>The column.</returns>
public Column Type(SqlTypes type)
{
return Type(type, null);
}
public BeforeClauseCreateEventArgs(SqlTypes queryType, ClauseTypes clauseType, StringBuilder clause)
{
SqlType = queryType;
ClauseType = clauseType;
SqlClause = clause;
}
private bool IsStringType(SqlTypes.SqlType propertyType)
{
switch (propertyType.DbType)
{
case System.Data.DbType.AnsiString:
return true;
case System.Data.DbType.AnsiStringFixedLength:
return true;
case System.Data.DbType.String:
return true;
case System.Data.DbType.StringFixedLength:
return true;
default:
return false;
}
}
public AfterClauseCreateEventArgs(SqlTypes sqlType, StringBuilder clause)
{
SqlType = sqlType;
SqlClause = clause;
}
/// <summary> /// Builds the <see cref="String"/> that represents the specified type. /// </summary> /// <param name="type">The type.</param> /// <param name="length">The length.</param> /// <param name="scale">The scale.</param> /// <param name="precision">The precision.</param> /// <returns>The data type.</returns> protected abstract string BuildDataType(SqlTypes type, int? length, int? scale, int? precision);
public BeforeSqlCreateEventArgs(SqlTypes queryType, StringBuilder sql)
{
QueryType = queryType;
Sql = sql;
}
/*public object DLookup(string table, string field, DLookupData[] clauses)
{
object data = null;
try
{
}
catch (Exception ee)
{
OnError(ee);
}
return data;
}*/
public object[] executeSql(string query, SqlTypes type, SqlParameter[] parameters)
{
/*if (!Licencing.validate())
return null;*/
object[] returnObj = new object[5];
returnObj[1] = SqlHasReturn.NO;
//if (key.Equals(SecurityKey))
{
bool connectedP = isConnectedDo;
try
{
if (type == SqlTypes.AUTO)
{
if (query.ToUpper().StartsWith("INSERT"))
type = SqlTypes.INSERT;
else if (query.ToUpper().StartsWith("SELECT"))
type = SqlTypes.SELECT;
else if (query.ToUpper().StartsWith("UPDATE"))
type = SqlTypes.UPDATE;
else
throw new Exception("");
if (type == SqlTypes.INSERT & parameters != null)
type = SqlTypes.INSERT_CUSTOM;
}
if (dbType == DatabaseType.MSSQL)
query = getQueryDbChanger(query, type);
if (type == SqlTypes.STORED_PROCEDURE)
{
if (dbType == DatabaseType.MSSQL)
{
SqlCommand command = new SqlCommand();
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(parameters);
command.Connection = MSSQLConn;
command.CommandText = query;
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(command);
da.Fill(dt);
da.Dispose();
if (dt.Rows.Count > 0)
{
returnObj[1] = SqlHasReturn.YES;
returnObj[2] = Int32.Parse(dt.Rows[0].ItemArray[0].ToString());
}
}
}
else if (type == SqlTypes.STORE_PROCEDURE_VALUE)
{
if (dbType == DatabaseType.MSSQL)
{
Object returnValue;
SqlCommand command = new SqlCommand();
foreach (SqlParameter obj in parameters)
command.Parameters.Add(obj);
var returnParameter = command.Parameters.Add("@ReturnVal", SqlDbType.Int);
returnParameter.Direction = ParameterDirection.ReturnValue;
command.CommandType = CommandType.StoredProcedure;
command.Connection = MSSQLConn;
command.CommandText = query;
command.ExecuteNonQuery();
returnValue = returnParameter;
returnObj[1] = SqlHasReturn.YES;
returnObj[2] = returnValue;
}
}
else if (type == SqlTypes.STORED_PROCEDURE_DATATABLE)
{
if (dbType == DatabaseType.MSSQL)
{
SqlCommand command = new SqlCommand();
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(parameters);
command.Connection = MSSQLConn;
command.CommandText = query;
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(command);
da.Fill(dt);
da.Dispose();
returnObj[1] = SqlHasReturn.YES;
returnObj[2] = dt;
}
}
else if (type == SqlTypes.INSERT_RETURN_ID)
{
if (dbType == DatabaseType.MSSQL)
{
using (SqlDataReader Dr = new SqlCommand(query + ";SELECT CAST(SCOPE_IDENTITY() as int)", MSSQLConn).ExecuteReader())
{
if (Dr.Read())
{
returnObj[1] = SqlHasReturn.YES;
returnObj[2] = Int32.Parse(Dr[0].ToString());
}
}
}
}
else if (type == SqlTypes.INSERT)
{
if (dbType == DatabaseType.MSSQL)
{
using (SqlCommand commando = new SqlCommand(query, MSSQLConn))
{
int rws = commando.ExecuteNonQuery();
if (rws > 0)
returnObj[1] = SqlHasReturn.YES;
returnObj[2] = rws;
}
}
else if (dbType == DatabaseType.MSSQL)
{
using (MySqlCommand commando = new MySqlCommand(query, MySQLConn))
{
int rws = commando.ExecuteNonQuery();
if (rws > 0)
returnObj[1] = SqlHasReturn.YES;
returnObj[2] = rws;
}
}
else if (dbType == DatabaseType.MSACCESS2003 | dbType == DatabaseType.MSACCESS2007)
{
using (OleDbCommand commando = new OleDbCommand(query, MSDBSQLConn))
{
int rws = commando.ExecuteNonQuery();
if (rws > 0)
returnObj[1] = SqlHasReturn.YES;
returnObj[2] = rws;
}
}
}
else if (type == SqlTypes.SELECT)
{
if (dbType == DatabaseType.MSSQL)
{
SqlDataReader Dr = new SqlCommand(query, MSSQLConn).ExecuteReader();
returnObj[1] = SqlHasReturn.YES;
returnObj[2] = Dr;
}
else if (dbType == DatabaseType.MYSQL)
{
MySqlDataReader Dr = new MySqlCommand(query, MySQLConn).ExecuteReader();
returnObj[1] = SqlHasReturn.YES;
returnObj[2] = Dr;
}
else if (dbType == DatabaseType.MSACCESS2003 | dbType == DatabaseType.MSACCESS2007)
{
OleDbDataReader Dr = new OleDbCommand(query, MSDBSQLConn).ExecuteReader();
returnObj[1] = SqlHasReturn.YES;
returnObj[2] = Dr;
}
}
else if (type == SqlTypes.SELECT_DATATABLE)
{
if (dbType == DatabaseType.MSSQL)
{
SqlDataAdapter a = new SqlDataAdapter(query, MSSQLConn);
DataTable dt = new DataTable();
a.Fill(dt);
returnObj[1] = SqlHasReturn.YES;
returnObj[2] = dt;
a.Dispose();
}
else if (dbType == DatabaseType.MYSQL)
{
MySqlDataAdapter a = new MySqlDataAdapter(query, MySQLConn);
DataTable dt = new DataTable();
a.Fill(dt);
returnObj[1] = SqlHasReturn.YES;
returnObj[2] = dt;
a.Dispose();
}
else if (dbType == DatabaseType.MSACCESS2003 | dbType == DatabaseType.MSACCESS2007)
{
OleDbDataAdapter a = new OleDbDataAdapter(query, MSDBSQLConn);
DataTable dt = new DataTable();
a.Fill(dt);
returnObj[1] = SqlHasReturn.YES;
returnObj[2] = dt;
a.Dispose();
}
}
else if (type == SqlTypes.UPDATE)
{
if (dbType == DatabaseType.MSSQL)
{
SqlDataReader Dr = new SqlCommand(query, MSSQLConn).ExecuteReader();
returnObj[1] = SqlHasReturn.YES;
returnObj[2] = Dr;
}
else if (dbType == DatabaseType.MYSQL)
{
MySqlDataReader Dr = new MySqlCommand(query, MySQLConn).ExecuteReader();
returnObj[1] = SqlHasReturn.YES;
returnObj[2] = Dr;
}
else if (dbType == DatabaseType.MSACCESS2003 | dbType == DatabaseType.MSACCESS2007)
{
OleDbDataReader Dr = new OleDbCommand(query, MSDBSQLConn).ExecuteReader();
returnObj[1] = SqlHasReturn.YES;
returnObj[2] = Dr;
}
}
else if (type == SqlTypes.INSERT_CUSTOM & parameters != null)
{
if (dbType == DatabaseType.MSSQL)
{
using (SqlCommand commanda = new SqlCommand(query, MSSQLConn))
{
commanda.Parameters.AddRange(parameters);
int rws = commanda.ExecuteNonQuery();
if (rws > 0)
returnObj[1] = SqlHasReturn.YES;
returnObj[2] = rws;
}
}
else if (dbType == DatabaseType.MYSQL)
{
using (MySqlCommand commanda = new MySqlCommand(query, MySQLConn))
{
commanda.Parameters.AddRange(parameters);
int rws = commanda.ExecuteNonQuery();
if (rws > 0)
returnObj[1] = SqlHasReturn.YES;
returnObj[2] = rws;
}
}
else if (dbType == DatabaseType.MSACCESS2003 | dbType == DatabaseType.MSACCESS2007)
{
using (OleDbCommand commanda = new OleDbCommand(query, MSDBSQLConn))
{
commanda.Parameters.AddRange(parameters);
int rws = commanda.ExecuteNonQuery();
if (rws > 0)
returnObj[1] = SqlHasReturn.YES;
returnObj[2] = rws;
}
}
}
}
catch (Exception eee)
{
OnError(eee);
returnObj[1] = SqlHasReturn.NO;
returnObj[4] = eee;
}
finally
{
if (!connectedP & type != SqlTypes.SELECT)
closeConnection();
}
returnObj[0] = SqlResult.SUCCESS;
return returnObj;
}
/*else
return new object[] { SqlResult.FAIL, false };*/
}
/// <summary>
/// Adds a new <see cref="Column"/> with the specified <paramref name="name"/>
/// this this <see cref="Table"/>.
/// </summary>
/// <param name="name">The name.</param>
/// <param name="type">The data type.</param>
/// <param name="length">The data type's length.</param>
/// <returns>The column.</returns>
public Column AddColumn(string name, SqlTypes type, int length)
{
Column c = new Column(this, name, Modifier.Add).Type(type, length);
Columns.Add(c);
return c;
}
public SqlGenerator(SqlTypes sqlType, string baseTableName, bool distinct)
{
Distinct = distinct;
BuildSQLGen(sqlType, baseTableName, null, distinct);
}
/// <summary>
/// Adds a new <see cref="Column"/> with the specified <paramref name="name"/>
/// this this <see cref="Table"/>.
/// </summary>
/// <param name="name">The name.</param>
/// <param name="type">The data type.</param>
/// <param name="scale">The scale.</param>
/// <param name="precision">The precision.</param>
/// <returns>The column.</returns>
public Column AddColumn(string name, SqlTypes type, int scale, int precision)
{
Column c = new Column(this, name, Modifier.Add).Type(type, scale, precision);
Columns.Add(c);
return c;
}
/// <summary>
/// Builds the <see cref="String"/> that represents the specified type.
/// </summary>
/// <param name="type">The type.</param>
/// <param name="length">The length.</param>
/// <param name="scale">The scale.</param>
/// <param name="precision">The precision.</param>
/// <returns>The data type.</returns>
protected override string BuildDataType(SqlTypes type, int? length, int? scale, int? precision)
{
switch (type)
{
case SqlTypes.TinyInt:
return "TINYINT";
case SqlTypes.SmallInt:
return "SMALLINT";
case SqlTypes.Int:
return "INT";
case SqlTypes.BigInt:
return "BIGINT";
case SqlTypes.Single:
return "FLOAT";
case SqlTypes.Double:
return "DOUBLE";
case SqlTypes.Currency:
case SqlTypes.Decimal:
if (scale != null && precision != null)
return string.Format("DECIMAL({0}, {1})", scale, precision);
else
return "DECIMAL";
case SqlTypes.Boolean:
return "BIT";
case SqlTypes.Char:
case SqlTypes.NChar:
if (length != null)
return string.Format("CHAR({0})", length);
else
return "CHAR";
case SqlTypes.VarChar:
case SqlTypes.NVarChar:
if (length != null)
return string.Format("VARCHAR({0})", length);
else
return "VARCHAR";
case SqlTypes.VarCharMax:
case SqlTypes.NVarCharMax:
return "VARCHAR(MAX)";
case SqlTypes.Text:
case SqlTypes.NText:
return "TEXT";
case SqlTypes.Date:
return "DATE";
case SqlTypes.Time:
return "TIME";
case SqlTypes.DateTime:
return "DATETIME";
case SqlTypes.TimeStamp:
return "TIMESTAMP";
case SqlTypes.TimeSpan:
return "DATETIMEOFFSET";
case SqlTypes.Binary:
if (length != null)
return string.Format("BINARY({0})", length);
else
return "BINARY";
case SqlTypes.VarBinary:
if (length != null)
return string.Format("VARBINARY({0})", length);
else
return "VARBINARY";
case SqlTypes.VarBinaryMax:
return "VARBINARY(MAX)";
}
return null;
}
protected override void InitializeParameter(System.Data.IDbDataParameter dbParam, string name, SqlTypes.SqlType sqlType)
{
base.InitializeParameter(dbParam, name, sqlType);
}