public static void Main()
{
var connection = new SqlConnection(connectionString: "Server=(local); Database=Northwind; Integrated Security=true;");
connection.Open();
var command = new SqlCommand(cmdText: "select c.CategoryName, p.ProductName from Products p inner join Categories c on c.CategoryId = p.CategoryId;", connection: connection);
var categoriesAndProducts = new Dictionary<string, List<string>>();
var reader = command.ExecuteReader();
while (reader.Read())
{
var category = (string)reader["CategoryName"];
var product = (string)reader["ProductName"];
if(categoriesAndProducts.ContainsKey(category))
{
categoriesAndProducts[category].Add(product);
}
else
{
categoriesAndProducts.Add(category, new List<string>());
}
}
connection.Close();
foreach (var kvp in categoriesAndProducts)
{
Console.WriteLine(new string(c: '=', count: 20) + "\n" + kvp.Key + "\n" + new string(c: '=', count: 20));
foreach (var product in kvp.Value)
{
Console.WriteLine("-- " + product);
}
}
}
public void GetScheduledAppointments()
{
var con = new SqlConnection("Data Source=CSDB;Initial Catalog=SEI_Ninja;Persist Security Info=True;UID=sei_timemachine;PWD=z5t9l3x0");
string sql = @"SELECT e.eventID, e.eventName, e.eventLocation, et.eventDate, et.eventDuration, u.user_first_name + ' ' + u.user_last_name AS name
FROM [SEI_Ninja].[dbo].SCHEDULED_USERS su
JOIN [SEI_Ninja].[dbo].EVENT_TIMES et ON (su.eventTimeID = et.eventTimeID)
JOIN [SEI_TimeMachine2].[dbo].[USER] u ON (su.userID = u.user_id)
JOIN [SEI_Ninja].[dbo].EVENT e ON (et.eventID = e.eventID)
WHERE e.eventOwner = 'mgeary'
ORDER BY e.eventID";
using (var command = new SqlCommand(sql, con))
{
con.Open();
using (var reader = command.ExecuteReader())
{
var list = new List<ScheduledAppointment>();
while (reader.Read())
list.Add(new ScheduledAppointment
{
eventID = reader.GetInt32(0),
eventName = reader.GetString(1),
eventLocation = reader.GetString(2),
eventDate = reader.GetDateTime(3),
eventDuration = (float)reader.GetDouble(4),
eventUserName = reader.GetString(5)
});
allAppointments = list.ToArray();
}
}
}
static void Main(string[] args)
{
Console.Write("Enter some text to search for it in the Products: ");
var searchedProduct = Console.ReadLine();
SqlConnection dbCon = new SqlConnection("Server=localhost; " +
"Database=NORTHWND; Integrated Security=true");
dbCon.Open();
using (dbCon)
{
SqlCommand cmd = new SqlCommand(
"SELECT ProductName FROM Products " +
"WHERE CHARINDEX (@searchedProduct, ProductName)>0", dbCon);
cmd.Parameters.AddWithValue("@searchedProduct", searchedProduct);
SqlDataReader reader = cmd.ExecuteReader();
using (reader)
{
while (reader.Read())
{
string productName = (string)reader["ProductName"];
Console.WriteLine(productName);
}
}
}
}
private void ReadOrderData(string filterPrimkey, string connectionString, int rowIndex)
{
var queryString =
"SELECT numcode, charcode, nominal, name, value FROM dbo.values_history inner join dbo.description on dbo.description.id = dbo.values_history.id WHERE dbo.values_history.primkey ='" + filterPrimkey + "' ;";
try
{
using (var connection =
new SqlConnection(connectionString))
{
var command =
new SqlCommand(queryString, connection);
connection.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
WriteCurrency(reader, rowIndex);
}
reader.Close();
}
}
catch (Exception ex)
{
Log.Text = ex.Message;
}
}
private static ICollection<string> FindProductBy(string partOfName)
{
var names = new List<string>();
string connectionString = "Data Source=.;Initial Catalog=Northwind;Integrated Security=SSPI;";
var dbCon = new SqlConnection(connectionString);
dbCon.Open();
using (dbCon)
{
SqlCommand command = new SqlCommand(
"SELECT p.ProductName FROM Products p WHERE p.ProductName LIKE @Search", dbCon);
command.Parameters.AddWithValue("@Search", "%" + partOfName + "%");
SqlDataReader reader = command.ExecuteReader();
Console.WriteLine("\nProductNams that contains ({0}):\n", partOfName);
using (reader)
{
string categoryName = string.Empty;
string currentCategoryName = string.Empty;
while (reader.Read())
{
string productName = (string)reader["ProductName"];
names.Add(productName);
}
}
}
return names;
}
public RootObjectOut GetMessageByUser(UserIn jm)
{
RootObjectOut output = new RootObjectOut();
String jsonString = "";
try
{
String strConnection = ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString;
SqlConnection Connection = new SqlConnection(strConnection);
String strSQL = string.Format("SELECT message FROM messages WHERE msgTo = '{0}' AND [msgID] = (SELECT MAX(msgID) FROM messages WHERE msgTo='{1}')", jm.user.ToString(),jm.user.ToString());
SqlCommand Command = new SqlCommand(strSQL, Connection);
Connection.Open();
SqlDataReader Dr;
Dr = Command.ExecuteReader();
if (Dr.HasRows)
{
if (Dr.Read())
{
jsonString = Dr.GetValue(0).ToString();
}
}
Dr.Close();
Connection.Close();
}
catch (Exception ex)
{
output.errorMessage = ex.Message;
}
finally
{
}
JavaScriptSerializer ser = new JavaScriptSerializer();
output = ser.Deserialize<RootObjectOut>(jsonString);
return output;
}
private void DataPortal_Fetch(CategoryCriteria criteria)
{
bool cancel = false;
OnFetching(criteria, ref cancel);
if (cancel) return;
string commandText = String.Format("SELECT [CategoryId], [Name], [Descn] FROM [dbo].[Category] {0}", ADOHelper.BuildWhereStatement(criteria.StateBag));
using (var connection = new SqlConnection(ADOHelper.ConnectionString))
{
connection.Open();
using (var command = new SqlCommand(commandText, connection))
{
command.Parameters.AddRange(ADOHelper.SqlParameters(criteria.StateBag));
using(var reader = new SafeDataReader(command.ExecuteReader()))
{
if (reader.Read())
Map(reader);
else
throw new Exception(String.Format("The record was not found in 'dbo.Category' using the following criteria: {0}.", criteria));
}
}
}
OnFetched();
}
public List<Model.Beneficios> Select()
{
List<Model.Beneficios> lstBeneficios = new List<Model.Beneficios>();
SqlConnection conexao = new SqlConnection(strCon);
string sql = "Select * from Beneficios";
SqlCommand cmd = new SqlCommand(sql, conexao);
conexao.Open();
try
{
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
Model.Beneficios beneficios = new Model.Beneficios();
beneficios.id = Convert.ToInt32(reader["id"].ToString());
beneficios.descr = Convert.ToString(reader["descr"].ToString());
lstBeneficios.Add(beneficios);
}
}
catch
{
Console.WriteLine("Deu erro na Seleção de Beneficios...");
}
finally
{
conexao.Close();
}
return lstBeneficios;
}
public void mandarMensajeDeExito()
{
SqlCommand cmd = new SqlCommand("select * from THE_CVENGERS.COMPRA where COMPRA_ID = "+ idComp, Conexion.getConexion());
SqlDataReader rd = cmd.ExecuteReader();
rd.Read();
bool sw = bool.Parse(rd["COMPRA_FORMA_DE_PAGO"].ToString());
rd.Close();
float monto = 0f;
foreach (int it in checkedListBox1.CheckedIndices) { monto += float.Parse(((ItemsDevolucion)checkedListBox1.Items[it]).getPrecio()); }
String mens = "La devolucion se ha realizado con exito. El monto de $"+ monto.ToString();
if (!sw)
mens += " sera retribuido en efectivo.";
else
{
cmd.CommandText = "select THE_CVENGERS.tipoTarjetaCompra(" + idComp + ") 't'";
rd = cmd.ExecuteReader();
rd.Read();
String tipoT = rd["t"].ToString();
rd.Close();
cmd.CommandText = "select THE_CVENGERS.numeroTarjetaCompra(" + idComp + ") 'n'";
rd = cmd.ExecuteReader();
rd.Read();
String numT = rd["n"].ToString();
rd.Close();
mens += " sera retribuido a la tarjeta "+tipoT+" de numero "+numT;
}
MessageBox.Show(mens,"Información",MessageBoxButtons.OK);
}
private void Page_Load(object sender, System.EventArgs e)
{
string strSQLQuery;
SqlConnection adoSqlConn =new SqlConnection("data source=blr-ec-112844;initial catalog=master;password=sa;persist security info=True;user id=sa;");
SqlCommand sqlcmdCommand = new SqlCommand("SELECT count(*) FROM testlang WHERE userfname=N'" + Request.Form["txtFName"] + "' AND userlname=N'" + Request.Form["txtLName"] + "'", adoSqlConn);
SqlDataReader adosqlDataReader;
adoSqlConn.Open();
sqlcmdCommand.CommandText = "SELECT count(*) FROM testlang WHERE userfname=N'" + Request.Form["txtFName"] + "' AND userlname=N'" + Request.Form["txtLName"] + "'";
adosqlDataReader = sqlcmdCommand.ExecuteReader();
adosqlDataReader.Read();
if (adosqlDataReader.Read()==false)
{
adosqlDataReader.Close();
Response.Write("<B>Thank You for Registering</B>");
//Format the SQL Query to Insert Data.
strSQLQuery = "INSERT INTO testlang (userfname, userlname, userlangid,useraddress) VALUES(N'" + Request.Form["txtFName"] + "', N'" + Request.Form["txtLName"] + "','" + Request.QueryString["lang"] + "', N'" + Request.Form["txtAddress"] + "')";
//Insert the New Record.
sqlcmdCommand.CommandText = strSQLQuery;
sqlcmdCommand.ExecuteNonQuery();
}
else
Response.Write("<B>You have already registered</B>");
adosqlDataReader.Close();
//Format the SQL Query to Get Data.
strSQLQuery = "SELECT UID, userfname, userlname, userlangid,useraddress FROM testlang WHERE userlangid='" + Request.QueryString["lang"] + "'";
//Response.Write(strSQLQuery)
//Get All the records satisfying the LangID.
sqlcmdCommand.CommandText = strSQLQuery;
adosqlDataReader = sqlcmdCommand.ExecuteReader();
}
private void button2_Click(object sender, EventArgs e)
{
SqlCommand sqlCmd = new SqlCommand("select * from THE_CVENGERS.ROL where ROL_NOMBRE ='" + rol + "'", Conexion.getConexion());
SqlDataReader sqlReader = sqlCmd.ExecuteReader();
sqlReader.Read();
String idRol = sqlReader["ROL_ID"].ToString();
sqlReader.Close();
sqlCmd.CommandText = "select * from THE_CVENGERS.FUNCIONALIDAD where FUNC_NOMBRE ='" + listBox2.SelectedItem.ToString() + "'";
sqlReader = sqlCmd.ExecuteReader();
sqlReader.Read();
String idFuncion = sqlReader["FUNC_ID"].ToString();
sqlReader.Close();
//hacer el insert
sqlCmd.CommandText = "insert into THE_CVENGERS.FUNCIONXROL(FXR_ROL_ID,FXR_FUNC_ID) values (" + idRol + "," + idFuncion + ")";
try
{
sqlCmd.ExecuteNonQuery();
listBox1.Items.Add(listBox2.SelectedItem.ToString());
int x = listBox2.SelectedIndex;
listBox2.Items.RemoveAt(x);
button2.Enabled = false;
listBox2.Refresh();
listBox1.Refresh();
}
catch (Exception ex) { MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK); }
}
/// <summary>
/// Return the song with a given id
/// </summary>
/// <param name="songId">The id of the song to get</param>
/// <returns>The song with the given id, or null if no such movie exists</returns>
public Song GetSong(int songId) {
SqlCommand command = new SqlCommand("SELECT * FROM Song WHERE id =" + songId, connection);
SqlDataReader reader = command.ExecuteReader();
if (reader.Read()) {
string album = reader["album"].ToString();
reader.Close();
command.CommandText = "SELECT * FROM Files WHERE id =" + songId;
reader = command.ExecuteReader();
reader.Read();
Song song = new Song() {
Id = songId,
Album = album,
RentPrice = int.Parse(reader["rentPrice"].ToString()),
BuyPrice = int.Parse(reader["buyPrice"].ToString()),
Uri = reader["URI"].ToString(),
Title = reader["title"].ToString(),
Description = reader["description"].ToString(),
Year = short.Parse(reader["year"].ToString()),
CoverUri = reader["coverURI"].ToString(),
ViewCount = int.Parse(reader["viewCount"].ToString())
};
reader.Close();
return song;
}
reader.Close();
return null;
}
private void button1_Click(object sender, EventArgs e)
{
listBox1.Items.Clear();
comboBox1.Items.Clear();
String strcon = "Data Source=VINOTH;Integrated Security=SSPI;Initial Catalog=Dafesty";
SqlConnection cn = new SqlConnection(strcon);
SqlCommand cm = new SqlCommand();
cm.CommandText = "select movietitle from movies order by movietitle";
cm.Connection = cn;
cn.Open();
SqlDataReader rd = cm.ExecuteReader();
int count = 0;
while (rd.Read())
{
listBox1.Items.Add(rd[0].ToString());
count++;
}
label3.Text = count.ToString() + " Row(s) Selected";
cm.CommandText = "select distinct Rating from Movies ";
rd.Close();
rd = cm.ExecuteReader();
while(rd.Read())
comboBox1.Items.Add(rd[0]);
rd.Close();
cn.Close();
}
public static void getBook(int OrgID, ref string[] fields, ref int[] fieldNums, ref string[,] books)
{
int ObjID = 0;
int counter = 0;
SqlDataReader read;
SqlCommand cmd = new SqlCommand();
string e4Conn = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
SqlConnection conn = new SqlConnection(e4Conn);
conn.Open();
cmd.Connection = conn;
cmd.CommandText = "SELECT * FROM Objects WHERE OrgID = '" + OrgID + "' AND ObjName = 'Book'";
read = cmd.ExecuteReader();
if (read.HasRows)
{
read.Read();
try
{
ObjID = System.Convert.ToInt32(read["ObjID"]);
read.Close();
cmd.CommandText = "Select * FROM Fields WHERE ObjID = '" + ObjID + "'";
read = cmd.ExecuteReader();
if (read.HasRows)
{
while (read.Read())
{
fields[counter] = System.Convert.ToString(read["FieldName"]);
fieldNums[counter] = System.Convert.ToInt32(read["FieldNumber"]);
counter++;
}
read.Close();
cmd.CommandText = "SELECT * FROM Data WHERE ObjID = '" + ObjID + "'";
read = cmd.ExecuteReader();
if (read.HasRows)
{
counter = 0;
while (read.Read())
{
books[counter, 0] = System.Convert.ToString(read["Value00"]);
books[counter, 1] = System.Convert.ToString(read["Value01"]);
books[counter, 2] = System.Convert.ToString(read["Value02"]);
books[counter, 3] = System.Convert.ToString(read["Value03"]);
books[counter, 4] = System.Convert.ToString(read["Value04"]);
books[counter, 5] = System.Convert.ToString(read["Value05"]);
books[counter, 6] = System.Convert.ToString(read["Value06"]);
books[counter, 7] = System.Convert.ToString(read["Value07"]);
books[counter, 8] = System.Convert.ToString(read["Value08"]);
books[counter, 9] = System.Convert.ToString(read["Value09"]);
books[counter, 10] = System.Convert.ToString(read["Value10"]);
counter++;
}
}
}
}
catch (Exception ex)
{
}
}
}
/// <summary>
/// Return the movie with a given id
/// </summary>
/// <param name="movieId">The id of the movie to get</param>
/// <returns>The movie with the given id, or null if no such movie exists</returns>
public Movie GetMovie(int movieId) {
SqlCommand command = new SqlCommand("SELECT * FROM Movie WHERE id =" + movieId, connection);
SqlDataReader reader = command.ExecuteReader();
if (reader.Read()) {
reader.Close();
command.CommandText = "SELECT * FROM Files WHERE id =" + movieId;
reader = command.ExecuteReader();
reader.Read();
Movie mov = new Movie() {
Id = movieId,
RentPrice = int.Parse(reader["rentPrice"].ToString()),
BuyPrice = int.Parse(reader["buyPrice"].ToString()),
Uri = reader["URI"].ToString(),
Title = reader["title"].ToString(),
Description = reader["description"].ToString(),
Year = short.Parse(reader["year"].ToString()),
CoverUri = reader["coverURI"].ToString(),
ViewCount = int.Parse(reader["viewCount"].ToString())
};
reader.Close();
return mov;
}
reader.Close();
return null;
}
/// <summary>
/// весь процесс заполнения информации
/// </summary>
void BaseCreation()
{
using (SqlConnection con = new SqlConnection(App.GetConnectString()))
{
con.Open();
//Имя пользователя
SqlCommand cmd = new SqlCommand(string.Format("SELECT Name FROM Password WHERE ID={0}", App.curPnID), con);
TBlName.Text = cmd.ExecuteScalar().ToString();
//Всего очков
cmd = new SqlCommand(string.Format("SELECT SUM(Points) FROM AchieveInfo WHERE PersonID={0}", App.curPnID), con);
TBlPAll.Text = "Всего: "+cmd.ExecuteScalar().ToString();
//Очки месяц
cmd = new SqlCommand(string.Format("SELECT SUM(Points) FROM AchieveInfo WHERE PersonID={0} AND Date Between convert(varchar(6), getdate(), 112) + '01' and dateadd(day, -1, dateadd(month, 1, convert(varchar(6), getdate(), 112) + '01'))", App.curPnID), con);
TBlPMnt.Text = App.MonthName[DateTime.Now.Month]+": " + cmd.ExecuteScalar().ToString();
//Лучший/худший результат всего
cmd = new SqlCommand(string.Format("SELECT DISTINCT(p.Name) , Sum(Points) FROM AchieveInfo ac LEFT OUTER JOIN Theme p ON ac.ThemeID=p.ID WHERE PersonID={0} GROUP BY p.Name", App.curPnID), con);
TBlBSAll.Text = "Всего: "+FindMax(cmd.ExecuteReader());
TBlWSAll.Text = "Всего: "+FindMin(cmd.ExecuteReader());
//Лучший/худший результат - месяц
cmd = new SqlCommand(string.Format("SELECT DISTINCT(p.Name) , Sum(Points) FROM AchieveInfo ac LEFT OUTER JOIN Theme p ON ac.ThemeID=p.ID WHERE PersonID={0} AND Date Between convert(varchar(6), getdate(), 112) + '01' and dateadd(day, -1, dateadd(month, 1, convert(varchar(6), getdate(), 112) + '01')) GROUP BY p.Name", App.curPnID), con);
TBlBSMnt.Text = App.MonthName[DateTime.Now.Month] + ": " + FindMax(cmd.ExecuteReader());
TBlWSMnt.Text = App.MonthName[DateTime.Now.Month] + ": " + FindMin(cmd.ExecuteReader());
}
}
public static string[,] GetListChevaux(SqlConnection conn)
{
SqlCommand sql = new SqlCommand("select id, nom, description, emplacement, race, discipline, idusager from cheval ");
sql.Connection = conn;
conn.Open();
SqlDataReader sqlDR = sql.ExecuteReader();
int nombre = 0;
while (sqlDR.Read())
{
nombre++;
}
string[,] Tab = new string[nombre, 7];
sqlDR.Close();
if (nombre > 0)
{
SqlDataReader sqlDR2 = sql.ExecuteReader();
int cpt = 0;
while (sqlDR2.Read())
{
Tab[cpt, 0] = sqlDR2.GetInt32(0).ToString();
Tab[cpt, 1] = sqlDR2.GetString(1);
Tab[cpt, 2] = sqlDR2.GetString(2);
Tab[cpt, 3] = sqlDR2.GetString(3);
Tab[cpt, 4] = sqlDR2.GetString(4);
Tab[cpt, 5] = sqlDR2.GetString(5);
Tab[cpt, 6] = sqlDR2.GetInt32(6).ToString();
cpt++;
}
sqlDR2.Close();
}
conn.Close();
return Tab;
}
protected void Button1_Click(object sender, EventArgs e)
{
MultiView1.ActiveViewIndex = 0;
MultiView2.ActiveViewIndex = 0;
if (ViewState["getdata"] == null)
{
SqlConnection conn = new SqlConnection(constrUDB);
string query = "select * from usertable where uname='" + Session["name"].ToString() + "'";
SqlCommand cmd = new SqlCommand();
cmd.CommandText = query;
cmd.Connection = conn;
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
dr.Read();
lblname.Text = dr["fname"].ToString();
lblabout.Text = dr["about"].ToString();
lblun.Text = dr["uname"].ToString();
lblabtname.Text = (dr["fname"].ToString()).Split(' ')[0];
// Label1.Text = (dr["fname"].ToString()).Split(' ')[0];
ImageButton1.ImageUrl = dr["picurl"].ToString();
/*
Image1.ImageUrl = dr["b1imgurl"].ToString();
Image2.ImageUrl = dr["b2imgurl"].ToString();
Image3.ImageUrl = dr["b3imgurl"].ToString();
Image4.ImageUrl = dr["b4imgurl"].ToString();
Image5.ImageUrl = dr["b5imgurl"].ToString();
lblbookname.Text = dr["b1name"].ToString();
lblbookname0.Text = dr["b2name"].ToString();
lblbookname1.Text = dr["b3name"].ToString();
lblbookname2.Text = dr["b4name"].ToString();
lblbookname3.Text = dr["b5name"].ToString();
lblbabt.Text = dr["b1abt"].ToString();
lblbabt0.Text = dr["b2abt"].ToString();
lblbabt1.Text = dr["b3abt"].ToString();
lblbabt2.Text = dr["b4abt"].ToString();
lblbabt3.Text = dr["b5abt"].ToString();*/
dr.Close();
//make connection only if required
SqlConnection conn1 = new SqlConnection(constrBDB);
cmd.Connection = conn1;
cmd.CommandText = "select * from bookrecord where uname='" + Session["name"].ToString() + "'";
conn1.Open();
SqlDataReader dr1 = cmd.ExecuteReader();
GridView3.DataSource = dr1;
GridView3.DataBind();
conn1.Close();
}
else
{
lblname.Text = "NO RECORD FOUND!";
lblabout.Text = Session["name"].ToString();
}
conn.Close();
ViewState["getdata"] = "I Have Done It Already";
}
}
protected void Button1_Click(object sender, EventArgs e)
{
string status = "";
if (Button1.Text == "Submit Instant Request")
{
status = "Allocated";
}
if (Button1.Text == "Submit Request")
{
status = "Pending";
}
string insertstring = "INSERT INTO Request (ModuleID, RoundID, UserID, NoStudents, Status, NoRooms) VALUES('" + ListBoxModCodes.SelectedItem.Text + "'," + DropDownList3.SelectedValue + ",@UserID," + TextBoxStudents.Text + ",'" + status + "',1) ";
InsertNewBooking.InsertCommand = insertstring;
InsertNewBooking.Insert();
System.Data.SqlClient.SqlConnection MyConnection;
System.Data.SqlClient.SqlCommand MyCommand;
System.Data.SqlClient.SqlDataReader MyDataReader;
MyConnection = new System.Data.SqlClient.SqlConnection("Data Source=co-web-3.lboro.ac.uk;Initial Catalog=team17;User ID=team17;Password=g6g88fcv");
MyConnection.Open();
MyCommand = new System.Data.SqlClient.SqlCommand();
MyCommand.Connection = MyConnection;
MyCommand.CommandText = "SELECT RequestID FROM REQUEST";
MyDataReader = MyCommand.ExecuteReader();
string reqID = "";
while (MyDataReader.Read())
{
reqID = MyDataReader.GetSqlValue(0).ToString();
}
MyDataReader.Close();
MyDataReader = null;
MyCommand.CommandText = "INSERT INTO RequestRoom (RequestID, RoomID) VALUES('" + reqID + "', '" + Label2.Text + "')";
MyDataReader = MyCommand.ExecuteReader();
MyDataReader.Close();
MyDataReader = null;
for (int z = 0; z < CheckBoxList1.Items.Count; z++)
{
if (CheckBoxList1.Items[z].Selected == true)
{
for (int b = 0; b < CheckBoxList2.Items.Count; b++)
{
if (CheckBoxList2.Items[b].Selected == true)
{
MyCommand.CommandText = "INSERT INTO RequestTime (RequestID, Week, TimeID) VALUES(" + reqID + ", " + (z + 1) + ", " + b + ") ";
MyDataReader = MyCommand.ExecuteReader();
MyDataReader.Close();
MyDataReader = null;
}
}
}
}
MyCommand = null;
MyConnection.Close();
MyConnection = null;
Response.Redirect("~/Secure/booked.aspx?"+reqID);
}
protected void Page_Load(object sender, EventArgs e)
{
if ((Session["SCSLogin"] != null) & (Session["SCSDate"] != null))
{
if ((DateTime)Session["SCSDate"] <= DateTime.Now)
Response.Redirect("Default.aspx");
login = (string)Session["SCSLogin"];
expDate = ((DateTime)Session["SCSDate"]).ToString();
Response.Write("<div align=center> В системе как: " + login + "<BR> В системе до: " + expDate + "<BR></div>");
Session["SCSDate"] = DateTime.Now.AddMinutes(10);
Title = Title + " : " + login;
/*if (Session["SCSReportDate1Flag"] == null)
{
Session["SCSReportDate1Flag"] = "False";
}
if (Session["SCSReportDate2Flag"] == null)
{
Session["SCSReportDate2Flag"] = "False";
}/**/
string connectionstring = WebConfigurationManager.ConnectionStrings["SCSDataBase"].ConnectionString;
SqlConnection con = new SqlConnection(connectionstring);
try
{
con.Open();
SqlCommand cmd = new SqlCommand("", con);
cmd.CommandText = "exec getUserAccess @lg";
cmd.Parameters.Add("@lg", login);
int access = (int)cmd.ExecuteScalar();
if (access <= 1)
{
Response.Redirect("Cabinet.aspx");
}
cmd.CommandText = "exec ShowFormReport @dtst, @dten, @lg";
cmd.Parameters.Add("@dtst", (DateTime)Session["SCSReportDate1"]);
cmd.Parameters.Add("@dten", (DateTime)Session["SCSReportDate2"]);
SqlDataReader dr = cmd.ExecuteReader();
gvVals.DataSource = dr;
gvVals.DataBind();
dr.Close();
cmd.CommandText = "exec ShowFormReportOut @dtst, @dten, @lg";
dr = cmd.ExecuteReader();
gvOut.DataSource = dr;
gvOut.DataBind();
dr.Close();
}
catch (Exception err)
{
Response.Write(err.Message);
}
finally
{
con.Close();
}
}
else Response.Redirect("Default.aspx");
}
protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\database.mdf;Integrated Security=True;User Instance=True");
con.Open();
string sel = DropDownList1.SelectedValue.Trim();
SqlDataReader read;
string s = "select * from factdata where uname = '" + TextBox1.Text + "'";
SqlCommand cmd = new SqlCommand(s, con);
read = cmd.ExecuteReader();
string s1, s2;
Label1.Visible = false;
Label2.Visible = false;
if (read.Read())
{
// Label1.Text = read["uname"].ToString();
s1 = read["sq_que"].ToString();
s2 = read["sq_ans"].ToString();
// TextBox6.Text = read["city"].ToString();
// TextBox8.Text = read["pcode"].ToString();
// TextBox10.Text = read["country"].ToString();
if (s1 != DropDownList1.SelectedValue.Trim())
{
Label1.Text = "Incorrect Security Question!";
Label1.Visible = true;
con.Close();
read.Close();
return;
}
if (s2 != TextBox3.Text)
{
Label2.Text = "Incorrect Answer for selected security Question!";
Label2.Visible = true;
con.Close();
read.Close();
return;
}
read.Close();
if (s1 == DropDownList1.SelectedValue.Trim() && s2 == TextBox3.Text)
{
string str = "select password from factdata where uname = '" + TextBox1.Text + "'";
SqlCommand cmnd = new SqlCommand(s, con);
read = cmd.ExecuteReader();
if (read.Read())
{
Label3.Text = "Your Password is : ";
LabelP.Text = read["password"].ToString();
}
// Response.Redirect("~/SetPass.aspx",true);
}
}
}
public static string ExibeDadosVenda()
{
List<ItenVenda> itensDaVenda = new List<ItenVenda>();
using (SqlConnection sqlConn =
new SqlConnection(ConnString))
{
using (SqlCommand sqlCommand = new SqlCommand())
{
string dadosVenda;
sqlConn.Open();
sqlCommand.CommandText = "select * from vendas order by Id desc";
sqlCommand.Connection = sqlConn;
SqlDataReader dataReader;
dataReader = sqlCommand.ExecuteReader();
if (dataReader.Read())
{
string idVenda = dataReader[0].ToString();
float valorDaVenda = float.Parse(dataReader[1].ToString());
int codOperador = int.Parse(dataReader[2].ToString());
dataReader.Close();
sqlCommand.Parameters.AddWithValue("idVenda", int.Parse(idVenda));
sqlCommand.CommandText = "Select * from itemVenda where IdVenda = @idVenda";
SqlDataReader dr;
dr = sqlCommand.ExecuteReader();
while(dr.HasRows)
{
while (dr.Read())
{
Produto p = ProdutoRepositoryIPML.BuscaProduto(int.Parse(dr[4].ToString()));
ItenVenda item = new ItenVenda(p, int.Parse(dr[2].ToString()));
itensDaVenda.Add(item);
}
dr.NextResult();
}
dr.Close();
dadosVenda = "Código da Venda: " + idVenda;
dadosVenda += "\nVendedor: " + codOperador;
dadosVenda += "\n\nProdutos vendidos: \n";
foreach (ItenVenda item in itensDaVenda)
{
dadosVenda += "\nProduto: " + item.Produto.NomeProduto;
dadosVenda += "\nPreço Unitário: " + item.Produto.PrecoVenda;
dadosVenda += "\nQuantidade Comprada: " + item.Qtd+ "\n";
}
dadosVenda += "\n\n Valor total da Venda: " + valorDaVenda;
}
else
{
return "Nenhuma venda encontrada";
}
return dadosVenda;
}
}
}
public Rendeles(int pAsztalID, int pRendeles_id)
{
_AsztalId = pAsztalID;
setColumnModel();
setColumnModelSum();
_ScrollPos = 0;
fRENDELES_ID = pRendeles_id;
fDATUM = DateTime.Now;
fFIZETVE = false;
fASZTAL_ID = pAsztalID;
fKEDVEZMENY = 0;
SqlConnection sc = new SqlConnection(DEFS.ConSTR);
sc.Open();
if (pRendeles_id != -1)
{
// meglevő rendelés be kell tölteni az ID-ra a rendelést.
SqlCommand cmd = new SqlCommand();
cmd.Connection = sc;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT ASZTAL_ID, isnull(PARTNER_ID,-1) PARTNER_ID, DATUM, FIZETVE, isnull(KEDVEZMENY,0) as KEDVEZMENY FROM RENDELES_FEJ WHERE RENDELES_ID =" + pRendeles_id.ToString();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
fASZTAL_ID = (int)rdr["ASZTAL_ID"];
fPARTNER_ID = (int)rdr["PARTNER_ID"];
if (fPARTNER_ID > 0) { usedPartner = new Partner(fPARTNER_ID); }
fDATUM = (DateTime)rdr["DATUM"];
fFIZETVE = (1 == (int)rdr["FIZETVE"]);
fKEDVEZMENY = (double)rdr["KEDVEZMENY"];
}
rdr.Close();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT SOR_ID FROM RENDELES_SOR WHERE isnull(DELETED,0) = 0 AND isnull(FIZETVE,0) = 0 AND RENDELES_ID =" + pRendeles_id.ToString();
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
lRendelesSor.Add(new RendelesSor((int)rdr["SOR_ID"], new SqlConnection(DEFS.ConSTR), false));
}
rdr.Close();
}
sc.Close();
}
//初始化信息
protected void BindData()
{
try
{
int courseID = Int32.Parse(Request.QueryString["courseID"].ToString());
List<String> allTeachers = new List<string>();
string queryCourse = "select name, startdate, enddate from Course where id=" + courseID;
string queryTeachers = "select name from Teacher";
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnStringSTEduSys"].ConnectionString))
{
connection.Open();
SqlCommand command = new SqlCommand(queryCourse, connection);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
oldCourseName.Value = reader[0].ToString();
tbCourseName.Text = reader[0].ToString();
tbStartDate.Text = reader[1].ToString();
tbEndDate.Text = reader[2].ToString();
}
reader.Close();
command.CommandText = queryTeachers;
reader = command.ExecuteReader();
while (reader.Read())
{
allTeachers.Add(reader[0].ToString());
}
// call close when done reading.
reader.Close();
}
String temp = Request.QueryString["teachers"].ToString();
String[] teachers = temp.Trim("'".ToCharArray()).Split('/');
if (teachers.Length > 1)
{
tbTeacher2.Text = teachers[1];
}
tbTeacher1.Text = teachers[0];
for (int i = 0; i < allTeachers.Count; i++)
{
ddlTeacher1.Items.Add(allTeachers[i]);
ddlTeacher2.Items.Add(allTeachers[i]);
}
}
catch (Exception e)
{
Response.Redirect("./Course_Manage.aspx");
}
}
public void getItems(string orderID, int OrgID, ref int[] items, ref int[] itemTypes, ref string[] inStock)
{
SqlDataReader read;
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(e4Conn);
string itemList = "";
string itemTypeList = "";
string[] parseString;
string[] parseTypes;
conn.Open();
cmd.Connection = conn;
cmd.CommandText = "SELECT * FROM Data WHERE Value00 = '" + orderID + "' AND Name = 'Order' AND OrgID = '" + OrgID + "'";
read = cmd.ExecuteReader();
if (read.HasRows)
{
read.Read();
try
{
itemList = System.Convert.ToString(read["Value05"]);
itemTypeList = System.Convert.ToString(read["Value06"]);
parseString = itemList.Split(',');
parseTypes = itemTypeList.Split(',');
for (int y = 0; y < parseString.Length; y++)
{
items[y] = System.Convert.ToInt32(parseString[y]);
itemTypes[y] = System.Convert.ToInt32(parseTypes[y]);
read.Close();
cmd.CommandText = "SELECT * FROM Data WHERE OrgID = '" + OrgID + "' AND Value00 = '" + items[y] + "' AND ObjID = '" + itemTypes[y] + "'";
read = cmd.ExecuteReader();
if (read.HasRows)
{
read.Read();
if (System.Convert.ToInt32(read["Value06"]) > 0)
{
inStock[y] = "Yes";
}
else
{
inStock[y] = "No";
}
}
}
}
catch (Exception ex)
{
}
}
}
protected void MostrarSeleccao(Object obj, EventArgs e)
{
saida.Text = "";
ddlMunicipios.Items.Clear();
ddlMunicipios.Items.Insert(0, new ListItem("Seleccione...", "0"));
//if(dDL.SelectedIndex!=0)
//{
//ddlMunicipios.DataSource = municipios[dDL.SelectedIndex-1];
//ddlMunicipios.DataBind();
SqlDB Bd = new SqlDB("ConStr_DivAdmin");
string str = "SELECT NomeMunicipio from Municipios where [CodigoDistrito] = @distrito";
SqlCommand cmd = new SqlCommand(str, Bd.SqlConDB);
cmd.Parameters.AddWithValue("@distrito",dDL.SelectedItem.Value);
cmd.Connection = Bd.SqlConDB;
Bd.SqlConDB.Open();
SqlDataReader dR = cmd.ExecuteReader();
//efectuar o data binding
ddlMunicipios.DataSource = dR;
ddlMunicipios.DataTextField = "NomeMunicipio";
ddlMunicipios.DataValueField = "IdMunicipio";
ddlMunicipios.DataBind();
dR.Close();
Bd.SqlConDB.Close();
//}
}
public List<ChatMessageModel> GetMessagesLimit(int limit)
{
List<ChatMessageModel> msg = new List<ChatMessageModel>();
if (limit < 0) limit = 0;
using (SqlConnection conn = SqlConnectionService.GetConnection())
{
string query = String.Format("SELECT TOP {0} u.UserId, u.Sex, m.Message, m.Time, u.UserLogin FROM MessageLogs m JOIN Users u on u.UserId = m.UserId ORDER BY m.MessageId DESC ", limit);
using (SqlCommand cmd = new SqlCommand(query, conn))
{
conn.Open();
cmd.Parameters.AddWithValue("@Limit", limit);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
msg.Add(Helper.CreateChatMessage(reader["UserLogin"].ToString(), Convert.ToInt32(reader["UserId"]), reader["Message"].ToString(), Convert.ToInt32(reader["Sex"]), reader["Time"].ToString()));
}
conn.Close();
return msg;
}
}
}
protected void LinkButton1_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("Data Source=DIP\\SQLEXPRESS;Initial Catalog=UVPCE_DB;Integrated Security=True");
SqlCommand cmd = new SqlCommand("insert into [user](username,subject,comment,posteddate) values(@username,@subject,@comment,@postedate)", con);
cmd.Parameters.AddWithValue("@username", Textname.Text);
cmd.Parameters.AddWithValue("@subject", txtSubject.Text);
cmd.Parameters.AddWithValue("@comment", txtComment.Text);
cmd.Parameters.AddWithValue("@postedate", DateTime.Now);
con.Open();
//SqlCommand cmd = new SqlCommand("insert into (username,subject,comment,posteddate) values('"+Textname.Text+"','"+txtSubject.Text+"','"+txtComment+"')",con);
cmd.ExecuteNonQuery();
// con.Close();
SqlCommand cmd1 = new SqlCommand("select no from [user] where username='******' and subject='" + txtSubject.Text + "' and comment='" + txtSubject.Text + "'", con);
SqlDataReader dr = cmd1.ExecuteReader();
while (dr.Read())
{
Label1.Text = dr["no"].ToString();
}
Textname.Text = string.Empty;
txtSubject.Text = string.Empty;
txtComment.Text = string.Empty;
BindRepeaterData();
}
public building(int id)
{
try{
SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["mainconn"].ConnectionString);
conn.Open();
SqlCommand cmd = new SqlCommand("select * FROM building WHERE id = @id", conn);
cmd.Parameters.AddWithValue("id", id);
SqlDataReader dr = cmd.ExecuteReader();
dr.Read();
ID = new dataObject(id);
Name = new dataObject(dr["Name"].ToString());
Address1 = new dataObject(dr["Address1"].ToString());
Address2 = new dataObject(dr["Address2"].ToString());
Address3 = new dataObject(dr["Address3"].ToString());
City = new dataObject(dr["City"].ToString());
State = new dataObject(dr["State"].ToString());
ZipCode = new dataObject(dr["ZipCode"].ToString());
PhoneNumber = new dataObject(dr["PhoneNumber"].ToString());
PhoneNumberExtension = new dataObject(dr["PhoneNumberExtension"].ToString());
FaxNumber = new dataObject(dr["FaxNumber"].ToString());
MobilePhoneNumber = new dataObject(dr["MobilePhoneNumber"].ToString());
EmailAddress = new dataObject(dr["EmailAddress"].ToString());
Contact = new dataObject(dr["Contact"].ToString());
Active = new dataObject(Convert.ToBoolean(dr["Active"]));
Comment = new dataObject(dr["Comment"].ToString());
InputDate = new dataObject(Convert.ToDateTime(dr["InputDate"]));
InputEmploee = new dataObject(new employee(Convert.ToInt32(dr["InputEmployeeID"])));
}catch(Exception e){
throw e;
}
}
protected override void OnLoad(EventArgs e)
{
base.OnLoad(e);
if (!this.IsPostBack)
{
//efectuar o data binding
//dDL.DataSource = distritos;
//dDL.DataBind();
SqlDB Bd = new SqlDB("ConStr_DivAdmin");
string str = "SELECT NomeDistrito from Distritos";
SqlCommand cmd = new SqlCommand(str, Bd.SqlConDB);
Bd.SqlConDB.Open();
SqlDataReader dR = cmd.ExecuteReader();
//efectuar o data binding
dDL.DataSource = dR;
dDL.DataTextField = "NomeDistrito";
dDL.DataBind();
dR.Close();
Bd.SqlConDB.Close();
}
return;
}
public List <BO.AssetInventoryTracking.workorder> GetAllworkorder()
{
List <BO.AssetInventoryTracking.workorder> xworkorderList = new List <BO.AssetInventoryTracking.workorder>();
string query = "SELECT [workorderID],[date_created],[date_completed],[status],[inventoryID],[date_modified] FROM dbo.[workorder]";
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["db_AssetInventoryTracking"].ConnectionString)) {
using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query, conn)) {
conn.Open();
using (System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)) {
while (reader.Read())
{
BO.AssetInventoryTracking.workorder xworkorder = new BO.AssetInventoryTracking.workorder();
if (!object.ReferenceEquals(reader["workorderID"], DBNull.Value))
{
xworkorder.workorderID = int.Parse(reader["workorderID"].ToString());
}
if (!object.ReferenceEquals(reader["date_created"], DBNull.Value))
{
xworkorder.date_created = DateTime.Parse(reader["date_created"].ToString());
}
if (!object.ReferenceEquals(reader["date_completed"], DBNull.Value))
{
xworkorder.date_completed = DateTime.Parse(reader["date_completed"].ToString());
}
if (!object.ReferenceEquals(reader["status"], DBNull.Value))
{
xworkorder.status = reader["status"].ToString();
}
if (!object.ReferenceEquals(reader["inventoryID"], DBNull.Value))
{
xworkorder.inventoryID = int.Parse(reader["inventoryID"].ToString());
}
if (!object.ReferenceEquals(reader["date_modified"], DBNull.Value))
{
xworkorder.date_modified = DateTime.Parse(reader["date_modified"].ToString());
}
xworkorderList.Add(xworkorder);
}
}
}
}
return(xworkorderList);
}
public void fillDDLStates()
{
using (System.Data.SqlClient.SqlConnection Connection = new SqlConnection(ConfigurationManager.ConnectionStrings["connProd"].ConnectionString))
{
System.Data.SqlClient.SqlCommand Command = new System.Data.SqlClient.SqlCommand();
Command.CommandText = "dbo.GetAllStates";
Command.Connection = Connection;
Connection.Open();
SqlDataReader ClientNameData = Command.ExecuteReader();
if (ClientNameData.HasRows)
{
ddlStates.DataSource = ClientNameData;
ddlStates.DataTextField = "descr";
ddlStates.DataValueField = "stateID";
ddlStates.DataBind();
}
}
}
public SqlDataReader ExecuteQuery(string sql)
{
try
{
sqlConn.Close();
sqlConn.Open();
SqlCommand sqlCom = new System.Data.SqlClient.SqlCommand();
sqlCom.Connection = sqlConn;
sqlCom.CommandType = CommandType.Text;
sqlCom.CommandText = sql;
SqlDataReader reader = sqlCom.ExecuteReader();
sqlCom.Dispose();
return(reader);
}
catch (Exception ex)
{
}
return(null);
}
public static System.Data.SqlClient.SqlDataReader ExecuteReader(string connectionString, System.Data.CommandType cmdType, string cmdText, params System.Data.SqlClient.SqlParameter[] commandParameters)
{
System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand();
System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(connectionString);
System.Data.SqlClient.SqlDataReader result;
try
{
SqlHelper.PrepareCommand(sqlCommand, sqlConnection, null, cmdType, cmdText, commandParameters);
System.Data.SqlClient.SqlDataReader sqlDataReader = sqlCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
sqlCommand.Parameters.Clear();
result = sqlDataReader;
}
catch
{
sqlConnection.Close();
throw;
}
return(result);
}
public bool VerifyEOBStatus(string strUsername, string strSourceId, string strCarrier)
{
bool ReturnVar = false;
System.Data.SqlClient.SqlDataReader reader = null;
try
{
System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand("[usp_VerifyEOBStatus]", sql.SqlConnection);
sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
sqlCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Username", strUsername));
sqlCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@SourceId", strSourceId));
sqlCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Carrier", strCarrier));
reader = sqlCommand.ExecuteReader();
if (reader.Read())
{
if ((!object.ReferenceEquals(reader[0], System.DBNull.Value)))
{
if (reader[0].ToString() == "1")
{
ReturnVar = true;
}
}
}
}
catch (Exception ex)
{
if (!reader.IsClosed)
{
reader.Close();
}
dbError(ex.StackTrace, ex.Message);
}
if (!reader.IsClosed)
{
reader.Close();
}
return(ReturnVar);
}
public Boolean yaExisteUser(String uName)
{
string conec = @"Data Source=BOSTON-PC\SQL_SERVER_2008;Initial Catalog=Kanban;Integrated Security=True";
SqlConnection cone = new SqlConnection(conec);
cone.Open();
string query = "select * from USUARIOS where USUARIO= '" + uName + "'";
SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query, cone);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
cone.Close();
return(true);
}
cone.Close();
return(false);
}
//Populate the Subjects Gridview
protected void Populate_SubjectDetails_Gridview()
{
Panel_SubjectDetails.Controls.Clear();
PlaceHolder1.Visible = false;
Panel_SubjectDetails.Visible = true;
try
{
SqlCommand sqlCmd_GetSubj = new System.Data.SqlClient.SqlCommand("exec spSEC_PIVOT__RowEQ_Subject__Dynamic_Where", oConn);
DataTable dt_GetSubj = new DataTable();
SqlDataReader SqlReader_GetSubj = sqlCmd_GetSubj.ExecuteReader();
dt_GetSubj.Load(SqlReader_GetSubj);
//SqlDataAdapter sqlAdapter_GetSubj = new SqlDataAdapter(sqlCmd_GetSubj);
//sqlAdapter_GetSubj.Fill(dt_GetSubj);
int num_subjects = dt_GetSubj.Rows.Count;
//build the Gridview & columns
GridView SubjectDetails_gridview = new GridView();
SubjectDetails_gridview.ID = "SubjectDetails_gridview";
SubjectDetails_gridview.AutoGenerateColumns = true;
SubjectDetails_gridview.CssClass = "tblinputsmall_blue";
//SubjectDetails_gridview.RowDataBound += new GridViewRowEventHandler(SubjStatus_Totals_gridview_RowDataBound);
//Bind the data
SubjectDetails_gridview.DataSource = dt_GetSubj;
SubjectDetails_gridview.DataBind();
Panel_SubjectDetails.Controls.Add(SubjectDetails_gridview);
}
catch (SqlException oException)
{
//errorLink1.InnerHtml += oException.Message; //new with full study
foreach (SqlError oErr in oException.Errors)
{
tblmyinfo.Rows[0].Cells[0].InnerHtml += oErr.Message;
}
}
}
} //========== end of the mtd
public List <AccountCustomerModel> GetAccountCustomerInfo(string SearchValue)
{
SearchValue = SearchValue.Replace("FORWARDSLASHXTER", "/");
SearchValue = SearchValue.Replace("DOTXTER", ".");
List <AccountCustomerModel> accountcustomerInfo = new List <AccountCustomerModel>();
using (var con = new System.Data.SqlClient.SqlConnection(connectionString))
{
var cmd = new System.Data.SqlClient.SqlCommand("spp_acountnumbers_customernames", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
//cmd.Parameters.Add("@result", System.Data.SqlDbType.TinyInt).Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add(new SqlParameter
{
ParameterName = "searchvalue",
Value = SearchValue,
});
con.Open();
//cmd.ExecuteNonQuery();
//cmd2.ExecuteNonQuery();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
AccountCustomerModel pObj = new AccountCustomerModel();
// progressInfo.TotalCount = reader["teambankid"] != DBNull.Value ? int.Parse(reader["teambankid"].ToString()) : 0;
pObj.AccountNumber = reader["AcccountNumber"] != DBNull.Value ? reader["AcccountNumber"].ToString() : "";
pObj.CustomerName = reader["CustomerName"] != DBNull.Value ? reader["CustomerName"].ToString() : "";
//pObj.Status = reader["Status"] != DBNull.Value ? reader["Status"].ToString() : "";
//pObj.Status = pObj.Status.ToUpper().Trim();
accountcustomerInfo.Add(pObj);
}
con.Close();
}
return(accountcustomerInfo);
} //========== end of the mtd
public void RunProcedure(string Name)
{
string connnecstring;
connnecstring = System.Configuration.ConfigurationManager.ConnectionStrings["SQLConnstr"].ConnectionString;
System.Data.SqlClient.SqlConnection PubsConn = new System.Data.SqlClient.SqlConnection(connnecstring);
System.Data.SqlClient.SqlCommand CMDproc = new System.Data.SqlClient.SqlCommand();
System.Data.SqlClient.SqlDataReader reader;
CMDproc.CommandType = System.Data.CommandType.StoredProcedure;
CMDproc.CommandText = Name;
CMDproc.Connection = PubsConn;
PubsConn.Open();
reader = CMDproc.ExecuteReader();
PubsConn.Close();
CMDproc.Dispose();
reader.Close();
reader.Dispose();
PubsConn.Dispose();
}
/// <summary>
///
/// </summary>
public void Load(System.Data.SqlClient.SqlConnection connection)
{
using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand())
{
cmd.Connection = connection;
cmd.CommandText = "SELECT ParentGroupID, AccountType, AccountID FROM SecurityRelation WHERE ";
System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader();
if (true == reader.Read())
{
_parentGroupID = reader.GetString(0);
_accountType = reader.GetString(1);
_accountID = reader.GetString(2);
}
reader.Close();
}
}
/// <summary>
///
/// </summary>
public void Load(System.Data.SqlClient.SqlConnection connection)
{
using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand())
{
cmd.Connection = connection;
cmd.CommandText = "SELECT AccountID, SysModID, SysFuncID FROM SecurityUserAccount_FunctionRole WHERE ";
System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader();
if (true == reader.Read())
{
_accountID = reader.GetString(0);
_sysModID = reader.GetString(1);
_sysFuncID = reader.GetString(2);
}
reader.Close();
}
}
public bool tareaRepetida(string codigo)
{
comando = new System.Data.SqlClient.SqlCommand("select codigo from TareasGenericas where codigo=@codigo", conexion);
comando.Parameters.AddWithValue("@codigo", codigo);
SqlDataReader reader = comando.ExecuteReader();
bool rep;
if (reader.Read())
{
rep = reader["codigo"].ToString() == codigo;
}
else
{
rep = false;
}
reader.Close();
return(rep);
}
/// <summary>
/// Read all MAX dates including NULL, ordering by not required inspection operations, in pipe test result
/// </summary>
/// <param name="number"></param>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
/// <returns>
/// List of KeyValuePair contains: date of last inspection result and id of not required inspection operations
/// </returns>
public List <KeyValuePair <DateTime, Guid> > GetAllNotRequiredOperationResult()
{
CreateConnection();
List <KeyValuePair <DateTime, Guid> > inspectionOperationsResult = new List <KeyValuePair <DateTime, Guid> >();
try
{
using (SqlCommand command = new System.Data.SqlClient.SqlCommand())
{
connection.Open();
command.Connection = connection;
command.CommandText = String.Format(@"Select r.Date, t.id From PipeTest t
full join
(Select Max(PipeTestResult.Date) date,PipeTestResult.pipeTestId testId
From PipeTestResult PipeTestResult where PipeTestResult.status not in('{0}')
group by PipeTestResult.pipeTestId) r on r.testId=t.id where t.frequencyType ='{1}'
", PipeTestResultStatus.Scheduled.ToString(), InspectionFrequencyType.U.ToString());
SqlDataReader dr = command.ExecuteReader();
while (dr.Read())
{
inspectionOperationsResult.Add(new KeyValuePair <DateTime, Guid>(
dr[0] == System.DBNull.Value ? (DateTime)(new DateTime(1950, 6, 10, 15, 24, 16)) : (DateTime)dr[0],
(Guid)dr[1]
));
}
}
}
catch (SqlException ex)
{
throw new RepositoryException("Get all not required operation", ex);
}
finally
{
if (connection.State == System.Data.ConnectionState.Open)
{
connection.Close();
}
}
return(inspectionOperationsResult);
}
protected void Page_Load(object sender, EventArgs e)
{
//index = lstRewardsView.SelectedIndex;
//lstRewardsView.SelectedIndex = index;
if (Session["employeeLoggedIn"] == null)
{
Response.Redirect("Login.aspx"); //check that the filepath is correct
}
if (Session["employeeLoggedIn"].ToString() != "True")
{
Response.Redirect("Login.aspx"); //check that the filepath works
}
Employee user = (Employee)Session["user"];
////loop for it to only load this on the first time the page loads
//ispostback
//for (int i = 0; i < 1; i++)
//{
try
{
SqlConnection conn = ProjectDB.connectToDB();
System.Data.SqlClient.SqlCommand insert = new System.Data.SqlClient.SqlCommand();
insert.Connection = conn;
insert.CommandText = "select concat([RewardID],' ',[Name],' ',[Description], ' ',[Price],' ',[StartDate]) AS search_RewardItems from [dbo].[Reward]";
lstRewardsView.DataSource = insert.ExecuteReader();
lstRewardsView.DataTextField = "search_RewardItems";
lstRewardsView.DataBind();
conn.Close();
}
//Shows an error message if there is a problem connecting to the database
catch (Exception)
{
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('ERROR')", true);
}
//}
}
protected void Page_Load(object sender, System.EventArgs e)
{
btnBorrar.Enabled = false;
btnBorrar.Visible = false;
if (Session["loggeado"] == null)
{
Response.Redirect(System.Configuration.ConfigurationManager.AppSettings["urlRoot"].ToString() + "/ingreso.aspx", true);
}
if (Session["administrador"].ToString() == "False" && Session["escritura"].ToString() == "False")
{
Response.Write("<h2>No tiene suficientes permisos para ver esta página.</h2>");
Response.End();
}
if ((Session["editar_formato_id"] != null) && !IsPostBack)
{
btnBorrar.Enabled = true;
btnBorrar.Visible = true;
string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["BaseSqlServer"].ConnectionString;
using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString))
{
string strQuery = "SELECT html, nombre FROM matricula.mailxls_formato WHERE id = " + Session["editar_formato_id"].ToString() + ";";
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(strQuery, connection);
connection.Open();
System.Data.SqlClient.SqlDataReader dr = cmd.ExecuteReader();
dr.Read();
html = dr[0].ToString();
if (html.Contains("\\'"))
{
html = html.Replace("\\'", "'");
}
if (html.Contains("\\\""))
{
html = html.Replace("\\\"", "\"");
}
elm1.Text = Server.HtmlDecode(html);
TextBox1.Text = dr[1].ToString();
connection.Close();
}
}
}
protected void btnLogin_Click(object sender, EventArgs e)
{
//if (System.Web.Security.FormsAuthentication.Authenticate(txtUsername.Text, txtPassword.Text))
//{
// System.Web.Security.FormsAuthentication.RedirectFromLoginPage(txtUsername.Text, false);
//}
//else
//{
// lblError.Text = "Your username or password was incorrect.";
//}
System.Data.SqlClient.SqlConnection conn =
new System.Data.SqlClient.SqlConnection();
var conString = System.Configuration.ConfigurationManager.ConnectionStrings["GoDaddySQL"];
conn.ConnectionString = conString.ConnectionString;
conn.Open();
System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand();
comm.Connection = conn;
string sql = "";
sql = "select customerlogon from huber_customers where customerlogon = @customerlogon and customerpass = @customerpass";
System.Data.SqlClient.SqlDataReader dr;
comm.CommandText = sql;
comm.Parameters.AddWithValue("@customerlogon", txtUsername.Text);
comm.Parameters.AddWithValue("@customerpass", txtPassword.Text);
dr = comm.ExecuteReader();
if (dr.HasRows)
{
System.Web.Security.FormsAuthentication.SetAuthCookie(txtUsername.Text, false);
Response.Redirect("default.aspx");
}
else
{
lblError.Text = "Your username or password was invalid.";
}
}
/// <summary>
/// Returns the supported countries.
/// </summary>
/// <param name="language">Code of the language that should be used.</param>
/// <remarks>Shows how to call a stored procedure.</remarks>
public Countries GetCountries(string language)
{
var result = new Countries();
//First: A connection is needed
using (var connection = new System.Data.SqlClient.SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=C:\\MyProjects\\Wifi-Kurs\\WIFI-Sisharp-Training\\wifi.sisharp.training.web\\App_Data\\AndritzHydro2019.mdf"))
{
//Second: A command is needed
using (var command = new System.Data.SqlClient.SqlCommand("GetCountries", connection))
{
//Configure the command
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("@language", language);
//The Sql Server should cache the procedure...
command.Prepare();
//Don't forget:
connection.Open();
//Third: (Not needed with INSERT, UPDATE or DELETE: command.ExecuteNonQuery() is used)
// - only with SELECT
using (var reader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
{
//Map the data to the data transfer objects
while (reader.Read())
{
result.Add(new Country
{
Code = reader["ISO"].ToString(),
Name = reader["Name"].ToString(),
MaxNumber = (int)reader["MaxNumber"],
NumberCount = (int)reader["NumberCount"]
});
}
}
}
}
return(result);
}
public BO.AssetInventoryTracking.workorder GetByIDworkorder(int workorderID)
{
BO.AssetInventoryTracking.workorder xworkorder = new BO.AssetInventoryTracking.workorder();
string query = "SELECT [workorderID],[date_created],[date_completed],[status],[inventoryID],[date_modified] FROM dbo.[workorder] WHERE workorderID=@workorderID";
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["db_AssetInventoryTracking"].ConnectionString)) {
using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query, conn)) {
cmd.Parameters.AddWithValue("@workorderID", workorderID);
conn.Open();
using (System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)) {
if (reader.Read())
{
if (!object.ReferenceEquals(reader["workorderID"], DBNull.Value))
{
xworkorder.workorderID = int.Parse(reader["workorderID"].ToString());
}
if (!object.ReferenceEquals(reader["date_created"], DBNull.Value))
{
xworkorder.date_created = DateTime.Parse(reader["date_created"].ToString());
}
if (!object.ReferenceEquals(reader["date_completed"], DBNull.Value))
{
xworkorder.date_completed = DateTime.Parse(reader["date_completed"].ToString());
}
if (!object.ReferenceEquals(reader["status"], DBNull.Value))
{
xworkorder.status = reader["status"].ToString();
}
if (!object.ReferenceEquals(reader["inventoryID"], DBNull.Value))
{
xworkorder.inventoryID = int.Parse(reader["inventoryID"].ToString());
}
if (!object.ReferenceEquals(reader["date_modified"], DBNull.Value))
{
xworkorder.date_modified = DateTime.Parse(reader["date_modified"].ToString());
}
}
}
}
}
return(xworkorder);
}
protected void BtnLogin_Click(object sender, EventArgs e)
{
bool validate = true;
String adminUser = HttpUtility.HtmlEncode(txtAdminUser.Value);
String adminPass = HttpUtility.HtmlEncode(txtAdminPass.Value);
System.Data.SqlClient.SqlCommand adminLogin = new System.Data.SqlClient.SqlCommand();
adminLogin.Connection = sc1;
sc1.Open();
adminLogin.CommandText = "SELECT * FROM ADMINACC WHERE Username = upper(@AdminUser)";
adminLogin.Parameters.Add(new SqlParameter("@AdminUser", adminUser));
System.Data.SqlClient.SqlDataReader adminReader = adminLogin.ExecuteReader();
if (adminReader.HasRows) // if the username exists, it will continue
{
while (adminReader.Read()) // this will read the single record that matches the entered username
{
String firstname = adminReader["firstname"].ToString();
CurrentSession.Current.userEmail = adminUser;
String adminPasstxt = adminReader["password"].ToString();
if (PasswordHash.ValidatePassword(txtAdminPass.Value, adminPasstxt)) // if the entered password matches what is stored, it will show success
{
CurrentSession.Current.AdminUser = adminUser;
Response.Write("<script>alert('Success!')</script>");
Response.Redirect("admin.aspx");
}
else
{
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alert", " alert('Sign in failed! Wrong password.');", true);
validate = false;
}
}
sc1.Close();
}
else
{
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alert", " alert('Admin user doesn't exist.');", true);
}
}
private void cbInitialCatalog_DropDown(object sender, System.EventArgs e)
{
using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(this.GetConnectionString()))
{
this.toolStripStatusLabel1.Text = "Установка подключения...";
System.Windows.Forms.Application.DoEvents();
try
{
using (sqlConnection)
{
sqlConnection.Open();
string cmdText = "SELECT name FROM sys.databases; ";
using (System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand(cmdText, sqlConnection))
{
System.Data.SqlClient.SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
this.cbInitialCatalog.Items.Clear();
using (sqlDataReader)
{
while (sqlDataReader.Read())
{
this.cbInitialCatalog.Items.Add(sqlDataReader.GetString(0));
}
}
}
}
this.toolStripStatusLabel1.Text = "Подключение установлено";
System.Windows.Forms.Application.DoEvents();
}
catch (System.Exception ex)
{
this.toolStripStatusLabel1.Text = "";
new ExceptionMessageBox(ex)
{
Caption = "Ошибка",
InnerException = ex.InnerException,
Buttons = ExceptionMessageBoxButtons.OK,
Symbol = ExceptionMessageBoxSymbol.Error
}.Show(this);
base.DialogResult = System.Windows.Forms.DialogResult.None;
}
}
}
protected void Populate_GridView_DataDict()
{
if (debugprint)
{
tblmyinfo.Rows[0].Cells[0].InnerHtml += "<br/>---> Populate_GridView_DataDict <br/>";
}
try
{
string mycmd = "exec spDataDict_by_measureID " + DDL_SelectMeasureID.SelectedValue.ToString();
if (debugprint)
{
tblmyinfo.Rows[0].Cells[0].InnerHtml += "=Pop GridView=" + mycmd + ".<br/>";
}
SqlCommand sqlCmd3 = new System.Data.SqlClient.SqlCommand(mycmd, oConn);
SqlDataReader sqlReader3 = sqlCmd3.ExecuteReader();
DataTable dt3 = new DataTable();
dt3.Load(sqlReader3);
GridView_DataDict.DataSource = dt3;
GridView_DataDict.CssClass = "DataDictCell";
GridView_DataDict.DataBind();
lblMeasName_ContentPage.Text = measurename;
GridView_DataDict.Visible = true;
lblINTRO_measname.Visible = true;
lblMeasName_ContentPage.Visible = true;
}
catch (SqlException oException)
{
foreach (SqlError oErr in oException.Errors)
{
tblmyinfo.Rows[0].Cells[0].InnerHtml += oErr.Message;
}
}
}
public ActionResult StudentList()
{
TempData["Class"] = TempData["Class"];
Session["Class"] = TempData["Class"];
String selectedclass = TempData["Class"].ToString();
string connectionString = @"Data Source=msi;Initial Catalog=SEFASSIGNMENT;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(connectionString);
sqlConnection.Open();
System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand("SELECT Stu_ID FROM [SEFASSIGNMENT].[dbo].[Student] WHERE CLASS_ID='" + selectedclass + "'");
sqlCommand.Connection = sqlConnection;
SqlDataReader myreader = sqlCommand.ExecuteReader();
List <String> StudentIDList = new List <String>();
while (myreader.Read())
{
StudentIDList.Add(myreader[0].ToString());
}
myreader.Close();
sqlCommand = new System.Data.SqlClient.SqlCommand("SELECT Stu_Name FROM [SEFASSIGNMENT].[dbo].[Student] WHERE CLASS_ID='" + selectedclass + "'");
sqlCommand.Connection = sqlConnection;
SqlDataReader newmyreader = sqlCommand.ExecuteReader();
List <String> StudentNameList = new List <String>();
while (newmyreader.Read())
{
StudentNameList.Add(newmyreader[0].ToString());
}
newmyreader.Close();
sqlConnection.Close();
var model = new StudentList
{
StudentNameList = StudentNameList,
StudentIDList = StudentIDList
}; return(View(model));
}
public Part lookupAssociatedPart(int partID)
{
Console.WriteLine(productID);
SqlConnection con = new System.Data.SqlClient.SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB; AttachDbFilename=" + Application.StartupPath + "\\DB.mdf; Integrated Security=True");
con.Open();
var query = "SELECT productID, partID, name, price, inStock FROM dbo.associatedParts where partID = @partID and productID = @productID";
var cmd = new System.Data.SqlClient.SqlCommand();
cmd.CommandText = query;
cmd.Connection = con;
cmd.Parameters.AddWithValue("@partID", partID);
Console.WriteLine("prodcutID" + getProductID());
cmd.Parameters.AddWithValue("@productID", getProductID());
String partsID;
String name;
String price;
String inStock;
String min;
String max;
Part lookedUpAssocPart = new Part();
SqlDataReader rdr = cmd.ExecuteReader();
// Fill the strings with the values retrieved, convert to types as needed
while (rdr.Read())
{
partsID = rdr["partID"].ToString();
lookedUpAssocPart.setPartID(Convert.ToInt32(partsID));
name = rdr["name"].ToString();
lookedUpAssocPart.setName(name);
price = rdr["price"].ToString();
lookedUpAssocPart.setPrice(Convert.ToDouble(price));
inStock = rdr["inStock"].ToString();
lookedUpAssocPart.setinStock(Convert.ToInt32(inStock));
}
con.Close();
return(lookedUpAssocPart);
}
/// <summary>
/// إرسال أمر SQl
/// وإرجعه على شكل مصفوفتين كائنات
/// </summary>
/// <param name="SqlCommand1">أمر SQL</param>
/// <returns>ليست بداخل ليست Object</returns>
public static async Task <List <List <object> > > GetSql(System.Data.SqlClient.SqlCommand SqlCommand1)
{
List <object> object1 = new List <object>();
List <List <object> > object2 = new List <List <object> >();
if (await openConction())
{
SqlCommand1.Connection = Sqldatabasethrding.SqlConnection1;
SqlDataReader SqlDataReader1 = null;
try
{
SqlDataReader1 = SqlCommand1.ExecuteReader();
while (await SqlDataReader1.ReadAsync())
{
for (int i = 0; i < SqlDataReader1.FieldCount; i++)
{
object1.Add(SqlDataReader1.GetValue(i));
}
object2.Add(object1);
object1 = new List <object>();
}
}
catch (Exception e)
{
ErrorClass.SaveErrorFile(e);
}
finally
{
SqlDataReader1.Close();
SqlDataReader1.Dispose();
}
return(object2);
}
else
{
return(object2);
}
}
public int checkproductqty(int batch_id, int productID)
{
int dt = 0;
using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
{
using (SqlCommand cmd = new System.Data.SqlClient.SqlCommand())
{
cmd.CommandText = "sp_selectbatchwisequantity";
cmd.CommandType = CommandType.StoredProcedure;
try
{
cmd.Parameters.AddWithValue("@batch_id", batch_id);
cmd.Parameters.AddWithValue("@product_id", productID);
cmd.Connection = con;
cmd.CommandTimeout = 600000;
con.Open();
using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
if (reader.Read())
{
dt = Convert.ToInt32(reader["StockAvl"].ToString());
}
}
}
catch (Exception ex)
{
ErrorLog.saveerror(ex);
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
return(dt);
}
}
}
//4)Once a case is chosen, this will load the data from the Persons table and data from the IndividualAtRisk table and loads it into the Care Giver tab on the
//Case View form.
public CareGiver RetrieveCareGiverData(int indivId)
{
var giver = new CareGiver();
sqlConnection1 = InitializeConnectionString();
using (sqlConnection1)
{
string data = "Select * from Persons, CareGiver, IndividualAtRisk where IndividualAtRisk.IndivId = '" + indivId + "' AND IndividualAtRisk.IndivId = CareGiver.IndivId AND Caregiver.PersonId = Persons.PersonId";
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(data, sqlConnection1);
sqlConnection1.Open();
using (SqlDataReader read = cmd.ExecuteReader())
{
while (read.Read())
{
giver.personId = read["PersonId"].ToString();
giver.lastName = read["Last_Name"].ToString();
giver.firstName = read["First_Name"].ToString();
giver.gender = read["Gender"].ToString();
giver.race = read["Race"].ToString();
giver.dob = read["DOB"].ToString();
giver.ssn = read["SSN"].ToString();
giver.milDependent = read["Military_Dependent"].ToString();
giver.churchConnection = read["Church_Connections"].ToString();
giver.streetAddress = read["Street_Address"].ToString();
giver.apartment = read["Apartment_Number"].ToString();
giver.city = read["City"].ToString();
giver.state = read["State"].ToString();
giver.zip = read["Zip_Code"].ToString();
giver.email = read["Email"].ToString();
giver.homePhone = read["Telephone_Home"].ToString();
giver.mobilePhone = read["Telephone_Mobile"].ToString();
giver.workPhone = read["Telephone_Work"].ToString();
giver.type = read["Person_Type"].ToString();
giver.relationship = read["Relationship"].ToString();
}
}
}
sqlConnection1.Close();
return giver;
}
} //========== end of the mtd
public IEnumerable <Models.IncomeAccountsListingModel> GetIncomeAccountsListingUsingParams(string search)
{
List <IncomeAccountsListingModel> obList = new List <IncomeAccountsListingModel>();
using (var con = new System.Data.SqlClient.SqlConnection(connectionString))
{
var cmd = new System.Data.SqlClient.SqlCommand("", con);
con.Open();
//cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "SELECT top 1000 * FROM Income_AccountsListing "
+ " where ACCOUNTNUMBER like @searchval";
cmd.Parameters.AddWithValue("@searchval", "%" + search + "%");
//cmd.CommandTimeout = 0;
//cmd.Parameters.Add("@result", System.Data.SqlDbType.TinyInt).Direction = System.Data.ParameterDirection.Output;
//cmd.ExecuteNonQuery();
//cmd2.ExecuteNonQuery();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
var ob = new IncomeAccountsListingModel();
//iob.ID = reader["ID"] != DBNull.Value ? int.Parse(reader["ID"].ToString()) : 0;
ob.ACCOUNTNUMBER = reader["ACCOUNTNUMBER"] != DBNull.Value ? reader["ACCOUNTNUMBER"].ToString() : "default";
ob.CustomerName = reader["CustomerName"] != DBNull.Value ? reader["CustomerName"].ToString() : "default";
ob.MIS_CODE = reader["MIS_CODE"] != DBNull.Value ? reader["MIS_CODE"].ToString() : "default";
ob.BranchCode = reader["BranchCode"] != DBNull.Value ? reader["BranchCode"].ToString() : "default";
ob.accountofficer_code = reader["accountofficer_code"] != DBNull.Value ? reader["accountofficer_code"].ToString() : "default";
ob.Team_branch = reader["Team_branch"] != DBNull.Value ? reader["Team_branch"].ToString() : "default";
ob.Date_Open = reader["Date_Open"] != DBNull.Value ? DateTime.Parse(reader["Date_Open"].ToString()) : DateTime.Parse("1000-01-01");
obList.Add(ob);
}
con.Close();
}
return(obList);
} //========== end of the mtd
public HttpResponseMessage GetAvailableIncomeAccountsUnit(HttpRequestMessage request)
{
return(GetHttpResponse(request, () =>
{
HttpResponseMessage res = null;
string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["FintrakDBConnection"].ConnectionString;
List <IncomeAccountsUnitModel> IncomeAccountsUnitList = new List <IncomeAccountsUnitModel>();
using (var con = new System.Data.SqlClient.SqlConnection(connectionString))
{
var cmd = new System.Data.SqlClient.SqlCommand("proc_Income_Accounts_Units_Get", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
//cmd.Parameters.Add("@result", System.Data.SqlDbType.TinyInt).Direction = System.Data.ParameterDirection.Output;
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
var tb = new IncomeAccountsUnitModel();
tb.ID = reader["ID"] != DBNull.Value ? int.Parse(reader["ID"].ToString()) : 0;
tb.AccountNumber = reader["AccountNumber"] != DBNull.Value ? reader["AccountNumber"].ToString() : "default";
tb.CustomerName = reader["CustomerName"] != DBNull.Value ? reader["CustomerName"].ToString() : "default";
tb.MIS_Code = reader["MIS_Code"] != DBNull.Value ? reader["MIS_Code"].ToString() : "default";
IncomeAccountsUnitList.Add(tb);
}
con.Close();
}
//comm = "Operation Successful.";
res = request.CreateResponse(HttpStatusCode.OK, IncomeAccountsUnitList);
return res;
}));
}
public void sendToButton_Click(object sender, EventArgs e)
{
List <int> studentIDList = new List <int>();
for (int i = 0; i < gridviewRefer.Rows.Count; i++)
{
CheckBox check = (CheckBox)gridviewRefer.Rows[i].FindControl("studentCheck");
if (check.Checked)
{
//find student ID for student who is checked
int studentID = Convert.ToInt32(gridviewRefer.DataKeys[i]["StudentEntityID"]);
studentIDList.Add(studentID);
}
}
String connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection(connectionString);
sc.Open();
System.Data.SqlClient.SqlConnection EmailQuery = new System.Data.SqlClient.SqlConnection(connectionString);
List <String> emailList = new List <String>();
// Mail Button Query
EmailQuery.Open();
System.Data.SqlClient.SqlCommand query = new System.Data.SqlClient.SqlCommand();
query.Connection = EmailQuery;
foreach (var studentID in studentIDList)
{
query.CommandText = "SELECT UserEntity.EmailAddress FROM UserEntity INNER JOIN Student ON UserEntity.UserEntityID = Student.StudentEntityID WHERE Student.StudentEntityID=" + studentID;
System.Data.SqlClient.SqlDataReader Result = query.ExecuteReader();
while (Result.Read())
{
String email = Result.GetString(0);
emailList.Add(email);
}
}
EmailQuery.Close();
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["AccountId"] != null && Convert.ToInt16(Session["type"]) == 1)
{
System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["roommagnetdbConnectionString"].ToString());
System.Data.SqlClient.SqlCommand selectEmail = new System.Data.SqlClient.SqlCommand();
selectEmail.Connection = sc;
sc.Open();
intellicorpEmail.Text = String.Empty;
//Populate Emails
selectEmail.CommandText = "SELECT Account.FirstName, Account.LastName, Account.Email " +
"FROM Account FULL OUTER JOIN Host ON Account.AccountID = Host.HostID FULL OUTER JOIN Tenant ON Account.AccountID = Tenant.TenantID " +
"WHERE Host.BackgroundCheckStatus = 0 OR Tenant.BackgroundCheckStatus = 0";
StringBuilder nameCard = new StringBuilder();
System.Data.SqlClient.SqlDataReader reader = selectEmail.ExecuteReader();
while (reader.Read())
{
String fName = reader["FirstName"].ToString();
String lName = reader["LastName"].ToString();
String email = reader["Email"].ToString();
//StringBuilder
StringBuilder myCard = new StringBuilder();
myCard.Append("<tr><td><a href =\"#\" class=\"tenantdashlist\">" + "Name: " + fName + " " + lName + "</a></td>" +
"<td><a href =\"#\" class=\"tenantdashlist\">" + "Email: " + email + "</a></td></tr>");
intellicorpEmail.Text += myCard.ToString();
}
reader.Close();
sc.Close();
}
else
{
Response.Redirect("Home.aspx");
}
}