//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");
}