internal KensaIraiTblDataSet.KensaIraiTblKensakuDataTable GetDataBySearchCond(string hokenjoCd, string kensaIraiNendo, string kensaIraiSetchishaNm, string kensaIraiShisetsuNm, string kensaIraiDtFrom, string kensaIraiDtTo, string kensaDtFrom, string kensaDtTo, string kensaIraiHoteiKbn) { SqlCommand command = CreateSqlCommand(hokenjoCd, kensaIraiNendo, kensaIraiSetchishaNm, kensaIraiShisetsuNm, kensaIraiDtFrom, kensaIraiDtTo, kensaDtFrom, kensaDtTo, kensaIraiHoteiKbn); SqlDataAdapter adpt = new SqlDataAdapter(command); adpt.SelectCommand.Connection = this.Connection; KensaIraiTblDataSet.KensaIraiTblKensakuDataTable dataTable = new KensaIraiTblDataSet.KensaIraiTblKensakuDataTable(); adpt.Fill(dataTable); return dataTable; }
protected void Fill_User_Header() { DataView view = null; SqlConnection con; SqlCommand cmd = new SqlCommand(); DataSet ds = new DataSet(); DataTable dt = new DataTable(); System.Configuration.Configuration rootWebConfig = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("/BitOp"); System.Configuration.ConnectionStringSettings connString; connString = rootWebConfig.ConnectionStrings.ConnectionStrings["BopDBConnectionString"]; con = new SqlConnection(connString.ToString()); cmd.Connection = con; con.Open(); string sql = @"SELECT Fecha_Desde, Inicio_Nombre, Region, Supervisor FROM Criterios WHERE Criterio_ID = " + @Criterio_ID; SqlDataAdapter da = new SqlDataAdapter(sql, con); da.Fill(ds); dt = ds.Tables[0]; view = new DataView(dt); foreach (DataRowView row in view) { Lbl_Fecha_Desde.Text = row["Fecha_Desde"].ToString("dd-MM-yyyy"); Lbl_Inicio_Descrip.Text = row["Inicio_Nombre"].ToString(); Lbl_Region.Text = row["Region"].ToString(); Lbl_Supervisor.Text = row["Supervisor"].ToString(); } con.Close(); }
//use this for SELECT statements public static DataTable GetData(string SelectStatement) { SqlDataAdapter da = new SqlDataAdapter(SelectStatement, ConnectionString); DataTable dt = new DataTable(); da.Fill(dt); return dt; }
internal KenchikuyotoMstDataSet.KenchikuyotoMstKensakuDataTable GetDataBySearchCond(string kenchikuyotoDaibunrui, string kenchikuyotoNm) { SqlCommand command = CreateSqlCommand(kenchikuyotoDaibunrui, kenchikuyotoNm); SqlDataAdapter adpt = new SqlDataAdapter(command); adpt.SelectCommand.Connection = this.Connection; KenchikuyotoMstDataSet.KenchikuyotoMstKensakuDataTable dataTable = new KenchikuyotoMstDataSet.KenchikuyotoMstKensakuDataTable(); adpt.Fill(dataTable); return dataTable; }
internal YubinNoAdrMstDataSet.YubinNoAdrMstKensakuDataTable GetDataBySearchCond(string zipCd, string address) { SqlCommand command = CreateSqlCommand(zipCd, address); SqlDataAdapter adpt = new SqlDataAdapter(command); adpt.SelectCommand.Connection = this.Connection; YubinNoAdrMstDataSet.YubinNoAdrMstKensakuDataTable dataTable = new YubinNoAdrMstDataSet.YubinNoAdrMstKensakuDataTable(); adpt.Fill(dataTable); return dataTable; }
internal YoshiZaikoTblDataSet.YoshiZaikoTblKensakuDataTable GetDataBySearchCond(string shishoCd, string yoshiCdFrom, string yoshiCdTo, string yoshiNm) { SqlCommand command = CreateSqlCommand(shishoCd, yoshiCdFrom, yoshiCdTo, yoshiNm); SqlDataAdapter adpt = new SqlDataAdapter(command); adpt.SelectCommand.Connection = this.Connection; YoshiZaikoTblDataSet.YoshiZaikoTblKensakuDataTable dataTable = new YoshiZaikoTblDataSet.YoshiZaikoTblKensakuDataTable(); adpt.Fill(dataTable); return dataTable; }
private void Consultar() { try { //Crio a variável que irá armazenar a string de conexão string strConn = @""; //Crio a conexão por meio do using, que me garante que após o uso, a conexão será fechada using (SqlConnection objConn = new SqlConnection(strConn)) { //Passo a instrução SQL por meio do SqlCommand e concateno //meu Where com o que o usuário digitar no TextBox SqlCommand objCmd = new SqlCommand("SELECT CODIGO, NOME FROM REGISTROS WHERE CODIGO = " + txtConsultar.Text, objConn); //Instancio o DataTable passando como parâmetro o SqlCommand SqlDataAdapter objDtAdapter = new SqlDataAdapter(objCmd); //Instancio o DataSet DataSet ds = new DataSet(); //Uso o método Fill do DataAdapter, passando como parâmetro o DataSet e minha Tabela objDtAdapter.Fill(ds, "REGISTROS"); //Uso o método DataSource de meu GridView, que receberá meu DataSet e chamo o DataBind dgvResultado.DataSource = ds; dgvResultado.DataBind(); } } catch (Exception ex) { throw new Exception(ex.Message.ToString()); } }
public static DataTable GetList(string sql, params SqlParameter[] ps) { using (SqlConnection connection = new SqlConnection(connStr)) { SqlDataAdapter adapter = new SqlDataAdapter(sql, connection); if (ps.Length > 0) { adapter.SelectCommand.Parameters.AddRange(ps); } DataTable dataTable = new DataTable(); adapter.Fill(dataTable); return dataTable; } }
internal ChikuMstDataSet.ChikuMstKensakuDataTable GetDataBySearchCond( String chikuCdFrom, String chikuCdTo, String chikuNm) { SqlCommand command = CreateSqlCommand(chikuCdFrom, chikuCdTo, chikuNm); SqlDataAdapter adpt = new SqlDataAdapter(command); adpt.SelectCommand.Connection = this.Connection; ChikuMstDataSet.ChikuMstKensakuDataTable dataTable = new ChikuMstDataSet.ChikuMstKensakuDataTable(); adpt.Fill(dataTable); return dataTable; }
internal ShishoMstDataSet.ShishoMstDataTable GetDataBySearchCond( string shishoCdFrom, string shishoCdTo, string shishoNm ) { SqlCommand command = CreateSqlCommand(shishoCdFrom, shishoCdTo, shishoNm); SqlDataAdapter adpt = new SqlDataAdapter(command); adpt.SelectCommand.Connection = this.Connection; ShishoMstDataSet.ShishoMstDataTable dataTable = new ShishoMstDataSet.ShishoMstDataTable(); adpt.Fill(dataTable); return dataTable; }
internal NameMstDataSet.NameMstKensakuDataTable GetDataBySearchCond( String nameKbn, String nameCdFrom, String nameCdTo, String name) { SqlCommand command = CreateNameMstKensakuSqlCommand(nameKbn, nameCdFrom, nameCdTo, name); SqlDataAdapter adpt = new SqlDataAdapter(command); adpt.SelectCommand.Connection = this.Connection; NameMstDataSet.NameMstKensakuDataTable dataTable = new NameMstDataSet.NameMstKensakuDataTable(); adpt.Fill(dataTable); return dataTable; }
internal FileKanriTblDataSet.FileKanriTblKensakuDataTable GetDataBySearchCond( string fileName, bool torokuDtUse, DateTime torokuDtFrom, DateTime torokuDtTo) { SqlCommand command = CreateSqlCommand(fileName,torokuDtUse, torokuDtFrom, torokuDtTo); SqlDataAdapter adpt = new SqlDataAdapter(command); adpt.SelectCommand.Connection = this.Connection; FileKanriTblDataSet.FileKanriTblKensakuDataTable dataTable = new FileKanriTblDataSet.FileKanriTblKensakuDataTable(); adpt.Fill(dataTable); return dataTable; }
internal MemoMstDataSet.MemoMstKensakuDataTable GetDataBySearchCond( string memoDaibunruiCd, string memo, bool juyo, bool hutsu, bool sentakuKa, bool sentakuHuka) { SqlCommand command = CreateSqlCommand(memoDaibunruiCd, memo, juyo, hutsu, sentakuKa, sentakuHuka); SqlDataAdapter adpt = new SqlDataAdapter(command); adpt.SelectCommand.Connection = this.Connection; MemoMstDataSet.MemoMstKensakuDataTable dataTable = new MemoMstDataSet.MemoMstKensakuDataTable(); adpt.Fill(dataTable); return dataTable; }
internal KatashikiMstDataSet.KatashikiMstKensakuDataTable GetDataBySearchCond( String katashikiMakerCdFrom, String katashikiMakerCdTo, String gyoshaNm, String katashikiCdFrom, String katashikiCdTo, String katashikiNm) { SqlCommand command = CreateSqlCommand(katashikiMakerCdFrom, katashikiMakerCdTo, gyoshaNm, katashikiCdFrom, katashikiCdTo, katashikiNm ); SqlDataAdapter adpt = new SqlDataAdapter(command); adpt.SelectCommand.Connection = this.Connection; KatashikiMstDataSet.KatashikiMstKensakuDataTable dataTable = new KatashikiMstDataSet.KatashikiMstKensakuDataTable(); adpt.Fill(dataTable); return dataTable; }
//检测当前客户是否允许签单 public static bool AllowSignPay(string customercode) { System.Data.SqlClient.SqlConnection conn1 = new System.Data.SqlClient.SqlConnection(); conn1.ConnectionString = ConnStr; try { SqlCommand selectCMD1 = new SqlCommand(); selectCMD1.Connection = conn1; selectCMD1.CommandText = "SELECT signed FROM customer where customercode='" + customercode + "'"; selectCMD1.CommandTimeout = 30; SqlDataAdapter dbDA1 = new SqlDataAdapter(); dbDA1.SelectCommand = selectCMD1; conn1.Open(); DataSet dbDS1 = new DataSet(); dbDA1.Fill(dbDS1, "t1"); if (dbDS1.Tables[0].Rows[0][0].ToString() == "1") { return true; } else { return false; } } catch (Exception ex) { MessageBox.Show(ex.ToString()); return false; } finally { conn1.Close(); } }
//返回时间段编码 public static string GetTimeBlockCode(string timeblock) { System.Data.SqlClient.SqlConnection conn1 = new System.Data.SqlClient.SqlConnection(); conn1.ConnectionString = ConnStr; try { SqlCommand selectCMD1 = new SqlCommand("SELECT timeblockCode FROM timeblock where name='" + timeblock + "'", conn1); selectCMD1.CommandTimeout = 30; SqlDataAdapter dbDA1 = new SqlDataAdapter(); dbDA1.SelectCommand = selectCMD1; conn1.Open(); DataSet dbDS1 = new DataSet(); dbDA1.Fill(dbDS1, "t1"); return dbDS1.Tables[0].Rows[0][0].ToString(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { conn1.Close(); } return ""; }
public static bool EmpIdExists(string empid) { System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(); conn.ConnectionString = ConnStr; try { SqlCommand selectCMD = new SqlCommand("SELECT * FROM Employee where " + "EmpID=" + "'" + empid + "'", conn); selectCMD.CommandTimeout = 30; SqlDataAdapter dbDA = new SqlDataAdapter(); dbDA.SelectCommand = selectCMD; conn.Open(); DataSet dbDS = new DataSet(); dbDA.Fill(dbDS, "t"); if (dbDS.Tables[0].Rows.Count > 0) //该雇员存在 { return true; } else //雇员不存在 { return false; } } catch (Exception ex) { MessageBox.Show(ex.ToString()); return false; } finally { conn.Close(); } }
private void PrintDocument_PrintPage(object sender, PrintPageEventArgs e) { Graphics graphic = e.Graphics; Font font = new Font("Courier New", 12); float fontHeight = font.GetHeight(); int startX = 10; int startY = 10; int offset = 40; total = 0; // Draw text to document. graphic.DrawString("PremierCare Hospital", new Font("Courier New", 18, FontStyle.Bold), new SolidBrush(Color.DarkRed), startX, startY); graphic.DrawString("Patient Name: " + cmb_invoicePatient.Text, new Font("Courier New", 11), new SolidBrush(Color.DarkBlue), startX, startY + offset); // Increase vertical spacing. offset = offset + (int)fontHeight + 10; // Step 1: Database Connection SqlConnection conn = new SqlConnection(myconnstr); try { // Iterate through items in Services ListBox for (int i = 0; i < lbx_services.Items.Count; i++) { DataRowView objDataRowView = (DataRowView)lbx_services.Items[i]; // Check if item at index 'i' is selected. if (lbx_services.GetSelected(i)) // Returns TRUE or FALSE. { DataTable dt = new DataTable(); // Step 2: Write SQL query string sql = "SELECT Cost, Name FROM Service WHERE Service_ID ='" + Convert.ToInt32(objDataRowView["Service_ID"].ToString()) + "'"; // Create cmd using sql and conn SqlCommand cmd = new SqlCommand(sql, conn); // Create SQL DataAdapter using cmd SqlDataAdapter adapter = new SqlDataAdapter(cmd); conn.Open(); adapter.Fill(dt); string serviceName = dt.Rows[0].Field <string>("Name").PadRight(30); decimal cost = dt.Rows[0].Field <decimal>("Cost"); string serviceCost = string.Format("{0:C}", cost); string serviceLine = serviceName + serviceCost; // Draw text to Document. graphic.DrawString(serviceLine, font, new SolidBrush(Color.Black), startX, startY + offset); // Increase vertical spacing. offset = offset + (int)fontHeight + 5; total += (double)cost; conn.Close(); } } // Iterate through items in Prescriptions ListBox for (int i = 0; i < lbx_prescription.Items.Count; i++) { DataRowView objDataRowView = (DataRowView)lbx_prescription.Items[i]; // Check if item at index 'i' is selected. if (lbx_prescription.GetSelected(i)) // Returns TRUE or FALSE. { DataTable dt = new DataTable(); // Step 2: Write SQL query string sql = "SELECT Drug.Name, Drug.Cost " + "FROM Prescription " + "INNER JOIN Drug ON Prescription.Drug_ID = Drug.Drug_ID " + "WHERE Prescription.Prescription_ID = '" + Convert.ToInt32(objDataRowView["Prescription_ID"].ToString()) + "'"; // Create cmd using sql and conn SqlCommand cmd = new SqlCommand(sql, conn); // Create SQL DataAdapter using cmd SqlDataAdapter adapter = new SqlDataAdapter(cmd); conn.Open(); adapter.Fill(dt); string prescriptionName = dt.Rows[0].Field <string>("Name").PadRight(30); decimal cost = dt.Rows[0].Field <decimal>("Cost"); string prescriptionCost = string.Format("{0:C}", cost); string prescriptionLine = prescriptionName + prescriptionCost; // Draw text to Document. graphic.DrawString(prescriptionLine, font, new SolidBrush(Color.Black), startX, startY + offset); // Increase vertical spacing. offset = offset + (int)fontHeight + 5; total += (double)cost; conn.Close(); } } // Increase vertical spacing. offset = offset + 20; // Draw text to Document. graphic.DrawString("Total".PadRight(30) + string.Format("{0:C}", total), new Font("Courier New", 12, FontStyle.Bold), new SolidBrush(Color.Black), startX, startY + offset); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
protected void Button1_Click(object sender, EventArgs e) { string ftime = TextBox1.Text; // DateTime ct = Convert.ToDateTime(ftime); // ftime = ct.ToString("hh:mm"); string ttime = TextBox2.Text; // DateTime tt = Convert.ToDateTime(ttime); // ttime = tt.ToString("hh:mm"); SqlDataAdapter da; DataSet ds = new DataSet(); string s = "select distinct foodno from transact where time >='" + ftime + "' and time<='" + ttime + "'"; da = new SqlDataAdapter(s, con); da.Fill(ds); if (ds.Tables[0].Rows.Count > 0) { for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { SqlDataAdapter da1; DataSet ds1 = new DataSet(); string m = "select foodname,quantity,amount from transact where foodno='" + ds.Tables[0].Rows[i][0].ToString() + "'"; da1 = new SqlDataAdapter(m, con); da1.Fill(ds1); if (ds1.Tables[0].Rows.Count > 0) { double totalquant = 0, totalamt = 0, per = 0;; string fname = ""; for (int j = 0; j < ds1.Tables[0].Rows.Count; j++) { totalquant = totalquant + Convert.ToDouble(ds1.Tables[0].Rows[j][1].ToString()); totalamt = totalamt + Convert.ToDouble(ds1.Tables[0].Rows[j][2].ToString()); } fname = ds1.Tables[0].Rows[0][0].ToString(); per = (totalamt / 100) * ds1.Tables[0].Rows.Count; TableRow tRow = new TableRow(); TableCell tcell = new TableCell(); TableCell tcell1 = new TableCell(); tcell.Text = "Food Name:"; tcell1.Text = fname; tRow.Cells.Add(tcell); tRow.Cells.Add(tcell1); Table1.Rows.Add(tRow); TableRow tr = new TableRow(); TableCell tc = new TableCell(); TableCell tc1 = new TableCell(); tc.Text = "Total Sales:"; tc1.Text = Convert.ToString(totalquant); tr.Cells.Add(tc); tr.Cells.Add(tc1); Table1.Rows.Add(tr); TableRow tr1 = new TableRow(); TableCell tcc = new TableCell(); TableCell tcc1 = new TableCell(); tcc.Text = "Total Amount:"; tcc1.Text = Convert.ToString(totalamt); tr1.Cells.Add(tcc); tr1.Cells.Add(tcc1); Table1.Rows.Add(tr1); TableRow tper = new TableRow(); TableCell tcp = new TableCell(); TableCell tcp1 = new TableCell(); tcp.Text = "Percentage:"; tcp1.Text = Convert.ToString(per) + " %"; tper.Cells.Add(tcp); tper.Cells.Add(tcp1); Table1.Rows.Add(tper); TableRow thr = new TableRow(); TableCell tch = new TableCell(); tch.ColumnSpan = 2; tch.Text = "<hr>"; tch.Width = 500; thr.Cells.Add(tch); Table1.Rows.Add(thr); } } } else { Page.ClientScript.RegisterStartupScript(GetType(), "msgtype()", "alert('No Data Available !!!')", true); } }
protected void Page_Load(object sender, EventArgs e) { //绑定对象 //默认值 txtRegTime.Text = DateTime.Now.ToString("yyyy-MM-dd"); txtExpire.Text = DateTime.Now.AddYears(1).ToString("yyyy-MM-dd"); string m = Request.QueryString["m"]; switch (m) { //添加 case "a": case "A": break; //查看 //修改 default: //获取数据 using (SqlConnection SqlConn = new SqlConnection(Apq.DB.Common.GetSqlConnectionString("SqlConnectionString2"))) { Apq.STReturn stReturn = new Apq.STReturn(); SqlDataAdapter sda = new SqlDataAdapter("dtxc.dtxc_Users_ListOne", SqlConn); sda.SelectCommand.CommandType = CommandType.StoredProcedure; Apq.Data.Common.DbCommandHelper dch = new Apq.Data.Common.DbCommandHelper(sda.SelectCommand); dch.AddParameter("rtn", 0, DbType.Int32); dch.AddParameter("ExMsg", stReturn.ExMsg, DbType.String, -1); dch.AddParameter("UserID", ApqSession.UserID); sda.SelectCommand.Parameters["rtn"].Direction = ParameterDirection.ReturnValue; sda.SelectCommand.Parameters["ExMsg"].Direction = ParameterDirection.InputOutput; SqlConn.Open(); sda.Fill(ds); stReturn.NReturn = System.Convert.ToInt32(sda.SelectCommand.Parameters["rtn"].Value); stReturn.ExMsg = sda.SelectCommand.Parameters["ExMsg"].Value.ToString(); stReturn.FNReturn = ds.Tables[0]; sda.Dispose(); SqlConn.Close(); } if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { //页面赋值 txtUserID.Text = ds.Tables[0].Rows[0]["UserID"].ToString(); txtName.Text = ds.Tables[0].Rows[0]["Name"].ToString(); ddlSex.SelectedValue = ds.Tables[0].Rows[0]["Sex"].ToString(); txtAlipay.Text = ds.Tables[0].Rows[0]["Alipay"].ToString(); if (!System.Convert.IsDBNull(ds.Tables[0].Rows[0]["Birthday"])) txtBirthday.Text = System.Convert.ToDateTime(ds.Tables[0].Rows[0]["Birthday"]).ToString("yyyy-MM-dd"); txtExpire.Text = System.Convert.ToDateTime(ds.Tables[0].Rows[0]["Expire"]).ToString("yyyy-MM-dd"); txtRegTime.Text = System.Convert.ToDateTime(ds.Tables[0].Rows[0]["RegTime"]).ToString("yyyy-MM-dd"); txtIntroUserID.Text = ds.Tables[0].Rows[0]["IntroUserID"].ToString(); txtIDCard.Text = ds.Tables[0].Rows[0]["IDCard"].ToString(); } break; } //设置只读 if (m == "v" || m == "V") { txtUserID.Enabled = false; txtName.Enabled = false; ddlSex.Enabled = false; txtAlipay.Enabled = false; txtBirthday.Enabled = false; txtIntroUserID.Enabled = false; txtIDCard.Enabled = false; } }
private void student_schedule_Load(object sender, EventArgs e) { // start build schedule int top = 50, index = 0; int left = 100; int i, j, counter = 0; for (i = 0; i < 6; i++) { for (j = 0; j < 12; j++) { buttonArray[i, j] = new Button(); buttonArray[i, j].Click += new System.EventHandler(ClickedButton); buttonArray[i, j].Size = new Size(120, 40); buttonArray[i, j].Left = left; buttonArray[i, j].Top = top; this.Controls.Add(buttonArray[i, j]); top += buttonArray[i, j].Height + 2; } top = 50; left += 125; } // finish build schedule visualy //load the schedule of the student //---------------------------------------------- SqlConnection conn = new SqlConnection(constring); conn.Open(); SqlCommand cmd0 = conn.CreateCommand(); cmd0.CommandType = CommandType.Text; cmd0.CommandText = "select * from student_schedule"; cmd0.ExecuteNonQuery(); DataTable dt0 = new DataTable(); SqlDataAdapter da0 = new SqlDataAdapter(cmd0); da0.Fill(dt0); SqlCommand cmd1 = conn.CreateCommand(); cmd1.CommandType = CommandType.Text; cmd1.CommandText = "select * from Courses"; cmd1.ExecuteNonQuery(); DataTable dt1 = new DataTable(); SqlDataAdapter da1 = new SqlDataAdapter(cmd1); da1.Fill(dt1); SqlCommand cmd2 = conn.CreateCommand(); cmd2.CommandType = CommandType.Text; cmd2.CommandText = "select * from Lessons"; cmd2.ExecuteNonQuery(); DataTable dt2 = new DataTable(); SqlDataAdapter da2 = new SqlDataAdapter(cmd2); da2.Fill(dt2); foreach (DataRow dr0 in dt0.Rows) { if (dr0["StudentID"].Equals(student.getID())) { foreach (DataRow dr2 in dt2.Rows) { if (dr0["LessonID"].Equals(dr2["LessonID"].ToString())) { foreach (DataRow dr1 in dt1.Rows) { if (dr2["CourseID"].Equals(dr1["Id"].ToString())) { Courses temp = new Courses(dr2["CourseID"].ToString(), dr1["Name"].ToString(), dr2["Day"].ToString(), dr2["StartH"].ToString(), dr2["EndH"].ToString(), dr2["ClassName"].ToString(), dr2["LessonType"].ToString(), dr2["LessonID"].ToString(), dr2["TSID"].ToString(), dr1["Pre"].ToString(), student.getID()); index++; for (int k = temp.getStartH(); k < temp.getEndH(); k++) { buttonArray[temp.getDay(), k].Text = temp.getName() + " " + temp.getType(); } buttonArray[temp.getDay(), temp.getStartH()].Height = buttonArray[temp.getDay(), temp.getStartH()].Height * (temp.getEndH() - temp.getStartH()) + 2 * (temp.getEndH() - temp.getStartH() - 1); buttonArray[temp.getDay(), temp.getStartH()].Text = temp.getName() + " " + temp.getType(); } } } } } } //---------------------------------------------- //------------------------------------------------------- //load combobox - courses that student able to register // getting the courses by semester and department into combobox1 comboBox2.Items.Clear(); comboBox1.Items.Clear(); foreach (DataRow dr2 in dt2.Rows) { foreach (DataRow dr in dt1.Rows) { if (student.getDep().Equals(dr["Department"]) && student.getSemester().Equals(dr["Semester"])) { if (dr["Id"].ToString().Equals(dr2["CourseID"])) { { if (checkifclassisfull(dr2["LessonID"].ToString()) == 1) { comboBox1.Items.Add(dr["Name"].ToString() + " " + dr2["LessonType"]); comboBox2.Items.Add(dr["Name"].ToString() + " " + dr2["LessonType"]); counter++; } } } } } } courses = new Courses[counter]; i = 0; // fill courses array with courses that the student can learn j = 0; foreach (DataRow dr2 in dt2.Rows) { foreach (DataRow dr in dt1.Rows) { if (student.getDep().Equals(dr["Department"]) && student.getSemester().Equals(dr["Semester"])) { if (dr["Id"].ToString().Equals(dr2["CourseID"])) { for (j = 0; j < i; j++) { if (courses[j].getLessonID().Equals(dr2["LessonID"])) { break; } } if (i == j) { courses[i] = new Courses(dr["Id"].ToString(), dr["Name"].ToString(), dr2["Day"].ToString(), dr2["StartH"].ToString(), dr2["EndH"].ToString(), dr2["ClassName"].ToString(), dr2["LessonType"].ToString(), dr2["LessonID"].ToString(), dr2["TSID"].ToString(), dr["Pre"].ToString(), student.getID()); i++; } } } } } conn.Close(); }
// DISPLAY TEACHER INFORMATION public void DisplayTeachers() { using (connection = new SqlConnection(connectionString)) { using (SqlDataAdapter adaptor = new SqlDataAdapter("SELECT * FROM Teachers", connection)) { DataTable TeacherTable = new DataTable(); adaptor.Fill(TeacherTable); cbTeacherID.DisplayMember = "First_Name"; cbTeacherID.ValueMember = "TeacherID"; cbTeacherID.DataSource = TeacherTable; lbTeacherID.DisplayMember = "TeacherID"; lbTeacherID.ValueMember = "TeacherID"; lbTeacherID.DataSource = TeacherTable; lbFirstName.DisplayMember = "First_Name"; lbFirstName.ValueMember = "TeacherID"; lbFirstName.DataSource = TeacherTable; lbSurname.DisplayMember = "Surname"; lbSurname.ValueMember = "TeacherID"; lbSurname.DataSource = TeacherTable; lbAddress.DisplayMember = "Address"; lbAddress.ValueMember = "TeacherID"; lbAddress.DataSource = TeacherTable; lbTown.DisplayMember = "Town"; lbTown.ValueMember = "TeacherID"; lbTown.DataSource = TeacherTable; lbPostCode.DisplayMember = "PostCode"; lbPostCode.ValueMember = "TeacherID"; lbPostCode.DataSource = TeacherTable; lbContactNum.DisplayMember = "Contact_Number"; lbContactNum.ValueMember = "TeacherID"; lbContactNum.DataSource = TeacherTable; lbEmail.DisplayMember = "Email_Address"; lbEmail.ValueMember = "TeacherID"; lbEmail.DataSource = TeacherTable; lbSpecialisation.DisplayMember = "Specialisation"; lbSpecialisation.ValueMember = "TeacherID"; lbSpecialisation.DataSource = TeacherTable; lbRoomID.DisplayMember = "RoomID"; lbRoomID.ValueMember = "TeacherID"; lbRoomID.DataSource = TeacherTable; using (SqlCommand Max = new SqlCommand("SELECT MAX(TeacherID) FROM Teachers", connection)) { connection.Open(); MaxTeacherID = (int)Max.ExecuteScalar(); } using (SqlCommand Min = new SqlCommand("Select Min(TeacherID) FROM Teachers", connection)) { MinTeacherID = (int)Min.ExecuteScalar(); connection.Close(); } } } using (connection = new SqlConnection(connectionString)) { connection.Open(); using (SqlCommand Command = new SqlCommand("SELECT * FROM Teachers", connection)) { DataReader = Command.ExecuteReader(); while (DataReader.Read()) { // For each record, store the corrisponding peice of information. Teacher_IDs.Add(DataReader.GetInt32(0)); Teachers_FirstName.Add(DataReader.GetString(1)); Teachers_Surname.Add(DataReader.GetString(2)); } } connection.Close(); } }
private DataTable getDataReport(string jenis) { var jenisRawat = 1; if (jenis == "Rawat Jalan") { jenisRawat = 2; } DataTable dt = new DataTable(); DateTime dateFrom = new DateTime(dtFrom.Value.Year, dtFrom.Value.Month, 1); DateTime firstdateTo = new DateTime(dtTo.Value.Year, dtTo.Value.Month, 1); DateTime dateTo = firstdateTo.AddMonths(1).AddDays(-1); //string query = @"SELECT // CASE // { fn MONTH ( dt_tgl_sep ) } // WHEN 1 THEN // 'JAN' // WHEN 2 THEN // 'FEB' // WHEN 3 THEN // 'MAR' // WHEN 4 THEN // 'APR' // WHEN 5 THEN // 'MAY' // WHEN 6 THEN // 'JUN' // WHEN 7 THEN // 'JUL' // WHEN 8 THEN // 'AUG' // WHEN 9 THEN // 'SEP' // WHEN 10 THEN // 'OCT' // WHEN 11 THEN // 'NOV' // WHEN 12 THEN // 'DEC' // END AS Bulan, // YEAR ( dt_tgl_sep ) AS Tahun, // COUNT ( sep.vc_No_sep ) AS Jumlah // FROM // INACBG_RAW_DATA inacbg // INNER JOIN bpjs_sep sep ON sep.vc_no_sep = inacbg.sep // AND sep.vc_no_rm = inacbg.mrn // AND ISNULL( sep.bt_hapus, 0 ) <> 1 // WHERE // vc_Jenis_perawatan = '"+jenis + "' " + // " AND Convert(DateTime, Convert(Varchar,Isnull(sep.dt_tgl_sep,0),101),101) between '" + String.Format(dateFrom.ToShortDateString(), "MM/DD/YYY") + "' and '" + String.Format(dateTo.ToShortDateString(), "MM/DD/YYY") + "' " + // " GROUP BY { fn MONTH ( dt_tgl_sep ) }, YEAR ( dt_tgl_sep ) " + // " ORDER BY YEAR ( dt_tgl_sep ),{ fn MONTH ( dt_tgl_sep ) } ASC"; string query = @"SELECT CASE { fn MONTH(Convert(datetime,ADMISSION_DATE,103)) } WHEN 1 THEN 'JAN' WHEN 2 THEN 'FEB' WHEN 3 THEN 'MAR' WHEN 4 THEN 'APR' WHEN 5 THEN 'MAY' WHEN 6 THEN 'JUN' WHEN 7 THEN 'JUL' WHEN 8 THEN 'AUG' WHEN 9 THEN 'SEP' WHEN 10 THEN 'OCT' WHEN 11 THEN 'NOV' WHEN 12 THEN 'DEC' END AS Bulan, year(Convert(datetime,ADMISSION_DATE,103)) AS Tahun, COUNT ( inacbg.SEP ) AS Jumlah FROM INACBG_RAW_DATA inacbg WHERE ptd = @jenisRawat AND Convert(datetime,ADMISSION_DATE,103) between @dateFrom and @dateTo GROUP BY { fn MONTH(Convert(datetime,ADMISSION_DATE,103)) }, year(Convert(datetime,ADMISSION_DATE,103)) ORDER BY year(Convert(datetime,ADMISSION_DATE,103)) ,{ fn MONTH(Convert(datetime,ADMISSION_DATE,103)) } ASC"; using (SqlCommand cmd = new SqlCommand(query, clMain.DBConn.objConnection)) { cmd.Parameters.AddWithValue("@jenisRawat", jenisRawat); cmd.Parameters.AddWithValue("@dateFrom", String.Format(dateFrom.ToShortDateString(), "YYYY-MM-DD")); cmd.Parameters.AddWithValue("@dateTo", String.Format(dateTo.ToShortDateString(), "YYYY-MM-DD")); using (SqlDataAdapter da = new SqlDataAdapter(cmd)) { da.Fill(dt); } } return(dt); }
public ActionResult EmployeesProfile() { string employeenumber = Session["employeenumber"].ToString(); ViewBag.empPicture = Session["picture"].ToString(); Masterlist masterlist = new Masterlist(); DataTable dtblEmployeeInfo = new DataTable(); using (SqlConnection sqlCon = new SqlConnection(connectionString)) { sqlCon.Open(); string query = "SELECT EmployeeNumber,FirstName,LastName,PersonalEmail,Department,JobTitle,DateHired,ContactNumber,Street_Address1,Street_Address2,City," + "Province,ZipCode,Birthday,Gender,MaritalStatus from Masterlist Where EmployeeNumber = @ID"; SqlDataAdapter sqlDa = new SqlDataAdapter(query, sqlCon); sqlDa.SelectCommand.Parameters.AddWithValue("@ID", employeenumber); sqlDa.Fill(dtblEmployeeInfo); } if (dtblEmployeeInfo.Rows.Count == 1) { ViewBag.empID = employeenumber; ViewBag.firstName = dtblEmployeeInfo.Rows[0][1].ToString(); ViewBag.lastName = dtblEmployeeInfo.Rows[0][2].ToString(); ViewBag.eMail = dtblEmployeeInfo.Rows[0][3].ToString(); ViewBag.dept = dtblEmployeeInfo.Rows[0][4].ToString(); ViewBag.jobTitle = dtblEmployeeInfo.Rows[0][5].ToString(); ViewBag.dateHired = dtblEmployeeInfo.Rows[0][6].ToString(); ViewBag.contactNum = dtblEmployeeInfo.Rows[0][7].ToString(); ViewBag.street1 = dtblEmployeeInfo.Rows[0][8].ToString(); ViewBag.street2 = dtblEmployeeInfo.Rows[0][9].ToString(); ViewBag.city = dtblEmployeeInfo.Rows[0][10].ToString(); ViewBag.province = dtblEmployeeInfo.Rows[0][11].ToString(); ViewBag.zipcode = dtblEmployeeInfo.Rows[0][12].ToString(); ViewBag.bday = dtblEmployeeInfo.Rows[0][13].ToString(); ViewBag.gender = dtblEmployeeInfo.Rows[0][14].ToString(); ViewBag.maritalStatus = dtblEmployeeInfo.Rows[0][15].ToString(); return(View(masterlist)); } EmpHistory empHistory = new EmpHistory(); DataTable dtblEmpHistory = new DataTable(); using (SqlConnection sqlConn = new SqlConnection(connectionString)) { sqlConn.Open(); string qryEmpHistory = "SELECT DatePromoted,GrossPay,Allowance,EmploymentStatus from EmpHistory Where EmployeeNumber = @ID"; SqlDataAdapter sqlDa = new SqlDataAdapter(qryEmpHistory, sqlConn); sqlDa.SelectCommand.Parameters.AddWithValue("@ID", employeenumber); sqlDa.Fill(dtblEmpHistory); } if (dtblEmployeeInfo.Rows.Count == 1) { ViewBag.firstName = dtblEmployeeInfo.Rows[0][1].ToString(); ViewBag.lastName = dtblEmployeeInfo.Rows[0][2].ToString(); ViewBag.eMail = dtblEmployeeInfo.Rows[0][3].ToString(); ViewBag.dept = dtblEmployeeInfo.Rows[0][4].ToString(); ViewBag.jobTitle = dtblEmployeeInfo.Rows[0][5].ToString(); ViewBag.dateHired = dtblEmployeeInfo.Rows[0][6].ToString(); ViewBag.contactNum = dtblEmployeeInfo.Rows[0][7].ToString(); ViewBag.street1 = dtblEmployeeInfo.Rows[0][8].ToString(); ViewBag.street2 = dtblEmployeeInfo.Rows[0][9].ToString(); ViewBag.city = dtblEmployeeInfo.Rows[0][10].ToString(); ViewBag.province = dtblEmployeeInfo.Rows[0][11].ToString(); ViewBag.zipcode = dtblEmployeeInfo.Rows[0][12].ToString(); ViewBag.bday = dtblEmployeeInfo.Rows[0][13].ToString(); ViewBag.gender = dtblEmployeeInfo.Rows[0][14].ToString(); ViewBag.maritalStatus = dtblEmployeeInfo.Rows[0][15].ToString(); return(View(masterlist)); } return(View()); }
protected void Page_Load(object sender, EventArgs e) { //jika userid kosong maka akan di link ke halaman awal if (!rtwin.azlib.AksesHalaman(32, Session["UserID"].ToString(), Session["GradeID"].ToString(), Session["MenuID"].ToString())) { Response.Redirect("notauthorized.aspx"); } string strQuery = ""; if ((Session["GradeID"].ToString().Substring(0, 1) == "1") || (Session["GradeID"].ToString() == "2")) //admin & validator { if (Session["GradeID"].ToString() == "2") //validator { strQuery = " AND KODE_CABANG =" + Session["CabangID"].ToString() + ""; } else if (Session["GradeID"].ToString() == "1a") //operator keuangan { strQuery = " AND KODE_CABANG IN (SELECT KODE_CABANG FROM taOtoritasCabang WHERE USERNAME='******')"; } else { strQuery = ""; } } if (Session["tipeRekap"].ToString() == "1") { SqlConnection cn = new SqlConnection(Application["strCn"].ToString()); string q = ""; string TTD1Header = ""; string TTD1 = ""; string TTD2Header = ""; string TTD2 = ""; string TTD3Header = ""; string TTD3 = ""; // q = "SELECT TTD1_HEADER, TTD1, TTD2_HEADER, TTD2, TTD3_HEADER, TTD3 FROM taLaporanPengesahan WHERE KODE_LAPORAN = '" + Session["strKodeRpt"] + "' AND KODE_CABANG='" + Session["CabangID"].ToString() + "'"; q = "SELECT TTD1_HEADER, TTD1, TTD2_HEADER, TTD2, TTD3_HEADER, TTD3 FROM taLaporanPengesahan WHERE KODE_LAPORAN = 'K1' AND KODE_CABANG='" + Session["CabangID"].ToString() + "'"; //Response.Write(q); SqlDataAdapter db2 = new SqlDataAdapter(q, cn); DataSet dsPengesahan = new DataSet(); db2.Fill(dsPengesahan); if (dsPengesahan.Tables[0].Rows.Count > 0) { DataTable tblPengesahan = dsPengesahan.Tables[0]; foreach (DataRow myRow in tblPengesahan.Rows) { TTD1Header = myRow["TTD1_HEADER"].ToString(); TTD1 = myRow["TTD1"].ToString(); TTD2Header = myRow["TTD2_HEADER"].ToString(); TTD2 = myRow["TTD2"].ToString(); TTD3Header = myRow["TTD3_HEADER"].ToString(); TTD3 = myRow["TTD3"].ToString(); } } DataSet ds = new DataSet(); string kodeRapel = Session["strKodeRapel"].ToString(); using (SqlConnection openCon = new SqlConnection(Application["strCn"].ToString())) { crys = new ReportDocument(); crys.Load(Server.MapPath("App_Data/RapelTKIndividu_font.rpt")); crys.DataSourceConnections[0].SetConnection("localhost", "DataReal", false); crys.DataSourceConnections[0].SetLogon("rekabio", "Meinardi"); //crys.VerifyDatabase(); crys.SetParameterValue("@KodeRapel", Session["strKodeRapel"].ToString()); crys.SetParameterValue("@sSqlFilter", strQuery); // crys.SetParameterValue("Tanggal", "BULAN JANUARI S.D. FEBRUARI 2018"); crys.SetParameterValue("TTD1.Header", TTD1Header); crys.SetParameterValue("TTD1", TTD1); crys.SetParameterValue("TTD2.Header", TTD2Header); crys.SetParameterValue("TTD2", TTD2); crys.SetParameterValue("TTD3.Header", TTD3Header); crys.SetParameterValue("TTD3", TTD3); crys.SetParameterValue("Tanggal", "BULAN JANUARI S.D. FEBRUARI 2018"); crAllowanceRekap.DisplayGroupTree = false; crAllowanceRekap.ReportSource = crys; crAllowanceRekap.DataBind(); } } else if (Session["tipeRekap"].ToString() == "2") { SqlConnection cn = new SqlConnection(Application["strCn"].ToString()); string q = ""; string TTD1Header = ""; string TTD1 = ""; string TTD2Header = ""; string TTD2 = ""; string TTD3Header = ""; string TTD3 = ""; // q = "SELECT TTD1_HEADER, TTD1, TTD2_HEADER, TTD2, TTD3_HEADER, TTD3 FROM taLaporanPengesahan WHERE KODE_LAPORAN = '" + Session["strKodeRpt"] + "' AND KODE_CABANG='" + Session["CabangID"].ToString() + "'"; q = "SELECT TTD1_HEADER, TTD1, TTD2_HEADER, TTD2, TTD3_HEADER, TTD3 FROM taLaporanPengesahan WHERE KODE_LAPORAN = 'K2' AND KODE_CABANG='" + Session["CabangID"].ToString() + "'"; //Response.Write(q); SqlDataAdapter db2 = new SqlDataAdapter(q, cn); DataSet dsPengesahan = new DataSet(); db2.Fill(dsPengesahan); if (dsPengesahan.Tables[0].Rows.Count > 0) { DataTable tblPengesahan = dsPengesahan.Tables[0]; foreach (DataRow myRow in tblPengesahan.Rows) { TTD1Header = myRow["TTD1_HEADER"].ToString(); TTD1 = myRow["TTD1"].ToString(); TTD2Header = myRow["TTD2_HEADER"].ToString(); TTD2 = myRow["TTD2"].ToString(); TTD3Header = myRow["TTD3_HEADER"].ToString(); TTD3 = myRow["TTD3"].ToString(); } } DataSet ds = new DataSet(); string kodeRapel = Session["strKodeRapel"].ToString(); using (SqlConnection openCon = new SqlConnection(Application["strCn"].ToString())) { crys = new ReportDocument(); crys.Load(Server.MapPath("App_Data/RekapRapelTK_font.rpt")); crys.DataSourceConnections[0].SetConnection("localhost", "DataReal", false); crys.DataSourceConnections[0].SetLogon("rekabio", "Meinardi"); //crys.VerifyDatabase(); crys.SetParameterValue("@KodeRapel", Session["strKodeRapel"].ToString()); crys.SetParameterValue("@sSqlFilter", strQuery); // crys.SetParameterValue("Tanggal", "BULAN JANUARI S.D. FEBRUARI 2018"); crys.SetParameterValue("TTD1.Header", TTD1Header); crys.SetParameterValue("TTD1", TTD1); crys.SetParameterValue("TTD2.Header", TTD2Header); crys.SetParameterValue("TTD2", TTD2); crys.SetParameterValue("TTD3.Header", TTD3Header); crys.SetParameterValue("TTD3", TTD3); crys.SetParameterValue("Tanggal", "BULAN JANUARI S.D. FEBRUARI 2018"); crAllowanceRekap.DisplayGroupTree = false; crAllowanceRekap.ReportSource = crys; crAllowanceRekap.DataBind(); } } else if (Session["tipeRekap"].ToString() == "3") { SqlConnection cn = new SqlConnection(Application["strCn"].ToString()); string q = ""; string TTD1Header = ""; string TTD1 = ""; string TTD2Header = ""; string TTD2 = ""; string TTD3Header = ""; string TTD3 = ""; // q = "SELECT TTD1_HEADER, TTD1, TTD2_HEADER, TTD2, TTD3_HEADER, TTD3 FROM taLaporanPengesahan WHERE KODE_LAPORAN = '" + Session["strKodeRpt"] + "' AND KODE_CABANG='" + Session["CabangID"].ToString() + "'"; q = "SELECT TTD1_HEADER, TTD1, TTD2_HEADER, TTD2, TTD3_HEADER, TTD3 FROM taLaporanPengesahan WHERE KODE_LAPORAN = 'K3' AND KODE_CABANG='" + Session["CabangID"].ToString() + "'"; //Response.Write(q); SqlDataAdapter db2 = new SqlDataAdapter(q, cn); DataSet dsPengesahan = new DataSet(); db2.Fill(dsPengesahan); if (dsPengesahan.Tables[0].Rows.Count > 0) { DataTable tblPengesahan = dsPengesahan.Tables[0]; foreach (DataRow myRow in tblPengesahan.Rows) { TTD1Header = myRow["TTD1_HEADER"].ToString(); TTD1 = myRow["TTD1"].ToString(); TTD2Header = myRow["TTD2_HEADER"].ToString(); TTD2 = myRow["TTD2"].ToString(); TTD3Header = myRow["TTD3_HEADER"].ToString(); TTD3 = myRow["TTD3"].ToString(); } } DataSet ds = new DataSet(); string kodeRapel = Session["strKodeRapel"].ToString(); using (SqlConnection openCon = new SqlConnection(Application["strCn"].ToString())) { crys = new ReportDocument(); crys.Load(Server.MapPath("App_Data/RekapRapelTKperUnit_font.rpt")); crys.DataSourceConnections[0].SetConnection("localhost", "DataReal", false); crys.DataSourceConnections[0].SetLogon("rekabio", "Meinardi"); crys.SetParameterValue("@KodeRapel", Session["strKodeRapel"].ToString()); crys.SetParameterValue("@sSqlFilter", strQuery); crys.SetParameterValue("TTD1.Header", ""); crys.SetParameterValue("TTD1", ""); crys.SetParameterValue("TTD2.Header", ""); crys.SetParameterValue("TTD2", ""); crys.SetParameterValue("TTD3.Header", ""); crys.SetParameterValue("TTD3", ""); crys.SetParameterValue("Tanggal", "BULAN JANUARI S.D. FEBRUARI 2018"); crys.SetParameterValue("Kelompok", ""); crys.SetParameterValue("Satker", ""); crAllowanceRekap.DisplayGroupTree = false; crAllowanceRekap.ReportSource = crys; crAllowanceRekap.DataBind(); openCon.Close(); } } }
private void SaveRecord() { if (txtLocationPoint.Text.Trim() == "") { MessageBox.Show("Please enter service description.", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Exclamation); txtLocationPoint.Focus(); return; } bsLocation.EndEdit(); dtExtDataLocation.Rows[0]["LocationPoint"] = txtLocationPoint.Text; DataTable dt = dtExtDataLocation.GetChanges(); if (dt == null) { MessageBox.Show("No data to be saved.", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return; } dt = null; SqlConnection sqlcnn = PSSClass.DBConnection.PSSConnection(); if (sqlcnn == null) { MessageBox.Show("Connection problem encountered." + Environment.NewLine + "Please try again later.", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return; } SqlDataAdapter da = new SqlDataAdapter("Select * From EMLocations", sqlcnn); SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(da); dt = dtExtDataLocation.GetChanges(DataRowState.Added); if (dt != null) { dtExtDataLocation.Rows[0]["LocationID"] = PSSClass.DataEntry.NewID("EMLocations", "LocationID").ToString(); cmdBuilder.GetInsertCommand(); da.Update(dtExtDataLocation); } dt = null; dt = dtExtDataLocation.GetChanges(DataRowState.Modified); if (dt != null) { dtExtDataLocation.Rows[0]["LastUpdate"] = DateTime.Now; dtExtDataLocation.Rows[0]["LastUserID"] = 1; cmdBuilder.GetUpdateCommand(); da.Update(dtExtDataLocation); dt.Dispose(); } da.Dispose(); cmdBuilder.Dispose(); sqlcnn.Close(); sqlcnn.Dispose(); pnlRecord.Visible = false; dgvFile.Visible = true; bnFile.Enabled = true; LoadRecords(); PSSClass.General.FindRecord("LocationID", txtLocId.Text, bsFile, dgvFile); // ClearControls(this.pnlRecord); AddEditMode(false); //Initialize Toolbar //Reload User's Access to this file - included in this function for sudden change in access level if (strFileAccess == "RO") { tsbAdd.Enabled = false; tsbEdit.Enabled = false; } else if (strFileAccess == "RW") { tsbAdd.Enabled = true; tsbEdit.Enabled = true; } else if (strFileAccess == "FA") { tsbAdd.Enabled = true; tsbEdit.Enabled = true; tsbDelete.Enabled = true; } nMode = 0; }
protected void Page_Load(object sender, EventArgs e) { using (SqlConnection connection = new SqlConnection(hashed.constring)) { if (Session["user"] != null) { SqlCommand cmd = connection.CreateCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = "SELECT * FROM Workouts_table "; connection.Open(); DataTable dt = new DataTable(); SqlDataAdapter sqladapter = new SqlDataAdapter(cmd); sqladapter.Fill(dt); //query string r = dt.Rows[0]["WorkoutName"].ToString(); if (Request.QueryString["selected"].ToString() == "beginer1") { if (Request.QueryString["action"] != null) { active.Text = "Delete from your training"; } name.Text = "<b>Training : " + Request.QueryString["selected"].ToString() + "</b>"; // <------------ Leg variables -----------------> string legn1, legn2, legd1, legd2, legp1, legp2, legb1, legb2; legn1 = legn2 = legd1 = legd2 = legp1 = legp2 = legb1 = legb2 = ""; // <------------ abs variables -----------------> string absn1, absn2, abs1d, abs2d, abs1p, abs2p, absb1, absb2; absn1 = absn2 = abs1d = abs2d = abs1p = abs2p = absb1 = absb2 = ""; // <------------ Back variables -----------------> string backn1, backn2, back1d, back2d, back1p, back2p, backb1, backb2; backn1 = backn2 = back1d = back2d = back1p = back2p = backb1 = backb2 = ""; // <------------ Biceps variables -----------------> string bicepsn1, bicepsn2, biceps1d, biceps2d, biceps1p, biceps2p, bicepsb1, bicepsb2; bicepsn1 = bicepsn2 = biceps1d = biceps2d = biceps1p = biceps2p = bicepsb1 = bicepsb2 = ""; // <------------ Chest variables -----------------> string chestn1, chestn2, chest1d, chest2d, chest1p, chest2p, chestb1, chestb2; chestn1 = chestn2 = chest1d = chest2d = chest1p = chest2p = chestb1 = chestb2 = ""; // <------------ Triceps variables -----------------> string tricepsn1, tricepsn2, triceps1d, triceps2d, triceps1p, triceps2p, tricepsb1, tricepsb2; tricepsn1 = tricepsn2 = triceps1d = triceps2d = triceps1p = triceps2p = tricepsb1 = tricepsb2 = ""; for (int i = 0; i < 17; i++) { if (i == 0) { legn1 = dt.Rows[i]["WorkoutName"].ToString(); legd1 = dt.Rows[i]["Description"].ToString(); legp1 = dt.Rows[i]["Workout_image"].ToString(); legb1 = dt.Rows[i]["Repetitions"].ToString(); } if (i == 2) { legn2 = dt.Rows[i]["WorkoutName"].ToString(); legd2 = dt.Rows[i]["Description"].ToString(); legp2 = dt.Rows[i]["Workout_image"].ToString(); legb2 = dt.Rows[i]["Repetitions"].ToString(); } if (i == 4) { absn1 = dt.Rows[i]["WorkoutName"].ToString(); abs1d = dt.Rows[i]["Description"].ToString(); abs1p = dt.Rows[i]["Workout_image"].ToString(); absb1 = dt.Rows[i]["Repetitions"].ToString(); } if (i == 3) { absn2 = dt.Rows[i]["WorkoutName"].ToString(); abs2d = dt.Rows[i]["Description"].ToString(); abs2p = dt.Rows[i]["Workout_image"].ToString(); absb2 = dt.Rows[i]["Repetitions"].ToString(); } if (i == 8) { backn1 = dt.Rows[i]["WorkoutName"].ToString(); back1d = dt.Rows[i]["Description"].ToString(); back1p = dt.Rows[i]["Workout_image"].ToString(); backb1 = dt.Rows[i]["Repetitions"].ToString(); } if (i == 6) { backn2 = dt.Rows[i]["WorkoutName"].ToString(); back2d = dt.Rows[i]["Description"].ToString(); back2p = dt.Rows[i]["Workout_image"].ToString(); backb2 = dt.Rows[i]["Repetitions"].ToString(); } if (i == 9) { bicepsn1 = dt.Rows[i]["WorkoutName"].ToString(); biceps1d = dt.Rows[i]["Description"].ToString(); biceps1p = dt.Rows[i]["Workout_image"].ToString(); bicepsb1 = dt.Rows[i]["Repetitions"].ToString(); } if (i == 11) { bicepsn2 = dt.Rows[i]["WorkoutName"].ToString(); biceps2d = dt.Rows[i]["Description"].ToString(); biceps2p = dt.Rows[i]["Workout_image"].ToString(); bicepsb2 = dt.Rows[i]["Repetitions"].ToString(); } if (i == 12) { tricepsn1 = dt.Rows[i]["WorkoutName"].ToString(); triceps1d = dt.Rows[i]["Description"].ToString(); triceps1p = dt.Rows[i]["Workout_image"].ToString(); tricepsb1 = dt.Rows[i]["Repetitions"].ToString(); } if (i == 13) { tricepsn2 = dt.Rows[i]["WorkoutName"].ToString(); triceps2d = dt.Rows[i]["Description"].ToString(); triceps2p = dt.Rows[i]["Workout_image"].ToString(); tricepsb2 = dt.Rows[i]["Repetitions"].ToString(); } if (i == 15) { chestn1 = dt.Rows[i]["WorkoutName"].ToString(); chest1d = dt.Rows[i]["Description"].ToString(); chest1p = dt.Rows[i]["Workout_image"].ToString(); chestb1 = dt.Rows[i]["Repetitions"].ToString(); } if (i == 16) { chestn2 = dt.Rows[i]["WorkoutName"].ToString(); chest2d = dt.Rows[i]["Description"].ToString(); chest2p = dt.Rows[i]["Workout_image"].ToString(); chestb2 = dt.Rows[i]["Repetitions"].ToString(); } // <------------------------- legs labels -----------------------> lblleg1.Text = legn1; lblleg1d.Text = legd1; lblleg1p.ImageUrl = legp1; lblleg2.Text = legn2; lblleg2d.Text = legd2; lblleg2p.ImageUrl = legp2; btnlegs1.Text = "repetitions :" + legb1.ToString(); btnlegs2.Text = "repetitions :" + legb2.ToString(); // <------------------------- abs labels-----------------------> lblabs1.Text = absn1; lblabsd1.Text = abs1d; lblabsp1.ImageUrl = abs1p; lblabs2.Text = absn2; lblabsd2.Text = abs2d; lblabsp2.ImageUrl = abs2p; btnabs1.Text = "repetitions :" + absb1.ToString(); btnabs2.Text = "repetitions :" + absb2.ToString(); // <------------------------- back labels-----------------------> lblback1.Text = backn1; lblbackd1.Text = back1d; lblbackp1.ImageUrl = back1p; lblback2.Text = backn2; lblbackd2.Text = back2d; lblbackp2.ImageUrl = back2p; btnback1.Text = "repetitions :" + backb1.ToString(); btnback2.Text = "repetitions :" + backb1.ToString(); // <------------------------- Chest labels-----------------------> lblchest1.Text = chestn1; lblchestd1.Text = chest1d; lblchestp1.ImageUrl = chest1p; lblchest2.Text = chestn2; lblchestd2.Text = chest2d; lblchestp2.ImageUrl = chest2p; btnchest1.Text = "repetitions :" + chestb1.ToString(); btnchest2.Text = "repetitions :" + chestb1.ToString(); // <------------------------- biceps labels-----------------------> lblbiceps1.Text = bicepsn1; lblbicepsd1.Text = biceps1d; lblbicepsp1.ImageUrl = biceps1p; lblbiceps2.Text = bicepsn2; lblbicepsd2.Text = biceps2d; lblbicepsp2.ImageUrl = biceps2p; btnbiceps1.Text = "repetitions :" + bicepsb1.ToString(); btnbiceps2.Text = "repetitions :" + bicepsb1.ToString(); // <------------------------- triceps labels-----------------------> lbltriceps1.Text = tricepsn1; lbltricepsd1.Text = triceps1d; lbltricepsp1.ImageUrl = triceps1p; lbltriceps2.Text = tricepsn2; lbltricepsd2.Text = triceps2d; lbltricepsp2.ImageUrl = triceps2p; btntriceps1.Text = "repetitions :" + tricepsb1.ToString(); btntriceps2.Text = "repetitions :" + tricepsb1.ToString(); } } if (Request.QueryString["selected"].ToString() == "intermediate1") { if (Request.QueryString["action"] != null) { active.Text = "Delete from your training"; } name.Text = "<b>Training : " + Request.QueryString["selected"].ToString() + "</b>"; // <------------ Leg variables -----------------> string legn1, legn2, legd1, legd2, legp1, legp2, legb1, legb2; legn1 = legn2 = legd1 = legd2 = legp1 = legp2 = legb1 = legb2 = ""; // <------------ abs variables -----------------> string absn1, absn2, abs1d, abs2d, abs1p, abs2p, absb1, absb2; absn1 = absn2 = abs1d = abs2d = abs1p = abs2p = absb1 = absb2 = ""; // <------------ Back variables -----------------> string backn1, backn2, back1d, back2d, back1p, back2p, backb1, backb2; backn1 = backn2 = back1d = back2d = back1p = back2p = backb1 = backb2 = ""; // <------------ Biceps variables -----------------> string bicepsn1, bicepsn2, biceps1d, biceps2d, biceps1p, biceps2p, bicepsb1, bicepsb2; bicepsn1 = bicepsn2 = biceps1d = biceps2d = biceps1p = biceps2p = bicepsb1 = bicepsb2 = ""; // <------------ Chest variables -----------------> string chestn1, chestn2, chest1d, chest2d, chest1p, chest2p, chestb1, chestb2; chestn1 = chestn2 = chest1d = chest2d = chest1p = chest2p = chestb1 = chestb2 = ""; // <------------ Triceps variables -----------------> string tricepsn1, tricepsn2, triceps1d, triceps2d, triceps1p, triceps2p, tricepsb1, tricepsb2; tricepsn1 = tricepsn2 = triceps1d = triceps2d = triceps1p = triceps2p = tricepsb1 = tricepsb2 = ""; for (int i = 0; i < 17; i++) { if (i == 1) { legn1 = dt.Rows[i]["WorkoutName"].ToString(); legd1 = dt.Rows[i]["Description"].ToString(); legp1 = dt.Rows[i]["Workout_image"].ToString(); legb1 = dt.Rows[i]["Repetitions"].ToString(); } if (i == 0) { legn2 = dt.Rows[i]["WorkoutName"].ToString(); legd2 = dt.Rows[i]["Description"].ToString(); legp2 = dt.Rows[i]["Workout_image"].ToString(); legb2 = dt.Rows[i]["Repetitions"].ToString(); } if (i == 3) { absn1 = dt.Rows[i]["WorkoutName"].ToString(); abs1d = dt.Rows[i]["Description"].ToString(); abs1p = dt.Rows[i]["Workout_image"].ToString(); absb1 = dt.Rows[i]["Repetitions"].ToString(); } if (i == 5) { absn2 = dt.Rows[i]["WorkoutName"].ToString(); abs2d = dt.Rows[i]["Description"].ToString(); abs2p = dt.Rows[i]["Workout_image"].ToString(); absb2 = dt.Rows[i]["Repetitions"].ToString(); } if (i == 6) { backn1 = dt.Rows[i]["WorkoutName"].ToString(); back1d = dt.Rows[i]["Description"].ToString(); back1p = dt.Rows[i]["Workout_image"].ToString(); backb1 = dt.Rows[i]["Repetitions"].ToString(); } if (i == 7) { backn2 = dt.Rows[i]["WorkoutName"].ToString(); back2d = dt.Rows[i]["Description"].ToString(); back2p = dt.Rows[i]["Workout_image"].ToString(); backb2 = dt.Rows[i]["Repetitions"].ToString(); } if (i == 9) { bicepsn1 = dt.Rows[i]["WorkoutName"].ToString(); biceps1d = dt.Rows[i]["Description"].ToString(); biceps1p = dt.Rows[i]["Workout_image"].ToString(); bicepsb1 = dt.Rows[i]["Repetitions"].ToString(); } if (i == 11) { bicepsn2 = dt.Rows[i]["WorkoutName"].ToString(); biceps2d = dt.Rows[i]["Description"].ToString(); biceps2p = dt.Rows[i]["Workout_image"].ToString(); bicepsb2 = dt.Rows[i]["Repetitions"].ToString(); } if (i == 12) { tricepsn1 = dt.Rows[i]["WorkoutName"].ToString(); triceps1d = dt.Rows[i]["Description"].ToString(); triceps1p = dt.Rows[i]["Workout_image"].ToString(); tricepsb1 = dt.Rows[i]["Repetitions"].ToString(); } if (i == 13) { tricepsn2 = dt.Rows[i]["WorkoutName"].ToString(); triceps2d = dt.Rows[i]["Description"].ToString(); triceps2p = dt.Rows[i]["Workout_image"].ToString(); tricepsb2 = dt.Rows[i]["Repetitions"].ToString(); } if (i == 15) { chestn1 = dt.Rows[i]["WorkoutName"].ToString(); chest1d = dt.Rows[i]["Description"].ToString(); chest1p = dt.Rows[i]["Workout_image"].ToString(); chestb1 = dt.Rows[i]["Repetitions"].ToString(); } if (i == 16) { chestn2 = dt.Rows[i]["WorkoutName"].ToString(); chest2d = dt.Rows[i]["Description"].ToString(); chest2p = dt.Rows[i]["Workout_image"].ToString(); chestb2 = dt.Rows[i]["Repetitions"].ToString(); } // <------------------------- legs labels -----------------------> lblleg1.Text = legn1; lblleg1d.Text = legd1; lblleg1p.ImageUrl = legp1; lblleg2.Text = legn2; lblleg2d.Text = legd2; lblleg2p.ImageUrl = legp2; btnlegs1.Text = "repetitions :" + legb1.ToString(); btnlegs2.Text = "repetitions :" + legb2.ToString(); // <------------------------- abs labels-----------------------> lblabs1.Text = absn1; lblabsd1.Text = abs1d; lblabsp1.ImageUrl = abs1p; lblabs2.Text = absn2; lblabsd2.Text = abs2d; lblabsp2.ImageUrl = abs2p; btnabs1.Text = "repetitions :" + absb1.ToString(); btnabs2.Text = "repetitions :" + absb2.ToString(); // <------------------------- back labels-----------------------> lblback1.Text = backn1; lblbackd1.Text = back1d; lblbackp1.ImageUrl = back1p; lblback2.Text = backn2; lblbackd2.Text = back2d; lblbackp2.ImageUrl = back2p; btnback1.Text = "repetitions :" + backb1.ToString(); btnback2.Text = "repetitions :" + backb1.ToString(); // <------------------------- Chest labels-----------------------> lblchest1.Text = chestn1; lblchestd1.Text = chest1d; lblchestp1.ImageUrl = chest1p; lblchest2.Text = chestn2; lblchestd2.Text = chest2d; lblchestp2.ImageUrl = chest2p; btnchest1.Text = "repetitions :" + chestb1.ToString(); btnchest2.Text = "repetitions :" + chestb1.ToString(); // <------------------------- biceps labels-----------------------> lblbiceps1.Text = bicepsn1; lblbicepsd1.Text = biceps1d; lblbicepsp1.ImageUrl = biceps1p; lblbiceps2.Text = bicepsn2; lblbicepsd2.Text = biceps2d; lblbicepsp2.ImageUrl = biceps2p; btnbiceps1.Text = "repetitions :" + bicepsb1.ToString(); btnbiceps2.Text = "repetitions :" + bicepsb1.ToString(); // <------------------------- triceps labels-----------------------> lbltriceps1.Text = tricepsn1; lbltricepsd1.Text = triceps1d; lbltricepsp1.ImageUrl = triceps1p; lbltriceps2.Text = tricepsn2; lbltricepsd2.Text = triceps2d; lbltricepsp2.ImageUrl = triceps2p; btntriceps1.Text = "repetitions :" + tricepsb1.ToString(); btntriceps2.Text = "repetitions :" + tricepsb1.ToString(); } } if (Request.QueryString["selected"].ToString() == "advanced1") { if (Request.QueryString["action"] != null) { active.Text = "Delete from your training"; } name.Text = "<b>Training : " + Request.QueryString["selected"].ToString() + "</b>"; // <------------ Leg variables -----------------> string legn1, legn2, legd1, legd2, legp1, legp2,legb1,legb2; legn1 = legn2 = legd1 = legd2 = legp1 = legp2=legb1=legb2 = ""; // <------------ abs variables -----------------> string absn1, absn2, abs1d, abs2d, abs1p, abs2p,absb1,absb2; absn1 = absn2 = abs1d = abs2d = abs1p = abs2p=absb1=absb2 = ""; // <------------ Back variables -----------------> string backn1, backn2, back1d, back2d, back1p, back2p,backb1,backb2; backn1 = backn2 = back1d = back2d = back1p = back2p=backb1=backb2 = ""; // <------------ Biceps variables -----------------> string bicepsn1, bicepsn2, biceps1d, biceps2d, biceps1p, biceps2p,bicepsb1,bicepsb2; bicepsn1 = bicepsn2 = biceps1d = biceps2d = biceps1p = biceps2p=bicepsb1=bicepsb2 = ""; // <------------ Chest variables -----------------> string chestn1, chestn2, chest1d, chest2d, chest1p, chest2p,chestb1,chestb2; chestn1 = chestn2 = chest1d = chest2d = chest1p = chest2p=chestb1=chestb2 = ""; // <------------ Triceps variables -----------------> string tricepsn1, tricepsn2, triceps1d, triceps2d, triceps1p, triceps2p,tricepsb1,tricepsb2; tricepsn1 = tricepsn2 = triceps1d = triceps2d = triceps1p = triceps2p=tricepsb1=tricepsb2 = ""; for (int i = 0; i < 17; i++) { if (i == 2) { legn1 = dt.Rows[i]["WorkoutName"].ToString(); legd1 = dt.Rows[i]["Description"].ToString(); legp1 = dt.Rows[i]["Workout_image"].ToString(); legb1 = dt.Rows[i]["Repetitions"].ToString(); } if (i == 0) { legn2 = dt.Rows[i]["WorkoutName"].ToString(); legd2 = dt.Rows[i]["Description"].ToString(); legp2 = dt.Rows[i]["Workout_image"].ToString(); legb2 = dt.Rows[i]["Repetitions"].ToString(); } if (i == 3) { absn1 = dt.Rows[i]["WorkoutName"].ToString(); abs1d = dt.Rows[i]["Description"].ToString(); abs1p = dt.Rows[i]["Workout_image"].ToString(); absb1 = dt.Rows[i]["Repetitions"].ToString(); } if (i == 5) { absn2 = dt.Rows[i]["WorkoutName"].ToString(); abs2d = dt.Rows[i]["Description"].ToString(); abs2p = dt.Rows[i]["Workout_image"].ToString(); absb2 = dt.Rows[i]["Repetitions"].ToString(); } if (i == 7) { backn1 = dt.Rows[i]["WorkoutName"].ToString(); back1d = dt.Rows[i]["Description"].ToString(); back1p = dt.Rows[i]["Workout_image"].ToString(); backb1 = dt.Rows[i]["Repetitions"].ToString(); } if (i == 6) { backn2 = dt.Rows[i]["WorkoutName"].ToString(); back2d = dt.Rows[i]["Description"].ToString(); back2p = dt.Rows[i]["Workout_image"].ToString(); backb2 = dt.Rows[i]["Repetitions"].ToString(); } if (i == 9) { bicepsn1 = dt.Rows[i]["WorkoutName"].ToString(); biceps1d = dt.Rows[i]["Description"].ToString(); biceps1p = dt.Rows[i]["Workout_image"].ToString(); bicepsb1 = dt.Rows[i]["Repetitions"].ToString(); } if (i == 11) { bicepsn2 = dt.Rows[i]["WorkoutName"].ToString(); biceps2d = dt.Rows[i]["Description"].ToString(); biceps2p = dt.Rows[i]["Workout_image"].ToString(); bicepsb2 = dt.Rows[i]["Repetitions"].ToString(); } if (i == 12) { tricepsn1 = dt.Rows[i]["WorkoutName"].ToString(); triceps1d = dt.Rows[i]["Description"].ToString(); triceps1p = dt.Rows[i]["Workout_image"].ToString(); tricepsb1 = dt.Rows[i]["Repetitions"].ToString(); } if (i == 13) { tricepsn2 = dt.Rows[i]["WorkoutName"].ToString(); triceps2d = dt.Rows[i]["Description"].ToString(); triceps2p = dt.Rows[i]["Workout_image"].ToString(); tricepsb2 = dt.Rows[i]["Repetitions"].ToString(); } if (i == 15) { chestn1 = dt.Rows[i]["WorkoutName"].ToString(); chest1d = dt.Rows[i]["Description"].ToString(); chest1p = dt.Rows[i]["Workout_image"].ToString(); chestb1 = dt.Rows[i]["Repetitions"].ToString(); } if (i == 16) { chestn2 = dt.Rows[i]["WorkoutName"].ToString(); chest2d = dt.Rows[i]["Description"].ToString(); chest2p = dt.Rows[i]["Workout_image"].ToString(); chestb2 = dt.Rows[i]["Repetitions"].ToString(); } // <------------------------- legs labels -----------------------> lblleg1.Text = legn1; lblleg1d.Text = legd1; lblleg1p.ImageUrl = legp1; lblleg2.Text = legn2; lblleg2d.Text = legd2; lblleg2p.ImageUrl = legp2; btnlegs1.Text = "repetitions :" + legb1.ToString(); btnlegs2.Text = "repetitions :" + legb2.ToString(); // <------------------------- abs labels-----------------------> lblabs1.Text = absn1; lblabsd1.Text = abs1d; lblabsp1.ImageUrl = abs1p; lblabs2.Text = absn2; lblabsd2.Text = abs2d; lblabsp2.ImageUrl = abs2p; btnabs1.Text = "repetitions :" + absb1.ToString(); btnabs2.Text = "repetitions :" + absb2.ToString(); // <------------------------- back labels-----------------------> lblback1.Text = backn1; lblbackd1.Text = back1d; lblbackp1.ImageUrl = back1p; lblback2.Text = backn2; lblbackd2.Text = back2d; lblbackp2.ImageUrl = back2p; btnback1.Text = "repetitions :" + backb1.ToString(); btnback2.Text = "repetitions :" + backb1.ToString(); // <------------------------- Chest labels-----------------------> lblchest1.Text = chestn1; lblchestd1.Text = chest1d; lblchestp1.ImageUrl = chest1p; lblchest2.Text = chestn2; lblchestd2.Text = chest2d; lblchestp2.ImageUrl = chest2p; btnchest1.Text = "repetitions :" + chestb1.ToString(); btnchest2.Text = "repetitions :" + chestb1.ToString(); // <------------------------- biceps labels-----------------------> lblbiceps1.Text = bicepsn1; lblbicepsd1.Text = biceps1d; lblbicepsp1.ImageUrl = biceps1p; lblbiceps2.Text = bicepsn2; lblbicepsd2.Text = biceps2d; lblbicepsp2.ImageUrl = biceps2p; btnbiceps1.Text = "repetitions :" + bicepsb1.ToString(); btnbiceps2.Text = "repetitions :" + bicepsb1.ToString(); // <------------------------- triceps labels-----------------------> lbltriceps1.Text = tricepsn1; lbltricepsd1.Text = triceps1d; lbltricepsp1.ImageUrl = triceps1p; lbltriceps2.Text = tricepsn2; lbltricepsd2.Text = triceps2d; lbltricepsp2.ImageUrl = triceps2p; btntriceps1.Text = "repetitions :" + tricepsb1.ToString(); btntriceps2.Text = "repetitions :" + tricepsb1.ToString(); } } } else { Response.Redirect("login.aspx"); } } }
private void LoadFoodTaste() { System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(); conn.ConnectionString = rms_var.ConnStr; try { SqlCommand selectCMD = new SqlCommand("SELECT name FROM foodtaste", conn); selectCMD.CommandTimeout = 30; SqlDataAdapter dbDA = new SqlDataAdapter(); dbDA.SelectCommand = selectCMD; conn.Open(); DataSet dbDS = new DataSet(); dbDA.Fill(dbDS, "t"); //clbTaste.Items.Clear() //For i As Integer = 0 To dbDS.Tables(0).Rows.Count - 1 //clbTaste.Items.Add(dbDS.Tables(0).Rows(i).Item(0), False) //Next } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { conn.Close(); } }
internal KensaIraiTblDataSet.EnkabutsuIonNodoHikakuListDataTable GetDataBySearchCond( string saisuiDtFrom, string saisuiDtTo, string saisuiinNm) { SqlCommand command = CreateSqlCommand(saisuiDtFrom, saisuiDtTo, saisuiinNm); SqlDataAdapter adpt = new SqlDataAdapter(command); adpt.SelectCommand.Connection = this.Connection; KensaIraiTblDataSet.EnkabutsuIonNodoHikakuListDataTable dataTable = new KensaIraiTblDataSet.EnkabutsuIonNodoHikakuListDataTable(); adpt.Fill(dataTable); return dataTable; }
public static SchemaData GenerateSchemaObjects(string entityName, string ConnectionString) { try { SchemaData schema = new SchemaData() { Name = entityName }; using (SqlConnection connection = new SqlConnection(ConnectionString)) { if (connection.State == ConnectionState.Closed) { connection.Open(); } SqlCommand cmdCompat11 = new SqlCommand("DECLARE @sql NVARCHAR(1000) = 'ALTER DATABASE ' + DB_NAME() + ' SET COMPATIBILITY_LEVEL = 110'; EXECUTE sp_executesql @sql", connection); cmdCompat11.CommandType = CommandType.Text; cmdCompat11.ExecuteNonQuery(); using (SqlCommand cmd = new SqlCommand(FKSQL, connection)) { cmd.CommandType = CommandType.Text; var ds = new DataSet(); var adapter = new SqlDataAdapter(cmd); adapter.Fill(ds); var tableColumnData = ds.Tables[0]; var CurrentEntityName = ""; EntityType entityType = null; PrimaryKeyProperties primaryKeyList = null; foreach (DataRow row in tableColumnData.Rows) { var tableName = row["TABLENAME"].ToString(); if (CurrentEntityName != tableName) { if (CurrentEntityName.Length > 0) { //Temporal views are handled below if ((!entityType.HasPrimaryKeys() && (!entityType.IsTemporalView) && (entityType.TemporalType != "HISTORY_TABLE"))) { Console.WriteLine("Warning... '" + CurrentEntityName + "' no primary keys.. adding all as primary keys"); int order = 0; foreach (var prop in entityType.Properties.Values) { order++; prop.IsKey = true; prop.KeyOrder = order; entityType.PrimaryKeys.Add(prop); } } schema.Add(CurrentEntityName, entityType); } entityType = new EntityType() { Name = tableName , Type = row["OBJECT_TYPE"].ToString() , Schema = row["SCHEMANAME"].ToString() , IsTemporalView = ((tableName.EndsWith("TemporalView")) && (row["OBJECT_TYPE"].ToString() == "VIEW")) , TemporalType = (row["TEMPORAL_TYPE_DESC"] == DBNull.Value ? "" : row["TEMPORAL_TYPE_DESC"].ToString()) }; primaryKeyList = new PrimaryKeyProperties(entityType); entityType.PrimaryKeys = primaryKeyList; CurrentEntityName = tableName; } Property property = new Property() { IsNullable = (bool)row["IS_NULLABLE"] }; property.Name = (row["COLUMNNAME"] == DBNull.Value ? "" : row["COLUMNNAME"].ToString()); property.MaxLength = (int)(row["CHARACTER_MAXIMUM_LENGTH"] == DBNull.Value ? 0 : row["CHARACTER_MAXIMUM_LENGTH"]); property.Precision = (int)(row["NUMERIC_PRECISION"] == DBNull.Value ? 0 : Convert.ToInt32(row["NUMERIC_PRECISION"])); property.Scale = (int)(row["NUMERIC_SCALE"] == DBNull.Value ? 0 : Convert.ToInt32(row["NUMERIC_SCALE"])); property.IsIdentity = (bool)(row["IS_IDENTITY"] == DBNull.Value ? false : row["IS_IDENTITY"]); property.IsKey = (bool)(row["PRIMARY_KEY_ORDER"] == DBNull.Value ? false : true); property.KeyOrder = (int)(row["PRIMARY_KEY_ORDER"] == DBNull.Value ? 0 : Convert.ToInt32(row["PRIMARY_KEY_ORDER"])); if ((entityType.IsTemporalView) && ((property.Name == "SysEndTime") || (property.Name == "SysStartTime"))) { property.IsKey = true; property.KeyOrder = ((property.Name == "SysStartTime") ? 1 : 2); } property.Type = (row["DATA_TYPE"] == DBNull.Value ? "" : row["DATA_TYPE"].ToString()); if (property.IsKey) { entityType.PrimaryKeys.Add(property); } entityType.Properties.Add(property.Name, property); } if (CurrentEntityName.Length > 0) { //Temporal views are handled abolve if ((!entityType.HasPrimaryKeys() && (!entityType.IsTemporalView) && (entityType.TemporalType != "HISTORY_TABLE"))) { Console.Write("Warning... '" + CurrentEntityName + "' no primary keys.. adding all as primary keys"); int order = 0; foreach (var prop in entityType.Properties.Values) { order++; prop.IsKey = true; prop.KeyOrder = order; entityType.PrimaryKeys.Add(prop); } } schema.Add(CurrentEntityName, entityType); } var tableFKeyData = ds.Tables[1]; foreach (DataRow row in tableFKeyData.Rows) { var tableName = row["EntityTable"].ToString(); string fromEntity = (row["EntityTable"] == DBNull.Value ? "" : row["EntityTable"].ToString()); string fromEntityField = (row["EntityColumn"] == DBNull.Value ? "" : row["EntityColumn"].ToString()); string toEntity = (row["RelatedTable"] == DBNull.Value ? "" : row["RelatedTable"].ToString()); string toEntityField = (row["RelatedColumn"] == DBNull.Value ? "" : row["RelatedColumn"].ToString()); string toEntityColumnName = toEntityField.AsFormattedName(); string fromEntityColumnName = fromEntityField.AsFormattedName(); string multiplicity = (row["Multiplicity"] == DBNull.Value ? "" : row["Multiplicity"].ToString()); var newRel = new Relationship() { Name = row["FK_Name"] == DBNull.Value ? "" : row["FK_Name"].ToString(), FromTableName = fromEntity, FromFieldName = fromEntityField, FromColumnName = fromEntityColumnName, ToFieldName = toEntityField, ToTableName = toEntity, ToColumnName = toEntityColumnName, Type = multiplicity }; schema[tableName].Relationships.Add(newRel); var fieldToMarkRelation = (tableName.Equals(newRel.FromTableName) ? newRel.FromFieldName : newRel.ToFieldName); if (schema[tableName].Properties.ContainsKey(fieldToMarkRelation)) { schema[tableName].Properties[fieldToMarkRelation].RelatedTo.Add(newRel); } } } if (schema.ContainsKey("sysdiagrams")) { schema.Entities.Remove("sysdiagrams"); } if (schema.ContainsKey("DeploymentMetadata")) { schema.Entities.Remove("DeploymentMetadata"); } //REMOVE TEMPORAL TABLES foreach (var t in schema.Keys.Where(k => k.EndsWith("Temporal", StringComparison.OrdinalIgnoreCase)).ToArray()) { schema.Entities.Remove(t); } foreach (var t in schema.Keys.Where(k => k.ToLower().Contains("sysdiagram")).ToArray()) { schema.Entities.Remove(t); } foreach (var t in schema.Keys.Where(k => k.Contains("TemporalHistoryFor")).ToArray()) { schema.Entities.Remove(t); } //REMOVE Redgate tables foreach (var t in schema.Keys.Where(k => k.EndsWith("DeploymentMetadata", StringComparison.OrdinalIgnoreCase)).ToArray()) { schema.Entities.Remove(t); } var temporal = schema.Keys.Where(k => k.EndsWith("_Archive", StringComparison.OrdinalIgnoreCase)).ToArray(); foreach (var t in temporal) { schema.Entities.Remove(t); } SqlCommand cmdCompat13 = new SqlCommand("DECLARE @sql NVARCHAR(1000) = 'ALTER DATABASE ' + DB_NAME() + ' SET COMPATIBILITY_LEVEL = 130'; EXECUTE sp_executesql @sql", connection); cmdCompat13.CommandType = CommandType.Text; cmdCompat13.ExecuteNonQuery(); return(schema); } } catch (Exception) { throw; } }
public DataTable GetDatasetTable(string p_sqlCommand) { SqlDataAdapter daRecords = new SqlDataAdapter(p_sqlCommand, RMSGlobal.m_connectionString); DataTable dsRecords = new DataTable(); daRecords.Fill(dsRecords); return dsRecords; }
private void Btnagregar_Click(object sender, EventArgs e) { //CREAR LAFACTURA if (txtcodigo.Text != "") { if (c == 0) { c++; try { conexion.Abrirconexion(); if (conexion.Estado == 1) { string query = string.Format("AGREGARFACTURA"); SqlCommand comando = new SqlCommand(query, conexion.Conexion); comando.CommandType = CommandType.StoredProcedure; SqlDataAdapter adaptador = new SqlDataAdapter(comando); using (adaptador) { comando.Parameters.AddWithValue("@Empleado", txtidemp.Text); comando.Parameters.AddWithValue("@IdCliente", txtide.Text); comando.ExecuteNonQuery(); MessageBox.Show(" Datos Insertado "); } } } catch (Exception ex) { MessageBox.Show(" Datos No Insertado" + ex.Message); } /////////////////////////////////////////////// ////////////////////////////////////////////// //BUSCAR LA FACTURA PARA ASIGNAR EL IDFACTURA try { conexion.Abrirconexion(); if (conexion.Estado == 1) { tabla.Reset(); string query = string.Format(("select MAX(idFactura) as id from REGISTRO.Factura")); SqlCommand comando = new SqlCommand(query, conexion.Conexion); comando.CommandType = CommandType.Text; SqlDataAdapter adaptador = new SqlDataAdapter(comando); adaptador.Fill(tabla); if (tabla.Rows.Count > 0) { ClaseUser.Idfactura = Convert.ToInt32(tabla.Rows[0][0]); txtfactura.Text = tabla.Rows[0][0].ToString(); } else { MessageBox.Show("factura no encontrada"); } } } catch (Exception) { throw; } } //////////////////////////////////////////////// ////////////////////////////////////////////// //calculo //txttotal.Text = Convert.ToString((Convert.ToInt32(txtcantidad.Text)* Convert.ToInt32(txtprecio.Text))); ////////////////////////////////////////////// ////////////////////////////////////////////// //GUARDAR DETALLE FACTURA if (txtnombreprod.Text != "") { try { conexion.Abrirconexion(); if (conexion.Estado == 1) { string query = string.Format("AGREGARDETALLEFACTURA"); SqlCommand comando = new SqlCommand(query, conexion.Conexion); comando.CommandType = CommandType.StoredProcedure; SqlDataAdapter adaptador = new SqlDataAdapter(comando); using (adaptador) { comando.Parameters.AddWithValue("@EMPLEADO", ClaseUser.IdEmpleado); comando.Parameters.AddWithValue("@ID_PRODUCTO", txtcodigo.Text); comando.Parameters.AddWithValue("@IDFACTURA", txtfactura.Text); comando.Parameters.AddWithValue("@CANTIDAD", txtcantidad.Text); comando.Parameters.AddWithValue("@TOTAL", txttotal.Text); comando.ExecuteNonQuery(); MessageBox.Show(" Datos Insertado"); } } } catch (Exception ex) { MessageBox.Show(" Datos No Insertado" + ex.Message); } //actualizar el total de la factura try { tabla.Reset(); string query = string.Format(("select SUM(total) as total from REGISTRO.DetalleFactura where idFactura = {0}"), txtfactura.Text); SqlCommand comando = new SqlCommand(query, conexion.Conexion); comando.CommandType = CommandType.Text; SqlDataAdapter adaptador = new SqlDataAdapter(comando); adaptador.Fill(tabla); if (tabla.Rows.Count > 0) { suma = tabla.Rows[0][0].ToString(); MessageBox.Show("{0} suma", suma); comando.CommandText = string.Format("UPDATE REGISTRO.factura SET total={0} where idFactura={1}", suma, txtfactura.Text); comando.ExecuteNonQuery(); } else { MessageBox.Show("factura no encontrada"); } } catch (Exception ex) { MessageBox.Show(ex.Message); } } txtcodigo.Text = ""; txtcantidad.Text = ""; txtnombreprod.Text = ""; txttotal.Text = ""; txtprecio.Text = ""; txtcodigo.Focus(); dat.cargardetallefactura(dgvfactura); } }
private void button2_Click_1(object sender, EventArgs e) { int r = 0; string selstr; SqlCommand cmd; switch (flag)//flag=1为第一个下拉框的第一个文本,flag=11,为第一个下拉框的第二个文本,flag=12位第三个。第二个下拉框为flag=2; { case 1: //insert into student values('20172','fw','男','21','1','16516','计算机学院','31215') //string selstr = "insert into student values('2017ddddd','老子第4','男','21','1','16516','计算机学院','31215')"; selstr = "insert into student values('" + this.dataGridView1.Rows[r].Cells[0].Value.ToString() + "','" + this.dataGridView1.Rows[r].Cells[1].Value.ToString() + "','" + this.dataGridView1.Rows[r].Cells[2].Value.ToString() + "','" + this.dataGridView1.Rows[r].Cells[3].Value.ToString() + "','" + this.dataGridView1.Rows[r].Cells[4].Value.ToString() + "','" + this.dataGridView1.Rows[r].Cells[5].Value.ToString() + "','" + this.dataGridView1.Rows[r].Cells[6].Value.ToString() + "','" + this.dataGridView1.Rows[r].Cells[7].Value.ToString() + "')"; cmd = new SqlCommand(selstr, CreateSqlConn.con); CreateSqlConn.con.Open(); if (cmd.ExecuteNonQuery() > 0) { MessageBox.Show("增加数据成功!"); } else { MessageBox.Show("增加数据失败!"); } CreateSqlConn.con.Close(); break; case 11: //string selstr = "insert into student values('2017ddddd','老子第4','男','21','1','16516','计算机学院','31215')"; selstr = "insert into teacher values('" + this.dataGridView1.Rows[r].Cells[0].Value.ToString() + "','" + this.dataGridView1.Rows[r].Cells[1].Value.ToString() + "','" + this.dataGridView1.Rows[r].Cells[2].Value.ToString() + "','" + this.dataGridView1.Rows[r].Cells[3].Value.ToString() + "','" + this.dataGridView1.Rows[r].Cells[4].Value.ToString() + "','" + this.dataGridView1.Rows[r].Cells[5].Value.ToString() + "','" + this.dataGridView1.Rows[r].Cells[6].Value.ToString() + "','" + this.dataGridView1.Rows[r].Cells[7].Value.ToString() + "')"; cmd = new SqlCommand(selstr, CreateSqlConn.con); CreateSqlConn.con.Open(); if (cmd.ExecuteNonQuery() > 0) { MessageBox.Show("增加数据成功!"); } else { MessageBox.Show("增加数据失败!"); } CreateSqlConn.con.Close(); break; case 12: //课程表(课程号,课程名,课程类型,课时); // Course(CNo,CName,CType,CTime); selstr = "insert into course values('" + this.dataGridView1.Rows[r].Cells[0].Value.ToString() + "','" + this.dataGridView1.Rows[r].Cells[1].Value.ToString() + "','" + this.dataGridView1.Rows[r].Cells[2].Value.ToString() + "','" + this.dataGridView1.Rows[r].Cells[3].Value.ToString() + "')"; cmd = new SqlCommand(selstr, CreateSqlConn.con); CreateSqlConn.con.Open(); if (cmd.ExecuteNonQuery() > 0) { MessageBox.Show("增加数据成功!"); } else { MessageBox.Show("增加数据失败!"); } CreateSqlConn.con.Close(); break; case 2: selstr = "select sno as 学号,sname as 姓名,ssex as 性别,sage as 年龄 ,cclass as 班级,sphone 电话号码,dname as 学院名,scode as 登录密码 from student"; cmd = new SqlCommand(selstr, CreateSqlConn.con); CreateSqlConn.con.Open(); cmd.ExecuteNonQuery(); SqlDataAdapter da = new SqlDataAdapter(selstr, CreateSqlConn.con); DataSet ds = new DataSet(); ds.Clear(); da.Fill(ds); //dataGridView1.Rows.Clear(); dataGridView1.Columns.Clear();//清空数据 if (ds.Tables[0].Rows.Count != 0) { this.dataGridView1.DataSource = ds.Tables[0]; } else { dataGridView1.DataSource = null; } CreateSqlConn.con.Close(); break; case 21: // where tno ='" + textBox1.Text.Trim() + "'", CreateSqlConn.con); selstr = "select tno as 教师号,tname as 姓名,tsex as 性别,tage as 年龄,tposition as 职位,tphone as 电话号码,dname as 学院名,tcode as 登录密码 from teacher"; cmd = new SqlCommand(selstr, CreateSqlConn.con); CreateSqlConn.con.Open(); cmd.ExecuteNonQuery(); da = new SqlDataAdapter(selstr, CreateSqlConn.con); ds = new DataSet(); ds.Clear(); da.Fill(ds); //dataGridView1.Rows.Clear(); dataGridView1.Columns.Clear(); //清空数据 if (ds.Tables[0].Rows.Count != 0) { this.dataGridView1.DataSource = ds.Tables[0]; } else { dataGridView1.DataSource = null; } CreateSqlConn.con.Close(); break; case 22: // where tno ='" + textBox1.Text.Trim() + "'", CreateSqlConn.con); selstr = "select cno as 课程号,cname as 课程名,ctype as 类型,ctime as 课时 from course"; cmd = new SqlCommand(selstr, CreateSqlConn.con); CreateSqlConn.con.Open(); cmd.ExecuteNonQuery(); da = new SqlDataAdapter(selstr, CreateSqlConn.con); ds = new DataSet(); ds.Clear(); da.Fill(ds); //dataGridView1.Rows.Clear(); dataGridView1.Columns.Clear(); //清空数据 if (ds.Tables[0].Rows.Count != 0) { this.dataGridView1.DataSource = ds.Tables[0]; } else { dataGridView1.DataSource = null; } CreateSqlConn.con.Close(); break; case 3: selstr = "delete from student where sno='" + this.dataGridView1.Rows[0].Cells[0].Value.ToString() + "'"; cmd = new SqlCommand(selstr, CreateSqlConn.con); CreateSqlConn.con.Open(); if (cmd.ExecuteNonQuery() > 0) { MessageBox.Show("删除数据成功!"); } else { MessageBox.Show("删除数据失败!"); } CreateSqlConn.con.Close(); break; case 31: selstr = "delete from teacher where tno='" + this.dataGridView1.Rows[0].Cells[0].Value.ToString() + "'"; cmd = new SqlCommand(selstr, CreateSqlConn.con); CreateSqlConn.con.Open(); if (cmd.ExecuteNonQuery() > 0) { MessageBox.Show("删除数据成功!"); } else { MessageBox.Show("删除数据失败!"); } CreateSqlConn.con.Close(); break; case 32: selstr = "delete from course where cno='" + this.dataGridView1.Rows[0].Cells[0].Value.ToString() + "'"; cmd = new SqlCommand(selstr, CreateSqlConn.con); CreateSqlConn.con.Open(); if (cmd.ExecuteNonQuery() > 0) { MessageBox.Show("删除数据成功!"); } else { MessageBox.Show("删除数据失败!"); } CreateSqlConn.con.Close(); break; } CreateSqlConn.con.Close(); }
//填充操作员信息 LogOperatorID public static void ReceiveOperatorInfo(string userid) { //根据LogOperator返回雇员信息 opinfo.OpID = userid; System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(); conn.ConnectionString = ConnStr; try { SqlCommand selectCMD = new SqlCommand("SELECT * FROM Operator where OperatorID='" + userid + "'", conn); selectCMD.CommandTimeout = 30; SqlDataAdapter dbDA = new SqlDataAdapter(); dbDA.SelectCommand = selectCMD; conn.Open(); DataSet dbDS = new DataSet(); dbDA.Fill(dbDS, "t"); //获得 EmployeeID->empno string empno; empno = dbDS.Tables[0].Rows[0]["EmpID"].ToString(); opinfo.EmpID = empno; //根据EmpNo返回EmployeeName 和 Department selectCMD.Dispose(); selectCMD.Connection = conn; selectCMD.CommandText = "SELECT * FROM Employee where EmpID='" + empno + "'"; dbDA.SelectCommand = selectCMD; dbDS.Clear(); dbDA.Fill(dbDS, "t"); opinfo.EmployeeName = dbDS.Tables[0].Rows[0]["name"].ToString(); //返回部门名称 string depno; depno = dbDS.Tables[0].Rows[0]["depcode"].ToString(); selectCMD.Dispose(); selectCMD.Connection = conn; selectCMD.CommandText = "SELECT * FROM Department where depcode='" + depno + "'"; dbDA.SelectCommand = selectCMD; dbDS.Clear(); dbDA.Fill(dbDS, "t"); opinfo.Department = dbDS.Tables[0].Rows[0]["deptname"].ToString(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { conn.Close(); } }
protected void Page_Load(object sender, EventArgs e) { myConnection = new SqlConnection("server=SISIANDL; database=Database1; integrated security=SSPI"); // ("server=fyscu-20150521p; database=WebTestDB01; integrated security=SSPI"); //("server=WY-PC; database=BS_TestDB; integrated security=SSPI"); //("server=ALIENWARE-PC\\RICHARDSERVER; database=BS_TestDB; integrated security=SSPI"); myConnection.Open(); string Time = ""; string patient_ID = ""; string patient_name = ""; string registration_ID = ""; string doc_ID = ""; string doc_name = ""; string departmentName = ""; string textArea = ""; if (!IsPostBack && string.IsNullOrEmpty(Request.QueryString["registrationID2"])) { Time = Request.QueryString["time"]; patient_ID = Request.QueryString["patientID"]; patient_name = Request.QueryString["patientName"]; registration_ID = Request.QueryString["RegistrationID"]; doc_ID = Request.QueryString["docID"]; string Command = "select DoctorName,BelongDepartmentName from [Doctor] where DoctorID=" + doc_ID; myCommand = new SqlCommand(Command, myConnection); myCommand.ExecuteNonQuery(); SDA.SelectCommand = myCommand; SDA.Fill(DT); doc_name = DT.Rows[0][0].ToString(); departmentName = DT.Rows[0][1].ToString(); registrationID.Value = registration_ID; } if (!string.IsNullOrEmpty(Request.QueryString["registrationID2"])) { patient_name = (string)Session["userName"]; patient_ID = (string)Session["userID"]; string registrationID = Request.QueryString["registrationID2"]; string Command = "select RegistrationTime,DoctorID from [Registration] where RegistrationID=" + registrationID; myCommand = new SqlCommand(Command, myConnection); myCommand.ExecuteNonQuery(); SDA.SelectCommand = myCommand; SDA.Fill(DT); Time = DT.Rows[0][0].ToString(); doc_ID = DT.Rows[0][1].ToString(); Command = "select DoctorName,BelongDepartmentName from [Doctor] where DoctorID=" + doc_ID; myCommand = new SqlCommand(Command, myConnection); myCommand.ExecuteNonQuery(); SqlDataAdapter SDA2 = new SqlDataAdapter(); DataTable DT2 = new DataTable(); SDA2.SelectCommand = myCommand; SDA2.Fill(DT2); doc_name = DT2.Rows[0][0].ToString(); departmentName = DT2.Rows[0][1].ToString(); Command = "select treatResult from [TreatInformation] where RegistrationID=" + registrationID; myCommand = new SqlCommand(Command, myConnection); myCommand.ExecuteNonQuery(); SqlDataAdapter SDA3 = new SqlDataAdapter(); DataTable DT3 = new DataTable(); SDA3.SelectCommand = myCommand; SDA3.Fill(DT3); try { textArea = DT3.Rows[0][0].ToString(); } catch { textArea = "暂无"; } info.InnerText = textArea; patientFlag.Value = "true"; CheckButton.Text = "返回"; } patientID.InnerText = patient_ID; patientName.InnerText = patient_name; docName.InnerText = doc_name; time.InnerText = Time; department.InnerText = departmentName; docID.Value = doc_ID; }
private void lbx_prescription_SelectedIndexChanged(object sender, EventArgs e) { if (!populating) { // Step 1: Database Connection SqlConnection conn = new SqlConnection(myconnstr); // Initialize variables. total = 0; description = ""; try { // Iterate through items in Services ListBox for (int i = 0; i < lbx_services.Items.Count; i++) { DataRowView objDataRowView = (DataRowView)lbx_services.Items[i]; // Check if item at index 'i' is selected. if (lbx_services.GetSelected(i)) // Returns TRUE or FALSE. { DataTable dt = new DataTable(); // Step 2: Write SQL query string sql = "SELECT Cost, Name FROM Service WHERE Service_ID ='" + Convert.ToInt32(objDataRowView["Service_ID"].ToString()) + "'"; // Create cmd using sql and conn SqlCommand cmd = new SqlCommand(sql, conn); // Create SQL DataAdapter using cmd SqlDataAdapter adapter = new SqlDataAdapter(cmd); conn.Open(); // Fill DataTable. adapter.Fill(dt); // Assign values from DataTable to variables. decimal cost = dt.Rows[0].Field <decimal>("Cost"); total += (double)cost; description += dt.Rows[0].Field <string>("Name") + ": " + string.Format("{0:C}", cost) + " "; // Close connection. conn.Close(); } } // Iterate through items in Prescriptions ListBox for (int i = 0; i < lbx_prescription.Items.Count; i++) { DataRowView objDataRowView = (DataRowView)lbx_prescription.Items[i]; // Check if item at index 'i' is selected. if (lbx_prescription.GetSelected(i)) // Returns TRUE or FALSE. { DataTable dt = new DataTable(); // Step 2: Write SQL query string sql = "SELECT Drug.Name, Drug.Cost " + "FROM Prescription " + "INNER JOIN Drug ON Prescription.Drug_ID = Drug.Drug_ID " + "WHERE Prescription.Prescription_ID = '" + Convert.ToInt32(objDataRowView["Prescription_ID"].ToString()) + "'"; // Create cmd using sql and conn SqlCommand cmd = new SqlCommand(sql, conn); // Create SQL DataAdapter using cmd SqlDataAdapter adapter = new SqlDataAdapter(cmd); conn.Open(); adapter.Fill(dt); decimal cost = dt.Rows[0].Field <decimal>("Cost"); total += (double)cost; description += dt.Rows[0].Field <string>("Name") + ": " + string.Format("{0:C}", cost) + " "; conn.Close(); } } } catch (Exception ex) { MessageBox.Show(ex.Message); } // Assign total value to label lbl_ValueInDollars.Text = string.Format("{0:C}", total); // DEBUG //MessageBox.Show(description); } }
//未结账是否允许删单据和菜品 df public static bool AllowDeleteBillFood() { System.Data.SqlClient.SqlConnection conn1 = new System.Data.SqlClient.SqlConnection(); conn1.ConnectionString = ConnStr; try { SqlCommand selectCMD1 = new SqlCommand(); selectCMD1.Connection = conn1; selectCMD1.CommandText = "SELECT count(*) FROM op_acs where operatorid='" + opinfo.OpID + "' and access='df'"; selectCMD1.CommandTimeout = 30; SqlDataAdapter dbDA1 = new SqlDataAdapter(); dbDA1.SelectCommand = selectCMD1; conn1.Open(); DataSet dbDS1 = new DataSet(); dbDA1.Fill(dbDS1, "t"); if (Convert.ToInt32(dbDS1.Tables[0].Rows[0][0]) > 0) { return true; } else { return false; } } catch (Exception ex) { MessageBox.Show(ex.ToString()); return false; } finally { conn1.Close(); } }
private void button1_Click(object sender, EventArgs e) { int resultado = validarCampos(); if (resultado == 0) { connection = new System.Data.SqlClient.SqlConnection(); try { connection.ConnectionString = Variables.connectionStr; connection.Open(); command = new SqlCommand("select fecha_inicio,codigo_estado,codigo_hotel from GITAR_HEROES.Reserva where codigo = " + numero_reserva.Text); command.Connection = connection; adapter = new SqlDataAdapter(command); dataTable = new DataTable(); adapter.Fill(dataTable); if (dataTable.Rows.Count == 0) { MessageBox.Show("Error: No se tiene ninguna reserva hecha con el número ingresado."); } else if (dataTable.Rows.Count == 1) { DateTime fecha_reserva = (DateTime)dataTable.Rows[0]["fecha_inicio"]; DateTime fecha_actual = DateTime.Today; int diferencia_en_dias = (int)(fecha_reserva - fecha_actual).TotalDays; int estado = Convert.ToInt32(dataTable.Rows[0]["codigo_estado"]); string hotel = dataTable.Rows[0]["codigo_hotel"].ToString(); if (estado >= 3) { MessageBox.Show("Error: El estado de la reserva no permite cancelación."); } else { if (diferencia_en_dias >= 1) { string query_test = "select * from GITAR_HEROES.UsuarioHotel where username = '******' and codigo_hotel = " + hotel; command = new SqlCommand(query_test); command.Connection = connection; adapter = new SqlDataAdapter(command); dataTable = new DataTable(); adapter.Fill(dataTable); if (dataTable.Rows.Count < 1) { MessageBox.Show("Error: No puede cancelar la reserva por no pertenecer al hotel."); } else if ((Convert.ToInt32(dataTable.Rows[0]["codigo_hotel"]) != Variables.hotel_id) && (Variables.usuario != "guest")) { MessageBox.Show("Error: No puede cancelar la reserva por no haber ingresado al hotel en donde se encuentra."); } else { string query; if (Variables.tipo_usuario == "Recepcionista") { query = "UPDATE GITAR_HEROES.Reserva SET codigo_estado = 3 WHERE codigo = " + numero_reserva.Text; } else// if (Variables.tipo_usuario == "Guest") { query = "UPDATE GITAR_HEROES.Reserva SET codigo_estado = 4 WHERE codigo = " + numero_reserva.Text; } command = new SqlCommand(query); command.Connection = connection; adapter = new SqlDataAdapter(command); dataTable = new DataTable(); adapter.Fill(dataTable); if (!dataTable.HasErrors) { query = "INSERT INTO GITAR_HEROES.ReservaCancelada (codigo_reserva,fecha_cancelacion,descripcion_motivo,username) VALUES (" + numero_reserva.Text + ",'" + Variables.fecha_sistema + " 00:00:00','" + motivo_textbox.Text + "','" + Variables.usuario + "')"; command = new SqlCommand(query); command.Connection = connection; adapter = new SqlDataAdapter(command); dataTable = new DataTable(); adapter.Fill(dataTable); if (!dataTable.HasErrors) { MessageBox.Show("ok cancelacion"); } } } } else { MessageBox.Show("Error: La cancelación solo se puede realizar hasta un día antes de la fecha de inicio."); } } } } catch (Exception exc) { MessageBox.Show("Error: " + exc); } } }
//获取时间段,返回dataview public static DataTable LoadTimeBlock() { System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(); conn.ConnectionString = ConnStr; try { SqlCommand selectCMD = new SqlCommand("SELECT name FROM timeblock", conn); selectCMD.CommandTimeout = 30; SqlDataAdapter dbDA = new SqlDataAdapter(); dbDA.SelectCommand = selectCMD; conn.Open(); DataSet dbDS = new DataSet(); dbDA.Fill(dbDS, "TimeBlockList"); return dbDS.Tables[0]; } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { conn.Close(); } return null; }
protected void b1_Click(object sender, EventArgs e) { Label3.Visible = true; Label4.Visible = true; id = Convert.ToInt32(Request.QueryString["id"].ToString()); con.Open(); SqlCommand cmd1 = con.CreateCommand(); cmd1.CommandType = CommandType.Text; cmd1.CommandText = "select * from cart where product_id=" + id + ""; cmd1.ExecuteNonQuery(); DataTable dt1 = new DataTable(); SqlDataAdapter da1 = new SqlDataAdapter(cmd1); da1.Fill(dt1); foreach (DataRow dr1 in dt1.Rows) { price = Convert.ToInt32(dr1["product_price"].ToString()); } con.Close(); if (checkemail() == true) { Label3.Text = "Your coupon Already Registered with Us"; // TextBox3.BackColor = System.Drawing.Color.PaleGreen; Label4.Visible = false; } else { String myquery = "Select * from coupon where couponcode='" + t1.Text + "'"; SqlCommand cmd = new SqlCommand(); cmd.CommandText = myquery; cmd.Connection = con; SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; DataSet ds = new DataSet(); da.Fill(ds); if (ds.Tables[0].Rows.Count > 0) { Label3.Text = "Coupon Code " + t1.Text + " Applied Successfully"; discount = Convert.ToInt16(ds.Tables[0].Rows[0]["discount"].ToString()); maxdiscount = Convert.ToInt16(ds.Tables[0].Rows[0]["maxdiscount"].ToString()); discountprice = (price * discount) / 100; if (discountprice > maxdiscount) { discountprice = maxdiscount; } Label4.Text = "Discount :" + discountprice.ToString() + " ( " + discount + "% ) Maximum Upto Rs." + maxdiscount; finalprice = price - discountprice; b2.Visible = true; b1.Visible = false; con.Open(); SqlCommand cmd6 = con.CreateCommand(); cmd6.CommandType = CommandType.Text; cmd6.CommandText = "update cart set product_price=" + finalprice + " where product_id=" + id; cmd6.ExecuteNonQuery(); con.Close(); con.Open(); SqlCommand cmd7 = con.CreateCommand(); cmd7.CommandType = CommandType.Text; cmd7.CommandText = "update cart set coupon='" + t1.Text + "' where product_id=" + id; cmd7.ExecuteNonQuery(); con.Close(); // Label4.Text = "" + finalprice; // Label5.Text = "Rs." + grandtotal.ToString(); // Label6.Text = "Rs." + finalprice.ToString(); } else { b1.Visible = false; b2.Visible = true; Label3.Text = "Invalid Coupon Code Applied : Not Exist"; // Label5.Text = ""; Label4.Text = ""; // Label6.Text = ""; } con.Close(); } }
//检测桌台使用状态 //0=空闲,1=使用,2=预定中,-1=不存在该桌台 与桌台列表单击事件的返回值不同! public static int GetTableStatus(string tableno) { System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(); conn.ConnectionString = ConnStr; try { SqlCommand selectCMD = new SqlCommand("SELECT status FROM Tablestatus where TableNo='" + tableno + "'", conn); selectCMD.CommandTimeout = 30; SqlDataAdapter dbDA = new SqlDataAdapter(); dbDA.SelectCommand = selectCMD; conn.Open(); DataSet dbDS = new DataSet(); dbDA.Fill(dbDS, "t"); if (! (dbDS.Tables[0].Rows.Count > 0)) //没有该桌台 { return -1; } else { //0=空闲,1=使用,2=预定中 return Convert.ToInt32(dbDS.Tables[0].Rows[0]["status"]); } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { conn.Close(); } return 0; }
protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { DataTable dt = new DataTable(); DataRow dr; dt.Columns.Add("sno"); dt.Columns.Add("phonename"); dt.Columns.Add("price"); dt.Columns.Add("quantity"); dt.Columns.Add("totalprice"); //dt.Columns.Add("productimage"); if (Request.QueryString["id"] != null) { if (Session["Buyitems"] == null) { dr = dt.NewRow(); String mycon = "Data Source=DESKTOP-49K071F\\SQLSERVER;Initial Catalog=MobileHubDB;Integrated Security=True"; SqlConnection scon = new SqlConnection(mycon); String myquery = "select * from tblPhone where PhoneId=" + Request.QueryString["id"]; SqlCommand cmd = new SqlCommand(); cmd.CommandText = myquery; cmd.Connection = scon; SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; DataSet ds = new DataSet(); da.Fill(ds); dr["sno"] = 1; //dr["phoneid"] = ds.Tables[0].Rows[0]["PhoneId"].ToString(); dr["phonename"] = ds.Tables[0].Rows[0]["PhoneName"].ToString(); //dr["productimage"] = ds.Tables[0].Rows[0]["productimage"].ToString(); dr["quantity"] = Request.QueryString["quantity"]; dr["price"] = ds.Tables[0].Rows[0]["PhonePrice"].ToString(); int price = Convert.ToInt16(ds.Tables[0].Rows[0]["PhonePrice"].ToString()); int quantity = Convert.ToInt16(Request.QueryString["quantity"].ToString()); int totalprice = price * quantity; dr["totalprice"] = totalprice; dt.Rows.Add(dr); GridView1.DataSource = dt; GridView1.DataBind(); Session["buyitems"] = dt; GridView1.FooterRow.Cells[3].Text = "Total Amount"; GridView1.FooterRow.Cells[4].Text = grandtotal().ToString(); Response.Redirect("AddToCart.aspx"); } else { dt = (DataTable)Session["buyitems"]; int sr; sr = dt.Rows.Count; dr = dt.NewRow(); String mycon = "Data Source=DESKTOP-49K071F\\SQLSERVER;Initial Catalog=MobileHubDB;Integrated Security=True"; SqlConnection scon = new SqlConnection(mycon); String myquery = "select * from tblPhone where PhoneId=" + Request.QueryString["id"]; SqlCommand cmd = new SqlCommand(); cmd.CommandText = myquery; cmd.Connection = scon; SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; DataSet ds = new DataSet(); da.Fill(ds); dr["sno"] = sr + 1; //dr["productid"] = ds.Tables[0].Rows[0]["productid"].ToString(); dr["phonename"] = ds.Tables[0].Rows[0]["PhoneName"].ToString(); //dr["productimage"] = ds.Tables[0].Rows[0]["productimage"].ToString(); dr["quantity"] = Request.QueryString["quantity"]; dr["price"] = ds.Tables[0].Rows[0]["PhonePrice"].ToString(); int price = Convert.ToInt16(ds.Tables[0].Rows[0]["PhonePrice"].ToString()); int quantity = Convert.ToInt16(Request.QueryString["quantity"].ToString()); int totalprice = price * quantity; dr["totalprice"] = totalprice; dt.Rows.Add(dr); GridView1.DataSource = dt; GridView1.DataBind(); Session["buyitems"] = dt; GridView1.FooterRow.Cells[3].Text = "Total Amount"; GridView1.FooterRow.Cells[4].Text = grandtotal().ToString(); Response.Redirect("AddToCart.aspx"); } } else { dt = (DataTable)Session["buyitems"]; GridView1.DataSource = dt; GridView1.DataBind(); if (GridView1.Rows.Count > 0) { GridView1.FooterRow.Cells[3].Text = "Total Amount"; GridView1.FooterRow.Cells[4].Text = grandtotal().ToString(); } } // Label2.Text = GridView1.Rows.Count.ToString(); } HiddenDate.Value = DateTime.Now.ToShortDateString(); findorderid(); }
//获取相应桌台类别的桌台记录 public static DataTable LoadTableList(string TableTypeName) { System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(); conn.ConnectionString = ConnStr; try { SqlCommand selectCMD = new SqlCommand(); selectCMD.Connection = conn; selectCMD.CommandText = "SELECT tableno,tablename,status,begintime FROM TableStatus"; if (TableTypeName != "全部") { selectCMD.CommandText = selectCMD.CommandText + " where TabletypeCode='" + GetTableTypeCode(TableTypeName) + "'"; } selectCMD.CommandTimeout = 30; SqlDataAdapter dbDA = new SqlDataAdapter(); dbDA.SelectCommand = selectCMD; conn.Open(); DataSet dbDS = new DataSet(); dbDA.Fill(dbDS, "TableListView"); return dbDS.Tables[0]; } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { conn.Close(); } return null; }
private void textBox1_KeyUp(object sender, KeyEventArgs e) { SqlConnection con = new SqlConnection(); con.ConnectionString = Properties.Settings.Default.bazaConnectionString1; if (textBox1.Text == "") { this.dataGridView1.DataSource = null; this.dataGridView1.Rows.Clear(); } else { if (comboBox1.Text == "Numar inventar") { SqlDataAdapter sda = new SqlDataAdapter("SELECT ID,Nrinv,Titlu,Autor,Editura,Anaparitie,Codbare,ISBN,Nrex FROM listacarti WHERE Nrinv like '" + textBox1.Text + "%'", con); DataTable dt = new DataTable(); sda.Fill(dt); dataGridView1.DataSource = dt; } else if (comboBox1.Text == "Titlu") { SqlDataAdapter sda = new SqlDataAdapter("SELECT ID,Nrinv,Titlu,Autor,Editura,Anaparitie,Codbare,ISBN,Nrex FROM listacarti WHERE Titlu like '" + textBox1.Text + "%'", con); DataTable dt = new DataTable(); sda.Fill(dt); dataGridView1.DataSource = dt; } else if (comboBox1.Text == "Autor") { SqlDataAdapter sda = new SqlDataAdapter("SELECT ID,Nrinv,Titlu,Autor,Editura,Anaparitie,Codbare,ISBN,Nrex FROM listacarti WHERE Autor like '" + textBox1.Text + "%'", con); DataTable dt = new DataTable(); sda.Fill(dt); dataGridView1.DataSource = dt; } else if (comboBox1.Text == "Editura") { SqlDataAdapter sda = new SqlDataAdapter("SELECT ID,Nrinv,Titlu,Autor,Editura,Anaparitie,Codbare,ISBN,Nrex FROM listacarti WHERE Editura like '" + textBox1.Text + "%'", con); DataTable dt = new DataTable(); sda.Fill(dt); dataGridView1.DataSource = dt; } else if (comboBox1.Text == "An aparitie") { SqlDataAdapter sda = new SqlDataAdapter("SELECT ID,Nrinv,Titlu,Autor,Editura,Anaparitie,Codbare,ISBN,Nrex FROM listacarti WHERE Anaparitie like '" + textBox1.Text + "%'", con); DataTable dt = new DataTable(); sda.Fill(dt); dataGridView1.DataSource = dt; } else if (comboBox1.Text == "Cod de bare") { SqlDataAdapter sda = new SqlDataAdapter("SELECT ID,Nrinv,Titlu,Autor,Editura,Anaparitie,Codbare,ISBN,Nrex FROM listacarti WHERE Codbare like '" + textBox1.Text + "%'", con); DataTable dt = new DataTable(); sda.Fill(dt); dataGridView1.DataSource = dt; } else if (comboBox1.Text == "ISBN") { SqlDataAdapter sda = new SqlDataAdapter("SELECT ID,Nrinv,Titlu,Autor,Editura,Anaparitie,Codbare,ISBN,Nrex FROM listacarti WHERE ISBN like '" + textBox1.Text + "%'", con); DataTable dt = new DataTable(); sda.Fill(dt); dataGridView1.DataSource = dt; } } }
static void Main(string[] args) { Vendedora celulares = new Vendedora(); Vendedora pantallas = new Vendedora(); DataTable tabla = new DataTable("historialVentas"); SqlDataAdapter dA = new SqlDataAdapter(); SqlConnection conexion = new SqlConnection(Properties.Settings.Default.conexionBD); SmartPhone s = new SmartPhone(1, "Galaxy Core", 240, "Apple", ESistemaOperativo.iOS, EMemoria.GB32); Pantalla p1 = new Pantalla(1, "Monitor", 450, "Samsung", EPulgadas.P32, EResolucion.P1080); Pantalla p2 = new Pantalla(2, "Monitor", 450, "Samsung", EPulgadas.P32, EResolucion.P1080); SmartPhone s2 = new SmartPhone(1, "Galaxy Plus", 220, "Samsung", ESistemaOperativo.Android, EMemoria.GB16); Pantalla p3 = new Pantalla(1, "Television", 500, "LG", EPulgadas.P32, EResolucion.K4); celulares += s; celulares += s2; pantallas += p1; pantallas += p2; pantallas += p3; try { conexion.Open(); if (conexion.State == System.Data.ConnectionState.Open) { conexion.Close(); Console.WriteLine("Conexion realizada con exito"); } } catch (Exception e) { Console.WriteLine(e.Message); } try { tabla.Columns.Add("idVenta", typeof(int)); tabla.PrimaryKey = new DataColumn[] { tabla.Columns[0] }; tabla.Columns[0].AutoIncrement = true; tabla.Columns[0].AutoIncrementSeed = 1; tabla.Columns[0].AutoIncrementStep = 1; Console.WriteLine("Data table configurado con exito"); } catch (Exception e) { Console.WriteLine(e.Message); } try { conexion = new SqlConnection(Properties.Settings.Default.conexionBD); dA.SelectCommand = new SqlCommand("SELECT * FROM [BaseTP4].[dbo].[pantalla] ", conexion); dA.InsertCommand = new SqlCommand("INSERT INTO [BaseTP4].[dbo].[pantalla] (producto, marca, resolucion, pulgadas, precio) VALUES (@producto, @marca, @resolucion, @pulgadas, @precio)", conexion); dA.InsertCommand.Parameters.Add("@producto", SqlDbType.VarChar, 50, "producto"); dA.InsertCommand.Parameters.Add("@marca", SqlDbType.VarChar, 50, "marca"); dA.InsertCommand.Parameters.Add("@pulgadas", SqlDbType.VarChar, 50, "pulgadas"); dA.InsertCommand.Parameters.Add("@precio", SqlDbType.Float, 10, "precio"); dA.InsertCommand.Parameters.Add("@resolucion", SqlDbType.VarChar, 50, "resolucion"); Console.WriteLine("Data adapter configurado con exito"); } catch (Exception e) { Console.WriteLine(e.Message); } //Se intenta llenar la data table con los datos de la base de datos// // try { int i = dA.Fill(tabla); if (i != 0) { Console.WriteLine("Se cargo el data table con exito"); } } catch (Exception e) { Console.WriteLine(e.Message); } //Se intenta actualziar la base de datos a traves del data adapter con los datos de la tabla // try { dA.Update(tabla); Console.WriteLine("Datos actualizados con exito"); } catch (Exception e) { Console.WriteLine(e.Message); } //Se intenta la escritura y lectura de archivos txt y xml // if (pantallas.Guardar("pantallas.txt", pantallas.ToString())) { Console.WriteLine("Archivo creado con exito"); } else { Console.WriteLine("Error al crear el archivo"); } string datos; if (pantallas.Leer("pantallas.txt", out datos)) { Console.WriteLine(datos); } else { Console.WriteLine("Error al leer el archivo"); } Console.ReadKey(true); Console.Clear(); if (Vendedora.GuardarXml("SmartPhone.xml", pantallas)) { Console.WriteLine("Vendedora serealizada con exito"); } Vendedora v2 = new Vendedora(); v2 = Vendedora.LeerXml("SmartPhone.xml"); Console.WriteLine(v2.ToString()); Console.ReadKey(true); }
private void btn_baja_Click(object sender, EventArgs e) { DataGridViewRow row = dgv_hab.CurrentRow; if (row.Cells[6].Value.ToString() == "N") { DialogResult result = MessageBox.Show("Desea dar de alta la habitación?" , "Confirmar habilitación", MessageBoxButtons.YesNo, MessageBoxIcon.Question); if (result == DialogResult.Yes) { SqlCommand alta = new SqlCommand("denver.alta_habitacion", db.Connection); alta.CommandType = CommandType.StoredProcedure; alta.Parameters.AddWithValue("@habitacion_nro", SqlDbType.Int).Value = Convert.ToInt32(row.Cells[0].Value.ToString()); alta.Parameters.AddWithValue("@habitacion_hotel_id", SqlDbType.Int).Value = Convert.ToInt32(row.Cells[5].Value.ToString()); alta.ExecuteNonQuery(); MessageBox.Show(" La habilitó la habitación " + row.Cells[0].Value.ToString(), "Mensaje"); btn_buscar_Click(null, null); } } else { SqlCommand sda = new SqlCommand("denver.buscar_reserva_hab_hotel", db.Connection); sda.CommandType = CommandType.StoredProcedure; sda.Parameters.AddWithValue("@habitacion_nro", SqlDbType.Int).Value = Convert.ToInt32(row.Cells[0].Value.ToString()); sda.Parameters.AddWithValue("@hotel_id", SqlDbType.Int).Value = Convert.ToInt32(row.Cells[5].Value.ToString()); DataTable dt = new DataTable(); using (var da = new SqlDataAdapter(sda)) { da.Fill(dt); } if (dt.Rows.Count != 0) { MessageBox.Show("No se puede dar de baja la habitacion número " + row.Cells[0].Value.ToString() + " ya que tiene reservas activas", "Mensaje"); } else { SqlCommand cmd = new SqlCommand("denver.eliminar_habitacion", db.Connection); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@habitacion_nro", SqlDbType.Int).Value = Convert.ToInt32(row.Cells[0].Value.ToString()); cmd.Parameters.AddWithValue("@habitacion_hotel_id", SqlDbType.Int).Value = Convert.ToInt32(row.Cells[5].Value.ToString()); cmd.ExecuteNonQuery(); MessageBox.Show(" La habitación se dió de baja correctamente", "Mensaje"); btn_buscar_Click(null, null); } } }
public void GetFoodTastes() { if (FoodTastesBtnList != null) { FoodTastesBtnList.RemoveAll(); FoodTastesBtnList = null; } FoodTastesBtnList = new FoodTastesButtonList(Panel2, 75, 45, Color.LightBlue, Color.FromArgb(255, 128, 128), 3); System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(); conn.ConnectionString = rms_var.ConnStr; try { SqlCommand selectCMD = new SqlCommand(); selectCMD.Connection = conn; selectCMD.CommandText = "SELECT name FROM foodtaste"; selectCMD.CommandTimeout = 30; SqlDataAdapter dbDA = new SqlDataAdapter(); dbDA.SelectCommand = selectCMD; conn.Open(); DataSet dbDS = new DataSet(); dbDA.Fill(dbDS, "t"); for (int i = 0; i <= dbDS.Tables[0].Rows.Count - 1; i++) { FoodTastesBtnList.AddNewButton(dbDS.Tables[0].Rows[i]["name"].ToString()); } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { conn.Close(); } }
public int GetDetails() { //Read connection string "DWABookConnectionString" from web.config file. string strConn = ConfigurationManager.ConnectionStrings ["DWABookConnectionString"].ToString(); //Create a SqlConnection object with the Connection String read. SqlConnection conn = new SqlConnection(strConn); //Create a SqlCommand object, with a SELECT SQL statement to retrieve all //attributes of a staff, and a connection object to open the database. SqlCommand cmd = new SqlCommand ("SELECT * FROM Staff WHERE StaffID = @selectedStaffID", conn); //Define parameter used in SQL, get its value from class property staffId cmd.Parameters.AddWithValue("@selectedStaffID", StaffId); //Create a DataAdapter object, pass SqlCommand object as parameter. SqlDataAdapter daStaff = new SqlDataAdapter(cmd); //Create a DataSet object result DataSet result = new DataSet(); //Open a database connection. conn.Open(); //Use DataAdapter to fetch data to a table "StaffDetails" in DataSet. daStaff.Fill(result, "StaffDetails"); //Close database connection conn.Close(); if (result.Tables["StaffDetails"].Rows.Count > 0) { //Assign values from DataSet to class properties of Staff //DBNUll() detects null value retrieved from the database for a //particular attribute //name if (!DBNull.Value.Equals(result.Tables["StaffDetails"].Rows[0]["Name"])) { Name = Convert.ToString(result.Tables["StaffDetails"].Rows[0]["Name"]); } //gender if (!DBNull.Value.Equals(result.Tables["StaffDetails"].Rows[0]["Gender"])) { Gender = Convert.ToString(result.Tables["StaffDetails"].Rows[0]["Gender"]); } //dob if (!DBNull.Value.Equals(result.Tables["StaffDetails"].Rows[0]["DOB"])) { Dob = Convert.ToDateTime(result.Tables["StaffDetails"].Rows[0]["DOB"]); } //salary if (!DBNull.Value.Equals(result.Tables["StaffDetails"].Rows[0]["Salary"])) { Salary = Convert.ToDouble(result.Tables["StaffDetails"].Rows[0]["Salary"]); } //email if (!DBNull.Value.Equals(result.Tables["StaffDetails"].Rows[0]["EmailAddr"])) { Email = Convert.ToString(result.Tables["StaffDetails"].Rows[0]["EmailAddr"]); } //nationality if (!DBNull.Value.Equals(result.Tables["StaffDetails"].Rows[0]["Nationality"])) { Nationality = Convert.ToString(result.Tables["StaffDetails"].Rows[0]["Nationality"]); } //is full time if (!DBNull.Value.Equals(result.Tables["StaffDetails"].Rows[0]["Status"])) { IsFullTime = Convert.ToBoolean(result.Tables["StaffDetails"].Rows[0]["Status"]); } //branchNo if (!DBNull.Value.Equals(result.Tables["StaffDetails"].Rows[0]["BranchNo"])) { BranchNo = Convert.ToInt32(result.Tables["StaffDetails"].Rows[0]["BranchNo"]); } return(0); } else { // No record found return(-2); } }
internal KensaIraiTblDataSet.KensaJokyoListDataTable GetDataBySearchCond( List<string> kensaIraiHoteiKbn, string jokasoSetchishaNm, string jokasoShisetsuNm, string settisyaCd, string kensaIraiDtFrom, string kensaIraiDtTo, string kensaDtFrom, string kensaDtTo) { SqlCommand command = CreateSqlCommand(kensaIraiHoteiKbn, jokasoSetchishaNm, jokasoShisetsuNm, settisyaCd, kensaIraiDtFrom, kensaIraiDtTo, kensaDtFrom, kensaDtTo); SqlDataAdapter adpt = new SqlDataAdapter(command); adpt.SelectCommand.Connection = this.Connection; KensaIraiTblDataSet.KensaJokyoListDataTable dataTable = new KensaIraiTblDataSet.KensaJokyoListDataTable(); adpt.Fill(dataTable); return dataTable; }
public DataSet fetch(string query) { using (this.sqlconnection = new SqlConnection(this.connstring)) { this.sqlconnection.Open(); //SqlTransaction trans = this.conn.BeginTransaction(); try { SqlDataAdapter da = new SqlDataAdapter(query, this.conn); DataSet ds = new DataSet(); da.Fill(ds); //trans.Commit(); conn.Close(); return ds; } catch (Exception ex) { try { if (this.sqlconnection.State != ConnectionState.Open) throw new Exception("Connection Is Not Open" + ex.Message); //else //trans.Rollback(); } catch (Exception ex1) { throw new Exception("Connection Is Not Open" + ex1.Message); } return (DataSet)null; } finally { if (this.sqlconnection.State != ConnectionState.Open) this.conn.Close(); } } }
public static void CreateTestRapportFromSQL(int tentamenID) { string connectionstring = ConfigurationManager.ConnectionStrings["NakijkTool.Properties.Settings.Database_NakijktoolConnectionString"].ConnectionString; using (SqlConnection connection = new SqlConnection(connectionstring)) { DataSet test = new DataSet(); DataSet vragen = new DataSet(); DataSet students = new DataSet(); DataSet commentaar = new DataSet(); DataSet opdr = new DataSet(); decimal totaalpunten = 0; int totaalvragen = 0; int totaalstudenten = 0; string queryTest = $"SELECT * FROM Tentamens WHERE tentamenid = {tentamenID}"; string queryStudents = $"SELECT distinct studentnummer, student_naam FROM Testrapport WHERE tentamenid = {tentamenID}"; string queryOpdrachten = "SELECT * FROM Testrapport WHERE tentamenid = @tentamenid"; string queryVragen = $"SELECT * FROM Vraag WHERE tentamenid = {tentamenID} ORDER BY vraagnummer ASC"; string queryCommentaar = "SELECT * FROM Commentaar WHERE vraagid in (SELECT DISTINCT vraagid FROM Testrapport WHERE tentamenid = @tentamenid)"; using (SqlDataAdapter command = new SqlDataAdapter(queryTest, connection)) { command.Fill(test); totaalpunten = 10.0m + Convert.ToDecimal(test.Tables[0].Rows[0]["aantal_punten"]); } using (SqlDataAdapter command = new SqlDataAdapter(queryVragen, connection)) { command.Fill(vragen); totaalvragen = vragen.Tables[0].Select($"tentamenid = {tentamenID}").Count(); } using (SqlDataAdapter command = new SqlDataAdapter(queryStudents, connection)) { command.Fill(students); totaalstudenten = students.Tables[0].Rows.Count; } using (SqlDataAdapter command = new SqlDataAdapter(queryCommentaar, connection)) { command.SelectCommand.Parameters.AddWithValue("tentamenid", tentamenID); command.Fill(commentaar); } using (SqlDataAdapter command = new SqlDataAdapter(queryOpdrachten, connection)) { command.SelectCommand.Parameters.AddWithValue("tentamenid", tentamenID); command.Fill(opdr); } Microsoft.Office.Interop.Excel.Application oXL; Microsoft.Office.Interop.Excel.Workbook oWB; Microsoft.Office.Interop.Excel.Worksheet oSheet; object misvalue = System.Reflection.Missing.Value; oXL = new Microsoft.Office.Interop.Excel.Application(); oWB = (Microsoft.Office.Interop.Excel.Workbook)(oXL.Workbooks.Add("")); oSheet = oWB.Worksheets[1]; oSheet.Name = "Toetsresultaten"; for (int o = 0; o < totaalvragen; o++) { // Opdrachtensheets Microsoft.Office.Interop.Excel.Worksheet newSheet; newSheet = oWB.Worksheets.Add(After: oWB.Sheets[oWB.Sheets.Count]); newSheet.Name = $"Opdracht {o + 1}"; newSheet.Cells[1, "A"] = "Studentnaam"; newSheet.Cells[1, "B"] = "Studentnummer"; newSheet.Cells[1, "C"] = "Resultaat"; newSheet.Cells[1, "D"] = "Punten"; newSheet.Cells[1, "E"] = "Commentaar"; newSheet.Range["A1"].EntireRow.Font.Bold = true; newSheet.Range["A1"].EntireRow.EntireColumn.AutoFit(); } // Resultatensheet oSheet.Cells[1, "A"] = "Toetsnaam"; oSheet.Cells[1, "B"] = "Datum"; oSheet.Cells[1, "C"] = "Totaal punten"; oSheet.Cells[2, "A"] = test.Tables[0].Rows[0]["tentamen_naam"].ToString(); oSheet.Cells[2, "B"] = test.Tables[0].Rows[0]["datum"].ToString(); oSheet.Cells[2, "C"] = totaalpunten; oSheet.Cells[4, "A"] = "Naam"; oSheet.Cells[4, "B"] = "Studentnummer"; oSheet.Cells[4, "C"] = "Cijfer"; oSheet.Cells[4, "D"] = "Testresultaat"; oSheet.Range["A1"].EntireRow.Font.Bold = true; oSheet.Range["A4"].EntireRow.Font.Bold = true; oSheet.Range["A1", "A4"].EntireRow.EntireColumn.AutoFit(); for (int s = 0; s < totaalstudenten; s++) { int c = s + 2; int m = 3; int punten = 0; int ec = 0; // compile error int er = 0; // execution error int ac = 0; // correct int ue = 0; // overig string studentnr = students.Tables[0].Rows[s]["studentnummer"].ToString(); string studentnaam = students.Tables[0].Rows[s]["student_naam"].ToString(); oSheet.Cells[c + m, "A"] = studentnaam; oSheet.Cells[c + m, "B"] = studentnr; // Opdrachten DataRow[] o = opdr.Tables[0].Select($"studentnummer = '{studentnr}'", "studentnummer ASC"); for (int v = 0; v < totaalvragen; v++) { punten += Convert.ToInt16(o[v]["studentpunten"]); Microsoft.Office.Interop.Excel.Worksheet newSheet; newSheet = oWB.Worksheets[v + 2]; newSheet.Cells[c, "A"] = studentnaam; newSheet.Cells[c, "B"] = studentnr; newSheet.Cells[c, "D"] = o[v]["studentpunten"]; newSheet.Cells[c, "E"] = o[v]["commentaartext"]; newSheet.Range[$"A{c}"].EntireRow.EntireColumn.AutoFit(); string error = o[v]["errors"].ToString(); if (error.StartsWith("Correct")) { ac++; newSheet.Cells[c, "C"] = "Correct!"; newSheet.Range[$"C{c}"].Interior.ColorIndex = 43; } else if (error.StartsWith("ExceptionDuringExecution")) { er++; newSheet.Cells[c, "C"] = "Runtime error"; newSheet.Range[$"C{c}"].Interior.ColorIndex = 44; } else if (error.StartsWith("CompileError")) { ec++; newSheet.Cells[c, "C"] = "Compile error"; newSheet.Range[$"C{c}"].Interior.ColorIndex = 46; } else if (error.StartsWith("Open vraag")) { newSheet.Cells[c, "C"] = "Open vraag"; newSheet.Range[$"C{c}"].Interior.ColorIndex = 15; } else { ue += 1; newSheet.Cells[c, "C"] = o[v]["errors"].ToString().Substring(0, o[v]["errors"].ToString().IndexOf(',')); newSheet.Range[$"C{c}"].Interior.ColorIndex = 48; } } decimal cijfer = Math.Round(((punten / totaalpunten) * 9) + 1, 1); if (cijfer > 10.0m) cijfer = 10.0m; else if (cijfer < 1.0m) cijfer = 1.0m; oSheet.Cells[c + m, "C"] = cijfer; if (cijfer >= 5.5m) oSheet.Range[$"C{c + m}"].Interior.ColorIndex = 43; else oSheet.Range[$"C{c + m}"].Interior.ColorIndex = 46; if (ue > 0) { oSheet.Cells[c + m, "D"] = $"{ue} onbekende fout(en)... {ec} compiler error(s), {er} runtime error(s), {ac} correct."; oSheet.Range[$"D{c + m}"].Interior.ColorIndex = 48; } else if (er == 0 && ec == 0) { oSheet.Cells[c + m, "D"] = "Alles correct!"; oSheet.Range[$"D{c + m}"].Interior.ColorIndex = 43; } else if (er != 0 && ec == 0) { oSheet.Cells[c + m, "D"] = $"{er} runtime error(s), {ac} correct."; oSheet.Range[$"D{c + m}"].Interior.ColorIndex = 44; } else if (er == 0 && ec != 0) { oSheet.Cells[c + m, "D"] = $"{ec} compiler error(s), {ac} correct."; oSheet.Range[$"D{c + m}"].Interior.ColorIndex = 46; } else { oSheet.Cells[c + m, "D"] = $"{ec} compiler error(s), {er} runtime error(s), {ac} correct."; oSheet.Range[$"D{c + m}"].Interior.ColorIndex = 46; } oSheet.Range[$"A{c}"].EntireRow.EntireColumn.AutoFit(); } for (int i = 0; i < totaalvragen; i++) { Microsoft.Office.Interop.Excel.Worksheet commentSheet; commentSheet = oWB.Worksheets[i + 2]; int x = totaalstudenten + 4; commentSheet.Cells[x - 1, "A"] = "Commentaarnaam"; commentSheet.Cells[x - 1, "B"] = "Aantal"; commentSheet.Range[$"A{x - 1}", $"B{x - 1}"].Font.Bold = true; DataRow r = vragen.Tables[0].Rows[i]; DataRow[] q = commentaar.Tables[0].Select($"vraagid = {r["vraagid"]}"); DataRow[] t = opdr.Tables[0].Select($"vraagid = {r["vraagid"]}"); int[] count = new int[q.Length]; for (int j = 0; j < q.Length; j++) { foreach (DataRow ts in t) { //todo: werkt nog niet helemaal goed if (ts["commentaar"].ToString().Length > j) if (ts["commentaar"].ToString()[j] == '1') { count[j]++; } } commentSheet.Cells[x, "A"] = q[j]["commentaarnaam"]; commentSheet.Cells[x, "B"] = count[j]; x++; } } oXL.Visible = true; } }
protected void Page_Load(object sender, EventArgs e) { if (Session["userid"] == null) { Response.Redirect("Login.aspx"); } else if (!IsPostBack) { try { String contact_id = Request.QueryString["id"]; int intTest = Convert.ToInt32(contact_id); string constr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; using (SqlConnection con = new SqlConnection(constr)) { using (SqlCommand cmd = new SqlCommand("SELECT * FROM EVENTS_CREATED WHERE event_id=" + contact_id)) { using (SqlDataAdapter sda = new SqlDataAdapter()) { cmd.Connection = con; sda.SelectCommand = cmd; using (DataTable dt = new DataTable()) { sda.Fill(dt); foreach (DataRow row in dt.Rows) { string itemid = row["event_id"].ToString(); string item_name = row["event_name"].ToString(); string group = row["event_group"].ToString(); string link = row["event_fb_link"].ToString(); string contactname = row["event_contact_name"].ToString(); string contactno = row["event_contact_no"].ToString(); startdate = (DateTime)row["event_start_date"]; enddate = (DateTime)row["event_end_date"]; string start_time = row["event_start_time"].ToString(); string end_time = row["event_end_time"].ToString(); string venue = row["event_venue"].ToString(); string formal_descr = row["event_background"].ToString(); string free = row["event_free"].ToString(); string no_of_p = row["event_no_of_participants"].ToString(); regclose = (DateTime)row["event_reg_closing_date"]; string resources = row["event_resources"].ToString(); string remarks = row["event_remarks"].ToString(); string item_price = row["event_price"].ToString(); string item_description = row["event_description"].ToString(); image = row["event_poster"].ToString(); start = startdate.ToString("ddd dd MMMM yyyy"); end = enddate.ToString("ddd dd MMMM yyyy"); close = regclose.ToString("ddd dd MMMM yyyy"); shirt = Convert.ToInt32(row["event_shirt"]); food = Convert.ToInt32(row["event_food"]); this.HiddenField_Id1.Value = itemid; this.txtOrgClub.Text = group; this.txtName.Text = item_name; this.txtDescr.Text = item_description; this.txtPrice.Text = item_price; this.txtStartDate.Text = start; this.txtEndDate.Text = end; this.txtStartTime.Text = start_time; this.txtEndTime.Text = end_time; this.txtVenue.Text = venue; this.txtFormalDesc.Text = formal_descr; this.txtNoOfP.Text = no_of_p; this.txtLink.Text = link; this.txtContactName.Text = contactname; this.txtContactNo.Text =contactno; this.txtRegClose.Text = close; this.txtRemarks.Text = remarks; if (free == "Y") { chkFree.Checked = true; panelPrice.Visible = false; } else { chkFree.Checked = false; panelPrice.Visible = true; } if (shirt == 1) { chkShirt.Checked = true; } if (food == 1) { chkFood.Checked = true; } drlEligibility.SelectedValue = row["event_eligibility"].ToString(); drlCategory.SelectedValue = row["event_category"].ToString(); string str = resources; string[] resource = str.Split(','); foreach (string res in resource) { CheckBoxList1.Items.FindByValue(res).Selected = true; } } con.Close(); } } } } } catch (Exception ex) { Response.Write("Error: " + ex.ToString()); } } }
/// <summary> /// Returns the dataset of the query. /// </summary> /// <param name="p_sqlCommand"></param> /// <returns></returns> public DataSet GetDataset(string p_sqlCommand) { SqlDataAdapter daRecords = new SqlDataAdapter(p_sqlCommand, RMSGlobal.m_serverConnectionString); DataSet dsRecords = new DataSet(); daRecords.Fill(dsRecords); return dsRecords; }
/// <summary> ///Metoda wyswietlenia losowych rekordow wybranej tablicy /// <param name="sender">kontrolka dla ktorej jest wykonywana akcja </param> /// <param name="r">argumenty który implementator tego zdarzenia może uznać za przydatny</param> /// <return> Zwróć tablice danych.</return> /// </summary> private void Random(object sender, RoutedEventArgs e) { int index = int.Parse(((Button)e.Source).Uid); //Przemieszcza margines (czesc estetyczna) dodaje 260 do parametru umozliwiajac przesuwanie poziome GridCursor.Margin = new Thickness(0 + (260 * index), 0 , 0, 0); //Polaczenie z baza danych SqlConnection sqlCon = new SqlConnection(Globals.ConnString); switch (index) { case 0: //Wyswietlenie losowego wiersza z tablicy "Movies" try { sqlCon.Open(); String query = "SELECT TOP 1 * FROM Movies ORDER BY NEWID()"; SqlCommand createCommand = new SqlCommand(query, sqlCon); createCommand.ExecuteNonQuery(); SqlDataAdapter dataAdp = new SqlDataAdapter(createCommand); DataTable dt = new DataTable("Movies"); dataAdp.Fill(dt); Data.ItemsSource = dt.DefaultView; dataAdp.Update(dt); sqlCon.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); } break; case 1: //Wyswietlenie losowego wiersza z tablicy "Series" try { sqlCon.Open(); String query = "SELECT TOP 1 * FROM Series ORDER BY NEWID()"; SqlCommand createCommand = new SqlCommand(query, sqlCon); createCommand.ExecuteNonQuery(); SqlDataAdapter dataAdp = new SqlDataAdapter(createCommand); DataTable dt = new DataTable("Series"); dataAdp.Fill(dt); Data.ItemsSource = dt.DefaultView; dataAdp.Update(dt); sqlCon.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); } break; case 2: //Wyswietlenie losowego wiersza z tablicy "MyBase" try { sqlCon.Open(); String query = "SELECT TOP 1 * FROM MyBase ORDER BY NEWID()"; SqlCommand createCommand = new SqlCommand(query, sqlCon); createCommand.ExecuteNonQuery(); SqlDataAdapter dataAdp = new SqlDataAdapter(createCommand); DataTable dt = new DataTable("MyBase"); dataAdp.Fill(dt); Data.ItemsSource = dt.DefaultView; dataAdp.Update(dt); sqlCon.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); } break; } }
async Task <List <Data_Documentos> > IData_Documentos.GetListFiltered(Int16 IdDatosFox, DateTime Start_FechaRegistro, DateTime End_FechaRegistro, int idTipoDocumento) { var task = Task.Factory.StartNew(() => { List <Data_Documentos> data_Documentos = new List <Data_Documentos>(); string procedure = string.Empty; if (idTipoDocumento != 0) { procedure = "[dbo].[Read_List_Documento_By_TipoDocumento]"; } else { procedure = "[dbo].[Read_List_Documento_By_Filters]"; } DataTable dataTable = new DataTable(); Connection connection = new Connection(); SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(); SqlCommand sqlCommand = new SqlCommand { CommandText = procedure, CommandType = CommandType.StoredProcedure, Connection = connection.connectionString }; sqlDataAdapter.SelectCommand = sqlCommand; SqlParameter parameterIdDatosFox = new SqlParameter { SqlDbType = SqlDbType.SmallInt, ParameterName = "@IdDatosFox", Value = IdDatosFox }; sqlCommand.Parameters.Add(parameterIdDatosFox); SqlParameter parameterStart_FechaRegistro = new SqlParameter { SqlDbType = SqlDbType.DateTime, ParameterName = "@Start_FechaRegistro", Value = Start_FechaRegistro }; sqlCommand.Parameters.Add(parameterStart_FechaRegistro); SqlParameter parameterEnd_FechaRegistro = new SqlParameter { SqlDbType = SqlDbType.DateTime, ParameterName = "@End_FechaRegistro", Value = End_FechaRegistro }; sqlCommand.Parameters.Add(parameterEnd_FechaRegistro); SqlParameter parameteridTipoDocumento = new SqlParameter { SqlDbType = SqlDbType.Int, ParameterName = "@idTipoDocumento", Value = idTipoDocumento }; sqlCommand.Parameters.Add(parameteridTipoDocumento); connection.Connect(); sqlDataAdapter.Fill(dataTable); connection.Disconnect(); DataRow row; Data_Documentos data_Documento; for (int i = 0; i < dataTable.Rows.Count; i++) { row = dataTable.Rows[i]; data_Documento = new Data_Documentos(); data_Documento.IdDocumento = row["IdDocumento"].ToString(); data_Documento.NombreModulo = row["NombreModulo"].ToString(); data_Documento.CodigoEmpresa = row["CodigoEmpresa"].ToString(); data_Documento.Ruta = row["Ruta"].ToString(); data_Documento.IdEmisor = Convert.ToInt32(row["IdEmisor"].ToString()); data_Documento.TipoDocumento = row["TipoDocumento"].ToString(); #region FechaRegistro try { data_Documento.FechaRegistro = Convert.ToDateTime(row["FechaRegistro"].ToString()); } catch (Exception) { data_Documento.FechaRegistro = Convert.ToDateTime("1900-01-01"); } #endregion FechaRegistro #region FechaEmisionSUNAT try { data_Documento.FechaEmisionSUNAT = Convert.ToDateTime(row["FechaEmisionSUNAT"].ToString()); } catch (Exception) { data_Documento.FechaEmisionSUNAT = Convert.ToDateTime("1900-01-01"); } #endregion FechaEmisionSUNAT #region EnviadoSunat data_Documento.EnviadoSunat = Convert.ToBoolean(row["EnviadoSunat"].ToString()); if (data_Documento.EnviadoSunat) { data_Documento.TextEnviadoSunat = "Enviado"; } else { data_Documento.TextEnviadoSunat = "No enviado"; } #endregion EnviadoSunat #region EnviadoServer data_Documento.EnviadoServer = Convert.ToBoolean(row["EnviadoServer"].ToString()); if (data_Documento.EnviadoServer) { data_Documento.TextEnviadoServer = "Enviado"; } else { data_Documento.TextEnviadoServer = "No enviado"; } #endregion EnviadoServer #region EnviadoEmailCliente data_Documento.EnviadoEmailCliente = Convert.ToBoolean(row["EnviadoEmailCliente"].ToString()); #endregion EnviadoEmailCliente data_Documento.EstadoSunat = row["EstadoSunat"].ToString(); data_Documento.ComentarioDocumento = row["ComentarioDocumento"].ToString(); data_Documento.SerieCorrelativo = row["SerieCorrelativo"].ToString(); data_Documento.Eliminado = Convert.ToBoolean(row["Eliminado"].ToString()); data_Documento.Anulado = Convert.ToBoolean(row["Anulado"].ToString()); data_Documento.TDDescripcion = row["TDDescripcion"].ToString(); data_Documento.IdCabeceraDocumento = Convert.ToInt32(row["IdCabeceraDocumento"].ToString()); #region ComunicacionBaja data_Documento.ComunicacionBaja = Convert.ToBoolean(row["ComunicacionBaja"].ToString()); if (data_Documento.ComunicacionBaja) { data_Documento.TextComunicacionBaja = "Si"; } else { data_Documento.TextComunicacionBaja = "No"; } #endregion ComunicacionBaja data_Documento.FechaEmision = DateTime.Parse(row["FechaEmision"].ToString()).ToShortDateString(); data_Documento.HoraEmision = row["HoraEmision"].ToString(); data_Documento.Selectable = false; data_Documentos.Add(data_Documento); } return(data_Documentos); }); return(await task); }
int alter;//定义一个变量随着查询的号码的不同类别而改变alter=1时是查询的学生信息,2是老师,3是课程 private void button1_Click_1(object sender, EventArgs e) { string selstr = "select scode from student where sno ='" + textBox1.Text.Trim() + "'"; string selstr1 = "select tcode from teacher where tno ='" + textBox1.Text.Trim() + "'"; string selstr2 = "select cno from course where cno ='" + textBox1.Text.Trim() + "'"; string selstr3 = "select scode from student where sname ='" + textBox1.Text.Trim() + "'"; string selstr4 = "select tcode from teacher where tname ='" + textBox1.Text.Trim() + "'"; string selstr5 = "select cno from course where cname ='" + textBox1.Text.Trim() + "'"; SqlCommand myCom = new SqlCommand(selstr, CreateSqlConn.con); //学号 SqlCommand myCom1 = new SqlCommand(selstr1, CreateSqlConn.con); //教师号 SqlCommand myCom2 = new SqlCommand(selstr2, CreateSqlConn.con); //课程号 SqlCommand myCom3 = new SqlCommand(selstr3, CreateSqlConn.con); SqlCommand myCom4 = new SqlCommand(selstr4, CreateSqlConn.con); SqlCommand myCom5 = new SqlCommand(selstr5, CreateSqlConn.con); CreateSqlConn.con.Open(); SqlDataAdapter da; DataSet ds; dataGridView1.Columns.Clear(); if (null != myCom.ExecuteScalar()) //&& myCom.ExecuteScalar().ToString() == textBox1.Text.Trim() { //Student(SNo,SName,SSex,SAge,CClass,SPhone,DName,SCode); //,sname,ssex,sage,cclass,sphone,dname,scode alter = 1; da = new SqlDataAdapter("select sno as 学号,sname as 姓名,ssex as 性别,sage as 年龄 ,cclass as 班级,sphone 电话号码,dname as 学院名,scode as 登录密码 from student where sno ='" + textBox1.Text.Trim() + "'", CreateSqlConn.con); ds = new DataSet(); ds.Clear(); da.Fill(ds); if (ds.Tables[0].Rows.Count != 0) { this.dataGridView1.DataSource = ds.Tables[0]; } else { dataGridView1.DataSource = null; } } if (null != myCom1.ExecuteScalar()) { //Teacher(TNo,TName,TSex,TAge,TPosition,TPhone,DName,TCode); alter = 2; da = new SqlDataAdapter("select tno as 教师号,tname as 姓名,tsex as 性别,tage as 年龄,tposition as 职位,tphone as 电话号码,dname as 学院名,tcode as 登录密码 from teacher where tno ='" + textBox1.Text.Trim() + "'", CreateSqlConn.con); ds = new DataSet(); ds.Clear(); da.Fill(ds); if (ds.Tables[0].Rows.Count != 0) { this.dataGridView1.DataSource = ds.Tables[0]; } else { dataGridView1.DataSource = null; } } if (null != myCom2.ExecuteScalar()) { //Course(CNo,CName,CType,CTime); alter = 3; da = new SqlDataAdapter("select cno as 课程号,cname as 课程名,ctype as 类型,ctime as 课时 from course where cno ='" + textBox1.Text.Trim() + "'", CreateSqlConn.con); ds = new DataSet(); ds.Clear(); da.Fill(ds); if (ds.Tables[0].Rows.Count != 0) { this.dataGridView1.DataSource = ds.Tables[0]; } else { dataGridView1.DataSource = null; } } if (null != myCom3.ExecuteScalar()) //&& myCom.ExecuteScalar().ToString() == textBox1.Text.Trim() { //Student(SNo,SName,SSex,SAge,CClass,SPhone,DName,SCode); //,sname,ssex,sage,cclass,sphone,dname,scode alter = 4; da = new SqlDataAdapter("select sno as 学号,sname as 姓名,ssex as 性别,sage as 年龄 ,cclass as 班级,sphone 电话号码,dname as 学院名,scode as 登录密码 from student where sname ='" + textBox1.Text.Trim() + "'", CreateSqlConn.con); ds = new DataSet(); ds.Clear(); da.Fill(ds); if (ds.Tables[0].Rows.Count != 0) { this.dataGridView1.DataSource = ds.Tables[0]; } else { dataGridView1.DataSource = null; } } if (null != myCom4.ExecuteScalar()) //&& myCom.ExecuteScalar().ToString() == textBox1.Text.Trim() { //Student(SNo,SName,SSex,SAge,CClass,SPhone,DName,SCode); //,sname,ssex,sage,cclass,sphone,dname,scode alter = 5; da = new SqlDataAdapter("select tno as 教师号,tname as 姓名,tsex as 性别,tage as 年龄,tposition as 职位,tphone as 电话号码,dname as 学院名,tcode as 登录密码 from teacher where tname ='" + textBox1.Text.Trim() + "'", CreateSqlConn.con); ds = new DataSet(); ds.Clear(); da.Fill(ds); if (ds.Tables[0].Rows.Count != 0) { this.dataGridView1.DataSource = ds.Tables[0]; } else { dataGridView1.DataSource = null; } } if (null != myCom5.ExecuteScalar()) //&& myCom.ExecuteScalar().ToString() == textBox1.Text.Trim() { //Student(SNo,SName,SSex,SAge,CClass,SPhone,DName,SCode); //,sname,ssex,sage,cclass,sphone,dname,scode alter = 6; da = new SqlDataAdapter("select cno as 课程号,cname as 课程名,ctype as 类型,ctime as 课时 from course where cname ='" + textBox1.Text.Trim() + "'", CreateSqlConn.con); ds = new DataSet(); ds.Clear(); da.Fill(ds); if (ds.Tables[0].Rows.Count != 0) { this.dataGridView1.DataSource = ds.Tables[0]; } else { dataGridView1.DataSource = null; } } if (null == myCom.ExecuteScalar() && null == myCom1.ExecuteScalar() && null == myCom2.ExecuteScalar() && null == myCom3.ExecuteScalar() && null == myCom4.ExecuteScalar() && null == myCom5.ExecuteScalar()) { MessageBox.Show("查找失败,请检查输入是否有误!"); } //} CreateSqlConn.con.Close(); }
public DBAdapter(int cType, object cLink,string Query) { this.cType = cType; this.cLink = cLink; switch (cType) { case 0: Adapter = new MySqlDataAdapter(Query, (MySqlConnection)cLink); break; case 1: Adapter = new SqlDataAdapter(Query, (SqlConnection)cLink); break; case 2: Adapter = new SQLiteDataAdapter(Query, (SQLiteConnection)cLink); break; } }
private void doQueryAfterSelection() { try { this.dataGridViewToReturn.DataSource = null; dataGridViewToReturn.Columns.Clear(); SqlConnection mConn = new SqlConnection(Constlist.ConStr); mConn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = mConn; //加入条件判断,只显示未收完的货物 cmd.CommandText = "select material_type,mpn, vendormaterialNo, number, stock_in_num from stock_in_sheet where buy_order_serial_no='" + this.buy_order_serial_noComboBox.Text + "' and _status='open' and material_type in ('BGA') "; cmd.CommandType = CommandType.Text; SqlDataAdapter sda = new SqlDataAdapter(); sda.SelectCommand = cmd; DataSet ds = new DataSet(); sda.Fill(ds, "stock_in_sheet"); dataGridViewToReturn.DataSource = ds.Tables[0]; dataGridViewToReturn.RowHeadersVisible = false; mConn.Close(); string[] hTxt = { "材料大类", "MPN", "厂商料号", "订单数量", "入库数量" }; for (int i = 0; i < hTxt.Length; i++) { dataGridViewToReturn.Columns[i].HeaderText = hTxt[i]; dataGridViewToReturn.Columns[i].Name = hTxt[i]; } DataGridViewColumn dc = new DataGridViewColumn(); dc.DefaultCellStyle.BackColor = Color.Red; dc.Name = "差数"; //dc.DataPropertyName = "FID"; dc.Visible = true; // dc.SortMode = DataGridViewColumnSortMode.NotSortable; dc.HeaderText = "差数"; dc.CellTemplate = new DataGridViewTextBoxCell(); int columnIndex = dataGridViewToReturn.Columns.Add(dc); foreach (DataGridViewRow dr in dataGridViewToReturn.Rows) { try { int oNum = Int32.Parse(dr.Cells["订单数量"].Value.ToString()); int rNum = Int32.Parse(dr.Cells["入库数量"].Value.ToString()); if (oNum - rNum == 0) { dr.Cells["差数"].Style.BackColor = Color.Green; } dr.Cells["差数"].Value = (oNum - rNum) + " "; } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } if (ds.Tables[0].Rows.Count > 0) { dataGridViewToReturn.Rows[0].Selected = false; } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
protected void Page_Load(object sender, EventArgs e) { string StuNameShow = string.Empty; string Capacity = string.Empty; //StuNameShow = Session["StuName"].ToString(); //DB連線才用 //Capacity = Session["Capacity"].ToString(); //DB連線才用 StuNameShow = "曾敏涵"; //先固定塞值 Capacity = "Member"; //先固定塞值 lStuName.Text = StuNameShow; if (StuNameShow == "" || Capacity == "Manager") { Response.Write("<script>alert('非一般會員,請先登入!');location.href='login.aspx'; </script>"); } else { try { //查股票名稱 收盤 餘額 SqlDataAdapter adapter1 = new SqlDataAdapter(); string cs1 = "Data Source=mynewserver-20171228.database.windows.net;User Id=ServerAdmin; Password=Zxcv123456; Initial Catalog=mySampleDatabase"; string qs1 = "SELECT Stock.收盤, [User].餘額,Stock.名稱 FROM [User] CROSS JOIN Stock WHERE ([User].帳號=@帳號) AND (Stock.代號=@代號)AND (Stock.時間=@時間)"; using (SqlConnection cn1 = new SqlConnection(cs1)) { cn1.Open(); using (SqlCommand command1 = new SqlCommand(qs1, cn1)) { command1.Parameters.AddWithValue("@帳號", Session["StuName"]); command1.Parameters.AddWithValue("@代號", Session["stock"]); String t1 = "1545"; String t2 = DateTime.Now.ToString("HHmm"); // Debug.WriteLine(string.Compare(t1, t2) ); 若>0 前面比較大 =0 一樣大 <0 後面比較大 if (string.Compare(t1, t2) >= 0) { //現在時間還不到15:45 command1.Parameters.AddWithValue("@時間", DateTime.Now.AddDays(-1).ToString("yyyy/MM/dd")); Session["Date"] = DateTime.Now.AddDays(-1).ToString("yyyy/MM/dd"); } //用昨天的股價 else { command1.Parameters.AddWithValue("@時間", DateTime.Now.ToString("yyyy/MM/dd")); Session["Date"] = DateTime.Now.ToString("yyyy/MM/dd"); } //用今天的股價 using (SqlDataReader dr = command1.ExecuteReader()) { while ((dr.Read())) { //5.判斷資料列是否為空 if (!dr[0].Equals(DBNull.Value)) { string temp = dr[0].ToString().Trim(); //把收盤放進temp float price = Convert.ToSingle(temp); //變成floar型態 temp = dr[1].ToString().Trim(); //把餘額放進temp float Money = Convert.ToSingle(temp); temp = dr[2].ToString().Trim(); //把股票名稱放進temp string Name = Convert.ToString(temp); lStockName.Text = Name; //股票名稱 lBalance.Text = Money.ToString(); //餘額 lCloseP.Text = price.ToString(); //收盤價 } } cn1.Close(); } } } } catch (Exception ex) { lTransationFail.ForeColor = System.Drawing.Color.Red; lTransationFail.Text = ex.Message; } try { lStockCode.Text = Session["stock"].ToString(); //股票代碼 lStockPrice.Text = Session["price"].ToString(); //出價 lStockBoardLot.Text = Session["amount"].ToString(); //數量 lAction.Text = Session["action"].ToString(); //買賣 lClosePT.Text = Session["Date"].ToString(); //Date } catch (Exception ex) { lTransationFail.ForeColor = System.Drawing.Color.Red; lTransationFail.Text = ex.Message; Response.Write("<script>alert('請勿按返回至 前次檢查頁面!');location.href='transaction.aspx'; </script>"); } } }
public int checkifclassisfull(String lessonid) { int count = 0; SqlConnection conn = new SqlConnection(constring); conn.Open(); SqlCommand cmd2 = conn.CreateCommand(); cmd2.CommandType = CommandType.Text; cmd2.CommandText = "SELECT ClassName FROM Lessons WHERE LessonID='" + lessonid + "'"; if (cmd2.CommandText.Length > 0) { cmd2.ExecuteNonQuery(); } else { conn.Close(); return(2); } DataTable dt2 = new DataTable(); SqlDataAdapter da2 = new SqlDataAdapter(cmd2); da2.Fill(dt2); SqlCommand cmd3 = conn.CreateCommand(); cmd3.CommandType = CommandType.Text; foreach (DataRow dr1 in dt2.Rows) { cmd3.CommandText = "SELECT Capacity FROM Classes WHERE Id='" + dr1["ClassName"].ToString() + "'"; //MessageBox.Show("class name:" + dr1["ClassName"].ToString()); } if (cmd3.CommandText.Length > 0) { cmd3.ExecuteNonQuery(); } else { conn.Close(); return(3); } DataTable dt3 = new DataTable(); SqlDataAdapter da3 = new SqlDataAdapter(cmd3); da3.Fill(dt3); SqlCommand cmd4 = conn.CreateCommand(); cmd4.CommandType = CommandType.Text; cmd4.CommandText = "SELECT StudentID FROM student_schedule WHERE LessonID='" + lessonid + "'"; if (cmd4.CommandText.Length > 0) { cmd4.ExecuteNonQuery(); } DataTable dt4 = new DataTable(); SqlDataAdapter da4 = new SqlDataAdapter(cmd4); da4.Fill(dt4); foreach (DataRow dr in dt4.Rows) { count++; } if ((int)dt3.Rows[0][0] >= count + 1) { conn.Close(); return(1); } else { conn.Close(); return(0); } }