private void tsiDropTable_Click(object sender, EventArgs e)
{
var node = tvMain.SelectedNode;
if (node == null || node.Tag == null)
{
return;
}
var newNode = node.PrevVisibleNode;
if (node.Tag.GetType() == typeof(SqlDataTable))
{
var table = node.Tag as SqlDataTable;
if (table == null)
{
return;
}
if (MessageBox.Show("Are you sure to drop table '" + table.Name + "'?", "Confirm", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question) == DialogResult.Yes)
{
var sql = "DROP TABLE " + table.Name;
using (var con = new SqlDatabaseConnection(table.ConnectionString))
{
con.Open();
new SqlDatabaseCommand(sql, con).ExecuteNonQuery();
con.Close();
tvMain.Nodes.Remove(node);
tvMain.SelectedNode = newNode;
}
}
}
}
public ICollection <Usuario> Buscar(ICollection <string> cpfs)
{
try
{
List <Usuario> lstUsuarios = new List <Usuario>();
using (var cn = SqlDatabaseConnection.GetOpenConnectionPecReceptor())
{
foreach (string cpf in cpfs)
{
var result = cn.Query <Dominio.Entidades.Usuario>(new UsuarioSqlCommand().GetCommandSelectByCpf(cpf)).FirstOrDefault();
if (result == null)
{
lstUsuarios.Add(new Usuario()
{
CPF = cpf
});
}
else
{
lstUsuarios.Add(result);
}
}
}
return(lstUsuarios);
}
catch (Exception ex)
{
throw new Exception("Erro ao buscar usuário: " + ex.Message);
}
}
public ICollection <Usuario> Buscar(ICollection <string> idsLdap, bool todasPropriedades)
{
try
{
List <Usuario> lstUsuarios = new List <Usuario>();
using (var cn = SqlDatabaseConnection.GetOpenConnectionPecReceptor())
{
foreach (string idLdap in idsLdap)
{
var result = cn.Query <Usuario>(new UsuarioSqlCommand().GetCommandSelectByIdLdap(idLdap)).FirstOrDefault();
if (result == null)
{
lstUsuarios.Add(new Usuario()
{
IDLDAP = idLdap
});
}
else
{
lstUsuarios.Add(result);
}
}
}
return(lstUsuarios);
}
catch (Exception ex)
{
throw new Exception("Erro ao buscar usuário: " + ex.Message);
}
}
public ICollection <Usuario> Buscar(Usuario filtro, Op operador)
{
try
{
using (var cn = SqlDatabaseConnection.GetOpenConnectionPecReceptor())
{
var resultArvore = cn.Query <Arvore>(new ArvoreSqlCommand().GetCommandSelectByName(filtro.Arvore)).FirstOrDefault();
if (resultArvore != null)
{
filtro.IdArvore = resultArvore.Id;
}
else
{
filtro.IdArvore = null;
}
return(cn.Query <Usuario>(new UsuarioSqlCommand().GetCommandSelectFiltro(filtro, operador)).ToList());
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public static void DeleteDirectoryFilesFromDb(SqlDatabaseConnection conn, string tableName, string schemaName,
IImportExportFactory factory, RootPathObject rootPath, List <string> paths)
{
try
{
var tableNames = factory.MakeImportExportFile(conn, rootPath, tableName, schemaName)
.GetAllTableNames();
foreach (var table in tableNames)
{
var tableNameId = $"[{table.SchemaName}].[{table.TableName}]";
Logger.Info($"Purging table: {tableNameId}");
var cmd = new SqlDatabaseCommand
{
Connection = conn,
CommandText =
$@"DROP TABLE IF EXISTS {tableNameId}"
};
cmd.ExecuteNonQuery();
}
}
catch (Exception e)
{
Logger.Error(e, e.Message);
Logger.Error(e, "Skipping delete");
}
}
public AS400ImportExport(SqlDatabaseConnection sqlDatabaseConnection, RootPathObject rootPath,
string tableName, string schemaName)
{
_conn = sqlDatabaseConnection;
_tableName = tableName;
_schemaName = schemaName;
}
public static async Task DeleteRecordAsync(SqlDatabaseConnection conn, ReplicationTable table,
string primaryKeyValue)
{
await conn.OpenAsync();
//TODO: first find record line number and delete it from the replication file
var query = string.Format(DeleteRecordQuery,
Utility.Utility.GetSafeName(table.SchemaName),
Utility.Utility.GetSafeName(table.TableName),
Utility.Utility.GetSafeName(table.Columns.Find(c => c.PrimaryKey == true)
.ColumnName),
primaryKeyValue
);
var cmd = new SqlDatabaseCommand
{
Connection = conn,
CommandText = query
};
cmd.ExecuteNonQuery();
// check if table exists
await cmd.ExecuteNonQueryAsync();
await conn.CloseAsync();
}
public static void AddIndexesToTables(IImportExportFile importExportFile, SqlDatabaseConnection conn)
{
// get all tables
var schemaTables = importExportFile.GetAllTableNames();
foreach (var schemaTable in schemaTables)
{
// find index
var tableIndexes = Indexes?.Where(i => i.TableName.ToLower() == schemaTable.TableName.ToLower()).ToList();
if (tableIndexes == null || tableIndexes?.Count == 0)
{
return;
}
// add all found indexes
foreach (var index in tableIndexes)
{
// create index command
var indexId = $"{index.TableName}_{Indexes.FindIndex(i => i == index)}";
var querySb = new StringBuilder($"CREATE INDEX IF NOT EXISTS {indexId} ON {schemaTable.TableName} (");
// add the columns
foreach (var column in index.IndexColumns)
{
querySb.Append(
$"[{column}],");
}
querySb.Length--;
querySb.Append(");");
var query = querySb.ToString();
Logger.Debug($"Create index query: {query}");
var cmd = new SqlDatabaseCommand
{
Connection = conn,
CommandText = query
};
// execute create index command
cmd.ExecuteNonQuery();
// rebuild the index
query = $"ReIndex {indexId}";
cmd = new SqlDatabaseCommand
{
Connection = conn,
CommandText = query
};
cmd.ExecuteNonQuery();
Logger.Info($"Added index {indexId} to {index.TableName} for columns {JsonConvert.SerializeObject(index.IndexColumns)}");
}
}
}
private void SimpleTransaction_Click(object sender, EventArgs e)
{
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("schemaname=db;uri=file://@memory;"))
{
cnn.Open();
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.CommandText = "Create Table If not exists temptable(Id Integer, TextValue Text) ; ";
cmd.ExecuteNonQuery();
SqlDatabaseTransaction trans = cnn.BeginTransaction();
cmd.Transaction = trans;
try
{
for (int i = 0; i < 1000; i++)
{
cmd.CommandText = "INSERT INTO temptable VALUES (" + i + ", 'AValue" + i + "');";
cmd.ExecuteNonQuery();
}
} catch (SqlDatabaseException sqlex)
{
trans.Rollback();
Debug.WriteLine(sqlex.Message);
}
finally
{
trans.Commit();
}
cmd.CommandText = "SELECT COUNT(*) FROM temptable;";
Debug.WriteLine(cmd.ExecuteScalar());
}
}
}
static void EXECUDFExample()
{
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("schemaname=db;uri=file://" + ExampleDatabaseFile + "; "))
{
cnn.Open();
// Available from version 2.7
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand())
{
cmd.Connection = cnn;
// SELECT - ExecScalarUDF
// ExecScalarUDF is called once for each row for each call.
// Following will call the event handler twice as ExecScalarUDF is called twice.
DataTable dt = new DataTable();
cmd.CommandText = "SELECT *, ExecScalarUDF('Exec1', Phone) AS ANumber, ExecScalarUDF('Exec2', 1, 2, @Param1, FirstName ) AS ExampleText FROM Customers LIMIT 10;";
cmd.Parameters.AddWithValue("@Param1", 3);
SqlDatabaseDataReader dr = cmd.ExecuteReader();
for (int c = 0; c < dr.VisibleFieldCount; c++)
{
Console.Write(dr.GetName(c) + "\t");
}
Console.WriteLine(Environment.NewLine + "----------------------");
while (dr.Read())
{
for (int c = 0; c < dr.VisibleFieldCount; c++)
{
Console.Write(dr.GetValue(c) + "\t");
}
Console.WriteLine("");
}
;
}
}
}
public void GetColumns(SqlDatabaseConnection con)
{
var sql = "SYSCMD table_info('" + Name + "')";
using (var cmd = new SqlDatabaseCommand(sql, con))
{
using (var dt = new DataTable("Schema"))
{
using (var da = new SqlDatabaseDataAdapter(cmd))
{
try
{
da.Fill(dt);
foreach (DataRow row in dt.Rows)
{
var col = new SqlDataColumn
{
DefaultValue = row["DefaultValue"],
IsPKey = Convert.ToBoolean(row["IsPrimaryKey"]),
Name = Convert.ToString(row["name"]),
Nullable = !Convert.ToBoolean(row["NotNull"]),
Type = Convert.ToString(row["type"])
};
Columns.Add(col);
}
}
catch
{
}
}
}
}
}
public void AtualizarSenha(string idLdap, string senha, bool gerarLog)
{
using (var cn = SqlDatabaseConnection.GetOpenConnectionPecReceptor())
{
try
{
var usuario = cn.Query <Dominio.Entidades.Usuario>(new UsuarioSqlCommand().GetCommandSelectByIdLdap(idLdap)).FirstOrDefault();
if (usuario == null || string.IsNullOrEmpty(usuario.IDLDAP))
{
throw new Exception(string.Concat("Usuário não encontrado ao atualizar senha"));
}
cn.Query <string>(new UsuarioSqlCommand().GetCommandAtualizarSenha(usuario.IDLDAP, senha));
if (gerarLog)
{
GerarLog("Senha alterado", Convert.ToInt32(usuario.IDLDAP), usuario.Nome, usuario.RG, usuario.CPF);
}
}
catch (Exception ex)
{
throw new Exception(string.Concat("Erro ao atualizar senha: ", ex.Message));
}
}
}
static void MetaCollections()
{
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("uri=@memory"))
{
cnn.Open();
//Available Meta Data Collections in this connection.
// Let's use foreach loop to get all the meta collections
foreach (DataRow r in cnn.GetSchema("METADATACOLLECTIONS").Rows)
{
if (r["NumberOfRestrictions"].Equals(0))
{
Console.WriteLine();
Console.WriteLine("Starting... {0}", r["CollectionName"].ToString());
Console.WriteLine();
//Load another datatable CollectionDT with values
DataTable CollectionDT = cnn.GetSchema(r["CollectionName"].ToString());
foreach (DataColumn column in CollectionDT.Columns)
{
Console.Write(column.ColumnName + "\t");
}
Console.WriteLine(Environment.NewLine);
foreach (DataRow row in CollectionDT.Rows)
{
foreach (DataColumn column in CollectionDT.Columns)
{
Console.Write(row[column.ColumnName] + "\t");
}
Console.WriteLine();
}
}
}
}
}
/// <summary>
/// Creates a new sql connection
/// </summary>
/// <param name="dbFilePrefix"></param>
/// <param name="onDisk"></param>
/// <param name="dbFolder"></param>
/// <returns>An open sql connection</returns>
public static SqlDatabaseConnection GetSqlConnection(string dbFilePrefix, bool onDisk = false, string dbFolder = null)
{
if (_connection != null)
{
if ((_connection.State & ConnectionState.Open) != 0)
{
return(_connection);
}
}
if (string.IsNullOrWhiteSpace(dbFolder))
{
dbFolder = Constants.DbFolder;
}
Directory.CreateDirectory(dbFolder);
var connBuilder = new SqlDatabaseConnectionStringBuilder
{
DatabaseFileMode = DatabaseFileMode.OpenOrCreate,
DatabaseMode = DatabaseMode.ReadWrite,
SchemaName = Constants.SchemaName,
Uri = onDisk ? $"file://{Path.Join(dbFolder, $"{dbFilePrefix}_{Constants.DbFile}")}" : "file://@memory"
};
_connection = new SqlDatabaseConnection(connBuilder.ConnectionString);
_connection.Open();
return(_connection);
}
public void GetColumns(SqlDatabaseConnection con)
{
Columns = new List <SqlDataColumn>();
var sql = "SYSCMD Index_Info('" + Name + "')";
using (var cmd = new SqlDatabaseCommand(sql, con))
{
using (var dt = new DataTable("IndexSchema"))
{
using (var da = new SqlDatabaseDataAdapter(cmd))
{
da.Fill(dt);
foreach (DataRow row in dt.Rows)
{
var col = new SqlDataColumn
{
Name = Convert.ToString(row["ColumnName"])
};
Columns.Add(col);
}
}
}
}
}
public static async Task <bool> RecordExistsAsync(SqlDatabaseConnection conn, ReplicationTable table,
string primaryKeyValue)
{
await conn.OpenAsync();
var query = string.Format(RecordExistsQuery,
Utility.Utility.GetSafeName(table.SchemaName),
Utility.Utility.GetSafeName(table.TableName),
Utility.Utility.GetSafeName(table.Columns.Find(c => c.PrimaryKey == true).ColumnName),
primaryKeyValue
);
var cmd = new SqlDatabaseCommand
{
Connection = conn,
CommandText = query
};
// check if record exists
var reader = await cmd.ExecuteReaderAsync();
await reader.ReadAsync();
var count = (long)reader["c"];
await conn.CloseAsync();
return(count != 0);
}
public System.Collections.Generic.IEnumerable <Dominio.Entidades.OrgaoExpeditor> ObterTodos()
{
using (var cn = SqlDatabaseConnection.GetOpenConnectionGestaoCorp())
{
try
{
var result = cn.Query <Dominio.Entidades.OrgaoExpeditor>(new OrgaoExpeditorSqlCommand().GetCommandSelectAll());
var sortedList = result
.OrderBy(i => i.Nome == "Outros")
.ThenBy(i => i.Nome == "MRE")
.ThenBy(i => i.Nome == "MEX")
.ThenBy(i => i.Nome == "MD")
.ThenBy(i => i.Nome == "MAR")
.ThenBy(i => i.Nome == "MAE")
.ThenBy(i => i.Nome == "SSP")
.ToList();
return(sortedList);
}
catch (System.Exception ex)
{
throw new Exception(string.Concat("Erro ao obter orgão expeditor: ", ex.Message));
}
}
}
public BillToIDsMaintenance(SqlDatabaseConnection databaseConnection, string databaseName, string userName)
{
InitializeComponent();
databaseLabel.Text = "Database: " + databaseName;
presenter = new BMPresenter(databaseConnection, userName, this);
presenter.Initialize();
}
public Usuario LoginByName(string name, string password)
{
try
{
using (var cn = SqlDatabaseConnection.GetOpenConnectionPecReceptor())
{
Usuario usuario = cn.Query <Usuario>(new UsuarioSqlCommand().GetCommandSelectByNameWithPassword(name)).FirstOrDefault();
if (usuario == null)
{
throw new Exception("Usuário não encontrado");
}
if (!(string.Equals(usuario.Senha, password)))
{
throw new Exception("Ops! Nossas aplicações foram aperfeiçoadas e, em função dessa manutenção, sua senha foi redefinida automaticamente para o seu CPF (onze dígitos, sem pontos e sem hífen). Tente novamente ou, se preferir, <a href=\"http://www.rededosaber.sp.gov.br/RecuperarSenha/Acoes/LembrarSenha.aspx\" target=\"_blank\">clique aqui</a> aqui para cadastrar uma nova senha pessoal!.");
}
return(usuario);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
private void cbTable_SelectedValueChanged(object sender, EventArgs e)
{
listBox1.DataSource = null;
var table = (cbTable.SelectedItem as ISqlDataObject);
if (table == null)
{
return;
}
if (table.Columns.Count == 0)
{
using (var con = new SqlDatabaseConnection(table.ConnectionString)) {
con.Open();
table.GetColumns(con);
}
}
aviableColumns = new BindingList <SqlDataColumn>(table.Columns.ToList());
listBox1.DataSource = aviableColumns;
listBox1.DisplayMember = "Name";
selectedColumns.Clear();
listBox2.DataSource = selectedColumns;
listBox2.DisplayMember = "Name";
}
public static void RestoreTableFromBackup(DataTable backupTable, SqlDatabaseConnection connection, SqlDatabaseTransaction transaction)
{
var fields = new List <string>();
var parameters = new List <string>();
using (var cmd = new SqlDatabaseCommand(connection))
{
foreach (DataRow row in backupTable.Rows)
{
cmd.Parameters.Clear();
foreach (DataColumn col in row.Table.Columns)
{
var paramName = "@" + col.ColumnName;
fields.Add(col.ColumnName);
cmd.Parameters.Add(paramName, row[col]);
parameters.Add(paramName);
}
var insertSql = "INSERT INTO " + backupTable.TableName;
insertSql += "(" + string.Join(",", fields) + ")";
insertSql += "\nVALUES\n";
insertSql += "(" + string.Join(",", parameters) + ")";
cmd.CommandText = insertSql;
cmd.Transaction = transaction;
cmd.ExecuteNonQuery();
}
}
}
static void ImportExportCSV()
{
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("uri=file://" + ExampleDatabaseFile))
{
cnn.DatabaseFileMode = DatabaseFileMode.OpenIfExists;
cnn.Open();
if (File.Exists("Products.csv"))
{
File.Delete("Products.csv");
}
CSVFile.CsvImportExport importExport = new CSVFile.CsvImportExport(cnn, "Products", "");
//Export Example
int rowcount = importExport.ExportTable("Products.csv", false);
Console.WriteLine("Number of Rows Imported : {0}", rowcount);
//Import Example
importExport.TableName = "Products1";
rowcount = importExport.ImportTable("Products.csv", true);
Console.WriteLine("Number of Rows Exported : {0}", rowcount);
cnn.Close();
}
}
public FixedWidthColumnsImportExport(SqlDatabaseConnection sqlDatabaseConnection, RootPathObject rootPath,
string tableName, string schemaName)
{
_conn = sqlDatabaseConnection;
_rootPath = rootPath;
_tableName = tableName;
_schemaName = schemaName;
}
// GET: SongAssignment
public ActionResult Index()
{
SongAssignmentViewModel songAssignmentVM = new SongAssignmentViewModel();
songAssignmentVM.SongsDetails = SqlDatabaseConnection.getSongsDetails();
songAssignmentVM.Users = SqlDatabaseConnection.getStudents();
return(View(songAssignmentVM));
}
public void SetUp()
{
databaseConnection = new SqlDatabaseConnection(@"Initial Catalog=UnitedStationers;Data Source=(localdb)\MSSQLLocalDB;Integrated Security=true;");
userName = "******";
var moqViewer = new Mock <IBMViewer>();
moqViewer.Setup(view => view.Show(new BMViewModel()));
viewer = moqViewer.Object;
}
static void AddImage()
{
// Following example code inserts and retrive files/images from database
Console.WriteLine("Example: How to add images or files in database.");
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("SchemaName=db;Uri=@memory")) //We will strore in memory for the example
{
cnn.Open();
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.CommandText = "CREATE TABLE Images (Id Integer Primary Key, FileName Text, ActualImage BLOB);";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO Images VALUES(@Id, @FileName, @Image);";
cmd.Parameters.Add("@Id");
cmd.Parameters.Add("@FileName");
cmd.Parameters.Add("@Image");
//Read the file
if (File.Exists("ImageFile.png"))
{
byte[] byteArray = File.ReadAllBytes("ImageFile.png");
// Assign values to parameters.
cmd.Parameters["@Id"].Value = 1;
cmd.Parameters["@FileName"].Value = "ImageFile.png";
cmd.Parameters["@Image"].Value = byteArray;
cmd.ExecuteNonQuery(); // Execute insert query
Console.WriteLine("Image / File example read..");
cmd.CommandText = "SELECT FileName , ActualImage FROM Images WHERE Id = 1 LIMIT 1;";
SqlDatabaseDataReader dr = cmd.ExecuteReader();
while (dr.HasRows)
{
while (dr.Read())
{
// Create Random file name so we won't overwrite the original actual file.
string NewFileName = Path.GetRandomFileName() + dr["FileName"].ToString();
byte[] FileBytes = (byte[])dr.GetValue(dr.GetOrdinal("ActualImage"));
File.WriteAllBytes(NewFileName, FileBytes);
if (File.Exists(NewFileName))
{
Console.WriteLine("File {0} created successfully.", NewFileName);
}
else
{
Console.WriteLine("Unable to create file {0}.", NewFileName);
}
}
}
}
else
{
Console.WriteLine("File ImageFile.png not found.");
}
}
}
}
public static void Delete(SqlDatabaseConnection connection, DbSettingItem item)
{
const string sql = "DELETE FROM CONNECTIONS WHERE Id=@Id";
using (var cmd = new SqlDatabaseCommand(sql, connection))
{
cmd.Parameters.Add("@Id", item.Id);
cmd.ExecuteNonQuery();
}
}
public BMPresenter(SqlDatabaseConnection databaseConnection, string userName, IBMViewer view)
{
// TODO: Complete member initialization
this.repository = new BillToIDsRepository(databaseConnection);
this.userName = userName;
this.viewer = view;
this.vm = new BMViewModel {
InputBillToID = string.Empty
};
}
private void EnsureColumns()
{
if (sourceTable != null && (sourceTable.Columns == null || sourceTable.Columns.Count == 0))
{
using (var con = new SqlDatabaseConnection(sourceTable.ConnectionString))
{
try
{
con.Open();
sourceTable.GetColumns(con);
}
finally
{
con.Close();
}
}
}
if (this.table.Columns.Count == 0)
{
this.table.BeginInit();
// happens when there is no data in the table
foreach (var c in sourceTable.Columns)
{
var dt = SqlDataColumn.SqlTypeToType(c.Type);
var col = new DataColumn
{
AllowDBNull = c.Nullable,
AutoIncrement = c.AutoInc,
Caption = c.Name,
DataType = dt,
ColumnName = c.Name
// DefaultValue = c.DefaultValue
};
var defaultString = Convert.ToString(c.DefaultValue);
if (defaultString.StartsWith("'") && defaultString.EndsWith("'"))
{
defaultString = defaultString.Substring(1, defaultString.Length - 2);
}
if (!string.IsNullOrEmpty(defaultString))
{
var defVal = Convert.ChangeType(defaultString, dt);
col.DefaultValue = defVal;
}
table.Columns.Add(col);
}
this.table.AcceptChanges();
this.table.EndInit();
}
}
public ExcelImportExport(SqlDatabaseConnection sqlDatabaseConnection, RootPathObject rootPath,
string tableName, string schemaName)
{
_conn = sqlDatabaseConnection;
_rootPath = rootPath;
_tableName = tableName;
_schemaName = schemaName;
// required for parsing dos era excel files
System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
}
