//Form 1 public void dataGrid2(DataTable dt, object textbox) { System.Data.SqlClient.SqlDataAdapter da; string sql = string.Format("SELECT * FROM itemtable where Item_Name LIKE '%{0}%'", textbox); da = new System.Data.SqlClient.SqlDataAdapter(sql, con); da.Fill(dt); }
//Form 1 public void dataGrid(DataTable dt, object table) { System.Data.SqlClient.SqlDataAdapter da; string sql = "SELECT * FROM " + table; da = new System.Data.SqlClient.SqlDataAdapter(sql, con); da.Fill(dt); }
private System.Data.DataRow GetParameter(string IDParametro, int? IDPortal, int? IDSistema, string IDUsuario) { // Aca se lee la informacion de la base de datos // y se preparan los layers string connStr = ValidacionSeguridad.Instance.GetSecurityConnectionString(); System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connStr); conn.Open(); System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand ("SELECT * FROM dbo.SF_VALOR_PARAMETRO(@IDParametro, @IDPortal, @IDSistema, @IDUsuario)", conn); System.Data.SqlClient.SqlParameter prm = new System.Data.SqlClient.SqlParameter("@IDParametro", System.Data.SqlDbType.VarChar, 100); prm.Value = IDParametro; cmd.Parameters.Add(prm); prm = new System.Data.SqlClient.SqlParameter("@IDPortal", System.Data.SqlDbType.Int); if (IDPortal.HasValue) { prm.Value = IDPortal.Value; } else { prm.Value = null; } cmd.Parameters.Add(prm); prm = new System.Data.SqlClient.SqlParameter("@IDSistema", System.Data.SqlDbType.Int); if (IDSistema.HasValue) { prm.Value = IDSistema.Value; } else { prm.Value = null; } cmd.Parameters.Add(prm); prm = new System.Data.SqlClient.SqlParameter("@IDUsuario", System.Data.SqlDbType.VarChar); if (IDUsuario != null) { prm.Value = IDUsuario; } else { prm.Value = null; } cmd.Parameters.Add(prm); // IdParametro, Alcance, ValorTexto, ValorEntero, ValorDecimal, ValorLogico, ValorFechaHora cmd.CommandType = System.Data.CommandType.Text; System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd); System.Data.DataSet ds = new System.Data.DataSet(); da.Fill(ds); conn.Close(); return ds.Tables[0].Rows[0]; //return resultado; }
private void FillDataSet() { //1. Make a Connection System.Data.SqlClient.SqlConnection objCon; objCon = new System.Data.SqlClient.SqlConnection(); objCon.ConnectionString = @"Data Source=(localDB)\v11.0;Initial Catalog = EmployeeProjects; Integrated Security=True;"; objCon.Open(); //2. Issue a Command System.Data.SqlClient.SqlCommand objCmd; objCmd = new System.Data.SqlClient.SqlCommand(); objCmd.Connection = objCon; objCmd.CommandType = CommandType.StoredProcedure; objCmd.CommandText = @"pSelEmployeeProjectHours"; //3. Process the Results System.Data.DataSet objDS = new DataSet(); System.Data.SqlClient.SqlDataAdapter objDA; objDA = new System.Data.SqlClient.SqlDataAdapter(); objDA.SelectCommand = objCmd; objDA.Fill(objDS); // objCon.Open() is not needed! dataGridView1.DataSource = objDS.Tables[0]; //4. Clean up code objCon.Close(); dataGridView1.Refresh(); }
private void ReloadStatistics() { System.Data.SqlClient.SqlCommand cmd = null; string d_member = ""; string v_member = ""; if(this.tsmiByReceiver.Checked){ cmd = Statistics.Contents.ByReceiver(this.receipt, out d_member, out v_member); } else if (this.tsmiByBuyer.Checked) { cmd = Statistics.Contents.ByBuyer(this.receipt, out d_member, out v_member); }else if( this.tsmiByProductTypes.Checked){ cmd = Statistics.Contents.ByProductTypes(this.receipt, out d_member, out v_member); } else if (this.tsmiByCategories.Checked) { cmd = Statistics.Contents.ByCategories(this.receipt, out d_member, out v_member); } if (cmd != null) { System.Data.DataTable content_stat = new DataTable("ContentsStatistics"); cmd.Connection = this.connection; System.Data.SqlClient.SqlDataAdapter sda = new System.Data.SqlClient.SqlDataAdapter(cmd); sda.Fill(content_stat); this.dgvData.DataSource = content_stat; this.dgvData.Columns[d_member].DisplayIndex = 0; this.dgvData.Columns[v_member].DisplayIndex = 1; } else { MessageBox.Show("Не выбрано ни одного критерия!", "Ошибка"); } return; }
private void ProductTypeForm_Load(object sender, EventArgs e) { // TODO сделать для обновления System.Data.SqlClient.SqlCommand cat_cmd = Producer.Categories.Select(Guid.Empty); cat_cmd.Connection = this.cConnection; System.Data.SqlClient.SqlDataAdapter catda = new System.Data.SqlClient.SqlDataAdapter(cat_cmd); System.Data.DataTable tbl = new System.Data.DataTable("Categories"); catda.Fill(tbl); this.cbxCategories.DataSource = tbl; this.cbxCategories.DisplayMember = "CategoryName"; this.cbxCategories.ValueMember = "CategoryID"; string col_name = "Category"; if (!System.Convert.IsDBNull(this.product_type[col_name])) this.cbxCategories.SelectedValue = this.product_type[col_name]; this.isNewType = (System.Convert.IsDBNull(this.product_type["TypeId"]) || ((int)this.product_type["TypeId"]) < 0); string caption = "Добавление нового типа продукта"; if (!this.isNewType) { int ptype = -1; col_name = "TypeId"; if (!System.Convert.IsDBNull(this.product_type[col_name])) ptype = (int)this.product_type[col_name]; col_name = "Name"; if (!System.Convert.IsDBNull(this.product_type[col_name])) this.tbxProductType.Text = (string)this.product_type[col_name]; col_name = "Comment"; if (!System.Convert.IsDBNull(this.product_type[col_name])) this.tbxComment.Text = (string)this.product_type[col_name]; caption = string.Format("Редактирование типа продукта #{0}", ptype); } this.Text = caption; return; }
public static System.Data.DataTable GetDataTable(string strSQL) { System.Data.DataTable dt = new System.Data.DataTable(); System.Data.SqlClient.SqlConnectionStringBuilder csb = new System.Data.SqlClient.SqlConnectionStringBuilder(); csb.DataSource = System.Environment.MachineName; csb.DataSource = @"VMSTZHDB08\SZH_DBH_1"; csb.InitialCatalog = "HBD_CAFM_V3"; csb.DataSource = "CORDB2008R2"; csb.InitialCatalog = "Roomplanning"; // csb.DataSource = "cordb2014"; // csb.InitialCatalog = "ReportServer"; csb.DataSource = @"CORDB2008R2"; csb.InitialCatalog = "COR_Basic_SwissLife"; csb.IntegratedSecurity = true; using (System.Data.Common.DbDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(strSQL, csb.ConnectionString)) { da.Fill(dt); } return dt; }
private void ChangePositionForm_Load(object sender, EventArgs e) { System.Data.SqlClient.SqlDataAdapter sda = null; System.Data.SqlClient.SqlCommand prdccmd = Producer.Commands.Products(-1, -1 ); prdccmd.Connection = this.connection; sda = new System.Data.SqlClient.SqlDataAdapter(prdccmd); this.products = new System.Data.DataTable("Products"); sda.Fill(this.products); this.cbxProducts.DataSource = this.products; this.cbxProducts.DisplayMember = "ProductName"; this.cbxProducts.ValueMember = "ProductID"; this.cbxProducts.SelectedValue = this.product_id; prdccmd = Producer.Commands.Products( -1, this.product_id ); prdccmd.Connection = this.connection; sda = new System.Data.SqlClient.SqlDataAdapter(prdccmd); System.Data.DataTable prods = new System.Data.DataTable("Product"); sda.Fill(prods); System.Data.SqlClient.SqlCommand catccmd = Producer.Commands.ProductCategories(); catccmd.Connection = this.connection; sda = new System.Data.SqlClient.SqlDataAdapter(catccmd); this.categories = new System.Data.DataTable("Categories"); sda.Fill(this.categories); this.cbxCategory.DataSource = this.categories; this.cbxCategory.DisplayMember = "CategoryName"; this.cbxCategory.ValueMember = "CategoryID"; this.cbxCategory.SelectedValue = prods.Rows[0]["Category"]; //this.block = false; //this.tbxCurrentProduct.Text = this.product["ProductName"].ToString(); }
private void cbxCategories_SelectedIndexChanged(object sender, EventArgs e) { if (!this.bBlockContent) { this.bBlockContent = true; System.Data.SqlClient.SqlCommand cmd = null; if (this.cbxCategories.SelectedIndex >= 0) { cmd = Producer.Commands.Products((Guid)this.cbxCategories.SelectedValue, System.Guid.Empty); } else { cmd = Producer.Commands.Products(Guid.Empty, System.Guid.Empty); } cmd.Connection = this.cConnection; System.Data.SqlClient.SqlDataAdapter sda = new System.Data.SqlClient.SqlDataAdapter(cmd); this.products = new DataTable("Products"); sda.Fill(this.products); this.cbxProducts.DataSource = this.products; this.cbxProducts.ValueMember = "ProductID"; this.cbxProducts.DisplayMember = "ProductName"; if( this.products.Rows.Count > 0 ) this.LoadPrices((Guid)this.products.Rows[0]["ProductID"]); this.bBlockContent = false; } }
public ReturnResult CheckUserAccount(string UserAccount, string UserPassWord) { ReturnResult ReturnResult = new ReturnResult(); ConnStr(0); System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.Text; cmd.CommandTimeout = 300; System.Data.SqlClient.SqlDataAdapter Adp; cmd.CommandText = "Select Login_id,Check_result from SchManage.dbo.V_Sys_id where Login_id=@Login_id and Pwd=@PWD"; //只要不是1就不是審核跟啟用 cmd.Parameters.AddWithValue("@Login_id", UserAccount); cmd.Parameters.AddWithValue("@PWD", GetMD5(UserPassWord.Trim().ToUpper())); Adp = new System.Data.SqlClient.SqlDataAdapter(cmd); DataSet DBViewDS = new DataSet(); try { Adp.Fill(DBViewDS); } catch (Exception ex) { ReturnResult.ReturnMsgNo = -99; ReturnResult.ReturnMsg = "WS的取得CheckUserAccount資料失敗" + ex.Message; return ReturnResult; } if (DBViewDS.Tables.Count == 0) { ReturnResult.ReturnMsgNo = -98; ReturnResult.ReturnMsg = "查詢不到您的帳號"; return ReturnResult; } if (DBViewDS.Tables[0].Rows.Count == 0) { ReturnResult.ReturnMsgNo = -98; ReturnResult.ReturnMsg = "查詢不到您的帳號"; return ReturnResult; } if (Convert.ToString(DBViewDS.Tables[0].Rows[0]["Login_id"]).ToLower() == UserAccount.Trim().ToLower()) { if (Convert.ToInt32(DBViewDS.Tables[0].Rows[0]["Check_result"]) == 1) { ReturnResult.ReturnMsgNo = 1; ReturnResult.ReturnMsg = "登入成功"; return ReturnResult; } else { ReturnResult.ReturnMsgNo = -97; ReturnResult.ReturnMsg = "帳號尚未啟用"; return ReturnResult; } } else { ReturnResult.ReturnMsgNo = -96; ReturnResult.ReturnMsg = "帳號不符"; return ReturnResult; } return ReturnResult; }
static RoomClient() { //string ConnectString = "metadata=res://*/Model2.csdl|res://*/Model2.ssdl|res://*/Model2.msl;provider=System.Data.SqlClient;provider connection string="Server=192.168.2.24,8605;Database=dbroom;User ID=sa;Password=654321;MultipleActiveResultSets=True""; //System.Data.SqlClient.SqlConnectionStringBuilder sqlConnection = new System.Data.SqlClient.SqlConnectionStringBuilder(); //sqlConnection.DataSource = @"192.168.2.24,8605"; //sqlConnection.ApplicationName = ""; //sqlConnection.InitialCatalog = "dbroom"; //sqlConnection.IntegratedSecurity = true; //sqlConnection.PersistSecurityInfo = true; //sqlConnection.UserID = "sa"; //sqlConnection.Password ="******"; //string connectString = "Server=10.21.99.82;Database=SecureDB;User ID=secure;Password=secure;"; string connectString = "data source=10.21.99.80;initial catalog=SecureDB;persist security info=True;user id=secure;password=secure;MultipleActiveResultSets=True;App=EntityFramework;"; string dir = System.Diagnostics.Process.GetCurrentProcess().MainModule.FileName; dir = dir.Remove(dir.LastIndexOf('\\')); if (System.IO.File.Exists(dir +@"\Server.txt")) { string serverSet = System.IO.File.ReadLines(dir + @".\Server.txt").First(); if (serverSet.Length < 300) { connectString = serverSet; } } //System.Data.SqlClient.SqlConnection s = new System.Data.SqlClient.SqlConnection(sqlConnection.ConnectionString); //System.Data.EntityClient.EntityConnectionStringBuilder ecsb = new System.Data.EntityClient.EntityConnectionStringBuilder(); //ecsb.Provider = "System.Data.SqlClient"; //ecsb.ProviderConnectionString = sqlConnection.ConnectionString; //ecsb.Metadata = @"res://*/Model2.csdl|res://*/Model2.ssdl|res://*/Model2.msl"; //System.Data.EntityClient.EntityConnection ec = new System.Data.EntityClient.EntityConnection(ecsb.ConnectionString); //dbroomClientEntities dbroom = new dbroomClientEntities(ec); try { System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter("Select * from tblHostConfig;", connectString); System.Data.DataTable DT = new System.Data.DataTable(); adapter.Fill(DT); adapter.Dispose(); objUrl = "tcp://" + DT.Rows[0]["IP"] + ":" + DT.Rows[0]["Port"] + "/RoomObj"; System.Runtime.Remoting.Channels.Tcp.TcpChannel tcp = new System.Runtime.Remoting.Channels.Tcp.TcpChannel(0); System.Runtime.Remoting.Channels.ChannelServices.RegisterChannel(tcp, false); //var HostData = (from o in dbroom.tblHostConfigs select o).First(); //objUrl = "tcp://" + HostData.IP + ":" + HostData.Port + "/RoomObj"; } catch (Exception ) { throw new Exception("資料庫讀取失敗"); } roomEvent.RoomEvent += new RoomEventHandler(RoomClient_RoomEvent); timer.Elapsed += new System.Timers.ElapsedEventHandler(timer_Elapsed); timer.Interval = 10000; timer.Start(); }
public static System.Data.DataTable GetDataTable(string sql, System.Data.SqlClient.SqlConnection conn) { System.Data.SqlClient.SqlDataAdapter adp = new System.Data.SqlClient.SqlDataAdapter(sql, conn); adp.MissingSchemaAction = System.Data.MissingSchemaAction.AddWithKey; System.Data.DataSet ds = new System.Data.DataSet(); adp.Fill(ds); System.Data.DataTable tbl = ds.Tables[0]; return tbl; }
public U_Dt_Zwd(string strSQL, U_Zwd.U_Db_Zwd pudb) { udb = pudb; udb.Connect(); dta = new System.Data.SqlClient.SqlDataAdapter(strSQL, udb.Connection); cmd = new System.Data.SqlClient.SqlCommandBuilder(dta); dst = new System.Data.DataSet(); dta.Fill(dst); dt = dst.Tables[0]; dstDeleted = new System.Data.DataSet(); dta.Fill(dstDeleted); dtDeleted = dstDeleted.Tables[0]; udb.DisConnect(); }
private System.Data.DataSet MyDataSet() { System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(str_con); con.Open(); da_1 = new System.Data.SqlClient.SqlDataAdapter(SQL_string, con); System.Data.DataSet data_set = new System.Data.DataSet(); da_1.Fill(data_set, "Test"); con.Close(); return data_set; }
public System.Data.DataSet consultaSimple(string sql_qry) { System.Data.SqlClient.SqlDataAdapter adaptador = new System.Data.SqlClient.SqlDataAdapter(sql_qry, con); System.Data.DataSet datos = new System.Data.DataSet(); adaptador.Fill(datos, "resultado"); return datos; }
public DataTable Search_Data(string query) { Conexion conn = new Conexion(); conn.Begin_conexion(); System.Data.SqlClient.SqlDataAdapter qu; DataSet ds = new DataSet(); qu = new System.Data.SqlClient.SqlDataAdapter(query, conn.conexion); qu.Fill(ds); conn.conexion.Close(); return ds.Tables[0]; }
private System.Data.DataSet MyDataSet() { System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(strCon); con.Open(); dataAdap1 = new System.Data.SqlClient.SqlDataAdapter(sql_string, con); System.Data.DataSet dataSet = new System.Data.DataSet(); dataAdap1.Fill(dataSet, "Table_Data_1"); con.Close(); return dataSet; }
public DataTable EjecutarProcedimientoAlmacenado(string nombreProceso,string datos) { Conexion conn = new Conexion(); conn.Begin_conexion(); System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(nombreProceso); command.CommandType = CommandType.StoredProcedure; string cadenaProcedimiento = nombreProceso + datos; System.Data.SqlClient.SqlDataAdapter oAdapter = new System.Data.SqlClient.SqlDataAdapter(cadenaProcedimiento,conn.conex); DataTable oTabla = new DataTable(); oAdapter.Fill(oTabla); return oTabla; }
private System.Data.DataSet MyDataSet() { System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(strCon); // SqlConnection will use our connection string in strCon to connect to the database. con.Open();//we open a connection to the database. da_1 = new System.Data.SqlClient.SqlDataAdapter(sql_string, con);//which records and which database System.Data.DataSet dat_set = new System.Data.DataSet();//l hold all the records from the table. da_1.Fill(dat_set, "Table_Data_1"); con.Close( ); return dat_set; }
//this takes a SQL select and returns a dataset public System.Data.DataSet GetDataSet(string SQL) { if (_connection.State != System.Data.ConnectionState.Open) { OpenConnection(connectionstring); } System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter (); System.Data.DataSet ds = new System.Data.DataSet(); _command.Connection = _connection; _command.CommandText = SQL; da.SelectCommand = _command; da.Fill(ds); return ds; }
protected System.Data.DataTable getData(string sql) { try { System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(sql, conNo); System.Data.DataTable dtable = new System.Data.DataTable(); adapter.Fill(dtable); return dtable; } catch(System.Exception ex) { conCloseNo(); return null; } }
protected void Button1_Click(object sender, EventArgs e) { System.Data.SqlClient.SqlConnection _SqlConnection = new System.Data.SqlClient.SqlConnection(); _SqlConnection.ConnectionString = "Server=tcp:iaz43o1slt.database.windows.net,1433;Database=NewCustomerDb2;User ID=Singhav@iaz43o1slt;Password=Tirlok_0173;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;"; System.Data.SqlClient.SqlCommand _SqlCommand = new System.Data.SqlClient.SqlCommand("Select * From customer", _SqlConnection); System.Data.SqlClient.SqlDataAdapter _SqlDataAdapter = new System.Data.SqlClient.SqlDataAdapter(); _SqlDataAdapter.SelectCommand = _SqlCommand; DataTable _DataTable = new DataTable(); _DataTable.Locale = System.Globalization.CultureInfo.InvariantCulture; _SqlDataAdapter.Fill(_DataTable); GridView1.DataSource = _DataTable; GridView1.DataBind(); }
private void cbxCategories_SelectedIndexChanged(object sender, EventArgs e) { if (!this.bBlockContent && !System.Convert.IsDBNull( this.cbxCategories.SelectedValue )){ Guid cat_id = (Guid)this.cbxCategories.SelectedValue; System.Data.SqlClient.SqlCommand tp_cmd = Producer.ProductTypes.Select(cat_id); tp_cmd.Connection = this.cConnection; System.Data.SqlClient.SqlDataAdapter tpda = new System.Data.SqlClient.SqlDataAdapter(tp_cmd); this.types_table = new System.Data.DataTable("Types"); tpda.Fill(this.types_table); this.clbxTypes.DataSource = this.types_table; this.clbxTypes.DisplayMember = "Name"; this.clbxTypes.ValueMember = "TypeId"; this.RefreshProductList(cat_id, null ); } return; }
private void ByMonths(object sender, EventArgs e) { try { System.Data.SqlClient.SqlCommand mnth = Statistics.Purchases.ByMonths(); mnth.Connection = this.connection; System.Data.SqlClient.SqlDataAdapter sda = new System.Data.SqlClient.SqlDataAdapter(mnth); System.Data.DataTable st = new System.Data.DataTable("Summary"); sda.Fill(st); ZedGraph.GraphPane pane = this.zgcStatistics.GraphPane; pane.CurveList.Clear(); pane.GraphObjList.Clear(); pane.YAxis.Title.Text = "Сумма, р"; ZedGraph.PointPairList list = new ZedGraph.PointPairList(); foreach (System.Data.DataRow row in st.Rows) { int year = 1970; int month = 1; if(!System.Convert.IsDBNull(row["Year"]) && !System.Convert.IsDBNull(row["Month"]) ){ year = (int)row["Year"]; month = (int)row["Month"]; System.DateTime dt = new DateTime(year, month, 1); ZedGraph.XDate xDate = new ZedGraph.XDate(dt); decimal val = (decimal)row["Summary"]; list.Add(xDate.XLDate, (double)val); } } ZedGraph.BarItem curve = pane.AddBar("", list, Color.Blue); // Для оси X установим календарный тип pane.XAxis.Type = ZedGraph.AxisType.Date; // pretty it up a little pane.Chart.Fill = new ZedGraph.Fill(Color.White, Color.LightGoldenrodYellow, 45.0f); pane.Fill = new ZedGraph.Fill(Color.White, Color.FromArgb(220, 220, 255), 45.0f); // Tell ZedGraph to calculate the axis ranges this.zgcStatistics.AxisChange(); this.zgcStatistics.Invalidate(); }catch (System.Exception ex){ MessageBox.Show(ex.Message); } return; }
private void MakersListForm_Load(object sender, EventArgs e) { this.lvMakers.Columns.Add("№", 30); this.lvMakers.Columns.Add("Идентификатор", 30); this.lvMakers.Columns.Add("Наименование организации", 190); this.lvMakers.Columns.Add("Тип", 50); this.lvMakers.Columns.Add("Продавец", 50); //this.lvMakers.Columns.Add("Создан", 50); System.Data.SqlClient.SqlCommand cmd = Producer.Maker.Select(-1); cmd.Connection = this.cConnection; System.Data.SqlClient.SqlDataAdapter sda = new System.Data.SqlClient.SqlDataAdapter(cmd); this.makers = new System.Data.DataTable("Makers"); sda.Fill(this.makers); this.RefreshMakersList(); }
protected System.Data.DataTable doAddUpdateDelete(System.Data.SqlClient.SqlCommand sql, bool ReturnDataTable) { try { sql.Connection = conNo; sql.CommandType = System.Data.CommandType.StoredProcedure; System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(sql); System.Data.DataTable dtable = new System.Data.DataTable(); adapter.Fill(dtable); return dtable; } catch (System.Exception ex) { conCloseNo(); return null; } }
protected void Page_Load(object sender, EventArgs e) { System.Data.SqlClient.SqlConnection _SqlConnection = new System.Data.SqlClient.SqlConnection(); _SqlConnection.ConnectionString = "Server=tcp:iaz43o1slt.database.windows.net,1433;Database=TestSqlDatabse;User ID=Singhav@iaz43o1slt;Password=Tirlok_0173;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;"; System.Data.SqlClient.SqlCommand CustomerlCommand = new System.Data.SqlClient.SqlCommand("Select * From student", _SqlConnection); System.Data.SqlClient.SqlCommand OrderCommand = new System.Data.SqlClient.SqlCommand("Select * From order", _SqlConnection); System.Data.SqlClient.SqlCommand DetailsCommand = new System.Data.SqlClient.SqlCommand("Select * From orderDetails", _SqlConnection); System.Data.SqlClient.SqlDataAdapter CustomerAdapter = new System.Data.SqlClient.SqlDataAdapter(); System.Data.SqlClient.SqlDataAdapter OrderAdapter = new System.Data.SqlClient.SqlDataAdapter(); System.Data.SqlClient.SqlDataAdapter DetailsAdapter = new System.Data.SqlClient.SqlDataAdapter(); CustomerAdapter.SelectCommand = CustomerlCommand; DataTable _DataTable = new DataTable(); DataTable _DataTable2 = new DataTable(); DataTable _DataTable3 = new DataTable(); _DataTable.Locale = System.Globalization.CultureInfo.InvariantCulture; CustomerAdapter.Fill(_DataTable); GridView1.DataSource = _DataTable; GridView1.DataBind(); /* OrderAdapter.SelectCommand = OrderCommand; // DataTable _DataTable = new DataTable(); _DataTable2.Locale = System.Globalization.CultureInfo.InvariantCulture; OrderAdapter.Fill(_DataTable2); GridView2.DataSource = _DataTable2; GridView2.DataBind(); */ DetailsAdapter.SelectCommand = DetailsCommand; // DataTable _DataTable = new DataTable(); _DataTable3.Locale = System.Globalization.CultureInfo.InvariantCulture; DetailsAdapter.Fill(_DataTable3); GridView3.DataSource = _DataTable3; GridView3.DataBind(); }
private void EditCompanyForm_Load(object sender, EventArgs e) { System.Data.SqlClient.SqlCommand cmd = Brands.Companies.Select(Guid.Empty); cmd.Connection = this.connection; System.Data.SqlClient.SqlDataAdapter sda = new System.Data.SqlClient.SqlDataAdapter(cmd); System.Data.DataTable companies = new System.Data.DataTable("Companies"); sda.Fill(companies); DataRow dr = companies.NewRow(); dr["CompanyName"] = "Компания не выбрана"; dr["CompanyID"] = DBNull.Value; companies.Rows.InsertAt(dr, 0); this.cbxCompanies.DataSource = companies; this.cbxCompanies.DisplayMember = "CompanyName"; this.cbxCompanies.ValueMember = "CompanyID"; if (this.parent != null) this.cbxCompanies.SelectedValue = this.parent["CompanyID"]; else if (!System.Convert.IsDBNull(this.company["ParentID"])) this.cbxCompanies.SelectedValue = this.company["ParentID"]; else this.cbxCompanies.SelectedValue = DBNull.Value; // A new company or existing? if (this.company.RowState == DataRowState.Unchanged || this.company.RowState == DataRowState.Added) { this.Text = string.Format("Редактирование компании ID: {0}", this.company["CompanyID"]); string col_name = "CompanyName"; if (!System.Convert.IsDBNull(this.company[col_name])) this.tbxCompanyName.Text = (string)this.company[col_name]; col_name = "Address"; if (!System.Convert.IsDBNull(this.company[col_name])) this.tbxAddress.Text = (string)this.company[col_name]; col_name = "WebSite"; if (!System.Convert.IsDBNull(this.company[col_name])) this.tbxWebAddress.Text = (string)this.company[col_name]; col_name = "Phones"; if (!System.Convert.IsDBNull(this.company[col_name])) this.tbxPhones.Text = (string)this.company[col_name]; } else { this.Text = "Создание новой компании"; } return; }
private System.Data.DataSet MyDataSet() { System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(strCon); con.Open(); if (con != null && con.State == System.Data.ConnectionState.Open) { MessageBox.Show("database is open"); } else { MessageBox.Show("database is closed"); } da_1 = new System.Data.SqlClient.SqlDataAdapter(sql_string, con); System.Data.DataSet dat_set = new System.Data.DataSet(); da_1.Fill(dat_set, "Users"); con.Close(); return dat_set; }
public static System.Data.DataTable GetDataTable(string strSQL) { System.Data.DataTable dt = new System.Data.DataTable(); System.Data.SqlClient.SqlConnectionStringBuilder csb = new System.Data.SqlClient.SqlConnectionStringBuilder(); csb.DataSource = System.Environment.MachineName; csb.DataSource = @"VMSTZHDB08\SZH_DBH_1"; csb.InitialCatalog = "HBD_CAFM_V3"; csb.IntegratedSecurity = true; using (System.Data.Common.DbDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(strSQL, csb.ConnectionString)) { da.Fill(dt); } return dt; }
/// <summary> /// Fills a DataTable from this report's query /// </summary> /// <param name="DBConnectionString">a working DB connection string</param> /// <returns>one DataTable</returns> public System.Data.DataTable GetDataTable(string DBConnectionString) { System.Data.DataTable dataTable = new System.Data.DataTable(); //OLEDB parameters if (DBConnectionString.Contains("Provider=")) { using (System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(this.Query.CommandText, DBConnectionString)) { if (this.Query.QueryParameters.Count > 0) { foreach (QueryParameter param in this.Query.QueryParameters) { string paramName = param.Name.Replace("@", ""); //OLEDB chokes on the @symbol, it prefers ? marks da.SelectCommand.CommandText = da.SelectCommand.CommandText.Replace(param.Name, "?"); switch (param.DataType) { case "Text": da.SelectCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter(paramName, System.Data.OleDb.OleDbType.VarWChar) { Value = param.Value }); break; case "Boolean": da.SelectCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter(paramName, System.Data.OleDb.OleDbType.Boolean) { Value = param.Value }); break; case "DateTime": da.SelectCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter(paramName, System.Data.OleDb.OleDbType.Date) { Value = param.Value }); break; case "Integer": da.SelectCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter(paramName, System.Data.OleDb.OleDbType.Integer) { Value = param.Value }); break; case "Float": da.SelectCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter(paramName, System.Data.OleDb.OleDbType.Decimal) { Value = param.Value }); break; default: da.SelectCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter(paramName, param.Value)); break; } } } da.Fill(dataTable); } } else //Sql Client parameters { using (System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(this.Query.CommandText, DBConnectionString)) { foreach (QueryParameter param in this.Query.QueryParameters) { string paramName = param.Name.Replace("@", ""); dynamic currentValue = param.Value; if (currentValue == null || currentValue.Equals($"=Parameters!{paramName}.Value")) { currentValue = DBNull.Value; } switch (param.DataType) { case "Text": da.SelectCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(paramName, System.Data.SqlDbType.VarChar) { Value = currentValue }); break; case "Boolean": da.SelectCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(paramName, System.Data.SqlDbType.Bit) { Value = currentValue }); break; case "DateTime": da.SelectCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(paramName, System.Data.SqlDbType.DateTime) { Value = currentValue }); break; case "Integer": da.SelectCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(paramName, System.Data.SqlDbType.Int) { Value = currentValue }); break; case "Float": da.SelectCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(paramName, System.Data.SqlDbType.Decimal) { Value = currentValue }); break; default: da.SelectCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(param.Name, currentValue)); break; } } da.Fill(dataTable); } } dataTable.TableName = this.Name; return(dataTable); }
/// <summary> /// 조회를 눌렀을 경우 /// </summary> /// <param name="sender">The source of the event.</param> /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param> private void btnSelect_Click(object sender, EventArgs e) { try { /*원래 조회시 마스터에 해당되는 항목을 가져와 다시 바인딩 해야되지만 * 현재 빠져 있어 상수값으로 대체 한다. * 추후 이동인차장이 추가 하기로 하였음 */ this.Cursor = Cursors.WaitCursor; dtDetail.Clear(); dtMaster.Clear(); string fromDate = Basic.MaskReplace(this.dtpFrom.Text.ToString().Trim());//((System.DateTime)this.dtpFrom.EditValue).ToString("yyyMMdd"); string sql = @"SELECT WRITE_DATE, DATA_NO AS CODE, CASE DATA_NO WHEN 200 THEN '입금' WHEN 300 THEN '전환' WHEN 900 THEN '해약' END AS GUBUN, CASE SIGN(COUNT(*)) WHEN 0 THEN '전표미발생' ELSE '전표발생' END AS CODE_NAME FROM AUTODOCU WHERE DATA_GUBUN = '85' AND WRITE_DATE = '{0}' AND DEPT_CODE = '002020102000' AND NODE_CODE = '1000' AND C_CODE = '1000' GROUP BY WRITE_DATE, DATA_NO " ; sql = string.Format(sql, fromDate); System.Data.SqlClient.SqlDataAdapter ad = new System.Data.SqlClient.SqlDataAdapter(sql, SQLConnectString); DataTable msCnt = new DataTable(); ad.Fill(msCnt); dtMaster.Load(msCnt.CreateDataReader()); int i200 = int.Parse(dtMaster.Compute("COUNT(CODE)", "CODE = '200'").ToString().Trim()); int i300 = int.Parse(dtMaster.Compute("COUNT(CODE)", "CODE = '300'").ToString().Trim()); int i900 = int.Parse(dtMaster.Compute("COUNT(CODE)", "CODE = '900'").ToString().Trim()); if (i200 <= 0) { dtMaster.Rows.Add(fromDate, "200", "입금", "전표미발생"); } if (i300 <= 0) { dtMaster.Rows.Add(fromDate, "300", "전환", "전표미발생"); } if (i900 <= 0) { dtMaster.Rows.Add(fromDate, "900", "해약", "전표미발생"); } } catch (Exception ex) { Basic.ShowMessage(3, ex.Message); } finally { this.Cursor = Cursors.Default; } }
private void LlenasSqlDA() { sqlDAAceroMdor.SelectCommand.Parameters["@IdObra"].Value = cmbIdObra.SelectedValue.ToString(); sqlDAAceroMdor.Fill(dsAceroMdor1, "AceroMdor"); }
public void Read(DataSetMuseum dataSet, Connection conn, Transaction tr) { dataAdapter = new System.Data.SqlClient.SqlDataAdapter(); dataAdapter.SelectCommand = new System.Data.SqlClient.SqlCommand("SELECT * FROM Maecenas", conn.getConnection(), tr.getTransaction()); dataAdapter.Fill(dataSet, "Maecenas"); }
public DataTable SP_28_GET_LISTA_PRODUCTO_LLAMADAS(DateTime?FECHA_CADUCUDAD, string ID_CATEGORIA, string ID_COMPAÑIA, string ID_CLIENTE) { Conectar(); DataTable result = new DataTable(); try { System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand("SP_28_GET_LISTA_PRODUCTO_LLAMADAS", cnn); command.CommandType = System.Data.CommandType.StoredProcedure; command.CommandTimeout = _commandTimeout; command.Parameters.Add(new System.Data.SqlClient.SqlParameter("@FECHA_CADUCUDAD", System.Data.SqlDbType.DateTime)); if (FECHA_CADUCUDAD == null) { command.Parameters["@FECHA_CADUCUDAD"].Value = DBNull.Value; } else { command.Parameters["@FECHA_CADUCUDAD"].Value = FECHA_CADUCUDAD; } command.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ID_CATEGORIA", System.Data.SqlDbType.DateTime)); if (string.IsNullOrEmpty(ID_CATEGORIA) || string.IsNullOrWhiteSpace(ID_CATEGORIA)) { command.Parameters["@ID_CATEGORIA"].Value = DBNull.Value; } else { command.Parameters["@ID_CATEGORIA"].Value = ID_CATEGORIA; } command.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ID_COMPAÑIA", System.Data.SqlDbType.DateTime)); if (string.IsNullOrEmpty(ID_COMPAÑIA) || string.IsNullOrWhiteSpace(ID_COMPAÑIA)) { command.Parameters["@ID_COMPAÑIA"].Value = DBNull.Value; } else { command.Parameters["@ID_COMPAÑIA"].Value = ID_COMPAÑIA; } command.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ID_CLIENTE", System.Data.SqlDbType.DateTime)); if (string.IsNullOrEmpty(ID_CLIENTE) || string.IsNullOrWhiteSpace(ID_CLIENTE)) { command.Parameters["@ID_CLIENTE"].Value = DBNull.Value; } else { command.Parameters["@ID_CLIENTE"].Value = ID_CLIENTE; } System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(command); da.Fill(result); command.Dispose(); } catch (Exception e) { result = null; Console.WriteLine(e.StackTrace); } finally { Desconectar(); } return(result); }// fin SP_28_GET_LISTA_PRODUCTO_LLAMADAS
public void Print() { try { string strReportPath; string strParm1 = ""; string strParm2 = ""; string strParm3 = ""; string strParm4 = ""; string strParm5 = ""; string strParm6 = ""; string strValue1 = Request.QueryString["value1"]; string[] parms = strValue1.Split(','); int intNumParms = parms.Count(); string strRpt = parms[0]; string strSP = parms[1]; if (intNumParms >= 3) { strParm1 = parms[2]; } if (intNumParms >= 4) { strParm2 = parms[3]; } if (intNumParms >= 5) { strParm3 = parms[4]; } if (intNumParms >= 6) { strParm4 = parms[5]; } if (intNumParms >= 7) { strParm5 = parms[6]; } if (intNumParms >= 8) { strParm6 = parms[7]; } System.Data.DataSet ds = new System.Data.DataSet(); System.Data.SqlClient.SqlConnection sqlcon = new System.Data.SqlClient.SqlConnection("Data Source=(local);Initial Catalog=ULS_db1;User ID=uls2008;Password=uls2008"); System.Data.SqlClient.SqlCommand comand = new System.Data.SqlClient.SqlCommand(); comand.Connection = sqlcon; comand.CommandText = strSP; comand.CommandType = System.Data.CommandType.StoredProcedure; switch (strRpt) { case "ElectronicsAssignedTo": strReportPath = Server.MapPath("~/Reports/ElectronicsAssignTo.rpt"); comand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@assignedTo", System.Data.SqlDbType.VarChar, 50)); comand.Parameters["@assignedTo"].Value = strParm1; break; case "ElectronicsAssignToHist": strReportPath = Server.MapPath("~/Reports/EquipAssignToHist.rpt"); comand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@assignedTo", System.Data.SqlDbType.VarChar, 50)); comand.Parameters["@assignedTo"].Value = strParm1; comand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@fromDt", System.Data.SqlDbType.DateTime)); comand.Parameters["@fromDt"].Value = Convert.ToDateTime(strParm2); comand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@toDt", System.Data.SqlDbType.DateTime)); comand.Parameters["@toDt"].Value = Convert.ToDateTime(strParm3); break; case "ElectronicsTotalInventory": strReportPath = Server.MapPath("~/Reports/EquipTotalInventory.rpt"); comand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@div", System.Data.SqlDbType.VarChar, 50)); comand.Parameters["@div"].Value = strParm1; break; case "ElectronicsTotalInvRegBy": strReportPath = Server.MapPath("~/Reports/EquipTotalInvRegBy.rpt"); comand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@div", System.Data.SqlDbType.VarChar, 50)); comand.Parameters["@div"].Value = strParm1; break; case "ElectronicsInvByType": strReportPath = Server.MapPath("~/Reports/ElectronicsInvByType.rpt"); comand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@regBy", System.Data.SqlDbType.VarChar, 50)); comand.Parameters["@regBy"].Value = strParm1; comand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@type_id", System.Data.SqlDbType.Int)); comand.Parameters["@type_id"].Value = Convert.ToInt32(strParm2); break; case "ElectronicsAirCardInv": strReportPath = Server.MapPath("~/Reports/ElectronicsAirCardInv.rpt"); comand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@regBy", System.Data.SqlDbType.VarChar, 50)); comand.Parameters["@regBy"].Value = strParm1; break; case "ElectronicsInvByLoc": strReportPath = Server.MapPath("~/Reports/EquipInvByLoc.rpt"); comand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@regBy", System.Data.SqlDbType.VarChar, 50)); comand.Parameters["@regBy"].Value = strParm1; comand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@workLoc", System.Data.SqlDbType.VarChar, 50)); comand.Parameters["@workLoc"].Value = strParm2; break; default: strReportPath = Server.MapPath("~/Reports/EquipAssinedTo.rpt"); break; } System.Data.SqlClient.SqlDataAdapter sqladp = new System.Data.SqlClient.SqlDataAdapter(comand); sqlcon.Open(); sqladp.Fill(ds, "myDataSet"); CrystalDecisions.CrystalReports.Engine.ReportDocument oRpt = new CrystalDecisions.CrystalReports.Engine.ReportDocument(); oRpt.Load(strReportPath); oRpt.SetDataSource(ds.Tables[0]); CrystalReportViewer1.ReportSource = oRpt; sqlcon.Close(); } catch (Exception ex) { string msg = ex.Message; Logit(msg); } }
private void QueryButton_Click(object sender, EventArgs e) { var sqlcon = new System.Data.SqlClient.SqlConnection(); string sqlqueryString; System.Data.SqlClient.SqlCommand sqlcommand; System.Data.SqlClient.SqlDataReader reader; if (IntegratedSecurityCheckBox.Checked == true) { sqlcon.ConnectionString = "Data Source=" + SQLSrvNameTextBox.Text + ";Database=" + SQLDBNameTextBox.Text + ";Integrated Security=true;"; } else { sqlcon.ConnectionString = "Data Source=" + SQLSrvNameTextBox.Text + ";Database=" + SQLDBNameTextBox.Text + ";User ID =" + SQLUserTextBox.Text + ";Password="******";"; } if (string.IsNullOrEmpty(SQLQueryTextBox.Text.Trim())) { MessageBox.Show("Must have any SQL query in query window and try again.", "No query", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } // Query rebuilding int iDataGrid; string sSQLQuery = SQLQueryTextBox.Text; var loopTo = DataGridView1.RowCount - 2; // '[{account_name}] for (iDataGrid = 0; iDataGrid <= loopTo; iDataGrid++) { sSQLQuery = sSQLQuery.Replace(DataGridView1[0, iDataGrid].Value.ToString(), DataGridView1[1, iDataGrid].Value.ToString()); } // sqlqueryString = SQLQueryTextBox.Text sqlqueryString = sSQLQuery; sqlcommand = new System.Data.SqlClient.SqlCommand(sqlqueryString, sqlcon); // SQLQueryTextBox.Text = "" try { sqlcon.Open(); } catch (Exception ex) { sqlcon.Close(); MessageBox.Show(ex.Message); return; } try { reader = sqlcommand.ExecuteReader(); reader.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message + "Your query is:" + Constants.vbNewLine + sSQLQuery); return; } QueryResultTextBox.Clear(); QueryResultTextBox.AppendText("ServerVersion: " + sqlcon.ServerVersion + Constants.vbNewLine); QueryResultTextBox.AppendText("DataSource: " + sqlcon.DataSource + Constants.vbNewLine); QueryResultTextBox.AppendText("Database: " + sqlcon.Database + Constants.vbNewLine); QueryResultTextBox.AppendText(Constants.vbNewLine); int ReadCount = 0; if (reader.RecordsAffected == -1) { var QueryData = new DataTable(); var QueryDA = new System.Data.SqlClient.SqlDataAdapter(sSQLQuery, sqlcon); try { ReadCount = QueryDA.Fill(QueryData); } catch (Exception ex) { sqlcon.Close(); MessageBox.Show(ex.Message + "Your query is:" + Constants.vbNewLine + sSQLQuery); sqlcon.Close(); return; } SQLDataGridView.DataSource = QueryData; } sqlcon.Close(); QueryResultTextBox.AppendText(Constants.vbNewLine); QueryResultTextBox.AppendText("Readed of '" + Conversions.ToString(ReadCount) + "' lines" + Constants.vbNewLine + "Affected of '" + Conversions.ToString(reader.RecordsAffected) + "' lines"); }
private void IzvjVozila_Load(object sender, System.EventArgs e) { daIzvVozila.Fill(this.dsIzvVozila1, "Vozila"); crystalReportViewer1.ReportSource = rpt; rpt.SetDataSource(this.dsIzvVozila1); }
private void btnAccountDelete_Click(object sender, EventArgs e) { try { this.Cursor = Cursors.WaitCursor; if (dtDetail.Rows.Count <= 0) { return; } else if (this.gridViewMaster.FocusedRowHandle < 0) { return; } string code = this.gridViewMaster.GetRowCellValue(this.gridViewMaster.FocusedRowHandle, "CODE").ToString().Trim(); string Write_date = this.gridViewMaster.GetRowCellValue(this.gridViewMaster.FocusedRowHandle, "WRITE_DATE").ToString().Trim(); /*체크한다.*/ string sql = @" SELECT COUNT(DOCU_STAT) AS CNT FROM AUTODOCU WHERE DATA_GUBUN = '85' AND DATA_NO = {0} AND WRITE_DATE = '{1}' AND DEPT_CODE = '002020102000' AND NODE_CODE = '1000' AND C_CODE = '1000' AND DOCU_STAT = 1" ; string sqldel = @" DELETE FROM AUTODOCU WHERE DATA_GUBUN = '85' AND DATA_NO = {0} AND WRITE_DATE = '{1}' AND DEPT_CODE = '002020102000' AND NODE_CODE = '1000' AND C_CODE = '1000' " ; /*카운터가 0 보다 크면 삭제 불가 * 0이면 삭제가능 */ sql = string.Format(sql, code, Write_date); sqldel = string.Format(sqldel, code, Write_date); string sql2 = "DELETE " + sql; System.Data.SqlClient.SqlDataAdapter ad = new System.Data.SqlClient.SqlDataAdapter(sql, SQLConnectString); DataTable dtCnt = new DataTable(); ad.Fill(dtCnt); if (int.Parse(dtCnt.Rows[0][0].ToString().Trim()) > 0) { Basic.ShowMessage(1, "이미 전표처리되어 삭제할 수 없습니다."); return; } using (System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(SQLConnectString)) { try { cn.Open(); } catch (System.Data.SqlClient.SqlException sqlex) { Basic.ShowMessage(3, sqlex.Message); cn.Dispose(); return; } System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sqldel, cn); int row = cmd.ExecuteNonQuery(); if (row > 0) { Basic.ShowMessage(1, "삭제 하였습니다."); CHK_MK("D_MK"); InvokeOnClick(this.btnSelect, new EventArgs()); } else { Basic.ShowMessage(1, "삭제할 전표가 없습니다."); } if (cn.State == ConnectionState.Open) { cn.Close(); } cn.Dispose(); } } catch (Exception ex) { Basic.ShowMessage(3, ex.Message); } finally { this.Cursor = Cursors.Default; } }
private void btnVistaPrevia_Click(object sender, System.EventArgs e) { crvInfAcero.Visible = true; dsAceroRep1.Clear(); sqlDAInfAcero.SelectCommand.Parameters["@IdObra"].Value = cmbIdObra.SelectedValue.ToString(); sqlDAInfAcero.SelectCommand.Parameters["@Folio1"].Value = (chbTodos.Checked ? " " : txtFolio.Text); sqlDAInfAcero.SelectCommand.Parameters["@FolioFin"].Value = (chbTodos.Checked ? "zzzzz" : txtFolio.Text); sqlDAInfAcero.SelectCommand.Parameters["@IdUnidad"].Value = cmbUnidad.SelectedValue; sqlDAInfAcero.SelectCommand.Parameters["@Numren"].Value = txtRenglon.Text; sqlDAInfAcero.Fill(dsAceroRep1, "AceroRep"); sqlDAFirmas.Fill(dsFirmas1, "Firmas"); sqlDANorma.SelectCommand.Parameters["@IdNorma"].Value = 3; sqlDANorma.Fill(dsAceroRep1, "Normas"); string unidad1 = (cmbUnidad.SelectedValue.ToString() == "2" ? "kilonewton ***" : "kgf ***"); string conversion = (cmbUnidad.SelectedValue.ToString() == "2" ? "* Para convertir a kg/cm² multiplique por 10,19716" : "* Para convertir en Mega Pascales multiplique por 0,0980665"); string conversion1 = (cmbUnidad.SelectedValue.ToString() == "2" ? "*** Para convertir a kgf multiplique por 101,9716" : "*** Para convertir en kilonewton multiplique por 0,00980665"); if (rbColada.Checked) { cryRepAcero1.PrintOptions.PaperOrientation = CrystalDecisions.Shared.PaperOrientation.Landscape; cryRepAcero1.SetDataSource(dsAceroRep1); cryRepAcero1.SetParameterValue("@F1", dsFirmas1.Tables[0].Rows[0][1].ToString()); cryRepAcero1.SetParameterValue("@F2", dsFirmas1.Tables[0].Rows[1][1].ToString()); cryRepAcero1.SetParameterValue("@F3", dsFirmas1.Tables[0].Rows[2][1].ToString()); cryRepAcero1.SetParameterValue("@F4", dsFirmas1.Tables[0].Rows[3][1].ToString()); cryRepAcero1.SetParameterValue("@F5", dsFirmas1.Tables[0].Rows[4][1].ToString()); cryRepAcero1.SetParameterValue("@C1", dsFirmas1.Tables[0].Rows[0][2].ToString()); cryRepAcero1.SetParameterValue("@C2", dsFirmas1.Tables[0].Rows[1][2].ToString()); cryRepAcero1.SetParameterValue("@C3", dsFirmas1.Tables[0].Rows[2][2].ToString()); cryRepAcero1.SetParameterValue("@C4", dsFirmas1.Tables[0].Rows[3][2].ToString()); cryRepAcero1.SetParameterValue("@C5", dsFirmas1.Tables[0].Rows[4][2].ToString()); cryRepAcero1.SetParameterValue("@Unidad", dsUnidad1.Tables[0].Rows[cmbUnidad.SelectedIndex][1].ToString()); cryRepAcero1.SetParameterValue("@Duplicado", chbDuplicado.Checked); cryRepAcero1.SetParameterValue("@Unidad1", unidad1); cryRepAcero1.SetParameterValue("@Conversion", conversion); cryRepAcero1.SetParameterValue("@Conversion1", conversion1); cryRepAcero1.SetParameterValue("@Ruta", chbFormato.Checked); cryRepAcero1.SetParameterValue("@Leyenda", chbLeyenda.Checked); cryRepAcero1.SetParameterValue("@Lab", chbLab.Checked); cryRepAcero1.SetParameterValue("@Ineco", chbLab1.Checked); cryRepAcero1.SetParameterValue("@Respaldo", chbRespaldo.Checked); crvInfAcero.ReportSource = cryRepAcero1; } else { cryRepAceroO1.PrintOptions.PaperOrientation = CrystalDecisions.Shared.PaperOrientation.Landscape; cryRepAceroO1.SetDataSource(dsAceroRep1); cryRepAcero1.SetParameterValue("@F1", dsFirmas1.Tables[0].Rows[0][1].ToString()); cryRepAceroO1.SetParameterValue("@F1", dsFirmas1.Tables[0].Rows[0][1].ToString()); cryRepAceroO1.SetParameterValue("@F2", dsFirmas1.Tables[0].Rows[1][1].ToString()); cryRepAceroO1.SetParameterValue("@F3", dsFirmas1.Tables[0].Rows[2][1].ToString()); cryRepAceroO1.SetParameterValue("@F4", dsFirmas1.Tables[0].Rows[3][1].ToString()); cryRepAceroO1.SetParameterValue("@F5", dsFirmas1.Tables[0].Rows[4][1].ToString()); cryRepAceroO1.SetParameterValue("@C1", dsFirmas1.Tables[0].Rows[0][2].ToString()); cryRepAceroO1.SetParameterValue("@C2", dsFirmas1.Tables[0].Rows[1][2].ToString()); cryRepAceroO1.SetParameterValue("@C3", dsFirmas1.Tables[0].Rows[2][2].ToString()); cryRepAceroO1.SetParameterValue("@C4", dsFirmas1.Tables[0].Rows[3][2].ToString()); cryRepAceroO1.SetParameterValue("@C5", dsFirmas1.Tables[0].Rows[4][2].ToString()); cryRepAceroO1.SetParameterValue("@Unidad", dsUnidad1.Tables[0].Rows[cmbUnidad.SelectedIndex][1].ToString()); cryRepAceroO1.SetParameterValue("@Duplicado", chbDuplicado.Checked); cryRepAceroO1.SetParameterValue("@Unidad1", unidad1); cryRepAceroO1.SetParameterValue("@Conversion", conversion); cryRepAceroO1.SetParameterValue("@Conversion1", conversion1); cryRepAceroO1.SetParameterValue("@Ruta", chbFormato.Checked); cryRepAceroO1.SetParameterValue("@Leyenda", chbLeyenda.Checked); cryRepAceroO1.SetParameterValue("@Lab", chbLab.Checked); cryRepAceroO1.SetParameterValue("@Ineco", chbLab1.Checked); cryRepAceroO1.SetParameterValue("@Respaldo", chbRespaldo.Checked); crvInfAcero.ReportSource = cryRepAceroO1; } //crvInfAcero.RefreshReport(); }
private void sqlConnectButton_Click(object sender, EventArgs e) { //define datasets and tables DataTable candidacyStandard = new DataTable(); if (maskedTextBox1.Text.Length == 4) { using (new CursorWait()) { //update UI label1.Text = "Fetching LECOM candidates. Please wait."; label2.Text = ""; label1.Refresh(); label2.Refresh(); maskedTextBox1.Visible = false; sqlConnectButton.Visible = false; string dbName = "tmsEPly", server = "SIS"; string connectionString = System.String.Format("Server={0};Database={1};Connection Timeout=90;Max Pool Size=2048;Pooling=true;Trusted_Connection=True;", server, dbName); string strSQLTemplate = "SELECT DISTINCT NAME_AND_ADDRESS.EMAIL_ADDRESS AS Email FROM CANDIDACY INNER JOIN NAME_AND_ADDRESS ON CANDIDACY.ID_NUM = NAME_AND_ADDRESS.ID_NUM WHERE yr_cde = {0}"; string yr_code = maskedTextBox1.Text; string strSQL = string.Format(strSQLTemplate, yr_code); try { conn = new System.Data.SqlClient.SqlConnection(connectionString); conn.Open(); //Check if the SQL Connection is open if (conn.State == System.Data.ConnectionState.Open) { System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(strSQL, conn); System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd); da.Fill(candidacyStandard); //make a table of candidates with values as strings DataTable candidacyStrings = new DataTable(); candidacyStrings.Columns.Add("Email", typeof(string)); cmd = null; if (candidacyStandard.Rows.Count > 0) { for (int r = 0; r < candidacyStandard.Rows.Count; r++) { string email = ""; email = candidacyStandard.Rows[r][0].ToString().Trim(); candidacyStrings.Rows.Add(email); } if (cn.IsOpen) { //delete any previously imported data string deleteSQL = "DELETE FROM LECOM_CURRENT_YEAR_CANDIDACY"; cn.Execute(deleteSQL, SQLTypes.Text); //populate a separate table in Sarah db with this year's candidates SqlParameters CParm = new SqlParameters(); CParm.Add("exampleDT", candidacyStrings, SqlDbType.Structured); CParm.List[0].TypeName = "dbo.LecomCurrentYearCandidacyTableType"; cn.Execute("dbo.LecomCurrentYearCandidacy", SQLTypes.StoredProcedure, CParm); label1.Text = "Candidate import complete! Please click 'Browse' and choose the excel spreadsheet you want to import."; browseButton.Visible = true; } } else { MessageBox.Show("Error: no candidacy results returned!"); } conn.Close(); } else { MessageBox.Show("Error: connection state not open. Current state is '" + conn.State.ToString() + "'"); } } catch (Exception ex) { MessageBox.Show("Unexpected error " + ex.ToString()); } } } else { MessageBox.Show("Year code not valid. Please enter a valid year code."); } }
private void toolStripButtonGD_Click(object sender, EventArgs e) { int i; string strTTemp = ""; cGetInformation.getSystemDateTime(); strDT0 = cGetInformation.strSYSDATATIME; labelZDRQ.Text = Convert.ToDateTime(strDT0).ToString("yyyy年M月dd日"); strDT0 = dateTimePickerE.Value.ToShortDateString(); strTTemp = dateTimePickerS.Value.ToShortDateString(); strDT1 = Convert.ToDateTime(strDT0).AddDays(-5).ToShortDateString(); if (Convert.ToDateTime(strDT1) < Convert.ToDateTime(strTTemp)) { strDT1 = strTTemp; } strDT2 = Convert.ToDateTime(strDT0).AddDays(-10).ToShortDateString(); if (Convert.ToDateTime(strDT2) < Convert.ToDateTime(strTTemp)) { strDT2 = strTTemp; } strDT3 = Convert.ToDateTime(strDT0).AddDays(-30).ToShortDateString(); if (Convert.ToDateTime(strDT3) < Convert.ToDateTime(strTTemp)) { strDT3 = strTTemp; } if (!checkBoxALL.Checked && intClassID != 0) //分类 { cGetInformation.getUnderClassInformation(intClassID); } string strA = "SELECT SUM(购进商品制单明细表.数量) AS 数量, 购进商品制单明细表.商品ID, MIN(商品表.分类编号) AS 分类编号 FROM 购进商品制单表 INNER JOIN 购进商品制单明细表 ON 购进商品制单表.ID = 购进商品制单明细表.表单ID INNER JOIN 商品表 ON 购进商品制单明细表.商品ID = 商品表.ID WHERE (购进商品制单表.BeActive = 1) AND (购进商品制单表.日期 <= CONVERT(DATETIME, '" + strDT0 + " 23:59:59', 102)) AND (购进商品制单表.日期 > CONVERT(DATETIME, '" + strDT1 + " 23:59:59', 102)) AND (商品表.beactive = 1) "; strA += " GROUP BY 购进商品制单明细表.商品ID"; string strB = "SELECT SUM(购进商品制单明细表.数量) AS 数量, 购进商品制单明细表.商品ID, MIN(商品表.分类编号) AS 分类编号 FROM 购进商品制单表 INNER JOIN 购进商品制单明细表 ON 购进商品制单表.ID = 购进商品制单明细表.表单ID INNER JOIN 商品表 ON 购进商品制单明细表.商品ID = 商品表.ID WHERE (购进商品制单表.BeActive = 1) AND (购进商品制单表.日期 <= CONVERT(DATETIME, '" + strDT1 + " 23:59:59', 102)) AND (购进商品制单表.日期 > CONVERT(DATETIME, '" + strDT2 + " 23:59:59', 102)) AND (商品表.beactive = 1) "; strB += " GROUP BY 购进商品制单明细表.商品ID"; string strC = "SELECT SUM(购进商品制单明细表.数量) AS 数量, 购进商品制单明细表.商品ID, MIN(商品表.分类编号) AS 分类编号 FROM 购进商品制单表 INNER JOIN 购进商品制单明细表 ON 购进商品制单表.ID = 购进商品制单明细表.表单ID INNER JOIN 商品表 ON 购进商品制单明细表.商品ID = 商品表.ID WHERE (购进商品制单表.BeActive = 1) AND (购进商品制单表.日期 <= CONVERT(DATETIME, '" + strDT2 + " 23:59:59', 102)) AND (购进商品制单表.日期 > CONVERT(DATETIME, '" + strDT3 + " 23:59:59', 102)) AND (商品表.beactive = 1) "; strC += " GROUP BY 购进商品制单明细表.商品ID"; string strD = "SELECT SUM(购进商品制单明细表.数量) AS 数量, 购进商品制单明细表.商品ID, MIN(商品表.分类编号) AS 分类编号 FROM 购进商品制单表 INNER JOIN 购进商品制单明细表 ON 购进商品制单表.ID = 购进商品制单明细表.表单ID INNER JOIN 商品表 ON 购进商品制单明细表.商品ID = 商品表.ID WHERE (购进商品制单表.BeActive = 1) AND (购进商品制单表.日期 <= CONVERT(DATETIME, '" + strDT3 + " 23:59:59', 102)) AND (购进商品制单表.日期 >= CONVERT(DATETIME, '" + dateTimePickerS.Value.ToShortDateString() + " 00:00:00', 102)) AND (商品表.beactive = 1) "; strD += " GROUP BY 购进商品制单明细表.商品ID"; sqlComm.CommandText = "SELECT 商品表.商品名称, 商品表.商品规格, 商品表.商品编号, 商品表.库存金额, 商品表.库存数量, [5天].数量 AS [0-5天], [10天].数量 AS [6-10天], [30天].数量 AS [11-30天], [30天以上].数量 AS [30天以上], 商品表.分类编号 FROM 商品表 LEFT OUTER JOIN (" + strD + ") [30天以上] ON 商品表.ID = [30天以上].商品ID LEFT OUTER JOIN (" + strC + ") [30天] ON 商品表.ID = [30天].商品ID LEFT OUTER JOIN (" + strB + ") [10天] ON 商品表.ID = [10天].商品ID LEFT OUTER JOIN (" + strA + ") [5天] ON 商品表.ID = [5天].商品ID WHERE (商品表.beactive = 1)"; if (!checkBoxALLSP.Checked && intCommID != 0) { sqlComm.CommandText += " AND (商品表.ID = " + intCommID.ToString() + ")"; } if (!checkBoxALL.Checked && intClassID != 0) //分类 { sqlComm.CommandText += " AND ((商品表.分类编号 = " + intClassID.ToString() + ")"; for (i = 0; i < cGetInformation.intUnderClassNumber; i++) { sqlComm.CommandText += " OR (商品表.分类编号 = " + cGetInformation.intUnderClass[i].ToString() + ")"; } sqlComm.CommandText += ") "; } sqlConn.Open(); if (dSet.Tables.Contains("商品表")) { dSet.Tables.Remove("商品表"); } sqlDA.Fill(dSet, "商品表"); sqlConn.Close(); adjustDataView(); dataGridViewDJMX.DataSource = dSet.Tables["商品表"]; dataGridViewDJMX.Columns[3].DefaultCellStyle.Format = "f2"; dataGridViewDJMX.Columns[4].DefaultCellStyle.Format = "f0"; dataGridViewDJMX.Columns[5].DefaultCellStyle.Format = "f0"; dataGridViewDJMX.Columns[6].DefaultCellStyle.Format = "f0"; dataGridViewDJMX.Columns[7].DefaultCellStyle.Format = "f0"; dataGridViewDJMX.Columns[8].DefaultCellStyle.Format = "f0"; dataGridViewDJMX.Columns[9].Visible = false; adjust(); }
private void FormXSSKCX_Load(object sender, EventArgs e) { sqlConn.ConnectionString = strConn; sqlComm.Connection = sqlConn; sqlDA.SelectCommand = sqlComm; cGetInformation = new ClassGetInformation(strConn); this.Top = 1; this.Left = 1; //得到开始时间 sqlConn.Open(); sqlComm.CommandText = "SELECT 公司宣传, 质量目标1, 质量目标2, 质量目标3, 质量目标4, 管理员权限, 总经理权限, 职员权限, 经理权限, 业务员权限 FROM 系统参数表"; sqldr = sqlComm.ExecuteReader(); while (sqldr.Read()) { try { LIMITACCESS1 = int.Parse(sqldr.GetValue(8).ToString()); } catch { LIMITACCESS1 = 15; } } sqldr.Close(); //初始化员工列表 sqlComm.CommandText = "SELECT ID, 职员编号, 职员姓名 FROM 职员表 WHERE (beactive = 1)"; if (dSet.Tables.Contains("职员表")) { dSet.Tables.Remove("职员表"); } sqlDA.Fill(dSet, "职员表"); object[] OTemp = new object[3]; OTemp[0] = 0; OTemp[1] = "全部"; OTemp[2] = "全部"; dSet.Tables["职员表"].Rows.Add(OTemp); comboBoxYWY.DataSource = dSet.Tables["职员表"]; comboBoxYWY.DisplayMember = "职员姓名"; comboBoxYWY.ValueMember = "ID"; comboBoxYWY.SelectedIndex = comboBoxYWY.Items.Count - 1; if (intUserLimit <= LIMITACCESS1) { comboBoxYWY.SelectedValue = intUserID; comboBoxYWY.Enabled = false; } //sqlComm.CommandText = "SELECT 开始时间 FROM 系统参数表"; sqlComm.CommandText = "SELECT GETDATE()"; sqldr = sqlComm.ExecuteReader(); while (sqldr.Read()) { //dateTimePickerS.Value = Convert.ToDateTime(sqldr.GetValue(0).ToString()); dateTimePickerS.Value = DateTime.Parse(Convert.ToDateTime(sqldr.GetValue(0).ToString()).Year.ToString() + "-" + Convert.ToDateTime(sqldr.GetValue(0).ToString()).Month.ToString() + "-1"); } sqldr.Close(); sqlConn.Close(); cGetInformation.getSystemDateTime(); string sDT = cGetInformation.strSYSDATATIME; labelZDRQ.Text = Convert.ToDateTime(sDT).ToString("yyyy年M月dd日"); labelCZY.Text = strUserName; }
/// <summary> /// 전표생성을 눌렀을경우 /// </summary> /// <param name="sender">The source of the event.</param> /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param> private void btnAccount_Click(object sender, EventArgs e) { try { this.Cursor = Cursors.WaitCursor; if (dtDetail.Rows.Count <= 0) { return; } else if (this.gridViewMaster.FocusedRowHandle < 0) { return; } string code = this.gridViewMaster.GetRowCellValue(this.gridViewMaster.FocusedRowHandle, "CODE").ToString().Trim(); string Write_date = this.gridViewMaster.GetRowCellValue(this.gridViewMaster.FocusedRowHandle, "WRITE_DATE").ToString().Trim(); if (CHK_MK("CHK") == "Y") { return; } /*기 전표 생성여부체크*/ /*삭제시 WHERE 조건동일사용*/ string SQL_CHECK_BILL = @"SELECT COUNT(BAN_DECI_DATE) AS CNT FROM AUTODOCU WHERE DATA_GUBUN = '85' AND DATA_NO = {0} AND WRITE_DATE = '{1}' AND DEPT_CODE = '002020102000' AND NODE_CODE = '1000' AND C_CODE = '1000'" ; /*{0} = 구분코드 {1} = 일자*/ /*있으면 * 전표삭제후 재생성 해야 합니다. */ SQL_CHECK_BILL = string.Format(SQL_CHECK_BILL, code, Write_date); System.Data.SqlClient.SqlDataAdapter ad = new System.Data.SqlClient.SqlDataAdapter(SQL_CHECK_BILL, SQLConnectString); DataTable dtCnt = new DataTable(); ad.Fill(dtCnt); if (int.Parse(dtCnt.Rows[0][0].ToString().Trim()) > 0) { Basic.ShowMessage(1, "기 생성된 전표가 존재 합니다.\n\r전표삭제 후 재생성 해야 합니다."); return; } string Query = @"INSERT INTO AUTODOCU ( DATA_GUBUN, WRITE_DATE, DATA_NO, DATA_LINE, DATA_SLIP, DEPT_CODE, NODE_CODE, C_CODE, DATA_CODE, DOCU_STAT, DOCU_TYPE, DOCU_GUBUN, AMT_GUBUN, DR_AMT, CR_AMT, ACCT_CODE, CHECK_CODE1, CHECK_CODE2, CHECK_CODE3, CHECK_CODE4, CHECKD_CODE1, CHECKD_CODE2, CHECKD_CODE3, CHECKD_CODE4, CHECKD_NAME1, CHECKD_NAME2, CHECKD_NAME3, CHECKD_NAME4 ) VALUES ('{0}' , '{1}' , {2} , {3} , {4} , '{5}' , '{6}' , '{7}' , '{8}' , '{9}' , '{10}' , '{11}' , '{12}' , {13} , {14} , '{15}' , '{16}' , '{17}' , '{18}' , '{19}' , '{20}' , '{21}' , '{22}' , '{23}' , '{24}' , '{25}' , '{26}' , '{27}' )"; using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope()) { using (System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(SQLConnectString)) { try { cn.Open(); } catch (System.Data.SqlClient.SqlException sqlex) { Basic.ShowMessage(3, sqlex.Message); cn.Dispose(); return; } this.pgb.Visible = true; this.pgb.Properties.Maximum = dtDetail.Rows.Count - 1; this.pgb.Properties.Minimum = 0; this.pgb.EditValue = 0; for (int i = 0; i < dtDetail.Rows.Count; i++) { this.pgb.EditValue = i; Application.DoEvents(); object[] obj = new object[28]; for (int j = 0; j < obj.Length; j++) { obj[j] = dtDetail.Rows[i][j].ToString().Trim(); } string insertQuery = string.Format(Query, obj); using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(insertQuery, cn)) { int row = cmd.ExecuteNonQuery(); cmd.Dispose(); } } string Query_In = @"INSERT INTO ONKET_ID SELECT '1000' C_CODE, CHECKD_CODE2, CHECKD_NAME2, '20101101' aS JOB_DATE FROM AUTODOCU WHERE DATA_GUBUN = '85' AND DATA_NO = {0} AND WRITE_DATE = '{1}' AND DEPT_CODE = '002020102000' AND NODE_CODE = '1000' AND C_CODE = '1000' AND CHECK_CODE2 = 'M57' AND CHECKD_CODE2 <> '999999' AND CHECKD_CODE2 NOT IN (SELECT ID_CODE FROM ONKET_ID WHERE C_CODE = '1000')"; Query_In = string.Format(Query_In, code, Write_date); using (System.Data.SqlClient.SqlCommand cmd1 = new System.Data.SqlClient.SqlCommand(Query_In, cn)) { int row1 = cmd1.ExecuteNonQuery(); cmd1.Dispose(); } CHK_MK("C_MK"); Basic.ShowMessage(1, "생성 하였습니다."); if (cn.State == ConnectionState.Open) { cn.Close(); } cn.Dispose(); } scope.Complete(); scope.Dispose(); } InvokeOnClick(this.btnSelect, new EventArgs()); } catch (Exception ex) { Basic.ShowMessage(3, ex.Message); } finally { this.pgb.Visible = false; this.Cursor = Cursors.Default; } }
private void Initialize() { ReportMenu.SubGroups = new ArrayList(); MonthsDT = new DataTable(); MonthsDT.Columns.Add(new DataColumn("MonthID", Type.GetType("System.Int32"))); MonthsDT.Columns.Add(new DataColumn("MonthName", Type.GetType("System.String"))); for (int i = 1; i <= 12; i++) { DataRow NewRow = MonthsDT.NewRow(); NewRow["MonthID"] = i; NewRow["MonthName"] = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(i).ToString(); MonthsDT.Rows.Add(NewRow); } cbxMonths.DataSource = MonthsDT.DefaultView; cbxMonths.ValueMember = "MonthID"; cbxMonths.DisplayMember = "MonthName"; DateTime LastDay = new System.DateTime(DateTime.Now.Year, 12, 31); System.Collections.ArrayList Quarters = new System.Collections.ArrayList(); System.Collections.ArrayList Years = new System.Collections.ArrayList(); for (int i = 1; i <= 4; i++) { Quarters.Add(i); } for (int i = 2013; i <= LastDay.Year; i++) { Years.Add(i); } cbxQuarters.DataSource = Quarters.ToArray(); cbxQuarters.SelectedIndex = 0; cbxYears.DataSource = Years.ToArray(); cbxYears.SelectedIndex = cbxYears.Items.Count - 1; GroupsDT = new DataTable(); SubGroupsDT = new DataTable(); GroupsBS = new BindingSource(); SubGroupsBS = new BindingSource(); if (StoreType == 1) { using (System.Data.SqlClient.SqlDataAdapter DA = new System.Data.SqlClient.SqlDataAdapter("SELECT * FROM TechStoreGroups" + " WHERE TechStoreGroupID IN (SELECT TechStoreGroupID FROM TechStoreSubGroups WHERE TechStoreSubGroupID IN" + " (SELECT TechStoreSubGroupID FROM TechStore WHERE TechStoreID IN (SELECT DISTINCT StoreItemID FROM infiniu2_storage.dbo.Store" + " WHERE FactoryID = " + FactoryID + ")))" + " ORDER BY TechStoreGroupName", ConnectionStrings.CatalogConnectionString)) { DA.Fill(GroupsDT); GroupsDT.Columns.Add(new DataColumn("Checked", Type.GetType("System.Boolean"))); foreach (DataRow Row in GroupsDT.Rows) { Row["Checked"] = false; } } using (System.Data.SqlClient.SqlDataAdapter DA = new System.Data.SqlClient.SqlDataAdapter("SELECT * FROM TechStoreSubGroups WHERE TechStoreSubGroupID IN" + " (SELECT TechStoreSubGroupID FROM TechStore WHERE TechStoreID IN (SELECT DISTINCT StoreItemID FROM infiniu2_storage.dbo.Store" + " WHERE FactoryID = " + FactoryID + "))" + " ORDER BY TechStoreSubGroupName", ConnectionStrings.CatalogConnectionString)) { DA.Fill(SubGroupsDT); SubGroupsDT.Columns.Add(new DataColumn("Checked", Type.GetType("System.Boolean"))); foreach (DataRow Row in SubGroupsDT.Rows) { Row["Checked"] = false; } } } if (StoreType == 2) { using (System.Data.SqlClient.SqlDataAdapter DA = new System.Data.SqlClient.SqlDataAdapter("SELECT * FROM TechStoreGroups" + " WHERE TechStoreGroupID IN (SELECT TechStoreGroupID FROM TechStoreSubGroups WHERE TechStoreSubGroupID IN" + " (SELECT TechStoreSubGroupID FROM TechStore WHERE TechStoreID IN (SELECT DISTINCT StoreItemID FROM infiniu2_storage.dbo.ManufactureStore" + " WHERE FactoryID = " + FactoryID + ")))" + " ORDER BY TechStoreGroupName", ConnectionStrings.CatalogConnectionString)) { DA.Fill(GroupsDT); GroupsDT.Columns.Add(new DataColumn("Checked", Type.GetType("System.Boolean"))); foreach (DataRow Row in GroupsDT.Rows) { Row["Checked"] = false; } } using (System.Data.SqlClient.SqlDataAdapter DA = new System.Data.SqlClient.SqlDataAdapter("SELECT * FROM TechStoreSubGroups WHERE TechStoreSubGroupID IN" + " (SELECT TechStoreSubGroupID FROM TechStore WHERE TechStoreID IN (SELECT DISTINCT StoreItemID FROM infiniu2_storage.dbo.ManufactureStore" + " WHERE FactoryID = " + FactoryID + "))" + " ORDER BY TechStoreSubGroupName", ConnectionStrings.CatalogConnectionString)) { DA.Fill(SubGroupsDT); SubGroupsDT.Columns.Add(new DataColumn("Checked", Type.GetType("System.Boolean"))); foreach (DataRow Row in SubGroupsDT.Rows) { Row["Checked"] = false; } } } GroupsBS.DataSource = GroupsDT; SubGroupsBS.DataSource = SubGroupsDT; GroupsGridSettings(); SubGroupsGridSettings(); }
//Execute query on appropriate database based on the configured data source private DataSet ExecuteQuery(string query) { DataSet ds = new DataSet(); status.Code = RequestStatus.StatusCode.InProgress; string server = Settings.GetAsString("Server", ""); string port = Settings.GetAsString("Port", ""); string userId = Settings.GetAsString("UserID", ""); string password = Settings.GetAsString("Password", ""); string database = Settings.GetAsString("Database", ""); string dataSourceName = Settings.GetAsString("DataSourceName", ""); string connectionTimeout = Settings.GetAsString("ConnectionTimeout", "15"); string commandTimeout = Settings.GetAsString("CommandTimeout", "120"); log.Debug("Connection timeout: " + connectionTimeout + ", Command timeout: " + commandTimeout); log.Debug("Executing Query: " + query); if (!Settings.ContainsKey("DataProvider")) { throw new Exception(CommonMessages.Exception_MissingDataProviderType); } string connectionString = string.Empty; switch ((Lpp.Dns.DataMart.Model.Settings.SQLProvider)Enum.Parse(typeof(Lpp.Dns.DataMart.Model.Settings.SQLProvider), Settings.GetAsString("DataProvider", ""), true)) { case Lpp.Dns.DataMart.Model.Settings.SQLProvider.ODBC: if (string.IsNullOrEmpty(dataSourceName)) { throw new Exception(CommonMessages.Exception_MissingODBCDatasourceName); } using (OdbcConnection connection = new OdbcConnection(string.Format("DSN={0}", dataSourceName))) try { OdbcDataAdapter da = new OdbcDataAdapter(query, connection); da.Fill(ds); } finally { connection.Close(); } break; case Lpp.Dns.DataMart.Model.Settings.SQLProvider.SQLServer: if (string.IsNullOrEmpty(server)) { throw new Exception(CommonMessages.Exception_MissingDatabaseServer); } if (string.IsNullOrEmpty(database)) { throw new Exception(CommonMessages.Exception_MissingDatabaseName); } if (!string.IsNullOrEmpty(userId) && string.IsNullOrEmpty(password)) { throw new Exception(CommonMessages.Exception_MissingDatabasePassword); } if (port != null && port != string.Empty) { server += ", " + port; } connectionString = userId != null && userId != string.Empty ? String.Format("server={0};User ID={1};Password={2};Database={3}; Connection Timeout={4}", server, userId, password, database, connectionTimeout) : String.Format("server={0};integrated security=True;Database={1}; Connection Timeout={2}", server, database, connectionTimeout); using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString)) { try { connection.Open(); System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(query, connection); command.CommandTimeout = int.Parse(commandTimeout); System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(command); da.Fill(ds); } finally { connection.Close(); } } break; default: throw new Exception(CommonMessages.Exception_InvalidDataProviderType); } status.Code = RequestStatus.StatusCode.Complete; status.Message = ""; return(ds); }
public DataSet TranslateData() //Method to translate the data in the Data2ToTranslate dataset { int rownumber; DataRow[] FoundRows; string sTempField; DataSet ds = new DataSet(); try { //get tranlation table data from database string connString = Properties.Settings.Default.ConnectionString; string query = "select [Data_Column_Name],[Value],[Export_Value] from Export_Values"; System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString); System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query, conn); conn.Open(); // create data adapter System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd); // this will query the database and return the result to your datatable da.Fill(ds); conn.Close(); da.Dispose(); TranslationTable = ds.Tables[0]; //set the translation table ds.Dispose(); } catch (Exception e) { MessageBox.Show("Error loading Export translation table from database. \n" + "Internet connection or SQL server may be down. \n" + "Please note what was done prior to error and contact administrator or see error log for more details. \n"); Error_Logging el = new Error_Logging("Error loading Export (Adams) translation table from database. \n" + e); ds.Dispose(); Completed = false; return(Data2Translate); } //Translate values generic routine ************************************************************************* //Takes the list of fields to evaluate and iterates through each of them. try { foreach (string sField in List2Translate) { rownumber = 0; //This is for the case where the size and style are contatonated into one field in the data streadsheet. *** if ((TemplateSettings.SizeColumn == TemplateSettings.StyleColumn) & ((sField == "Size") || (sField == "Style"))) { sTempField = "PackCode"; } else { sTempField = sField; } //*** FoundRows = TranslationTable.Select("Data_Column_Name = " + "'" + sTempField + "'"); //get all rows for sField from Translation table foreach (DataRow row in Data2Translate.Tables[0].Rows) { foreach (DataRow TranslatingRow in FoundRows) //FoundRows contains all the translation rows for sField { if (row[TemplateSettings.DataColumnLocation(sField).Column].ToString().ToUpper().Trim() == TranslatingRow[1].ToString().ToUpper().Trim()) // && TranslatingRow[2].ToString() != "") //Famous value in column 1 { //set field to translated value. Export value in column 2 Data2Translate.Tables[0].Rows[rownumber][TemplateSettings.DataColumnLocation(sField).Column] = TranslatingRow[2].ToString(); } } rownumber++; } } Completed = true; } //end of try block catch (Exception e) { Completed = false; MessageBox.Show("Export Translation process had an error. Please note what was done and contact administrator for help or see error log for more details. \n"); Error_Logging el = new Error_Logging("Export (Adams) Translation process had an error. \n " + e); } return(Data2Translate); } //end of TranslateData method
private void 升级数据库版本ToolStripMenuItem_Click(object sender, EventArgs e) { if (MessageBox.Show("确定需要升级数据库?", "提示", MessageBoxButtons.OKCancel) == System.Windows.Forms.DialogResult.Cancel) { return; } var re = ServiceContext.LoadConnectionStringSettings(); try { System.Data.SqlClient.SqlConnection oleConnection = new System.Data.SqlClient.SqlConnection(re.ConnectionString); oleConnection.Open(); string sql = "select * from __MigrationHistory"; System.Data.SqlClient.SqlDataAdapter sda = new System.Data.SqlClient.SqlDataAdapter(sql, oleConnection); DataSet dsM = new DataSet(); sda.Fill(dsM); DataTable dtM = dsM.Tables[0]; Action <System.Data.SqlClient.SqlConnection, System.Data.SqlClient.SqlDataAdapter, DataSet, DataTable> ReleaseDb = (a, b, c, d) => { a.Close(); a.Dispose(); a = null; b.Dispose(); b = null; c.Dispose(); c = null; d.Dispose(); d = null; }; var v = dtM.AsEnumerable().OrderBy(r => r.Field <string>(0)).LastOrDefault(); DateTime DBMDT = DateTime.ParseExact(v.Field <string>(0).Substring(0, 8), "yyyyMMdd", null, System.Globalization.DateTimeStyles.None); DateTime MDT = DateTime.ParseExact(DataBaseMigrationDataModel.MigrationKeys.MigrationId.Substring(0, 8), "yyyyMMdd", null, System.Globalization.DateTimeStyles.None); if (MDT <= DBMDT) { MessageBox.Show("数据库服务器已经是最新的版本!"); ReleaseDb(oleConnection, sda, dsM, dtM); return; } System.Data.SqlClient.SqlCommand sqlc = new System.Data.SqlClient.SqlCommand(); System.Data.SqlClient.SqlTransaction sqltrans = oleConnection.BeginTransaction(); sqlc.Connection = oleConnection; sqlc.Transaction = sqltrans; string ins = "INSERT INTO __MigrationHistory (MigrationId,ProductVersion,Model) VALUES('" + DataBaseMigrationDataModel.MigrationKeys.MigrationId + "','" + DataBaseMigrationDataModel.MigrationKeys.ProductVersion + "'," + DataBaseMigrationDataModel.MigrationKeys.Model + ")"; sqlc.CommandText = ins; sqlc.ExecuteNonQuery(); System.IO.StreamReader sr = new System.IO.StreamReader("pharmacy_Update3.publish.sql", Encoding.UTF8); System.Text.RegularExpressions.Regex reg = new System.Text.RegularExpressions.Regex(@";Initial Catalog=\w+;"); var Regdbname = reg.Match(re.ConnectionString).ToString(); Regdbname = Regdbname.Substring(0, Regdbname.Length - 1); int idx = Regdbname.LastIndexOf("="); string DBName = Regdbname.Substring(idx + 1); sqlc.CommandText = string.Format("use [{0}] ", DBName); bool IsNote = false; while (sr.Peek() > -1) { string s = sr.ReadLine(); if (string.IsNullOrEmpty(s.Trim())) { continue; } if (s.StartsWith("/*")) { IsNote = true; } if (s.StartsWith("*/")) { IsNote = false; continue; } if (s.Trim().Contains("GO")) { continue; } if (s.StartsWith(":")) { s = s.Substring(1); } if (s.Trim().Contains("USE [$(DatabaseName)]")) { continue; } if (s.Contains("PRINT")) { continue; } if (!IsNote) { sqlc.CommandText += s + "\r\n"; } } sqlc.ExecuteNonQuery(); sqltrans.Commit(); sqlc.Dispose(); sqlc = null; ReleaseDb(oleConnection, sda, dsM, dtM); MessageBox.Show("数据库服务器升级完毕!"); } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { } }
private void button1_Click(object sender, System.EventArgs e) { DataSet ds = new DataSet(); DataAdapter1.Fill(ds); dataGrid1.DataSource = ds.Tables[0]; }
public string ExportExcel(System.Collections.Hashtable filters, out Library.DTO.Notification notification) { notification = new Library.DTO.Notification() { Type = Library.DTO.NotificationType.Success }; ShippingInstructionMngObject ds = new ShippingInstructionMngObject(); string ClientUD = null; string ProformaInvoiceNo = null; string Priority = null; int? PODID = null; bool? IsDefault = null; bool? IsSample = null; if (filters.ContainsKey("ClientUD") && !string.IsNullOrEmpty(filters["ClientUD"].ToString())) { ClientUD = filters["ClientUD"].ToString().Replace("'", "''"); } if (filters.ContainsKey("ProformaInvoiceNo") && !string.IsNullOrEmpty(filters["ProformaInvoiceNo"].ToString())) { ProformaInvoiceNo = filters["ProformaInvoiceNo"].ToString().Replace("'", "''"); } if (filters.ContainsKey("PODID") && !string.IsNullOrEmpty(filters["PODID"].ToString())) { PODID = (int)filters["PODID"]; } if (filters.ContainsKey("Priority") && !string.IsNullOrEmpty(filters["Priority"].ToString())) { Priority = filters["Priority"].ToString().Replace("'", "''"); } if (filters.ContainsKey("IsDefault") && !string.IsNullOrEmpty(filters["IsDefault"].ToString())) { if (filters["IsDefault"].ToString() == "1") { IsDefault = true; } else { IsDefault = false; } } if (filters.ContainsKey("IsSample") && !string.IsNullOrEmpty(filters["IsSample"].ToString())) { if (filters["IsSample"].ToString() == "1") { IsSample = true; } else { IsSample = false; } } try { System.Data.SqlClient.SqlDataAdapter adap = new System.Data.SqlClient.SqlDataAdapter(); adap.SelectCommand = new System.Data.SqlClient.SqlCommand("ShippingInstructionMng_Function_ExportExcelWithFilter", new System.Data.SqlClient.SqlConnection(DALBase.Helper.GetSQLConnectionString())); adap.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure; adap.SelectCommand.Parameters.AddWithValue("ClientUD", ClientUD); adap.SelectCommand.Parameters.AddWithValue("ProformaInvoiceNo", ProformaInvoiceNo); adap.SelectCommand.Parameters.AddWithValue("Priority", Priority); adap.SelectCommand.Parameters.AddWithValue("IsDefault", IsDefault); adap.SelectCommand.Parameters.AddWithValue("IsSample", IsSample); adap.TableMappings.Add("Table", "ShippingInstructionMng_ShippingInstructionExportExcel_View"); adap.Fill(ds); ds.AcceptChanges(); return(Library.Helper.CreateReportFileWithEPPlus(ds, "ShippingInstructionRpt")); } catch (Exception ex) { notification.Type = Library.DTO.NotificationType.Error; notification.Message = ex.Message; if (ex.InnerException != null && !string.IsNullOrEmpty(ex.InnerException.Message)) { notification.DetailMessage.Add(ex.InnerException.Message); } return(string.Empty); } }
private void Page_Load(object sender, System.EventArgs e) { sqlDataAdapter1.Fill(dsUser); DataBind(); // Put user code to initialize the page here }
public Stream printer(string printerId, string parameters) { var request = WebOperationContext.Current.IncomingRequest; var headers = request.Headers; var JSONString = string.Empty; try { //var httpToken = headers["Authorization"].Trim().Replace("Bearer ", ""); //using (SecureData sd = new SecureData(false, httpToken)) //{ using (var connection = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["Oraculus"].ConnectionString)) { connection.Open(); var dt = new DataTable(); var command = new System.Data.SqlClient.SqlCommand("select PrinterId, rtrim(PrinterName) as PrinterPath, PrinterResolution, rtrim(PrinterMethod) as PrinterMethod from printers where PrinterId = " + int.Parse(printerId), connection); var da = new System.Data.SqlClient.SqlDataAdapter(command); da.Fill(dt); if (dt.Rows.Count > 0) { var dtr = dt.Rows[0]; var printerPath = dtr.Field <string>("PrinterPath"); var printerMethod = dtr.Field <string>("PrinterMethod"); if (string.IsNullOrEmpty(parameters)) { parameters = "^XA\r\n^LH20,20 ^BY2,3\r\n^FO40,10 ^BXN,8,200 ^FD280810^FS\r\n^FO10,5 ^ADR,18^FD280810^FS\r\n^FO175,10 ^ADR,18^FDCOL Number^FS\r\n^FO200,5 ^ADR,18^FDCIP 460630^FS\r\n^LH20,110\r\n^FO130,10 ^ADR,18 ^FDCultvrname ^FS\r\n^FO100,10 ^ADR,18 ^FDCultvrname ^FS\r\n^FO70,10 ^ADR,18 ^FDTypeCrossName^FS\r\n^FO40,10 ^ADR,18 ^FD2017^FS\r\n^FO40,85 ^ADR,18 ^FDSPP ^FS\r\n^XZ"; } JSONString = JsonConvert.SerializeObject(dt, Formatting.Indented); if (printerMethod.Equals("Shared")) { var FILE_NAME = System.Web.Hosting.HostingEnvironment.MapPath("~/ZPLII.txt"); //var directory = System.Web.Hosting.HostingEnvironment.MapPath("~/uploads/logs"); //File.Create(FILE_NAME.Replace("ZPLII","ZPL2")); File.WriteAllText(FILE_NAME, parameters); File.Copy(FILE_NAME, printerPath); } else if (printerMethod.Equals("IP")) { const int port = 9100; using (System.Net.Sockets.TcpClient client = new System.Net.Sockets.TcpClient()) { client.Connect(printerPath, port); using (System.IO.StreamWriter writer = new System.IO.StreamWriter(client.GetStream())) { writer.Write(parameters); } } } } da.Dispose(); connection.Close(); } //} } catch (Exception e) { WebOperationContext.Current.OutgoingResponse.StatusCode = System.Net.HttpStatusCode.InternalServerError; //500 JSONString = JsonConvert.SerializeObject(e.Message); } WebOperationContext.Current.OutgoingResponse.ContentType = "application/json; charset=utf-8"; return(new MemoryStream(Encoding.UTF8.GetBytes(JSONString))); }
public static System.Data.DataSet GetInputDataset(Activity dnActivity, IEnumerable <LinkedService> linkedServices, IEnumerable <Dataset> datasets) { //SQL or Azure Blob CSV only var inLS = LinkedServiceHelper.GetInputLinkedService(dnActivity, linkedServices, datasets); System.Data.DataSet dsRtn = GetInputDatasetShell(dnActivity, linkedServices, datasets); //Figure out which Type switch (inLS.Properties.Type) { case "AzureStorage": CloudStorageAccount inputStorageAccount = CloudStorageAccount.Parse(((AzureStorageLinkedService)inLS.Properties.TypeProperties).ConnectionString); CloudBlobClient inputClient = inputStorageAccount.CreateCloudBlobClient(); foreach (var ds in dnActivity.Inputs) { var curTbl = dsRtn.Tables[ds.Name]; AzureBlobDataset abdInput = datasets.First(d => d.Name == ds.Name).Properties.TypeProperties as AzureBlobDataset; CloudBlockBlob cbbInputFile = new CloudBlockBlob(new Uri(inputStorageAccount.BlobEndpoint.AbsoluteUri + abdInput.FolderPath + "/" + abdInput.FileName)); System.IO.MemoryStream ms = new System.IO.MemoryStream(); cbbInputFile.DownloadToStream(ms); ms.Position = 0; using (Microsoft.VisualBasic.FileIO.TextFieldParser tfp = new Microsoft.VisualBasic.FileIO.TextFieldParser(ms)) { tfp.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited; tfp.SetDelimiters(","); while (!tfp.EndOfData) { string[] fields = tfp.ReadFields(); curTbl.LoadDataRow(fields, true); } } } break; case "AzureSqlDatabase": System.Data.SqlClient.SqlConnection scInput = new System.Data.SqlClient.SqlConnection(((AzureSqlDatabaseLinkedService)inLS.Properties.TypeProperties).ConnectionString); foreach (var ds in datasets) { var curTbl = dsRtn.Tables[ds.Name]; AzureSqlTableDataset astInput = ds.Properties.TypeProperties as AzureSqlTableDataset; System.Data.SqlClient.SqlCommand commInput = new System.Data.SqlClient.SqlCommand(); commInput.Connection = scInput; commInput.CommandType = System.Data.CommandType.Text; commInput.CommandText = string.Format("SELECT * FROM [{0}]", astInput.TableName); System.Data.SqlClient.SqlDataAdapter sdaInput = new System.Data.SqlClient.SqlDataAdapter(commInput); sdaInput.Fill(curTbl); } break; default: throw new NotImplementedException(); } return(dsRtn); }
public string FetchRailList(bool ActiveUnits) { short DayAmt = 0; int RecCount = 0; string BookingNumbers = ""; string Sql = ""; MdlContainerManagement.RailSight RailStructure; int TotalRecords = 0; string TempLine = ""; var UnitLineStatus = default(char); if (ActiveUnits == true) { Sql = "Select * From VW_UnitSource Where Active = 1 and UnitStatus <> 'RELEASED' Order By UnitNumber"; } else { Sql = "Select * From VW_UnitSource Where Active = 0 UnitStatus = 'RELEASED' Order By UnitNumber"; } var DbObjDataTable = new DataTable(); var DbObjAdapter = new System.Data.SqlClient.SqlDataAdapter(Sql, DBConnect); var DbObjCommand = new System.Data.SqlClient.SqlCommandBuilder(DbObjAdapter); DbObjAdapter.Fill(DbObjDataTable); TotalRecords = DbObjDataTable.Rows.Count; if (TotalRecords > 0) { BookingInfo = new string[TotalRecords]; Refresh(); foreach (DataRow RailListRow in DbObjDataTable.Rows) { RailStructure.UnitNumber = MdlContainerManagement.DoFunctions.PadToString(Conversions.ToString(RailListRow["OriginalUnitNumber"]), 16); // .CustomerName = DoFunctions.PadToString(RailListRow("Consignee Name"), 50) RailStructure.CustomerName = MdlContainerManagement.DoFunctions.PadToString(Conversions.ToString(RailListRow["CustomerName"]), 50); RailStructure.UnitStatus = RailList1.ConvertUnitStatus(Conversions.ToString(RailListRow["UnitStatus"])); if (Information.IsDate(RailListRow["LastTraced"]) == true) { RailStructure.LastUpdate = MdlContainerManagement.DoFunctions.DateToNum(DateAndTime.DateValue(Conversions.ToString(RailListRow["LastTraced"]))); } else { RailStructure.LastUpdate = MdlContainerManagement.DoFunctions.MKL(0); } if (!(Information.IsDBNull(RailListRow["CallDate"]) == true)) { RailStructure.CallDate = MdlContainerManagement.DoFunctions.DateToNum(Conversions.ToDate(RailListRow["CallDate"])); } else { RailStructure.CallDate = MdlContainerManagement.DoFunctions.MKL(0); } RailStructure.ArrivalDate = MdlContainerManagement.DoFunctions.MKL(0); if (Information.IsDate(RailListRow["BillDate"]) == true) { RailStructure.WayBillDate = MdlContainerManagement.DoFunctions.DateToNum(DateAndTime.DateValue(Conversions.ToString(RailListRow["BillDate"]))); } else { RailStructure.WayBillDate = MdlContainerManagement.DoFunctions.MKL(0); } // .ShipperName = DoFunctions.PadToString(RailListRow("Shipper Name"), 35) RailStructure.ShipperName = MdlContainerManagement.DoFunctions.PadToString(Conversions.ToString(RailListRow["ShipperName"]), 35); if (RailListRow.IsNull("OriginLocation") == true) { RailStructure.OriginLocation = MdlContainerManagement.DoFunctions.PadToString(Conversions.ToString(' '), 35); } else { RailStructure.OriginLocation = MdlContainerManagement.DoFunctions.PadToString(Conversions.ToString(RailListRow["OriginLocation"]), 35); } if (RailListRow.IsNull("UnitLine") == true) { RailStructure.SASC = MdlContainerManagement.DoFunctions.PadToString("", 4); } else { RailStructure.SASC = MdlContainerManagement.DoFunctions.PadToString(Conversions.ToString(RailListRow["UnitLine"]), 4); } if (RailListRow.IsNull("ETADate") == true) { RailStructure.ETADate = MdlContainerManagement.DoFunctions.MKL(0); RailStructure.ETADays = MdlContainerManagement.DoFunctions.MKI(0); } else { RailStructure.ETADate = MdlContainerManagement.DoFunctions.DateToNum(DateAndTime.DateValue(Conversions.ToString(RailListRow["ETADate"]))); DayAmt = (short)DateAndTime.DateDiff(DateInterval.Day, DateAndTime.Today.Date, DateAndTime.DateValue(Conversions.ToString(RailListRow["ETADate"]))); if (DayAmt > 0) { RailStructure.ETADays = MdlContainerManagement.DoFunctions.MKI(DayAmt); } else { RailStructure.ETADays = MdlContainerManagement.DoFunctions.MKI(0); } } RailStructure.EquipType = MdlContainerManagement.DoFunctions.PadToString(Conversions.ToString(RailListRow["UnitType"]), 6); if (RailListRow.IsNull("TotalBilled") == true) { RailStructure.WgtMT = MdlContainerManagement.DoFunctions.MKS(0f); } else { RailStructure.WgtMT = MdlContainerManagement.DoFunctions.MKS(Conversions.ToSingle(RailListRow["TotalBilled"])); } RailStructure.Bags = MdlContainerManagement.DoFunctions.MKL(0); if (RailListRow.IsNull("LastLocation") == true) { RailStructure.SightingLocation = MdlContainerManagement.DoFunctions.PadToString(Conversions.ToString(' '), 13); } else { RailStructure.SightingLocation = MdlContainerManagement.DoFunctions.PadToString(Conversions.ToString(RailListRow["LastLocation"]), 13); } RailStructure.CaargoDescription = MdlContainerManagement.DoFunctions.PadToString(Conversions.ToString(' '), 15); BookingInfo[RecCount] = ""; TempLine += Conversions.ToString('\0') + RailStructure.UnitNumber + RailStructure.CustomerName + Conversions.ToString(RailStructure.UnitStatus) + RailStructure.LastUpdate + RailStructure.SightingLocation + RailStructure.ETADate + RailStructure.ETADays + RailStructure.CallDate + RailStructure.ArrivalDate + RailStructure.WayBillDate + RailStructure.ShipperName + RailStructure.OriginLocation + RailStructure.SASC + RailStructure.EquipType + RailStructure.WgtMT + RailStructure.Bags + RailStructure.CaargoDescription + Conversions.ToString(UnitLineStatus) + MdlContainerManagement.DoFunctions.MKL(0) + MdlContainerManagement.DoFunctions.MKL(Conversions.ToInteger(RailListRow["UnitSourceId"])); RecCount += 1; } } deactivateBtns(); return(TempLine); }
private string FetchThisBooking(string ThisBookNumber, int railListID) { short x = 0; short cnt = 0; string SQL = ""; string BookingLine = ""; string BookingNumber = ""; string DeliverBook = ""; string SSLineCode = ""; string DeliverCanTotal = ""; string LRDDate = ""; char LRDTime = '\0'; string TerminalName = ""; string VesselName = ""; char SplitBooking = '\0'; string DeliverBookings = ""; string TempLine = ""; string ChangedMask = ""; string DeliverReadyCans = ""; int BookCount = 0; short TotalCans = 0; short DeliverCans = 0; string bkLetter; string unit; string recordKeyQuery; if (railListID == 0) { recordKeyQuery = "[RailListID] is null"; } else { recordKeyQuery = "[RailListID] = '" + railListID.ToString() + "'"; } SQL = @"select b.*, [MiniBookingOrder], MiniUnitOrder, bus.Unit from Bookings as b join MiniBooking as mb on b.[Booking Number] = mb.[BookingNumber] and b.[Booking Number] = '" + ThisBookNumber + @"' join [Booking Unit Sources] bus on bus.[Minibooking ID] = mb.[MiniBookingId] and " + recordKeyQuery + " and Unit = '" + focusedUnitNumber + @"' left join MiniUnit as mu on mu.MiniUnitId = bus.MiniUnitID order by [MiniBookingOrder] "; var DbObjDataTable = new DataTable(); var DbObjAdapter = new System.Data.SqlClient.SqlDataAdapter(SQL, DBConnect); var DbObjCommand = new System.Data.SqlClient.SqlCommandBuilder(DbObjAdapter); DbObjAdapter.Fill(DbObjDataTable); Refresh(); foreach (DataRow BookNum in DbObjDataTable.Rows) { bool multiMB = DbObjDataTable.Select(Conversions.ToString(Operators.AddObject(Operators.AddObject("[Booking Number] = '", BookNum["Booking Number"]), "'"))).Length > 1; if (multiMB) { bkLetter = "(" + MdlBookingManagement.digitToLetter(BookNum["MiniBookingOrder"]) + ")"; } else { bkLetter = ""; } BookingNumber = MdlContainerManagement.DoFunctions.PadToString(Strings.Trim(Conversions.ToString(BookNum["Booking Number"])) + bkLetter, 40); if (!string.IsNullOrEmpty(Strings.RTrim(BookingNumber))) { if (!BookNum.IsNull("LRD") & Information.IsDate(BookNum["LRD"])) { LRDDate = MdlContainerManagement.DoFunctions.DateToNum(DateAndTime.DateValue(Conversions.ToString(BookNum["LRD"]))); } else { LRDDate = MdlContainerManagement.DoFunctions.MKL(0); } if (BookNum.IsNull("Terminal Name") == true) { TerminalName = MdlContainerManagement.DoFunctions.PadToString(Conversions.ToString(' '), 40); } else { TerminalName = MdlContainerManagement.DoFunctions.PadToString(Conversions.ToString(BookNum["Terminal Name"]), 40); } if (BookNum.IsNull("Vessel Name") == true) { VesselName = MdlContainerManagement.DoFunctions.PadToString(Conversions.ToString(' '), 50); } else { VesselName = MdlContainerManagement.DoFunctions.PadToString(Conversions.ToString(BookNum["Vessel Name"]), 50); } if (BookNum.IsNull("Split Booking") == false && Conversions.ToBoolean(Operators.ConditionalCompareObjectEqual(BookNum["Split Booking"], true, false))) { SplitBooking = '\u0001'; } else { SplitBooking = '\0'; } if (BookNum.IsNull("SS Line Code") == true) { SSLineCode = MdlContainerManagement.DoFunctions.PadToString(Conversions.ToString(' '), 10); } else { SSLineCode = MdlContainerManagement.DoFunctions.PadToString(Conversions.ToString(BookNum["SS Line Code"]), 10); } LRDTime = '\0'; ChangedMask = Conversions.ToString('\0') + Conversions.ToString('\0') + Conversions.ToString('\0') + Conversions.ToString('\0'); // DeliverBook = Mid(DeliverBookings, x, 40) DeliverCanTotal = MdlContainerManagement.DoFunctions.MKL(Conversions.ToInteger(BookNum["Number Of Cans"])); if (multiMB) { if (!Information.IsDBNull(BookNum["MiniUnitOrder"])) { bkLetter = "(" + MdlBookingManagement.digitToLetter(BookNum["MiniUnitOrder"]).ToUpper() + ")"; } else { bkLetter = ""; } } else { bkLetter = ""; } unit = MdlContainerManagement.DoFunctions.PadToString(Strings.Trim(Conversions.ToString(BookNum["Unit"])) + bkLetter, 40); TempLine = Conversions.ToString('\0') + BookingNumber + unit + SSLineCode + LRDDate + Conversions.ToString(LRDTime) + TerminalName + VesselName + Conversions.ToString(SplitBooking) + DeliverCanTotal + MdlContainerManagement.DoFunctions.MKL(0) + MdlContainerManagement.DoFunctions.MKL(0) + MdlContainerManagement.DoFunctions.MKL(0) + MdlContainerManagement.DoFunctions.MKL(0) + ChangedMask; BookingLine += TempLine; // Next x // End If } } return(BookingLine); }
private void FormYFZKJS_EDIT_Load(object sender, EventArgs e) { int i; sqlConn.ConnectionString = strConn; sqlComm.Connection = sqlConn; sqlDA.SelectCommand = sqlComm; cGetInformation = new ClassGetInformation(strConn); this.Top = 1; this.Left = 1; if (intDJID == 0) { return; } this.Text += ":单据冲红"; sqlConn.Open(); sqlComm.CommandText = "SELECT 结算付款汇总表.单据编号, 结算付款汇总表.日期, 职员表.职员编号,[职员表_1].职员姓名, 单位表.单位编号, 单位表.单位名称, 结算付款汇总表.发票号, 结算付款汇总表.开票日期, 结算付款汇总表.备注, 结算付款汇总表.实计金额, 结算付款汇总表.税号, 结算付款汇总表.单位ID, 结算付款汇总表.业务员ID, 结算付款汇总表.部门ID FROM 结算付款汇总表 INNER JOIN 职员表 ON 结算付款汇总表.操作员ID = 职员表.ID INNER JOIN 职员表 [职员表_1] ON 结算付款汇总表.业务员ID = [职员表_1].ID INNER JOIN 单位表 ON 结算付款汇总表.单位ID = 单位表.ID WHERE (结算付款汇总表.ID = " + intDJID.ToString() + ") AND (结算付款汇总表.BeActive<>0)"; sqldr = sqlComm.ExecuteReader(); if (!sqldr.HasRows) { isSaved = true; sqldr.Close(); sqlConn.Close(); return; } while (sqldr.Read()) { if (sqldr.GetValue(13).ToString() != "") { try { iBM = int.Parse(sqldr.GetValue(13).ToString()); } catch { iBM = 0; } } labelZDRQ.Text = Convert.ToDateTime(sqldr.GetValue(1).ToString()).ToString("yyyy年M月dd日"); labelDJBH.Text = sqldr.GetValue(0).ToString(); labelCZY.Text = sqldr.GetValue(2).ToString(); comboBoxYWY.Text = sqldr.GetValue(3).ToString(); textBoxDWBH.Text = sqldr.GetValue(4).ToString(); textBoxDWMC.Text = sqldr.GetValue(5).ToString(); textBoxFPH.Text = sqldr.GetValue(6).ToString(); textBoxSH.Text = sqldr.GetValue(10).ToString(); textBoxBZ.Text = sqldr.GetValue(8).ToString(); iSupplyCompany = Convert.ToInt32(sqldr.GetValue(11).ToString()); iYWY = Convert.ToInt32(sqldr.GetValue(12).ToString()); } sqldr.Close(); if (iBM != 0) { sqlComm.CommandText = "SELECT 部门名称 FROM 部门表 WHERE (ID = " + iBM.ToString() + ")"; sqldr = sqlComm.ExecuteReader(); while (sqldr.Read()) { comboBoxBM.Items.Add(sqldr.GetValue(0).ToString()); comboBoxBM.Text = sqldr.GetValue(0).ToString(); break; } sqldr.Close(); } sqlComm.CommandText = "SELECT 结算付款明细表.ID, 账簿表.账簿编号, 账簿表.账簿名称, 结算付款明细表.摘要,结算付款明细表.冲应付款, 账簿表.扣率, 结算付款明细表.付款金额, 结算付款明细表.支票号, 结算付款明细表.备注, 账簿表.账簿ID, 结算付款定义表.勾兑标记, 结算付款定义表.勾兑纪录, 结算付款明细表.单据ID FROM 账簿表 INNER JOIN 结算付款明细表 ON 账簿表.ID = 结算付款明细表.账簿ID CROSS JOIN 结算付款定义表 WHERE (结算付款明细表.单据ID = " + intDJID.ToString() + ")"; if (dSet.Tables.Contains("单据表")) { dSet.Tables.Remove("单据表"); } sqlDA.Fill(dSet, "单据表"); dataGridViewDJMX.DataSource = dSet.Tables["单据表"]; sqlConn.Close(); dataGridViewDJMX.Columns[0].Visible = false; dataGridViewDJMX.Columns[9].Visible = false; dataGridViewDJMX.Columns[10].Visible = false; dataGridViewDJMX.Columns[11].Visible = false; dataGridViewDJMX.Columns[12].Visible = false; dataGridViewDJMX.Columns[6].ReadOnly = true; dataGridViewDJMX.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells; dataGridViewDJMX.Columns[2].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells; dataGridViewDJMX.Columns[3].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells; dataGridViewDJMX.Columns[4].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells; dataGridViewDJMX.Columns[5].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells; dataGridViewDJMX.Columns[6].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells; dataGridViewDJMX.Columns[7].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells; dataGridViewDJMX.Columns[8].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells; dataGridViewDJMX.ShowCellErrors = true; dataGridViewDJMX.ReadOnly = true; dataGridViewDJMX.AllowUserToAddRows = false; dataGridViewDJMX.AllowUserToDeleteRows = false; countAmount(); }
private void initDatatable(bool bPCode, bool bICode) { bool bFirst = true; sqlConn.Open(); bFirst = true; sqlComm.CommandText = "SELECT product.ID, product.[Product Name], product.[Product Code], indentor.ID AS iID, indentor.[Indentor Name], indentor.[Indentor Code], product.[Number of IMEI] FROM product CROSS JOIN indentor"; if (bPCode && textBoxPC.Text.Trim() != "") { sqlComm.CommandText += " WHERE (product.[Product Code] LIKE N'%" + textBoxPC.Text.Trim() + "%')"; bFirst = false; } if (bICode && textBoxIC.Text.Trim() != "") { if (bFirst) { sqlComm.CommandText += " WHERE (indentor.[Indentor Code] LIKE N'%" + textBoxIC.Text.Trim() + "%')"; } else { sqlComm.CommandText += " AND (indentor.[Indentor Code] LIKE N'%" + textBoxIC.Text.Trim() + "%')"; } } if (dSet.Tables.Contains("pi")) { dSet.Tables["pi"].Clear(); } sqlDA.Fill(dSet, "pi"); bFirst = true; sqlComm.CommandText = "SELECT actual.ID, actual.[Product ID], actual.[Product Code], actual.[Indentor ID], actual.[Indentor Code], actual.[Start Number], actual.[End Number], actual.[Acquire ID], actual.Date, actual.Year, actual.[Num of Week], actual.[TAC ID], actual.[TAC Code], actual.[Total Number] FROM actual INNER JOIN (SELECT [Product Code], [Indentor Code], MAX([Total Number]) AS [Total Number] FROM actual AS actual_1 GROUP BY [Product Code], [Indentor Code]) AS A ON actual.[Product Code] = A.[Product Code] AND actual.[Indentor Code] = A.[Indentor Code] AND actual.[Total Number] = A.[Total Number]"; if (bPCode && textBoxPC.Text.Trim() != "") { sqlComm.CommandText += " WHERE (actual.[Product Code] LIKE N'%" + textBoxPC.Text.Trim() + "%')"; bFirst = false; } if (bICode && textBoxIC.Text.Trim() != "") { if (bFirst) { sqlComm.CommandText += " WHERE (actual.[Indentor Code] LIKE N'%" + textBoxIC.Text.Trim() + "%')"; } else { sqlComm.CommandText += " AND (actual.[Indentor Code] LIKE N'%" + textBoxIC.Text.Trim() + "%')"; } } if (dSet.Tables.Contains("maxnumber")) { dSet.Tables["maxnumber"].Clear(); } sqlDA.Fill(dSet, "maxnumber"); bFirst = true; sqlComm.CommandText = "SELECT ID, [TAC Code], [Product ID], [Product Code], [Indentor ID], [Indentor Code], [Init Number] FROM TAC"; if (bPCode && textBoxPC.Text.Trim() != "") { sqlComm.CommandText += " WHERE (TAC.[Product Code] LIKE N'%" + textBoxPC.Text.Trim() + "%')"; bFirst = false; } if (bICode && textBoxIC.Text.Trim() != "") { if (bFirst) { sqlComm.CommandText += " WHERE (TAC.[Indentor Code] LIKE N'%" + textBoxIC.Text.Trim() + "%')"; } else { sqlComm.CommandText += " AND (TAC.[Indentor Code] LIKE N'%" + textBoxIC.Text.Trim() + "%')"; } } if (dSet.Tables.Contains("TAC")) { dSet.Tables["TAC"].Clear(); } sqlDA.Fill(dSet, "TAC"); sqlComm.CommandText += " ORDER BY [Init Number]"; sqlConn.Close(); object[] oTemp = new object[13]; dtManu.Clear(); int i; for (i = 0; i < dSet.Tables["pi"].Rows.Count; i++) { oTemp[0] = dSet.Tables["pi"].Rows[i][0]; oTemp[1] = dSet.Tables["pi"].Rows[i][2]; oTemp[2] = dSet.Tables["pi"].Rows[i][6]; oTemp[3] = dSet.Tables["pi"].Rows[i][3]; oTemp[4] = dSet.Tables["pi"].Rows[i][5]; oTemp[5] = 0; //TAC ID oTemp[6] = ""; //TAC Code oTemp[7] = 0; //init number oTemp[8] = 0; //start oTemp[9] = 0; //end oTemp[10] = 0; //total number oTemp[11] = 0; //check oTemp[12] = 0; //backlog var q1 = from dt1 in dSet.Tables["maxnumber"].AsEnumerable() //查询最后 where (dt1.Field <string>("Product Code") == oTemp[1].ToString()) && (dt1.Field <string>("Indentor Code") == oTemp[4].ToString()) //条件 select dt1; if (q1.Count() > 0) { foreach (var item in q1)//显示查询结果 { oTemp[6] = item.Field <string>("TAC Code"); oTemp[8] = item.Field <int>("End Number"); break; } } else //以前没有记录 { var q2 = from dt2 in dSet.Tables["TAC"].AsEnumerable()//查询TAC区间 where (dt2.Field <string>("Product Code") == oTemp[1].ToString()) && (dt2.Field <string>("Indentor Code") == oTemp[4].ToString()) && (dt2.Field <int>("Init Number") == 0)//条件 select dt2; foreach (var item in q2)//显示查询结果 { oTemp[5] = item.Field <int>("ID"); oTemp[6] = item.Field <string>("TAC Code"); oTemp[7] = item.Field <int>("Init Number"); break; } } dtManu.Rows.Add(oTemp); } dataGridViewP.DataSource = dtManu; for (i = 0; i < dataGridViewP.ColumnCount; i++) { dataGridViewP.Columns[i].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells; dataGridViewP.Columns[i].ReadOnly = true; } dataGridViewP.Columns[6].ReadOnly = false; dataGridViewP.Columns[9].ReadOnly = false; dataGridViewP.Columns[12].ReadOnly = false; dataGridViewP.Columns[0].Visible = false; dataGridViewP.Columns[3].Visible = false; dataGridViewP.Columns[5].Visible = false; dataGridViewP.Columns[11].Visible = false; setSTAUS(); }
public static void Demonstration() { var st = DateTime.Now; var executeSql = "SELECT * FROM dbo.States"; var sqlConnection = new System.Data.SqlClient.SqlConnection(connectionString); if (sqlConnection.State == System.Data.ConnectionState.Closed) { sqlConnection.Open(); } #region Dapper for (int i = 0; i < 5; i++) { st = DateTime.Now; var list = sqlConnection.Query <dynamic>(executeSql); list = null; Console.WriteLine($"Dapper第{i + 1}查询耗时{(DateTime.Now - st).TotalMilliseconds}ms"); st = DateTime.Now; } #endregion #region ADO.NET for (int i = 0; i < 5; i++) { st = DateTime.Now; var sqlCommand = new System.Data.SqlClient.SqlCommand(executeSql, sqlConnection); var sqlDa = new System.Data.SqlClient.SqlDataAdapter(sqlCommand); var SqlDt = new System.Data.DataTable(); sqlDa.Fill(SqlDt); SqlDt.Clear(); Console.WriteLine($"ADO.NET第{i + 1}查询耗时{(DateTime.Now - st).TotalMilliseconds}ms"); st = DateTime.Now; } #endregion #region EF var context = new MyDbContext(connectionString); for (int i = 0; i < 5; i++) { st = DateTime.Now; var si = context.States.AsNoTracking().ToList(); Console.WriteLine($"EF第{i + 1}查询耗时{(DateTime.Now - st).TotalMilliseconds}ms"); st = DateTime.Now; } #endregion }
void ReloadTime_Elapsed(object sender, System.Timers.ElapsedEventArgs e) { DateTime startLoad = DateTime.Now; System.Xml.XmlDocument mv_schema_xml_temp = new System.Xml.XmlDocument(); System.Xml.XmlDocument import_attribute_flow_xml_temp = new System.Xml.XmlDocument(); System.Data.DataTable mms_management_agent_temp = new System.Data.DataTable("mms_metaverse_multivalue"); System.Data.DataTable mms_metaverse_temp = new System.Data.DataTable("mms_metaverse"); System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.AppSettings["FIMSynchronizationService"]); connection.Open(); //Load XML schema //mv_schema_xml System.Data.SqlClient.SqlCommand SqlCommand = new System.Data.SqlClient.SqlCommand("select mv_schema_xml from mms_server_configuration (nolock)", connection); System.Xml.XmlReader XmlReader = SqlCommand.ExecuteXmlReader(); mv_schema_xml_temp.Load(XmlReader); XmlReader.Dispose(); SqlCommand.Dispose(); //import_attribute_flow_xml SqlCommand = new System.Data.SqlClient.SqlCommand("select import_attribute_flow_xml from mms_server_configuration (nolock)", connection); XmlReader = SqlCommand.ExecuteXmlReader(); import_attribute_flow_xml_temp.Load(XmlReader); XmlReader.Dispose(); SqlCommand.Dispose(); // ////Load Tabell schema //SqlCommand = new System.Data.SqlClient.SqlCommand("select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'mms_metaverse'", connection); //System.Data.DataTable INFORMATION_SCHEMA = new System.Data.DataTable("INFORMATION_SCHEMA"); //System.Data.SqlClient.SqlDataAdapter DataAdapter = new System.Data.SqlClient.SqlDataAdapter(SqlCommand); //int count = DataAdapter.Fill(INFORMATION_SCHEMA); //DataAdapter.Dispose(); //SqlCommand.Dispose(); System.Data.SqlClient.SqlDataAdapter DataAdapter; int count; //Load mms_metaverse attributes SqlCommand = new System.Data.SqlClient.SqlCommand("select top 1 * from mms_metaverse (nolock)", connection); DataAdapter = new System.Data.SqlClient.SqlDataAdapter(SqlCommand); count = DataAdapter.Fill(mms_metaverse_temp); DataAdapter.Dispose(); SqlCommand.Dispose(); //Load mms_management_agent SqlCommand = new System.Data.SqlClient.SqlCommand("select * from mms_management_agent (nolock)", connection); DataAdapter = new System.Data.SqlClient.SqlDataAdapter(SqlCommand); count = DataAdapter.Fill(mms_management_agent_temp); DataAdapter.Dispose(); SqlCommand.Dispose(); connection.Close(); connection.Dispose(); mv_schema_xml = mv_schema_xml_temp; import_attribute_flow_xml = import_attribute_flow_xml_temp; //mms_management_agent Dictionary <Guid, LD.IdentityManagement.API.Models.managementagent> Schema_managementagent_temp = new Dictionary <Guid, LD.IdentityManagement.API.Models.managementagent>(); foreach (DataRow row in mms_management_agent_temp.Rows) { LD.IdentityManagement.API.Models.managementagent ma = new LD.IdentityManagement.API.Models.managementagent(); ma.name = (string)row["ma_name"]; ma.guid = (Guid)row["ma_id"]; ma.description = (string)row["ma_description"]; ma.type = (string)row["ma_type"]; Schema_managementagent_temp.Add(ma.guid, ma); //import-flow foreach (System.Xml.XmlNodeList flowrule in import_attribute_flow_xml.SelectNodes(string.Format("/import-attribute-flow/import-flow-set/import-flows/import-flow[@src-ma='{0}']", ma.guid))) { flowObject temp = new flowObject() { type = "", mvclassobjectname = "", csclassobjectname = "", attribute = new flowAttribute[0] }; } } Schema_managementagent = Schema_managementagent_temp; System.Xml.XmlNamespaceManager ns = new System.Xml.XmlNamespaceManager(mv_schema_xml.NameTable); ns.AddNamespace("dsml", "http://www.dsml.org/DSML"); //Create Schema Attributes list Dictionary <string, LD.IdentityManagement.API.Models.Attribute> Schema_Attributes_temp = new Dictionary <string, LD.IdentityManagement.API.Models.Attribute>(); Dictionary <string, LD.IdentityManagement.API.Models.Attribute> Schema_Private_Attributes_temp = new Dictionary <string, LD.IdentityManagement.API.Models.Attribute>(); foreach (System.Xml.XmlNode Node in mv_schema_xml.SelectNodes("dsml:dsml/dsml:directory-schema/dsml:attribute-type", ns)) { LD.IdentityManagement.API.Models.Attribute NewAttribute = new LD.IdentityManagement.API.Models.Attribute(); NewAttribute.name = Node["dsml:name"].InnerText.ToLower(); NewAttribute.mulitvalue = Node.Attributes["single-value"] != null ? false : true; NewAttribute.indexable = Node.Attributes["ms-dsml:indexable"] == null || Node.Attributes["ms-dsml:indexable"].Value == "false" ? false : true; NewAttribute.indexed = Node.Attributes["ms-dsml:indexed"] == null || Node.Attributes["ms-dsml:indexed"].Value == "false" ? false : true; NewAttribute.syntax = Node["dsml:syntax"].InnerText; //syntax to type switch (NewAttribute.syntax) { case "1.3.6.1.4.1.1466.115.121.1.5": //Binary NewAttribute.type = typeof(byte[]); break; case "1.3.6.1.4.1.1466.115.121.1.7": //Boolean NewAttribute.type = typeof(bool); break; case "1.3.6.1.4.1.1466.115.121.1.12": //DN (string) NewAttribute.type = typeof(string); break; case "1.3.6.1.4.1.1466.115.121.1.15": //DirectoryString NewAttribute.type = typeof(string); break; case "1.3.6.1.4.1.1466.115.121.1.27": //Integer NewAttribute.type = typeof(int); break; default: //NewAttribute.type = typeof(string); break; } if (!Schema_private.Contains(NewAttribute.name)) { if (NewAttribute.mulitvalue || mms_metaverse_temp.Columns.Contains(NewAttribute.name)) { //logger.Debug("{0} {1}", NewAttribute.name, NewAttribute.mulitvalue); Schema_Attributes_temp.Add(NewAttribute.name, NewAttribute); } } else { Schema_Private_Attributes_temp.Add(NewAttribute.name, NewAttribute); } } Schema_Attributes = Schema_Attributes_temp; Schema_Private_Attributes = Schema_Private_Attributes_temp; //Create Schema Object list Dictionary <string, LD.IdentityManagement.API.Models.classobject> Schema_Object_temp = new Dictionary <string, LD.IdentityManagement.API.Models.classobject>(); foreach (System.Xml.XmlNode Node in mv_schema_xml.SelectNodes("dsml:dsml/dsml:directory-schema/dsml:class", ns)) { LD.IdentityManagement.API.Models.classobject newClassObject = new LD.IdentityManagement.API.Models.classobject(); newClassObject.name = Node["dsml:name"].InnerText.ToLower(); List <LD.IdentityManagement.API.Models.objectattribute> ObjectAttributeList = new List <LD.IdentityManagement.API.Models.objectattribute>(); foreach (System.Xml.XmlNode attributeNodes in Node.SelectNodes("dsml:attribute", ns)) { LD.IdentityManagement.API.Models.objectattribute NewObjectAttribute = new LD.IdentityManagement.API.Models.objectattribute(); NewObjectAttribute.required = attributeNodes.Attributes["required"].Value == "true" ? true : false; LD.IdentityManagement.API.Models.Attribute Attribute; string attname = attributeNodes.Attributes["ref"].Value.Substring(1); if (Schema_Attributes.TryGetValue(attname, out Attribute) || Schema_Private_Attributes.TryGetValue(attname, out Attribute)) { NewObjectAttribute.attribute = Attribute; ObjectAttributeList.Add(NewObjectAttribute); } } newClassObject.objectattributes = ObjectAttributeList.ToArray(); Schema_Object_temp.Add(newClassObject.name, newClassObject); } Schema_Object = Schema_Object_temp; TimeSpan time = DateTime.Now - startLoad; ReloadTime.Enabled = true; }
private void InfAcero_Load(object sender, System.EventArgs e) { sqlDABusObra.Fill(dsBusObra1, "Obra"); sqlDAUnidad.Fill(dsUnidad1, "Unidad"); }