public override Midia InsertMidia(MidiaType midia_type, string nome_album)
{
Midia new_album = new CD(nome_album);
string sql = "INSERT INTO CD (nome_album) " +
"VALUES (@Nome_Album)";
SqlCommand cmd = new SqlCommand(sql, SqlDbConnection.getConnection());
cmd.Parameters.AddWithValue("@Nome_Album", nome_album);
cmd.CommandType = CommandType.Text;
SqlDbConnection.cn.Open();
try
{
int i = cmd.ExecuteNonQuery();
if (i > 0)
{
Console.WriteLine("Registro incluido com sucesso!");
}
}
catch (Exception ex)
{
Console.WriteLine("Erro: " + ex.ToString());
}
finally
{
SqlDbConnection.cn.Close();
}
return(new_album);
}
public override void DeleteMidia(int codigo)
{
string sql = "DELETE FROM CD WHERE ID=@Id";
SqlCommand cmd = new SqlCommand(sql, SqlDbConnection.getConnection());
cmd.Parameters.AddWithValue("@Id", codigo);
cmd.CommandType = CommandType.Text;
SqlDbConnection.cn.Open();
try
{
int i = cmd.ExecuteNonQuery();
if (i > 0)
{
Console.WriteLine("Registro incluido com sucesso!");
}
}
catch (Exception ex)
{
Console.WriteLine("Erro: " + ex.ToString());
}
finally
{
SqlDbConnection.cn.Close();
}
//throw new NotImplementedException();
}
public jailofficers(int id)
{
SqlDbConnection con = new SqlDbConnection();
con.Adaptor("select * from jailofficer where id=" + id.ToString());
DataTable dt = con.Fill();
this.id = dt.Rows[0].Field <string>("id");
this.username = dt.Rows[0].Field <String>("username");
this.password = dt.Rows[0].Field <String>("password");
this.position = dt.Rows[0].Field <String>("position");
this.firstname = dt.Rows[0].Field <String>("firstname");
this.lastname = dt.Rows[0].Field <String>("lastname");
this.middlename = dt.Rows[0].Field <String>("middlename");
this.prisoner = dt.Rows[0].Field <int>("prisoner");
this.visitor = dt.Rows[0].Field <int>("visitor");
this.casee = dt.Rows[0].Field <int>("casee");
this.cell = dt.Rows[0].Field <int>("cell");
this.escort = dt.Rows[0].Field <int>("escort");
this.prisoner_report = dt.Rows[0].Field <int>("prisoner_report");
this.visitor_report = dt.Rows[0].Field <int>("visitor_report");
this.case_report = dt.Rows[0].Field <int>("case_report");
this.cell_report = dt.Rows[0].Field <int>("cell_report");
this.hearing_report = dt.Rows[0].Field <int>("hearing_report");
this.bail_report = dt.Rows[0].Field <int>("bail_report");
this.jailofficer_report = dt.Rows[0].Field <int>("jailofficer_report");
}
/// <summary>
/// A public version of Arena's ExecuteSqlDataReader class.
/// </summary>
/// <param name="sqlData"></param>
/// <param name="storedProcedure"></param>
/// <param name="list"></param>
/// <returns></returns>
public static SqlDataReader ExecuteSqlDataReader(this SqlData sqlData, string storedProcedure, ArrayList list)
{
SqlDataReader reader;
SqlConnection dbConnection = new SqlDbConnection().GetDbConnection();
SqlCommand command = new SqlCommand(storedProcedure, dbConnection);
command.CommandTimeout = 360;
command.CommandType = CommandType.StoredProcedure;
for (int i = 0; i <= (list.Count - 1); i++)
{
command.Parameters.Add((SqlParameter)list[i]);
}
try
{
dbConnection.Open();
reader = command.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (SqlException exception)
{
throw exception;
}
return(reader);
}
//private void dbconnection()
//{
// for (int i = 0; i < 100; i++)
// {
// SqlDbConnection SQL = new SqlDbConnection();
// SQL.Procedurename = "readFromTbl";
// SQL.AddParameter("command", "getText");
// SQL.ExecuteObject();
// for (int j = 0; j < SQL.ResultRowAmount; j++)
// {
// textBox2.Text += i + " ";
// textBox1.Text += SQL.GetFieldByName(j, "text");
// }
// string ss = "Inverted indexes are the most fundamental and widely used data structures in information retrieval. For each unique word occurring in a document collection, the inverted index indexes which work naturally for strings as";
// }
//}
private static Dictionary <int, List <List <int> > > GetDocuments(List <String> input)
{
Dictionary <int, List <List <int> > > result = new Dictionary <int, List <List <int> > >();
SqlDbConnection SQL = new SqlDbConnection();
SQL.Procedurename = "readFromTbl";
SQL.AddParameter("command", "getDocuments");
SQL.AddParameter("firstWord", input[0]);
SQL.AddParameter("secondWord", input[1]);
SQL.AddParameter("thirdWord", input[2]);
SQL.AddParameter("fourthWord", input[3]);
SQL.AddParameter("fifthWord", input[4]);
SQL.ExecuteObject();
for (int j = 0; j < SQL.ResultRowAmount; j++)
{
List <List <int> > PositionsLists = new List <List <int> >();
PositionsLists.Add(ParsePositions(SQL.GetFieldByName(j, "FirstList")));
PositionsLists.Add(ParsePositions(SQL.GetFieldByName(j, "SecondList")));
PositionsLists.Add(ParsePositions(SQL.GetFieldByName(j, "ThirdList")));
PositionsLists.Add(ParsePositions(SQL.GetFieldByName(j, "FourthList")));
PositionsLists.Add(ParsePositions(SQL.GetFieldByName(j, "FifthList")));
result.Add(Convert.ToInt32(SQL.GetFieldByName(j, "DocumentId")), PositionsLists);
}
return(result);
}
public prisoner(int id)
{
SqlDbConnection con = new SqlDbConnection();
con.Adaptor("select * from prisoner where id=" + id.ToString());
DataTable dt = con.Fill();
this.id = dt.Rows[0].Field <int>("id");
this.firstname = dt.Rows[0].Field <String>("firstname");
this.lastname = dt.Rows[0].Field <String>("lastname");
this.middlename = dt.Rows[0].Field <String>("middlename");
this.address = dt.Rows[0].Field <String>("address");
this.age = dt.Rows[0].Field <String>("age");
this.gender = dt.Rows[0].Field <String>("gender");
this.birthdate = dt.Rows[0].Field <String>("birthdate");
this.height = dt.Rows[0].Field <String>("height");
this.weight = dt.Rows[0].Field <String>("weight");
this.citizenship = dt.Rows[0].Field <String>("citizenship");
this.religion = dt.Rows[0].Field <String>("religion");
this.datein = dt.Rows[0].Field <String>("datein");
this.civilstatus = dt.Rows[0].Field <String>("civilstatus");
this.jailstatus = dt.Rows[0].Field <String>("jailstatus");
this.person_id = dt.Rows[0].Field <String>("person_id");
this.face_id = dt.Rows[0].Field <String>("face_id");
this.searchdb = dt.Rows[0].Field <String>("searchdb");
}
public void Get_connectionstring_from_config2()
{
var sqlDbConnection = new SqlDbConnection();
var connectionString = sqlDbConnection.GetConnectionString();
Assert.IsTrue(sqlDbConnection.OpenConnection_Test(connectionString), "Failed to Open Database Connection");
}
public bool UpdateAttachmentData(PayerHospActionAttachment payerHospActionAttachment)
{
using (var dbConn = new SqlDbConnection())
{
using (var tran = dbConn.BeginTransaction())
{
var data = dbConn.GetTable <PayerHospActionAttachment>().Where(x => x.AttachName == payerHospActionAttachment.AttachName).ToList();
if (data.HasRecords())
{
data.ForEach(x =>
{
x.AttachPath = payerHospActionAttachment.AttachPath;
x.ModifiedOn = DateTime.Now;
x.ModifiedBy = -1;
dbConn.Update <PayerHospActionAttachment>(x);
});
}
else
{
Console.WriteLine("No DB records found for :" + payerHospActionAttachment.AttachName);
}
tran.Commit();
}
}
return(true);
}
public Warden(String username)
{
SqlDbConnection con = new SqlDbConnection();
cons.Open();
con.Adaptor("select * from warden where username='******'", "") + "'");
DataTable dt = con.Fill();
this.username = dt.Rows[0].Field<String>("username");
cons.Close();
}
private static void AddDocument(String name, String text)
{
SqlDbConnection SQL = new SqlDbConnection();
SQL.Procedurename = "readFromTbl";
SQL.AddParameter("command", "addDoc");
SQL.AddParameter("name", name);
SQL.AddParameter("text", text);
SQL.ExecuteObject();
}
private static void AddWords(Dictionary <String, String> words, String docName)
{
SqlDbConnection SQL = new SqlDbConnection();
var dt = ConvertToDataTable(words, docName);
SQL.Procedurename = "readFromTbl";
SQL.AddParameter("command", "addWordPos");
SQL.AddParameter("name", docName);
SQL.AddDict("go", dt);
}
public void Inline_Linq_Query_To_DB()
{
var connectionString = "Server=Tesseract;Database=LineTess;Uid=Hugh;Pwd=Pr0ct0r";
var sqlDbConnection = new SqlDbConnection();
var connection = sqlDbConnection.CreateConnection(connectionString);
// Act
// Assert
Assert.IsTrue(sqlDbConnection.OpenConnection_Test(connectionString), "Failed to Open Database Connection");
}
public override DataTable getData()
{
SqlDataAdapter ada = new SqlDataAdapter("select * from CD", SqlDbConnection.getOpenedConnection());
DataTable dt = new DataTable();
ada.Fill(dt);
SqlDbConnection.cn.Close();
return(dt);
}
public void OpenConnection_Test()
{
// Prepare
var connectionString = "Server=Tesseract;Database=LineTess;Uid=Hugh;Pwd=Pr0ct0r";
var sqlDbConnection = new SqlDbConnection();
var connection = sqlDbConnection.CreateConnection(connectionString);
// Act
// Assert
Assert.IsTrue(sqlDbConnection.OpenConnection_Test(connectionString), "Failed to Open Database Connection");
connection.Close();
}
private void Button_Click_2(object sender, RoutedEventArgs e)
{
visitors visitors = new visitors();
visitors.lastname = lastname.Text.ToString();
visitors.firstname = firstname.Text.ToString();
visitors.middlename = middlename.Text.ToString();
visitors.address = address0.Text.ToString() + " " + address1.Text.ToString();
visitors.gender = gender.Text.ToString();
visitors.visit = vp_id.Text.ToString();
visitors.relation = relation.Text.ToString();
if (lastname.Text.ToString() != "" && firstname.Text.ToString() != "" && middlename.Text.ToString() != "" && address0.Text.ToString() != "" && gender.Text.ToString() != "" && vp_id.Text.ToString() != "" && relation.Text.ToString() != "" && address1.Text.ToString() != "")
{
string query = "select * from visitor where lastname = '" + lastname.Text.ToString() + "'";
SqlCommand coms = new SqlCommand(query, con);
SqlDbConnection cons = new SqlDbConnection();
cons.Adaptor(query);
DataTable dt = cons.Fill();
if (dt.Rows.Count == 1)
{
con.Open();
SqlDataReader reads = coms.ExecuteReader();
while (reads.Read())
{
string fn = (reads["firstname"].ToString());
string ln = (reads["lastname"].ToString());
string mn = (reads["middlename"].ToString());
if (firstname.Text != fn && lastname.Text != ln && middlename.Text != mn)
{
visitors.add();
MessageBox.Show("You have Added a new Visitor!");
this.Close();
visitormain pm = new visitormain();
pm.Show();
loading l = new loading();
l.Show();
}
else
{
MessageBox.Show("THIS VISITOR IS ALREADY IN THE DATABASE");
}
}
reads.Close();
con.Close();
}
else
{
MessageBox.Show("INVALID INPUTS!");
}
}
}
public escortClass(int id)
{
SqlDbConnection con = new SqlDbConnection();
con.Adaptor("select * from escort where id=" + id.ToString());
DataTable dt = con.Fill();
this.id = dt.Rows[0].Field <int>("id");
this.escort = dt.Rows[0].Field <String>("escort");
this.firstname = dt.Rows[0].Field <String>("firstname");
this.lastname = dt.Rows[0].Field <String>("lastname");
this.position = dt.Rows[0].Field <String>("position");
}
public void OpenConnection_Test_2()
{
// Prepare
//var connectionString = @"Server=EST-SQL-SVR01;Database=BAMEsteemExportDb;User ID=Esteem\BAMEsteemExportAdmin;Password=4m18w0:0&X39;Integrated Security=SSPI;";
var connectionString = @"Server=EST-SQL-SVR01;Database=BAMEsteemExportDb;User Id=BamEsteemExportAPISQL1;Password='******';";
var sqlDbConnection = new SqlDbConnection();
var connection = sqlDbConnection.CreateConnection(connectionString);
// Act
// Assert
Assert.IsTrue(sqlDbConnection.OpenConnection_Test(connectionString), "Failed to Open Database Connection");
connection.Close();
}
public audittrail(int id)
{
SqlDbConnection con = new SqlDbConnection();
con.Adaptor("select * from audit where id=" + id.ToString());
DataTable dt = con.Fill();
this.id = dt.Rows[0].Field <int>("id");
this.users = dt.Rows[0].Field <String>("users");
this.activity = dt.Rows[0].Field <String>("activity");
this.dateOfActivity = dt.Rows[0].Field <String>("dateOfActivity");
this.timeOfActivity = dt.Rows[0].Field <String>("timeOfActivity");
}
public Warden(int id)
{
SqlDbConnection con = new SqlDbConnection();
con.Adaptor("select * from warden where id=" + id.ToString());
DataTable dt = con.Fill();
this.id = dt.Rows[0].Field<string>("id");
this.username = dt.Rows[0].Field<String>("username");
this.firstname = dt.Rows[0].Field<String>("firstname");
this.lastname = dt.Rows[0].Field<String>("lastname");
this.password = dt.Rows[0].Field<String>("password");
}
public cell(int id)
{
SqlDbConnection con = new SqlDbConnection();
con.Adaptor("select * from cell where id=" + id.ToString());
DataTable dt = con.Fill();
this.id = dt.Rows[0].Field <int>("id");
this.cellnum = dt.Rows[0].Field <String>("cellnum");
this.description = dt.Rows[0].Field <String>("description");
this.num_inmates = dt.Rows[0].Field <int>("num_inmates");
this.status = dt.Rows[0].Field <String>("status");
}
public void Get_connectionstring_from_config()
{
var tesseractConnectionString = "Tesseract_Test_ConnectionString";
var connectionStringSection = ConfigurationManager.ConnectionStrings[tesseractConnectionString];
Assert.IsNotNull(connectionStringSection, "App.Config ConnectionString settings section is null or empty");
var connectionString = connectionStringSection.ConnectionString;
Assert.IsNotNull(connectionString, "ConnectionString section " + tesseractConnectionString + "is null or empty");
var sqlDbConnection = new SqlDbConnection();
var connection = sqlDbConnection.CreateConnection(connectionString);
Assert.IsTrue(sqlDbConnection.OpenConnection_Test(connectionString), "Failed to Open Database Connection");
}
public hearing(int id)
{
SqlDbConnection con = new SqlDbConnection();
con.Adaptor("select * from hearing where id=" + id.ToString());
DataTable dt = con.Fill();
this.id = dt.Rows[0].Field <int>("id");
this.inmatenum = dt.Rows[0].Field <String>("inmatenum");
this.hearingdate = dt.Rows[0].Field <String>("hearingdate");
this.hearingtime = dt.Rows[0].Field <String>("hearingtime");
this.escort = dt.Rows[0].Field <String>("escort");
}
public transfer(int id)
{
SqlDbConnection con = new SqlDbConnection();
con.Adaptor("select * from celltransfer where id=" + id.ToString());
DataTable dt = con.Fill();
this.id = dt.Rows[0].Field <int>("id");
this.cell = dt.Rows[0].Field <String>("cell");
this.reason = dt.Rows[0].Field <String>("reason");
this.lastname = dt.Rows[0].Field <String>("lastname");
this.firstname = dt.Rows[0].Field <String>("firstname");
this.inmatenum = dt.Rows[0].Field <String>("inmatenum");
}
private void DataGrid_MouseDoubleClick(object sender, MouseButtonEventArgs e)
{
DataGrid gd = (DataGrid)sender;
DataRowView row_selected = gd.SelectedItem as DataRowView;
if (row_selected != null)
{
id.Text = row_selected["id"].ToString();
relation.Text = row_selected["relation"].ToString();
lastname.Text = row_selected["lastname"].ToString();
firstname.Text = row_selected["firstname"].ToString();
middlename.Text = row_selected["middlename"].ToString();
address0.Text = row_selected["address"].ToString();
gender.Text = row_selected["gender"].ToString();
relation.Text = row_selected["relation"].ToString();
string inmate = row_selected["inmate"].ToString();
string query = "select * from prisoner where id = '" + inmate + "'";
SqlCommand coms = new SqlCommand(query, con);
SqlDbConnection cons = new SqlDbConnection();
cons.Adaptor(query);
DataTable dt = cons.Fill();
if (dt.Rows.Count == 1)
{
con.Open();
SqlDataReader reads = coms.ExecuteReader();
while (reads.Read())
{
vp_firstname.Text = (reads["firstname"].ToString());
vp_lastname.Text = (reads["lastname"].ToString());
vp_middlename.Text = (reads["middlename"].ToString());
vp_id.Text = (reads["id"].ToString());
}
reads.Close();
con.Close();
edit_visitor.Visibility = Visibility.Visible;
datagrid_visitor.Visibility = Visibility.Hidden;
}
else
{
MessageBox.Show("please select an object");
}
}
}
public visitors(int id)
{
SqlDbConnection con = new SqlDbConnection();
con.Adaptor("select * from visitor where id=" + id.ToString());
DataTable dt = con.Fill();
this.id = dt.Rows[0].Field <int>("id");
this.relation = dt.Rows[0].Field <String>("relation");
this.firstname = dt.Rows[0].Field <String>("firstname");
this.lastname = dt.Rows[0].Field <String>("lastname");
this.middlename = dt.Rows[0].Field <String>("middlename");
this.address = dt.Rows[0].Field <String>("address");
this.gender = dt.Rows[0].Field <String>("gender");
this.visit = dt.Rows[0].Field <String>("visit");
}
public DataTable CheckListOfForeignKeyReferenceFromThisTable(string TableName)
{
string sqlQuery = @"SELECT
ccu.table_name AS SourceTable
,ccu.constraint_name AS SourceConstraint
,ccu.column_name AS SourceColumn
,kcu.table_name AS TargetTable
,kcu.column_name AS TargetColumn
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
ON ccu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
ON kcu.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME
WHERE kcu.table_name ='" + TableName + "' ORDER BY ccu.table_name";
DataTable dtAllForeignKey = new DataTable();
using (SqlConnection connection = new SqlDbConnection()._sqlConn)
{
connection.Open();
_dataSet = new DataSet();
try
{
_sqlCommand = new SqlCommand {
CommandText = sqlQuery
};
_sqlDataAdapter = new SqlDataAdapter(Convert.ToString(sqlQuery), connection);
DataSet ds = new DataSet();
_sqlDataAdapter.Fill(ds, TableName);
dtAllForeignKey = ds.Tables[0];
}
catch (Exception)
{
_sqlCommand.Dispose();
}
finally
{
connection.Close();
_sqlCommand.Dispose();
if (_sqlDataAdapter != null)
{
_sqlDataAdapter.Dispose();
}
}
return(dtAllForeignKey);
}
}
/// <summary>
/// Get a collection of the current web requests active for these topic areas. I forget why
/// I had to do this as a custom method, I think something about the way the data is returned
/// by the standard built-in methods.
/// </summary>
/// <returns>A collection of PromotionRequest objects.</returns>
private PromotionRequestCollection GetCurrentWebRequests()
{
SqlConnection conn = new SqlDbConnection().GetDbConnection();
SqlCommand command = new SqlCommand("cust_sp_get_promotion_web_requests", conn);
SqlDataReader reader = null;
PromotionRequestCollection prc = new PromotionRequestCollection();
ArrayList paramList = new ArrayList();
//
// Build up all the command parameters and options.
//
command.Parameters.Add(new SqlParameter("@TopicAreaIDs", (String.IsNullOrEmpty(TopicAreaList) ? "-1" : TopicAreaList)));
command.Parameters.Add(new SqlParameter("@AreaFilter", "both"));
command.Parameters.Add(new SqlParameter("@CampusID", -1));
command.Parameters.Add(new SqlParameter("@MaxItems", 1000));
command.Parameters.Add(new SqlParameter("@EventsOnly", false));
command.Parameters.Add(new SqlParameter("@DocumentTypeID", -1));
command.CommandText = "cust_sp_get_promotion_web_requests";
command.Connection = conn;
command.CommandType = CommandType.StoredProcedure;
try
{
//
// Run the SP and load in all the promotions.
//
conn.Open();
reader = command.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
prc.Add(new PromotionRequest((int)reader["promotion_request_id"]));
}
}
catch (System.Exception e)
{
throw e;
}
finally
{
if (reader != null)
{
reader.Close();
}
}
return(prc);
}
public caseregistration(int id)
{
SqlDbConnection con = new SqlDbConnection();
con.Adaptor("select * from cases where id=" + id.ToString());
DataTable dt = con.Fill();
this.id = dt.Rows[0].Field <int>("id");
this.inmatename = dt.Rows[0].Field <String>("inmatename");
this.inmatenum = dt.Rows[0].Field <String>("inmatenum");
this.casenum = dt.Rows[0].Field <String>("case number");
this.offense = dt.Rows[0].Field <String>("offense");
this.datefiled = dt.Rows[0].Field <String>("datefiled");
this.datesen = dt.Rows[0].Field <String>("date sentence");
this.senstatus = dt.Rows[0].Field <String>("sentence status");
this.senreceive = dt.Rows[0].Field <String>("sentence receive");
this.sendue = dt.Rows[0].Field <String>("sentence due");
}
public SCPartRepository(IQueryBuilder queryBuilder, string connectionString = null)
{
if (queryBuilder != null)
{
base.DateRange = queryBuilder.DateRange;
base.EndDate = queryBuilder.EndDate;
base.EndDateInclusive = queryBuilder.EndDateInclusive;
base.EndDateString = queryBuilder.EndDateString;
base.LastQueryString = queryBuilder.LastQueryString;
base.OrderBy = queryBuilder.OrderBy;
base.PageCount = queryBuilder.PageCount;
base.SkipCount = queryBuilder.SkipCount;
base.StartDate = queryBuilder.StartDate;
base.StartDateString = queryBuilder.StartDateString;
base.TimeRange = queryBuilder.TimeRange;
base.WhereExpression = queryBuilder.WhereExpression;
}
_sqlDbConnection = new SqlDbConnection();
}
public DataTable CheckPrimaryKeyOfTable(string TableName)
{
string sqlQuery = @"SELECT X.NAME AS INDEXNAME,
COL_NAME(IC.OBJECT_ID,IC.COLUMN_ID) AS COLUMNNAME
FROM SYS.INDEXES X
INNER JOIN SYS.INDEX_COLUMNS IC
ON X.OBJECT_ID = IC.OBJECT_ID
AND X.INDEX_ID = IC.INDEX_ID
WHERE X.IS_PRIMARY_KEY = 1
AND OBJECT_NAME(IC.OBJECT_ID)='" + TableName + "'";
DataTable dtAllPrimaryKey = new DataTable();
using (SqlConnection connection = new SqlDbConnection()._sqlConn)
{
connection.Open();
_dataSet = new DataSet();
try
{
_sqlCommand = new SqlCommand {
CommandText = sqlQuery
};
_sqlDataAdapter = new SqlDataAdapter(Convert.ToString(sqlQuery), connection);
DataSet ds = new DataSet();
_sqlDataAdapter.Fill(ds, TableName);
dtAllPrimaryKey = ds.Tables[0];
}
catch (Exception)
{
_sqlCommand.Dispose();
}
finally
{
connection.Close();
_sqlCommand.Dispose();
if (_sqlDataAdapter != null)
{
_sqlDataAdapter.Dispose();
}
}
return(dtAllPrimaryKey);
}
}
