private void CreateConnection()
{
sqlCM = new SqlProxyCommand(sqlCN);
AddParameters();
sqlCM.CommandText = CreateQuery();
sqlDA = new SqlProxyDataAdapter(sqlCM);
}
private static string GetConstraint(IColumn columnName)
{
const string command = "select object_name(cdefault) from syscolumns where [id] = object_id(@tn) and [name] like @cn ";
using (var cmd = new SqlProxyCommand(command, GlobalInfo.DBaseInfo.dbManager.DB_Connection))
{
var tn = new SqlProxyParameter("@tn", DbType.String, 64);
var cn = new SqlProxyParameter("@cn", DbType.String, 64);
cmd.Parameters.Add(tn);
cmd.Parameters.Add(cn);
tn.Value = "dbo." + columnName.Table;
cn.Value = columnName.Name;
var constraint = string.Empty;
try
{
var result = cmd.ExecuteScalar();
if (result != null)
{
constraint = result.ToString();
}
}
catch (Exception e)
{
System.Windows.Forms.MessageBox.Show(e.Message);
}
return(constraint);
}
}
private static bool WriteData(string tableName, DataSet dataSet)
{
var query = $"SELECT * FROM {tableName}";
using (var sqlCM = new SqlProxyCommand(query, GlobalInfo.DBaseInfo.dbManager.DB_Connection))
{
var adp = new SqlProxyDataAdapter(sqlCM);
var scb = new SqlProxyCommandBuilder(adp);
try
{
adp.InsertCommand = scb.GetInsertCommand();
var rows = adp.Update(dataSet, dataSet.Tables[0].TableName);
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine(e.StackTrace, e.Message);
MessageBox.Show(e.StackTrace, e.Message);
}
finally
{
}
}
return(true);
}
protected override string DefineBrowseQuery(SqlProxyCommand sqlCmd, string findQuery)
{
qb.Clear();
qb.SelectAllFrom <EF_Users>().
AddFilter(findQuery);
return(qb.Query);
}
/// <summary>
/// Add a slave table
/// </summary>
/// <param name="createCommand"></param>
/// <returns></returns>
public SqlProxyDataAdapter AddSlave <T>(bool createCommand = true)
{
System.Diagnostics.Debug.Assert(typeof(T).BaseType == typeof(Table));
var tableName = typeof(T).Tablename();
if (masterDataAdapterProperties == null || Dataset == null)
{
return(null);
}
var dCommand = new SqlProxyCommand(DBConnection);
var parameters = dCommand.Parameters;
CreateSlaveParam <T>(parameters);
var sqlQuery = CreateSlaveQuery <T>(parameters);
dCommand.CommandText = sqlQuery;
// AddOn management
if (sqlQuery == string.Empty && myDocument.AddonList != null)
{
foreach (Addon scr in myDocument.AddonList)
{
sqlQuery = scr.CreateSlaveQuery(tableName, parameters);
if (sqlQuery != string.Empty)
{
break;
}
}
}
//for (int t = 0; t < parameters.Count; t++)
// dCommand.Parameters.Add(parameters[t]);
// AddOn management
if ((parameters.Count == 0) && myDocument.AddonList != null)
{
foreach (Addon scr in myDocument.AddonList)
{
scr.CreateSlaveParam(tableName, parameters);
if (parameters.Count == 0)
{
continue;
}
//for (int t = 0; t < parameters.Count; t++)
// dCommand.Parameters.Add(parameters[t]);
break;
}
}
var dAdapter = CreateDataAdapter(tableName, dCommand);
masterDataAdapterProperties.AddSlave(tableName, dAdapter, dCommand);
dAdapter.RowUpdating += dAdapter_RowUpdating;
masterDataAdapterProperties.HasToCreateCommand = createCommand;
return(dAdapter);
}
public QueryBuilder(bool qualified = false, bool hasExecuter = false)
{
_qualified = qualified;
if (hasExecuter)
{
_hasExecuter = hasExecuter;
scc = new SqlProxyCommand(GlobalInfo.SqlConnection);
}
}
protected override bool DefineFindQuery(SqlProxyCommand sqlCmd)
{
qb.Clear();
sqlCmd.CommandText = qb
.SelectAllFrom <CustomerTable>()
.Where(CustomerTable.ID).IsEqualTo(rdrParameters["@p1"])
.Query;
return(true);
}
protected override bool DefineFindQuery(SqlProxyCommand sqlCmd)
{
qb.Clear();
qb.SelectAllFrom <EF_Codes>().
Where(EF_Codes.CodeType).IsEqualTo(rdrParameters["@p1"]);
sqlCmd.CommandText = qb.Query;
sqlCmd.Parameters.Add(rdrParameters["@p1"]);
return(true);
}
private static DataSet ReadData(string tableName)
{
var dataSet = new DataSet();
var query = $"SELECT * FROM {tableName}";
using (var sqlCM = new SqlProxyCommand(query, GlobalInfo.DBaseInfo.dbManager.DB_Connection))
{
var sqlDA = new SqlProxyDataAdapter(sqlCM);
sqlDA.Fill(dataSet, tableName);
}
return(dataSet);
}
protected override bool DefineFindQuery(SqlProxyCommand sqlCmd)
{
qb.Clear();
qb.SelectAllFrom <EF_Users>().
Where(EF_Users.Username).IsEqualTo(rdrParameters["@p1"]);
sqlCmd.CommandText = qb.Query;
for (int t = 0; t > rdrParameters.Count; t++)
{
sqlCmd.Parameters.Add(rdrParameters[t]);
}
return(true);
}
protected override bool DefineFindQuery(SqlProxyCommand sqlCmd)
{
qb.Clear();
qb.SelectAllFrom <EF_Counter>().
Where(EF_Counter.Year).IsEqualTo(rdrParameters["@p1"]).
And(EF_Counter.Type).IsEqualTo(rdrParameters["@p2"]);
sqlCmd.CommandText = qb.Query;
//for (int t = 0; t < rdrParameters.Count; t++)
// sqlCmd.Parameters.Add(rdrParameters[t]);
return(true);
}
private static bool ExecuteCommand()
{
try
{
using (SqlProxyCommand MyCommand = new SqlProxyCommand(sbuilder.ToString(), GlobalInfo.DBaseInfo.dbManager.DB_Connection))
{
MyCommand.ExecuteNonQuery();
}
return(true);
}
catch (System.Exception ex)
{
System.Diagnostics.Debug.Assert(false, ex.ToString(), sbuilder.ToString());
return(false);
}
}
private void CreateConnection()
{
rdrDataSet = new DataSet("GridDataSet");
rdrFindSqlCommand = new SqlProxyCommand()
{
Connection = rdrConnection
};
rdrBrowseSqlCommand = new SqlProxyCommand
{
Connection = rdrConnection
};
rdrParameters = new SqlProxyParameterCollection(rdrBrowseSqlCommand);
rdrBrowseSqlCommand.CommandText = DefineBrowseQuery(rdrBrowseSqlCommand, string.Empty);
rdrDataAdapter = new SqlProxyDataAdapter(rdrBrowseSqlCommand);
}
private void Requery()
{
var requery = new SqlProxyCommand()
{
Connection = rdrConnection
};
var strSelect = string.IsNullOrEmpty(FindQuery) ? string.Empty : FindQuery;
strSelect = DefineBrowseQuery(requery, strSelect);
PrepareBrowseQuery();
rdrDataSet.Clear();
rdrDataAdapter.SelectCommand = requery;
requery.CommandText = strSelect;
rdrDataAdapter.Fill(rdrDataSet, "Radar");
}
private SqlProxyDataAdapter CreateDataAdapter(string name, SqlProxyCommand sqlCommand)
{
SqlProxyDataAdapter dAdapter = null;
dAdapter = new SqlProxyDataAdapter(sqlCommand);
try
{
dAdapter.FillSchema(Dataset, SchemaType.Source);
dAdapter.Fill(Dataset, name);
SetDefaultValue(name);
}
catch (Exception exc)
{
MessageBox.Show(exc.Message);
}
return(dAdapter);
}
protected virtual SqlProxyCommand CreateMasterCommand()
{
//var parameters = new SqlProxyParameterCollection();
//CreateMasterParam(parameters);
//var dCommand = new SqlProxyCommand(CreateMasterQuery(parameters), DBConnection);
//if (parameters != null)
// dCommand.Parameters.AddRange(parameters);
//return (dCommand, parameters);
var dCommand = new SqlProxyCommand(DBConnection);
CreateMasterParam(dCommand.Parameters);
dCommand.CommandText = CreateMasterQuery(dCommand.Parameters);
return(dCommand);
}
private static void DeleteData(string tableName)
{
var query = $"DELETE FROM {tableName}";
using (var sqlCM = new SqlProxyCommand(query, GlobalInfo.DBaseInfo.dbManager.DB_Connection))
{
try
{
sqlCM.ExecuteNonQuery();
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine(e.StackTrace, e.Message);
MessageBox.Show(e.StackTrace, e.Message);
}
finally
{
}
}
}
private void FillComboBox()
{
QueryBuilder qB = new QueryBuilder().
SelectAllFrom(dC.TableType);
SqlProxyCommand fillCM = new SqlProxyCommand(qB.Query, sqlCN);
try
{
SqlProxyDataReader fillRD = fillCM.ExecuteReader();
while (fillRD.Read())
{
this.Items.Add(fillRD.GetString(dC.Name));
}
fillRD.Close();
}
catch (SqlException exc)
{
MessageBox.Show(exc.StackTrace, exc.Message);
}
}
abstract protected string DefineBrowseQuery(SqlProxyCommand sqlCmd, string findQuery);
static void Main(string[] args)
{
var result = GetDBType(typeof(String));
ProxyProviderLoader.UseProvider = ProviderType.SQL;
//var list = SqlProxyDatabaseHelper.ListDatabase("USR-ROGLEDIMAU1", "sa", "", true);
var sqlconnectiostring = new SqlProxyConnectionStringbuilder
{
DataSource = @"SALA\SQLEXPRESS",
//UserID = "sa",
InitialCatalog = "PLUMBER",
//Password = "******",
IntegratedSecurity = true
};
//var creadb = new SqlProxyDataBaseHelper
//{
// DataSource = @"C:\Users\Rogledi\Desktop\Clothes.db"
//};
//creadb.CreateDatabase();
//var creadb = new SqlProxyCreateDatabase
//{
// DataSource = "USR-ROGLEDIMAU1",
// UserID = "sa",
// InitialCatalog = "PROVADB",
//};
//creadb.CreateDatabase();
SqlProxyDatabaseHelper.Password = "******";
//var sqlconnectiostring = new SqlProxyConnectionStringbuilder
//{
// DataSource = @"USR-ROGLEDIMAU1",
// UserID = "sa",
// InitialCatalog = "NORTHWIND"
//};
//ProxyProviderLoader.UseProvider = ProviderType.SQLite;
//var sqlconnectiostring = new SqlProxyConnectionStringbuilder
//{
// DataSource = @"D:\Utenti\Mauro\Desktop\Clothes.db"
//};
using (var connection = new SqlProxyConnection(sqlconnectiostring.ConnectionString))
{
connection.Open();
{
// var transaction = connection.BeginTransaction();
var p1 = new SqlProxyParameter("@p1", DbType.String, 16)
{
Value = "ARDUINO"
};
//var p1 = new SqlProxyParameter("@p1", DbType.Int16)
//{
// Value = 1
//};
//var p1 = new SqlProxyParameter("@p1", DbType.String, 5)
//{
// Value = "ALFKI"
//};
var dataSet = new DataSet();
//using (var sqlCmd = new SqlProxyCommand("SELECT * FROM CUSTOMERS where CustomerID = @p1"))
//using (var sqlCmd = new SqlProxyCommand("SELECT * FROM CL_MASTER where ID = @p1", connection))
using (var sqlCmd = new SqlProxyCommand(connection))
{
var p2 = sqlCmd.Parameters.Add(p1);
sqlCmd.CommandText = "SELECT * FROM PL_MASTERS where Code = @p1";
var dAdapter = new SqlProxyDataAdapter()
{
SelectCommand = sqlCmd
};
var cBuilder = new SqlProxyCommandBuilder(dAdapter)
{
QuotePrefix = "[",
QuoteSuffix = "]",
ConflictOption = ConflictOption.CompareRowVersion
};
//dAdapter.UpdateCommand = cBuilder.GetUpdateCommand();
dAdapter.InsertCommand = cBuilder.GetInsertCommand();
//dAdapter.DeleteCommand = cBuilder.GetDeleteCommand();
//dAdapter.RowUpdating += DAdapter_RowUpdating;
dAdapter.Fill(dataSet, "tablename");
var e = new RowUpdatedEventArgs(dataSet.Tables[0].Rows[0], new SqlCommand(), StatementType.Batch, new DataTableMapping());
//sqlCmd.Transaction = transaction;
//var sqldatareader = sqlCmd.ExecuteReader();
//sqldatareader.Read();
//var l = sqldatareader[3];
//sqldatareader.Close();
}
//transaction.Commit();
}
connection.Close();
}
}
public void Dispose()
{
scc.Dispose();
scc = null;
}
abstract protected bool DefineFindQuery(SqlProxyCommand sqlCmd);