public QueryBuilder Between(object val1, object val2)
{
if (val1 is SqlProxyParameter && val2 is SqlProxyParameter)
{
sb.AppendFormat("BETWEEN {0} AND {1} ", (val1 as SqlProxyParameter).ParameterName, (val2 as SqlProxyParameter).ParameterName);
if (_hasExecuter && scc != null)
{
scc.Parameters.Add((val1 as SqlProxyParameter));
scc.Parameters.Add((val2 as SqlProxyParameter));
}
}
else if (val1 is double || val1 is int)
{
sb.AppendFormat("BETWEEN {0} AND {1} ", val1, val2);
}
else if (val1 is Enum && val2 is Enum)
{
var nVal1 = (int)Convert.ChangeType(val1, typeof(int));
var nVal2 = (int)Convert.ChangeType(val1, typeof(int));
sb.AppendFormat("BETWEEN {0} AND {1} ", val1, val2);
}
else if (val1 is DateTime && val2 is DateTime)
{
var dVal1 = (DateTime)Convert.ChangeType(val1, typeof(DateTime));
var dVal2 = (DateTime)Convert.ChangeType(val2, typeof(DateTime));
sb.AppendFormat("BETWEEN {0} AND {1} ", $"{SqlProxyDatabaseHelper.ConvertDate(dVal1)}", $"{SqlProxyDatabaseHelper.ConvertDate(dVal2)}");
}
else
{
sb.AppendFormat("BETWEEN '{0}' AND '{1}' ", val1, val2);
}
return(this);
}
private async void ListDataBase()
{
cbbExistSQL.Items.Clear();
var serverList = await SqlProxyDatabaseHelper.ListDatabase(cbbServer.Text, txtUser.Text, txtPass.Text, cbbAuthentication.SelectedIndex == AuthenticationMode.Windows.Int());
cbbExistSQL.Items.AddRange(serverList.ToArray());
}
public QueryBuilder Is(string operators, object val, string qualified = "")
{
if (val is ISqlProviderParameter)
{
sb.AppendFormat("{0} {1} ", operators, (val as ISqlProviderParameter).ParameterName);
if (_hasExecuter && scc != null)
{
scc.Parameters.Add((val as ISqlProviderParameter));
}
}
else if (val is double || val is int)
{
sb.AppendFormat("{0} {1} ", operators, val);
}
else if (val is bool)
{
var bValue = (bool)Convert.ChangeType(val, typeof(bool));
var bVal = bValue ? 1 : 0;
sb.AppendFormat("{0} {1} ", operators, bVal);
}
else if (val is Enum)
{
var nVal = (int)Convert.ChangeType(val, typeof(int));
sb.AppendFormat("{0} {1} ", operators, nVal);
}
else if (val is DateTime)
{
var dVal = (DateTime)Convert.ChangeType(val, typeof(DateTime));
sb.AppendFormat("{0} {1} ", operators, $"{SqlProxyDatabaseHelper.ConvertDate(dVal)}");
}
else if (val is QueryBuilder)
{
var qb = val as QueryBuilder;
sb.AppendFormat("{0} ({1}) ", operators, qb.ToString());
}
else if (val is IColumn)
{
if (qualified.IsEmpty())
{
sb.AppendFormat("{0} {1} ", operators, DecodeColumn(val));
}
else
{
sb.AppendFormat("{0} [{1}].[{2}] ", operators, qualified, (val as IColumn).Name);
}
}
else
{
sb.AppendFormat("{0} '{1}' ", operators, val);
}
return(this);
}
protected override bool UpdateDBTables()
{
if (dbVersion < 2)
{
if (!SqlProxyDatabaseHelper.SearchColumn(CustomerTable.Email, GlobalInfo.SqlConnection))
{
SqlCreateTable.AlterTable <CustomerTable>();
SqlCreateTable.AddColumn(CustomerTable.Email);
SqlCreateTable.Alter();
}
}
return(true);
}
private string InConcat(string concat, object param)
{
if (param is SqlProxyParameter)
{
if (_hasExecuter && scc != null)
{
scc.Parameters.Add((param as SqlProxyParameter));
}
concat = concat.SeparConcat((param as SqlProxyParameter).ParameterName, ",");
}
else if (param is double || param is int)
{
concat = concat.SeparConcat(param.ToString(), ",");
}
else if (param is Enum)
{
var nVal = (int)Convert.ChangeType(param, typeof(int));
concat = concat.SeparConcat(nVal.ToString(), ",");
}
else if (param is DateTime)
{
var dVal = (DateTime)Convert.ChangeType(param, typeof(DateTime));
concat = concat.SeparConcat($"{SqlProxyDatabaseHelper.ConvertDate(dVal)}", ",");
}
else if (param is bool)
{
var bVal = ((bool)param) ? 1 : 0;
concat = concat.SeparConcat(bVal.ToString(), ",");
}
else if (param is Array)
{
foreach (object p in param as Array)
{
concat = InConcat(concat, p);
}
}
else
{
concat = concat.SeparConcat($"'{param.ToString()}'", ",");
}
return(concat);
}
/// <summary>
/// ConnectToDatabase.
/// Si connette ad un database esistente
/// </summary>
private bool ConnectToDatabase()
{
var connected = false;
var connectionString = GetConnectionString();
try
{
myConnection = new SqlProxyConnection(connectionString);
myConnection.Open();
connected = (myConnection.State == ConnectionState.Open);
// @@ try to remove changedatabase
//if (connected)
// myConnection.ChangeDatabase(lI.InitialCatalog);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.ToString(), GlobalInfo.LoginInfo.InitialCatalog, MessageBoxButtons.OK, MessageBoxIcon.Information);
return(false);
}
if (SqlProxyDatabaseHelper.SearchTable <EF_Version>(myConnection))
{
if (MessageBox.Show(Properties.Resources.Database_WrongType,
Properties.Resources.Attention,
MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.No)
{
connected = false;
return(false);
}
else
{
RegisterModule RsT = new ERPFramework.ModuleData.RegisterModule();
RsT.CreateTable(myConnection, GlobalInfo.UserInfo.userType);
AddAdminUser();
}
}
else
{
RegisterModule RsT = new ERPFramework.ModuleData.RegisterModule();
connected = RsT.CreateTable(myConnection, GlobalInfo.UserInfo.userType);
}
return(connected);
}
public QueryBuilder Values(params object[] values)
{
var concat = "";
foreach (object param in values)
{
if (param is SqlProxyParameter)
{
if (_hasExecuter && scc != null)
{
scc.Parameters.Add((param as SqlProxyParameter));
}
concat = concat.SeparConcat((param as SqlProxyParameter).ParameterName, ",");
}
else if (param is double || param is int)
{
concat = concat.SeparConcat(param.ToString(), ",");
}
else if (param is Enum)
{
var nVal = (int)Convert.ChangeType(param, typeof(int));
concat = concat.SeparConcat(nVal.ToString(), ",");
}
else if (param is DateTime)
{
var dVal = (DateTime)Convert.ChangeType(param, typeof(DateTime));
concat = concat.SeparConcat(SqlProxyDatabaseHelper.ConvertDate(dVal), ",");
}
else
{
concat = concat.SeparConcat($"'{param.ToString()}'", ",");
}
}
sb.AppendFormat("VALUES ( {0} ) ", concat);
return(this);
}
private string DecodeColumn(object column)
{
if (column is IColumn)
{
return((column as IColumn).ColumnName(_qualified, qualifiedMap));
}
else if (column is QueryBuilder)
{
return((column as QueryBuilder).ToString());
}
else if (column is string)
{
return(column as string);
}
else if (column is DateTime)
{
var dVal = (DateTime)Convert.ChangeType(column, typeof(DateTime));
return(SqlProxyDatabaseHelper.ConvertDate(dVal));
}
System.Diagnostics.Debug.Assert(false, nameof(DecodeColumn));
return("");
}
public void AddTable <T>(bool toExport = true)
{
System.Diagnostics.Debug.Assert(typeof(T).BaseType == typeof(Table));
var existVirtual = typeof(T).GetField("IsVirtual");
var isVirtual = existVirtual == null
? false
: bool.Parse(existVirtual.GetValue(null).ToString());
var table = new TableDefinition {
Module = Module(), Table = typeof(T), ToExport = toExport, IsVirtual = isVirtual
};
if (!GlobalInfo.Tables.ContainsKey(table.Table.Name))
{
GlobalInfo.Tables.Add(table.Table.Name, table);
AddTableType <T>();
}
if (!isVirtual && SqlProxyDatabaseHelper.SearchTable <T>(GlobalInfo.SqlConnection))
{
SqlCreateTable.CreateTable <T>();
}
}
/// <summary>
/// CreateNewDatabase.
/// Crea un nuovo database
/// </summary>
private bool CreateNewDatabase()
{
SqlProxyDatabaseHelper.DataSource = GlobalInfo.LoginInfo.DataSource;
SqlProxyDatabaseHelper.UserID = GlobalInfo.LoginInfo.UserID;
SqlProxyDatabaseHelper.Password = GlobalInfo.LoginInfo.Password;
SqlProxyDatabaseHelper.InitialCatalog = GlobalInfo.LoginInfo.InitialCatalog;
SqlProxyDatabaseHelper.IntegratedSecurity = GlobalInfo.LoginInfo.AuthenicationMode == AuthenticationMode.Windows;
SqlProxyDatabaseHelper.CreateDatabase();
try
{
var connectionString = GetConnectionString();
myConnection = new SqlProxyConnection(connectionString);
myConnection.Open();
if (myConnection.State == ConnectionState.Open)
{
MessageBox.Show(Properties.Resources.Database_Create,
GlobalInfo.LoginInfo.InitialCatalog,
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
//myConnection.ChangeDatabase(GlobalInfo.LoginInfo.InitialCatalog);
RegisterModule RsT = new ERPFramework.ModuleData.RegisterModule();
RsT.CreateTable(myConnection, GlobalInfo.UserInfo.userType);
AddAdminUser();
}
catch (System.Exception ex)
{
MessageBox.Show(ex.ToString(), GlobalInfo.LoginInfo.InitialCatalog, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
if (myConnection.State == ConnectionState.Open)
{
myConnection.Clone();
}
return(true);
}
private async void SearchSqlServer()
{
this.cbbServer.Items.Clear();
this.cbbServer.DropDownStyle = ComboBoxStyle.DropDown;
this.cbbServer.Text = "Search SqlServer";
Application.DoEvents();
wizard1.NextEnabled = false;
var serverList = await SqlProxyDatabaseHelper.GetServers();
cbbServer.Items.AddRange(serverList.ToArray());
if (this.cbbServer.Items.Count > 0)
{
this.cbbServer.SelectedIndex = 0;
this.cbbServer.DropDownStyle = ComboBoxStyle.DropDown;
wizard1.NextEnabled = true;
}
else
{
this.cbbServer.Text = "<No available SQL Servers>";
}
}
public string DayOfWeek(IColumn val)
{
System.Diagnostics.Debug.Assert(val.ColType == typeof(DateTime), "Column type must be DateTime type");
return(SqlProxyDatabaseHelper.DayOfWeek(DecodeColumn(val)));
}
