/// <summary> /// Make a connection to the excel file using OleDB and read from it like a SQL database /// and then create a dataTable to be converted into a list of objects /// </summary> /// <param name="filePath"></param> /// <returns></returns> internal static System.Data.DataTable ReadExcelFile(string filePath) { string sheetName = null; string connStr = String.Format(_excelConnection, filePath); System.Data.DataTable tablesList = new System.Data.DataTable(); System.Data.DataTable excelData = new System.Data.DataTable(); OleDbCommand oleExcelCommand = default(OleDbCommand); OleDbDataReader oleExcelReader = default(OleDbDataReader); OleDbConnection oleExcelConnection = default(OleDbConnection); //Connect to Excel File oleExcelConnection = new OleDbConnection(connStr); oleExcelConnection.Open(); try { tablesList = oleExcelConnection.GetSchema("Tables"); //Grab all the first sheet, which work as a table if (tablesList.Rows.Count > 0) { sheetName = tablesList.Rows[0]["TABLE_NAME"].ToString(); } tablesList.Clear(); tablesList.Dispose(); //If a sheet exists, then select all the content from it and load it into a data file if (!string.IsNullOrEmpty(sheetName)) { oleExcelCommand = oleExcelConnection.CreateCommand(); oleExcelCommand.CommandText = "Select * From [" + sheetName + "]"; oleExcelCommand.CommandType = CommandType.Text; oleExcelReader = oleExcelCommand.ExecuteReader(); excelData.Load(oleExcelReader); oleExcelReader.Close(); } } catch (Exception ex) { Console.WriteLine(ex.Message); } finally { tablesList.Clear(); tablesList.Dispose(); oleExcelConnection.Close(); } return(excelData); }
protected void Page_Load(object sender, EventArgs e) { #region 标签信息 string strTagInfo = ""; try { string sql = "select * from [tb_tag] where tag_ispublic=1"; DataTable dt = new DataTable(); DBHelper dbh = new DBHelper(config.DBConn); dt = dbh.ExecuteDataTable("", sql); if (dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { strTagInfo += "<a href=\"default.aspx?id=" + dt.Rows[i]["tag_id"].ToString() + "\" class=\"tag\" target=\"_blank\">" + dt.Rows[i]["tag_name"].ToString() + "</a>"; } } dbh.Dispose(); dt.Dispose(); } catch (Exception ex) { strTagInfo = ex.ToString(); } this.tagBall.InnerHtml = strTagInfo; #endregion }
public DataTable GetCustomerByID(int id) { string connectionString = WebConfigurationManager.AppSettings["ConnectionString"]; using (SqlConnection connection = new SqlConnection(connectionString)) { DataTable dt = new DataTable(); SqlCommand command = new SqlCommand(Resources.Resource.GetCustomerByID, connection); command.Parameters.AddWithValue("@id", id); try { connection.Open(); SqlDataReader reader = command.ExecuteReader(); dt.Load(reader); return dt; } catch (Exception ex) { Console.WriteLine(ex.Message); } finally { dt.Dispose(); connection.Close(); } return null; } }
public DataTable compareBilling(int customerId, string month1, int year1, string month2, int year2) { string query_select1 = "select * from BILLING where customerId = " + customerId + "and billingMonth = '" + month1 + "' and billingYear = " + year1; string query_select2 = "select * from BILLING where customerId = " + customerId + "and billingMonth = '" + month2 + "' and billingYear = " + year2; try { using (connect = new SqlConnection(connString)) { connect.Open(); dt = new DataTable(); sda = new SqlDataAdapter(query_select1, connect); sda.Fill(dt); sda = new SqlDataAdapter(query_select2, connect); sda.Fill(dt); } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { sda.Dispose(); dt.Dispose(); connect.Close(); } return dt; }
private void MaterialRaisedButton2_Click(object sender, EventArgs e) { try { DialogResult x = MessageBox.Show(dataGridView1.CurrentRow.Cells[2].Value.ToString() + "Seçili kayıtı Silmek istiyormusnuz ?", "Uyarı", MessageBoxButtons.YesNo); if (x == DialogResult.Yes) { baglan(); System.Data.DataTable dt1 = new System.Data.DataTable(); OleDbCommand Cmd1 = new OleDbCommand("Delete From Sayac where Kimlik=" + dataGridView1.CurrentRow.Cells[5].Value + "", baglanti); OleDbDataAdapter da1 = new OleDbDataAdapter(Cmd1); da1.Fill(dt1); da1.Dispose(); dt1.Dispose(); baglanti.Close(); MessageBox.Show("Seçili Kayıt Silindi"); dataGridView1.DataSource = null; dataGridView1.Rows.Clear(); dtst.Clear(); sqlcek(); grafikciz(); grafikciz2(); } else { MessageBox.Show("Silme İşlem, İptal edildi..."); } } catch (Exception hata) { } }
protected void add_catagroy() { ddl_complaint.Items.Clear(); System.Data.DataTable dt_item = new System.Data.DataTable(); MySqlDataAdapter cmd_item = new MySqlDataAdapter("SELECT category FROM `pay_complaint_category` ", d.con); d.con.Open(); try { cmd_item.Fill(dt_item); if (dt_item.Rows.Count > 0) { ddl_complaint.DataSource = dt_item; // ddl_category.DataTextField = dt_item.Columns[1].ToString(); ddl_complaint.DataValueField = dt_item.Columns[0].ToString(); ddl_complaint.DataBind(); } dt_item.Dispose(); d.con.Close(); ddl_complaint.Items.Insert(0, "Select"); } catch (Exception ex) { throw ex; } finally { d.con.Close(); } }
public void AddIventoryItem() { string connectionString = WebConfigurationManager.AppSettings["ConnectionString"]; using (SqlConnection connection = new SqlConnection(connectionString)) { DataTable dt = new DataTable(); SqlCommand command = new SqlCommand(Resources.Resource.AddOnInventory, connection); command.Parameters.AddWithValue("@Categoria", Categoria); command.Parameters.AddWithValue("@NomeFornecedor", NomeFornecedor); command.Parameters.AddWithValue("@NumeroDoc", NumeroDoc); command.Parameters.AddWithValue("@NomeProduto", NomeProduto); command.Parameters.AddWithValue("@Valor", Valor); command.Parameters.AddWithValue("@Observacao", Observacao); command.Parameters.AddWithValue("@DataCriacao", DateTime.Now); try { connection.Open(); command.CommandType = CommandType.Text; command.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine(ex.Message); } finally { dt.Dispose(); connection.Close(); } } }
public void LlenarGridPermisos() { /*SqlConnection cnn = new SqlConnection(ConfigurationManager.AppSettings.Get("strConnection"));*/ string conexion = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString; SqlConnection cnn; cnn = new SqlConnection(conexion); cnn.Open(); SqlCommand comando = new SqlCommand("SP_Catalgo_Permisos", cnn); comando.CommandType = CommandType.StoredProcedure; comando.Parameters.AddWithValue("@Opcion", "LISTADO"); SqlDataAdapter da = new SqlDataAdapter(comando); DataTable ds = new DataTable(); da.Fill(ds); Grid_Permisos.Visible = true; Grid_Permisos.DataSource = ds; Grid_Permisos.Columns[0].Visible = true; Grid_Permisos.Columns[1].Visible = true; Grid_Permisos.DataBind(); Grid_Permisos.Columns[0].Visible = false; ds.Dispose(); da.Dispose(); }
protected void ButtonSubmit_Click(object sender, EventArgs e) { string InstanceName = TextBoxSource.Text; //List Catalogs(DBs) string strMDX = "select * from $system.DBSCHEMA_CATALOGS"; string strConnString = "Datasource=" + InstanceName; System.Data.DataTable dtDB = GetDataTable(strConnString, strMDX); //returns data table with DB details //Enumerate cubes in each database ArrayList DBList = ReturnValueFromDataTable(dtDB, 0); //Get DB Name (Catalog_Name) from data table dtDB.Dispose(); System.Data.DataTable dtCubes = new System.Data.DataTable(); System.Data.DataTable dtPartial = new System.Data.DataTable(); foreach (string DBName in DBList) { strMDX = "SELECT * FROM $system.MDSchema_Cubes WHERE CUBE_SOURCE=1"; strConnString = "Datasource=" + TextBoxSource.Text + ";Catalog=" + DBName; dtPartial = GetDataTable(strConnString, strMDX); //get all Cubes for the DB DBName DataColumn CubeLink = dtPartial.Columns.Add("Link"); // Add a column "Link" in the data table CubeLink.SetOrdinal(0); //make new column as first column in table dtCubes.Merge(dtPartial); dtPartial.Dispose(); } GridViewResults.AutoGenerateColumns = true; GridViewResults.DataSource = dtCubes; //returns data table with DB details GridViewResults.DataBind(); dtCubes.Dispose(); }
/// <summary> /// 获取Excel工作薄中Sheet页(工作表)名集合 /// </summary> /// <param name="excelFile">Excel文件名及路径,EG:C:\Users\JK\Desktop\导入测试.xls</param> /// <returns>Sheet页名称集合</returns> private String[] GetExcelSheetNames(string fileName) { // 清理 if (objConn != null) { objConn.Close(); objConn.Dispose(); } System.Data.DataTable dt = null; try { string connString = string.Empty; string FileType = fileName.Substring(fileName.LastIndexOf(".")); if (FileType == ".xls") { connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + fileName + ";Extended Properties=Excel 8.0;"; } else//.xlsx { connString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + fileName + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\""; } // 创建连接对象 objConn = new OleDbConnection(connString); // 打开数据库连接 objConn.Open(); // 得到包含数据架构的数据表 dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (dt == null) { return(null); } String[] excelSheets = new String[dt.Rows.Count]; int i = 0; // 添加工作表名称到字符串数组 foreach (DataRow row in dt.Rows) { string strSheetTableName = row["TABLE_NAME"].ToString(); //过滤无效SheetName if (strSheetTableName.Contains("$") && strSheetTableName.Replace("'", "").EndsWith("$")) { excelSheets[i] = strSheetTableName.Substring(0, strSheetTableName.Length - 1); } i++; } return(excelSheets); } catch (Exception ex) { MessageBox.Show(ex.ToString()); return(null); } finally { if (dt != null) { dt.Dispose(); } } }
private void button2_Click(object sender, EventArgs e) { DataTable tb = new DataTable(); SQLiteDataAdapter ad = new SQLiteDataAdapter("select distinct max(serialNo) + 1 from StockPrice", ConfigurationSettings.AppSettings["connectionString"]); ad.Fill(tb); string SerialNo = tb.Rows[0][0].ToString().Trim(); tb.Dispose(); tb = new DataTable(); ad = new SQLiteDataAdapter("select StockId from Stock",ConfigurationSettings.AppSettings["connectionString"]); ad.Fill(tb); StockProcess s = null; System.Threading.Thread t = null; foreach (DataRow r in tb.Rows) { s = new StockProcess(); s.SerialNo = SerialNo; s.股票代號 = r["StockId"].ToString().Trim(); t = new System.Threading.Thread(s.Process); t.Start(); System.Threading.Thread.Sleep(200); } }
public int AddInfoModel(DataTable dt, string TableName) { int num; string str = ""; string str2 = ""; for (num = 0; num < dt.Rows.Count; num++) { if ((dt.Rows.Count - 1) == num) { str = str + "[" + dt.Rows[num]["FieldName"].ToString() + "])"; str2 = str2 + "@" + dt.Rows[num]["FieldName"].ToString() + ");select scope_identity()"; } else { str = str + "[" + dt.Rows[num]["FieldName"].ToString() + "],"; str2 = str2 + "@" + dt.Rows[num]["FieldName"].ToString() + ","; } } StringBuilder builder = new StringBuilder(); builder.Append("insert into " + TableName + "("); builder.Append(str); builder.Append(" values ("); builder.Append(str2); SqlParameter[] commandParameters = new SqlParameter[dt.Rows.Count]; for (num = 0; num < dt.Rows.Count; num++) { commandParameters[num] = new SqlParameter("@" + dt.Rows[num]["FieldName"].ToString(), dt.Rows[num]["FieldValue"].ToString()); } int num2 = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringKy, CommandType.Text, builder.ToString(), commandParameters)); dt.Clear(); dt.Dispose(); return num2; }
public void AddOrder() { string connectionString = WebConfigurationManager.AppSettings["ConnectionString"]; using (SqlConnection connection = new SqlConnection(connectionString)) { DataTable dt = new DataTable(); SqlCommand command = new SqlCommand(Resources.Resource.InsertOrder, connection); command.Parameters.AddWithValue("@ID", ID); command.Parameters.AddWithValue("@Cliente", Cliente); command.Parameters.AddWithValue("@Telefone", Telefone); command.Parameters.AddWithValue("@Pedido", Pedido); command.Parameters.AddWithValue("@DataPedido", DateTime.Now); try { connection.Open(); command.CommandType = CommandType.Text; command.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine(ex.Message); } finally { dt.Dispose(); connection.Close(); } } }
//''' <summary> //''' 檢查帳號是否已存在 //''' </summary> //''' <param name="Uid"></param> //''' <param name="CurrentCde"></param> //''' <returns></returns> //''' <remarks></remarks> Boolean ChkUsrUidExist(String Uid, String CurrentCde) { IzDataSource IzDataSource = new IzDataSource(); Boolean Tmp = false; if (CurrentCde == "") { IzDataSource.SelectString = "select tbUsrCde from tbUsr where tbUsrUid=@tbUsrUid"; IzDataSource.ParametersAdd("tbUsrUid", Uid); System.Data.DataTable tb = IzDataSource.SelectDataTable(); if (tb.Rows.Count > 0) { Tmp = true; } tb.Dispose(); } else { IzDataSource.SelectString = "select tbUsrCde from tbUsr where tbUsrUid=@tbUsrUid and tbUsrCde<>@tbUsrCde"; IzDataSource.ParametersAdd("tbUsrUid", Uid); IzDataSource.ParametersAdd("tbUsrCde", CurrentCde); System.Data.DataTable tb = IzDataSource.SelectDataTable(); if (tb.Rows.Count > 0) { Tmp = true; } tb.Dispose(); } IzDataSource.Dispose(); return(Tmp); }
protected void ddl_state_labour_SelectedIndexChanged(object sender, EventArgs e) { ddl_labour_branch.Items.Clear(); System.Data.DataTable dt_item = new System.Data.DataTable(); MySqlDataAdapter cmd_item = new MySqlDataAdapter("Select CONCAT((SELECT DISTINCT(STATE_CODE) FROM pay_state_master WHERE STATE_NAME = pay_unit_master.STATE_NAME),'_',UNIT_NAME,'_',UNIT_ADD1) as UNIT_NAME, unit_code,flag from pay_unit_master where state_name = '" + ddl_labour_state.SelectedValue + "' and comp_code='" + Session["comp_code"] + "' and client_code = '" + ddl_labour_client.SelectedValue + "' AND UNIT_CODE in(select UNIT_CODE from pay_client_state_role_grade where COMP_CODE='" + Session["COMP_CODE"].ToString() + "' AND EMP_CODE in (" + Session["REPORTING_EMP_SERIES"].ToString() + ") AND client_code='" + ddl_labour_client.SelectedValue + "') AND branch_status = 0 ORDER BY 1", d.con); d.con.Open(); try { ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "CallMyFunction", "unblock()", true); cmd_item.Fill(dt_item); if (dt_item.Rows.Count > 0) { ddl_labour_branch.DataSource = dt_item; ddl_labour_branch.DataTextField = dt_item.Columns[0].ToString(); ddl_labour_branch.DataValueField = dt_item.Columns[1].ToString(); ddl_labour_branch.DataBind(); } dt_item.Dispose(); d.con.Close(); //vikas ddl_labour_branch.Items.Insert(0, "Select"); } catch (Exception ex) { throw ex; } finally { d.con.Close(); } }
public static DataRowCollection GetQueryResults(string query, Dictionary<string, object> parameters, bool log = true) { DataRowCollection retResults; using(SqlConnection s = GetActiveConnection()) { SqlCommand sQuery = new SqlCommand(); sQuery.Connection = s; sQuery.CommandText = query; sQuery.CommandType = CommandType.Text; foreach(KeyValuePair<string, object> i in parameters) { sQuery.Parameters.AddWithValue(i.Key, i.Value); } SqlDataReader sReader = sQuery.ExecuteReader(); DataTable dTable = new DataTable(); dTable.Load(sReader); retResults = dTable.Rows; sReader.Close(); dTable.Dispose(); } if(log) Logging.Log("Query {0} successfully executed ({1} Parameters)", query, parameters.Count); return retResults; }
/// <summary> /// Execute Array List /// </summary> /// <param name="commandType">Command Type</param> /// <param name="mySqlList">Sql List</param> /// <returns></returns> public static ArrayList ExecuteArrayList(CommandType cmdType, List<SqlList> mySqlList) { SqlCommand cmd = new SqlCommand(); ArrayList al = new ArrayList(); DataTable dt = null; int i = 0; using (SqlConnection con = new SqlConnection(sqlconn)) { foreach (SqlList item in mySqlList) { PrepareCommand(cmd, con, null, cmdType, item.strSQL, item.myPrams); SqlDataAdapter adp = new SqlDataAdapter(cmd); try { dt = new DataTable(); adp.Fill(dt); i = i + 1; if (dt.Rows.Count > 0) al.Add(dt.Rows[0].ItemArray); dt.Dispose(); cmd.Parameters.Clear(); } finally { adp.Dispose(); } } } return al; }
public static async Task<List<double>> RetriveTaxesByType(string taxName) { List<double> taxList = new List<double>(); string sqlCmdString = "Select tax_amount From dbo.taxes Where tax_name=@tax_name"; Database myDb = new Database(); SqlCommand sqlCmd = new SqlCommand(sqlCmdString, myDb.Connection); SqlDataAdapter sqlAdapt = new SqlDataAdapter(sqlCmd); DataTable table = new DataTable(); double tax; await Task.Run(() => { try { myDb.OpenConnection(); sqlCmd.Parameters.Add(new SqlParameter("@tax_name", taxName)); sqlAdapt.Fill(table); foreach (DataRow row in table.Rows) { tax = Convert.ToDouble(row["tax_amount"]); taxList.Add(tax); } } finally { sqlAdapt.Dispose(); table.Dispose(); myDb.CloseConnection(); } }); return taxList; }
public System.Data.DataRow GetDataRow(DbCommand SelectCommand) { System.Data.DataRow objDataRow = null; System.Data.DataTable objDataTable = null; try { objDataTable = this.GetDataTable(SelectCommand); if (objDataTable != null) { if (objDataTable.Rows.Count > 0) { objDataRow = objDataTable.Rows[0]; } } } catch (Exception ex) { throw new Exception("GetDataRow: " + ex.Message); } finally { if (objDataTable != null) { objDataTable.Dispose(); objDataTable = null; } } return(objDataRow); }
protected void cbp_SaveOrg_Callback(object sender, CallbackEventArgsBase e) { FormsIdentity id = (FormsIdentity)HttpContext.Current.User.Identity; FormsAuthenticationTicket ticket = id.Ticket; if (!ticket.Expired) { DataTable Dt = new DataTable(); try { FormTVP(ref Dt); PopulateTVP(ref Dt); string err = OrganisasiMethods.SP_LoadUpdate_Organisation(ref Dt, ticket.Name, "SAVE"); if (err == "") { cbp_SaveOrg.JSProperties["cpErrMsg"] = string.Empty; } else { cbp_SaveOrg.JSProperties["cpErrMsg"] = ConfigurationManager.AppSettings["ErrorMessageStaringSentenceDBError"].ToString() + " " + err; } } catch (Exception err) { cbp_SaveOrg.JSProperties["cpErrMsg"] = ConfigurationManager.AppSettings["ErrorMessageStaringSentenceCaughtByExceptions"].ToString() + " " + err.Message; } finally { Dt.Dispose(); } } else { cbp_SaveOrg.JSProperties["cpErrMsg"] = "Session Expired."; } }
//''' <summary> //''' 取排序最新一碼 //''' </summary> //''' <param name="TableNme">Table 名稱</param> //''' <param name="SrtNme">排序欄位名稱</param> //''' <param name="Typ">A:全欄位,B:前幾碼,"":不選</param> //''' <param name="TypFdNme">篩選欄位</param> //''' <param name="TypFdVal">篩選資料</param> //''' <returns></returns> //''' <remarks></remarks> public Int32 getNextSrt(string TableNme, string SrtNme, string Typ = "", string TypFdNme = "", string TypFdVal = "") { int Tmp = 1; IzDataSource IzDataSource = new IzDataSource(); if (Typ == "A") { //'全欄位 IzDataSource.SelectString = "select top 1 " + SrtNme + " from " + TableNme + " where " + TypFdNme + "='" + TypFdVal + "' order by " + SrtNme + " desc"; } else if (Typ == "B") { //'前幾碼 int Num = TypFdVal.Length; IzDataSource.SelectString = "select top 1 " + SrtNme + " from " + TableNme + " where left(" + TypFdNme + "," + Num + ")='" + TypFdVal + "' order by " + SrtNme + " desc"; } else { IzDataSource.SelectString = "select top 1 " + SrtNme + " from " + TableNme + " order by " + SrtNme + " desc"; } System.Data.DataTable tb = IzDataSource.SelectDataTable(); if (tb.Rows.Count > 0) { Tmp = Convert.ToInt32(tb.Rows[0][0]) + 10; } tb.Dispose(); IzDataSource.Dispose(); return(Tmp); }
//''' <summary> //''' 產生下一位數類別編號 //''' </summary> //''' <param name="Tid">前三碼</param> //''' <param name="TidNum">數字位數</param> //''' <returns></returns> //''' <remarks></remarks> public String getTypNexTid(String Tid, int TidNum) { IZCls.StringAccess StringAccess = new IZCls.StringAccess(); String StrTmp = ""; IzDataSource IzDataSource = new IzDataSource(); IzDataSource.SelectString = "select top 1 tbTypTid from tbTyp where left(tbTypTid," + Tid.Length + ")=@tbTypTopCde order by tbTypTid desc"; IzDataSource.ParametersClear(); IzDataSource.ParametersAdd("tbTypTopCde", Tid); System.Data.DataTable tb = IzDataSource.SelectDataTable(); if (tb.Rows.Count > 0) { //String TidTNum = Right("" & tb.Rows(0).Item("tbTypTid"), Len("" & tb.Rows(0).Item("tbTypTid")) - Len(Tid)); String TidTNum = tb.Rows[0]["tbTypTid"].ToString().Substring(tb.Rows[0]["tbTypTid"].ToString().IndexOf(Tid) + Tid.Length); if (StringAccess.IsNum(TidTNum) == true) { StrTmp = Tid + StringAccess.addZeroBfStr(Convert.ToInt32(TidTNum) + 1, TidNum); } else { StrTmp = Tid + StringAccess.addZeroBfStr(1, TidNum); } } else { StrTmp = Tid + StringAccess.addZeroBfStr(1, TidNum); } tb.Dispose(); IzDataSource.Dispose(); return(StrTmp); }
protected void cbp_UploadImage_Loader_Callback(object sender, DevExpress.Web.CallbackEventArgsBase e) { DataTable Dt = new DataTable(); try { string err = ImageUploaderMethods.ImageInfo(RowId.Text, ref Dt); if (err == string.Empty) { if (Dt.Rows.Count == 1) { ImageFileUserName.Text = Dt.Rows[0]["ImageFileUserName"].ToString(); UploadedBy.Text = Dt.Rows[0]["UploadedBy"].ToString(); Notes.Text = Dt.Rows[0]["Notes"].ToString(); UploadedDate.Text = Convert.ToDateTime(Dt.Rows[0]["UploadedDate"]).ToString("dd/MM/yyyy HH:mm"); CurrImage.ImageUrl = Path.Combine(ConfigurationManager.AppSettings["ImageUploadFolder"].ToString(), Dt.Rows[0]["ImageFileSystemName"].ToString()); cbp_UploadImage_Loader.JSProperties["cpErrMsg"] = string.Empty; } else { cbp_UploadImage_Loader.JSProperties["cpErrMsg"] = "Sistem tidak dapat membuka info gambar yang telah dipilih."; } } else { cbp_UploadImage_Loader.JSProperties["cpErrMsg"] = err; } } catch (Exception err) { cbp_UploadImage_Loader.JSProperties["cpErrMsg"] = err.Message; } finally { Dt.Dispose(); } }
protected void cbp_UploadDocument_Loader_Callback(object sender, DevExpress.Web.CallbackEventArgsBase e) { DataTable Dt = new DataTable(); try { string err = DocumentUploaderMethods.DocumentInfo(RowId.Text, ref Dt); if (err == string.Empty) { if (Dt.Rows.Count == 1) { DocumentFileUserName.Text = Dt.Rows[0]["DocumentFileUserName"].ToString(); UploadedBy.Text = Dt.Rows[0]["UploadedBy"].ToString(); Notes.Text = Dt.Rows[0]["Notes"].ToString(); UploadedDate.Text = Convert.ToDateTime(Dt.Rows[0]["UploadedDate"]).ToString("dd/MM/yyyy HH:mm"); cbp_UploadDocument_Loader.JSProperties["cpErrMsg"] = string.Empty; } else { cbp_UploadDocument_Loader.JSProperties["cpErrMsg"] = "Sistem tidak dapat membuka info gambar yang telah dipilih."; } } else { cbp_UploadDocument_Loader.JSProperties["cpErrMsg"] = err; } } catch (Exception err) { cbp_UploadDocument_Loader.JSProperties["cpErrMsg"] = err.Message; } finally { Dt.Dispose(); } }
public static void nseCompanyCodes() { StreamReader sr = new StreamReader(@"C:\\Documents and Settings\\Amey\\Desktop\\EQUITY_L.csv"); DataTable nsedata = new DataTable(); DataColumn companyName = new DataColumn("CompanyName"); nsedata.Columns.Add(companyName); DataColumn nsecode = new DataColumn("NSE Code"); nsedata.Columns.Add(nsecode); DataRow row; string[] values = null; sr.ReadLine(); while (!sr.EndOfStream) { values = sr.ReadLine().Split(','); row = nsedata.NewRow(); row[companyName] = values[1]; row[nsecode] = values[0]; nsedata.Rows.Add(row); } CommonFunctions.Write(nsedata, "nsecompanies.txt"); sr.Close(); nsedata.Dispose(); companyName.Dispose(); nsecode.Dispose(); }
public void Loaddata() { try { ConnectDB.MoKetNoi(); da = new SqlDataAdapter("select * from GroupClothes", ConnectDB.connect); dt = new DataTable(); da.Fill(dt); if (dt.Rows.Count > 0) { grvgroupclothes.DataSource = dt; grvgroupclothes.DataBind(); } else { lblerror.Text = "Loi"; } } catch (Exception ex) { lblerror.Text = "Loi" + ex.Message; } finally { ConnectDB.DongKetNoi(); da.Dispose(); dt.Dispose(); } }
//''' <summary> //''' 取功能權限字串 //''' </summary> //''' <param name="UsrCde"></param> //''' <param name="MnuCde"></param> //''' <returns></returns> //''' <remarks></remarks> public String GetPow(String UsrCde, String MnuCde) { String tmp = ""; if (UsrCde == "USR00000000000000001") { tmp = "BOW01,BOW02,BOW03,ADD,EDIT,DEL"; } else { IzDataSource IzDataSource = new IzDataSource(); IzDataSource.SelectString = "select * from rlGrpMnu where tbMnuCde=@tbMnuCde and tbGrpCde in (select tbGrpCde from rlUsrGrp where tbUsrCde=@tbUsrCde)"; IzDataSource.ParametersClear(); IzDataSource.ParametersAdd("tbMnuCde", MnuCde); IzDataSource.ParametersAdd("tbUsrCde", UsrCde); System.Data.DataTable tb = IzDataSource.SelectDataTable(); for (int i = 0; i < tb.Rows.Count; i++) { tmp += "" + tb.Rows[i]["tbMnuPow"].ToString().Trim() + ","; } tb.Dispose(); IzDataSource.Dispose(); } return(tmp); }
/// <summary> /// 取得 VDS_CRM_ORDER_MAIN && VDS_CRM_ORDER_DETL 的 Data /// </summary> /// <param name="ParameterList"></param> /// <returns>回傳查詢結果</returns> public DataSet QUERY_CRM041_MAIN_AND_DETL(ArrayList ParameterList) { try { DataSet ds_Return = new DataSet(); DataTable dt_Return_Main = new DataTable(); DataTable dt_Return_Detl = new DataTable(); DBO.VDS_CRM_ORDER_MAINDBO VCOM = new VDS_CRM_ORDER_MAINDBO(ref USEDB); DBO.VDS_CRM_ORDER_DETLDBO VCOD = new VDS_CRM_ORDER_DETLDBO(ref USEDB); dt_Return_Main = VCOM.QUERY_CRM041_MAIN(ParameterList); dt_Return_Main.TableName = "VDS_CRM_ORDER_MAIN"; dt_Return_Detl = VCOD.QUERY_CRM041_DETL(ParameterList); dt_Return_Detl.TableName = "VDS_CRM_ORDER_DETL"; ds_Return.Tables.Add(dt_Return_Main.Copy()); ds_Return.Tables.Add(dt_Return_Detl.Copy()); dt_Return_Main.Clear(); dt_Return_Detl.Clear(); dt_Return_Main.Dispose(); dt_Return_Detl.Dispose(); return ds_Return; } catch (Exception ex) { throw GetNewException(ex); } }
private void Button2_Click(object sender, EventArgs e) { try { DialogResult x = MessageBox.Show(listBox1.Items[listBox1.SelectedIndex].ToString() + " Adlı Seçili kayıtı Silmek istiyormusnuz ?", "Uyarı", MessageBoxButtons.YesNo); if (x == DialogResult.Yes) { baglan(); System.Data.DataTable dt1 = new System.Data.DataTable(); OleDbCommand Cmd1 = new OleDbCommand("Delete From Kisiler where Isim=" + listBox1.Items[listBox1.SelectedIndex].ToString() + "", baglanti); OleDbDataAdapter da1 = new OleDbDataAdapter(Cmd1); da1.Fill(dt1); da1.Dispose(); dt1.Dispose(); baglanti.Close(); MessageBox.Show("Seçili Kayıt Silindi"); } else { MessageBox.Show("Silme İşlem, İptal edildi..."); } } catch (Exception hata) { } }
private void btnNew_Click( object sender, EventArgs e ) { if( Program.UserType == UserType.Customer ) return; AddDlg dlg = new AddDlg(); if( dlg.ShowDialog( this ) != DialogResult.OK ) return; RefillComboBoxes(); OleDbCommand cmd = new OleDbCommand(); cmd.CommandText = "select top 1 * from WedDress order by [ID] desc"; cmd.Connection = Program.Database; cmd.CommandType = CommandType.Text; OleDbDataAdapter oda = new OleDbDataAdapter( cmd); DataTable dt = new DataTable(); oda.Fill( dt ); dsDress.Tables["WedDress"].Merge( dt ); dt.Dispose(); oda.Dispose(); cmd.Dispose(); }
public static DataTable ConvertTableType(DataTable dt, Dictionary<string, Type> types) { DataTable newDt = dt.Clone(); //convert all columns' datatype newDt.TableName = dt.TableName; foreach (DataColumn dc in newDt.Columns) { if (types.ContainsKey(dc.ColumnName)) { dc.DataType = types[dc.ColumnName]; } } //import data from original table //foreach (DataRow dr in dt.Rows) //{ // newDt.ImportRow(dr); //} FillData(dt, newDt); dt.Dispose(); return newDt; }
protected void Page_Load(object sender, System.EventArgs e) { // �ڴ˴������û������Գ�ʼ��ҳ�� DataTable dt = new DataTable(); string sql = "Select semployeeid,semployeename from FCS_EMPLOYEE "; DBConn myCn= new DBConn(); myCn.DBOpen(); OleDbCommand myCommand = new OleDbCommand(sql,new OleDbConnection(myCn.strConn)); OleDbDataAdapter sdap = new OleDbDataAdapter(myCommand); sdap.Fill(dt); myCn.DBClose(); StringBuilder sb = new StringBuilder(); sb.Append("<table cellpadding=\"0\" id=\"tab\" name=\"tab\" cellspacing=\"0\" height=\"25\" width=\"100%\" frame=border>"); if (dt.Rows.Count >0) { foreach(DataRow iRow in dt.Rows) { sb.Append("<tr class=\"alternate2\" >"); sb.Append("<td width = \"100px\">"+ iRow["semployeename"].ToString()+"</td>"); sb.Append("<td><input type='checkbox' id='chkDisabled' name='chkDisabled'> ���� <input type='checkbox' id='chkDisplay' name='chkDisplay'> ���� <input type='checkbox' id='chkRead' name='chkRead'> ֻ��</td>"); sb.Append("</tr>"); } } sb.Append("</table>"); if (dt != null) dt.Dispose(); div1.InnerHtml = sb.ToString(); }
public void search_usr() { string query = ""; if ((fname_txt.Text.Length == 0) && (lname_txt.Text.Length == 0)) { query = "select id,user_id,first_name,last_name,usr_name,role_name,mob_no,usr_address,email_id from user_view where role_id=" + role_drop.SelectedIndex; } else if ((fname_txt.Text.Length != 0) && (role_drop.SelectedIndex != 0)) { query = "select id,user_id,first_name,last_name,usr_name,role_name,mob_no,usr_address,email_id from user_view where first_name='" + fname_txt.Text.Trim() + "' and role_id=" + role_drop.SelectedIndex; } else if (fname_txt.Text.Length != 0) { query = "select id,user_id,first_name,last_name,usr_name,role_name,mob_no,usr_address,email_id from user_view where first_name='" + fname_txt.Text.Trim() + "'"; } else { query = "select id,user_id,first_name,last_name,usr_name,role_name,mob_no,usr_address,email_id from user_view where first_name='" + fname_txt.Text.Trim() + "' and last_name='" + lname_txt.Text.Trim() + "' and role_id=" + role_drop.SelectedIndex; } try { SqlDataAdapter ad = new SqlDataAdapter(query, impfunc.cnn); DataTable dt = new System.Data.DataTable(); ad.Fill(dt); usr_grd.DataSource = dt; // this.dataGridView1.Columns[0].Visible = false; ad.Dispose(); dt.Dispose(); } catch (Exception ex) { MessageBox.Show("Something goes wrong, please close the software and open again.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
/// <summary> /// Authenticates the user. /// </summary> /// <param name="userName">Name of the user.</param> /// <param name="password">The password.</param> /// <returns></returns> public bool AuthenticateUser(string userName, string password) { bool success = false; DataTable requestTable = new DataTable(); using (IDbConnection conn = UserDataProvider.GetAuthenticationConnection()) { conn.Open(); IDbCommand cmd = conn.CreateCommand(); cmd.CommandText = "select UserName, Password from users where UserName = @Username and Password = @Password"; cmd.CommandType = CommandType.Text; cmd.Parameters.Add(UserDataProvider.AddParameterWithValue("@UserName",userName)); cmd.Parameters.Add(UserDataProvider.AddParameterWithValue("@Password", password)); IDataReader reader = cmd.ExecuteReader(); using (reader) { requestTable.Load(reader); } } if (requestTable.Rows.Count==1 ) { success = true; } requestTable.Dispose(); return success; }
/// <summary> /// Devuelve un DataTable con la información del Archivo Xlsx y Hoja deseadas /// </summary> /// <param name="pRuta"></param> /// <param name="pHoja"></param> /// <returns></returns> public static System.Data.DataTable getDataXlsToDt(string pRuta, string pHoja, string pCols = "*", string pRngo = "") { System.Data.DataTable dt = new System.Data.DataTable(); try { if (!string.IsNullOrEmpty(pRuta) && !string.IsNullOrEmpty(pHoja)) { setConnection(pRuta); setDataAdapter(pHoja, pCols, pRngo); myDataAdapter.Fill(dt); } return(dt); } catch (Exception ex) { dt = new System.Data.DataTable(); return(dt); throw ex; } finally { if (conn != null) { conn.Close(); conn.Dispose(); } if (dt != null) { dt.Dispose(); } } }
public string GetReturnString(string p_SqlString) { try { Open(); SqlDataAdapter l_DataAdapter = new SqlDataAdapter(p_SqlString, cn); System.Data.DataTable l_DataTable = new System.Data.DataTable(); l_DataAdapter.Fill(l_DataTable); l_DataAdapter.Dispose(); if (cn.State == ConnectionState.Open) { Close(); } if (l_DataTable.Rows.Count > 0) { string l_count = l_DataTable.Rows[0].ItemArray.GetValue(0).ToString(); l_DataTable.Dispose(); return(l_count); } else { return(""); } } catch (Exception E) { writeTxtCls wtc = new writeTxtCls(); wtc.writeTxtToFile("\r\n[" + System.DateTime.Now.ToString() + "]执行RunSqlReturn方法[" + p_SqlString + "]语句发生问题:" + E.Message); return(""); } }
private static void ImportIDs(long[] IDs, SqlCommand command) { long start = DateTime.Now.Ticks; DataTable t = new DataTable(); DataColumn c = new DataColumn("ID", typeof(long)); t.Columns.Add(c); DataColumn c2 = new DataColumn("seq_nr", typeof(long)); c2.AutoIncrementSeed = 1; c2.AutoIncrementStep = 1; c2.AutoIncrement = true; t.Columns.Add(c2); foreach (long id in IDs) { DataRow dr = t.NewRow(); dr[0] = id; t.Rows.Add(dr); } command.CommandText = "IF OBJECT_ID(N'tempdb..#temp', N'U') IS NOT NULL " + "DROP TABLE #temp " + "CREATE TABLE #temp(ID BIGINT, seq_nr BIGINT); CREATE INDEX ix ON #temp (ID);"; command.ExecuteNonQuery(); SqlBulkCopy copy = new SqlBulkCopy(command.Connection, SqlBulkCopyOptions.UseInternalTransaction, null); copy.DestinationTableName = "#temp"; copy.WriteToServer(t); t.Dispose(); Debug.WriteLine("<<ImportIDs>> " + new TimeSpan(DateTime.Now.Ticks - start).ToString()); }
//讀取Excel中的所有表名 //读取Excel文件时,可能一个文件中会有多个Sheet,因此获取Sheet的名称是非常有用的 /// <summary> /// 根据Excel物理路径获取Excel文件中所有表名,列名是TABLE_NAME /// </summary> /// <param name="excelFile">Excel物理路径</param> /// <returns>DataTable</returns> public static System.Data.DataTable GetExcelSheetNames2DataTable(string excelFile) { OleDbConnection objConn = null; System.Data.DataTable dt = null; try { string strConn = GetExcelConnectionString(excelFile); objConn = new OleDbConnection(strConn); objConn.Open(); dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (dt == null) { return(null); } return(dt); } catch (Exception ee) { throw new Exception(ee.Message); } finally { if (objConn != null) { objConn.Close(); objConn.Dispose(); } if (dt != null) { dt.Dispose(); } } }
public void client_list() { d.con1.Close(); ddl_labour_client.Items.Clear(); System.Data.DataTable dt_item = new System.Data.DataTable(); MySqlDataAdapter cmd_item = new MySqlDataAdapter("SELECT DISTINCT pay_client_master.client_code, client_NAME FROM pay_client_master INNER JOIN pay_client_state_role_grade ON pay_client_master.COMP_CODE = pay_client_state_role_grade.COMP_CODE and pay_client_master.client_code = pay_client_state_role_grade.client_code WHERE pay_client_master.comp_code = '" + Session["COMP_CODE"].ToString() + "' AND pay_client_state_role_grade.emp_code IN (" + Session["REPORTING_EMP_SERIES"] + ")", d.con1); d.con1.Open(); try { cmd_item.Fill(dt_item); if (dt_item.Rows.Count > 0) { ddl_labour_client.DataSource = dt_item; ddl_labour_client.DataTextField = dt_item.Columns[1].ToString(); ddl_labour_client.DataValueField = dt_item.Columns[0].ToString(); ddl_labour_client.DataBind(); } dt_item.Dispose(); d.con1.Close(); ddl_labour_client.Items.Insert(0, "Select"); } catch (Exception ex) { throw ex; } finally { d.con1.Close(); } }
/* * * private void ExportExcel1() { * HttpResponse response = HttpContext.Current.Response; * String FileName = "NewExcel.xls"; * response.Clear(); * response.ClearContent(); * response.ClearHeaders(); * response.Buffer = true; * response.ContentType = "application/ms-excel"; * response.AddHeader("content-disposition", "attachment;filename="+ FileName ); * response.AddHeader("Content-Type", "application/Excel"); * response.Charset = ""; * * using (StringWriter sw = new StringWriter()) * { * HtmlTextWriter hw = new HtmlTextWriter(sw); * * //To Export all pages * NewGeneratedBill.AllowPaging = false; * // this.LoadLatestBillData(); * * NewGeneratedBill.HeaderRow.BackColor = System.Drawing.Color.White; * foreach (TableCell cell in NewGeneratedBill.HeaderRow.Cells) * { * cell.BackColor = NewGeneratedBill.HeaderStyle.BackColor; * } * foreach (GridViewRow row in NewGeneratedBill.Rows) * { * row.BackColor = System.Drawing.Color.White; * foreach (TableCell cell in row.Cells) * { * if (row.RowIndex % 2 == 0) * { * cell.BackColor = NewGeneratedBill.AlternatingRowStyle.BackColor; * } * else * { * cell.BackColor = NewGeneratedBill.RowStyle.BackColor; * } * cell.CssClass = "textmode"; * } * } * * NewGeneratedBill.RenderControl(hw); * * //style to format numbers to string * //string style = @"<style> .textmode { } </style>"; * //response.Write(style); * response.Output.Write(sw.ToString()); * Response.Flush(); * Response.End(); * } * } * * private void ExportExcel2() { * Response.Clear(); * Response.ClearContent(); * // Response.ContentType = "application/octet-stream"; * // Response.AddHeader("Content-Disposition", "attachment; filename=ExcelFile.xls"); * * * NewGeneratedBill.RenderControl(new HtmlTextWriter(Response.Output)); * * Response.Flush(); * Response.End(); * * } * * private void ExportExcel3() { * * } */ public void DataTableToExcel() { try { HttpResponse response = HttpContext.Current.Response; response.Clear(); response.ClearHeaders(); response.ClearContent(); response.Charset = Encoding.UTF8.WebName; response.AddHeader("content-disposition", "attachment; filename=" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls"); response.AddHeader("Content-Type", "application/Excel"); response.ContentType = "application/vnd.xlsx"; using (StringWriter sw = new StringWriter()) { using (HtmlTextWriter htw = new HtmlTextWriter(sw)) { tableGeneratedBill.TableName = "Sheet1"; GridView gridView = new GridView(); gridView.DataSource = tableGeneratedBill; gridView.DataBind(); gridView.RenderControl(htw); response.Write(sw.ToString()); gridView.Dispose(); tableGeneratedBill.Dispose(); response.End(); } } } catch (Exception ex) { throw new Exception(); } }
DataTable IConsulta.CarregaDadosMae(string idUnit) { DataTable table2; StringBuilder builder = new StringBuilder(); DataTable table = new DataTable(); try { builder.Length = 0; builder.AppendLine("EXECUTE INTOUCH.DBO.[BIINTOUCH.CARREGAINFORMACOES_MAE] "); builder.AppendLine("@ID_UNIT = '" + idUnit + "' "); table = DbIntouch.ExecuteDataTable(builder.ToString()); table2 = table.Copy(); } catch { table2 = null; } finally { if (table != null) { table.Dispose(); } } return table2; }
private void timtheotenbn() // Tìm kiếm dữ liệu theo mã { Ketnoi(); // Tạo kết nối file Access // Tạo Command SqlCommand objcommand = new SqlCommand(); objcommand.Connection = objcon; objcommand.CommandType = CommandType.Text; objcommand.CommandText = "Select * From tbbenhnhan Where tenbn LIKE '%" + txttimtheotenbn.Text + "%' ORDER BY tenbn"; //Tạo đối tượng Adapter SqlDataAdapter objAdapter = new SqlDataAdapter(); objAdapter.SelectCommand = objcommand; // Tạo DataTable nhận dữ liệu trả về DataTable objDataTable = new DataTable("abcd"); objAdapter.Fill(objDataTable); // Gán dữ liệu vào dataGrid dgwtim.DataSource = objDataTable; // Hủy các đối tượng objcommand.Dispose(); objcommand = null; objDataTable.Dispose(); objDataTable = null; objAdapter.Dispose(); objAdapter = null; // hủy kết nối }
public Dictionary <string, string> getItems() { Dictionary <string, string> items = new Dictionary <string, string>(); SQLiteConnection conn = getConnection(); System.Data.DataTable dt = new System.Data.DataTable(); try { SQLiteCommand cmd; //conn.Open(); //Initiate connection to the db cmd = conn.CreateCommand(); cmd.CommandText = QUERY_ITEMS; ad = new SQLiteDataAdapter(cmd); ad.Fill(dt); //fill the datasource DataRow[] rows = dt.Select(); for (int i = 0; i < rows.Length; i++) { items.Add((string)rows[i][Constants.ITEMS_1], (string)rows[i][Constants.ITEMS_2]); } ad.Dispose(); dt.Dispose(); rows = null; } catch (SQLiteException ex) { System.Windows.Forms.MessageBox.Show(ex.Message); } sqlite = null; return(items); }
private String[] GetExcelSheetNames(string excelFile) { OleDbConnection objConn = null; System.Data.DataTable dt = null; try { // Connection String. Change the excel file to the file you // will search. String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";Extended Properties=Excel 8.0;"; // Create connection object by using the preceding connection string. objConn = new OleDbConnection(connString); // Open connection with the database. objConn.Open(); // Get the data table containg the schema guid. dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (dt == null) { return(null); } String[] excelSheets = new String[dt.Rows.Count]; int i = 0; // Add the sheet name to the string array. foreach (DataRow row in dt.Rows) { excelSheets[i] = row["TABLE_NAME"].ToString(); i++; } // Loop through all of the sheets if you want too... for (int j = 0; j < excelSheets.Length; j++) { // Query each excel sheet. } return(excelSheets); } catch (Exception ex) { return(null); } finally { // Clean up. if (objConn != null) { objConn.Close(); objConn.Dispose(); } if (dt != null) { dt.Dispose(); } } }
public Dictionary <string, string[]> getAreas() { Dictionary <string, string[]> areas = new Dictionary <string, string[]>(); SQLiteConnection conn = getConnection(); System.Data.DataTable dt = new System.Data.DataTable(); try { SQLiteCommand cmd; //conn.Open(); //Initiate connection to the db cmd = conn.CreateCommand(); cmd.CommandText = QUERY_AREA; ad = new SQLiteDataAdapter(cmd); dt = new System.Data.DataTable(); ad.Fill(dt); //fill the datasource DataRow[] rows = dt.Select(); for (int i = 0; i < rows.Length; i++) { string[] marca_agrup = new string[2]; marca_agrup[0] = (string)rows[i][Constants.AREA_2]; marca_agrup[1] = (string)rows[i][Constants.AREA_3]; areas.Add((string)rows[i][Constants.AREA_1], marca_agrup); } ad.Dispose(); dt.Dispose(); } catch (SQLiteException ex) { System.Windows.Forms.MessageBox.Show(ex.Message); } sqlite = null; return(areas); }
public ArrayList getEERRs() { ArrayList eerrs = new ArrayList(); SQLiteConnection conn = getConnection(); System.Data.DataTable dt = new System.Data.DataTable(); try { SQLiteCommand cmd; //conn.Open(); //Initiate connection to the db cmd = conn.CreateCommand(); cmd.CommandText = QUERY_EERR; //set the passed query ad = new SQLiteDataAdapter(cmd); dt = new System.Data.DataTable(); ad.Fill(dt); //fill the datasource DataRow[] rows = dt.Select(); for (int i = 0; i < rows.Length; i++) { string[] prefix_desc = new string[2]; prefix_desc[0] = (string)rows[i][Constants.EERR_1]; prefix_desc[1] = (string)rows[i][Constants.EERR_2]; eerrs.Add(prefix_desc); } ad.Dispose(); dt.Dispose(); } catch (SQLiteException ex) { System.Windows.Forms.MessageBox.Show(ex.Message); } sqlite = null; return(eerrs); }
/// <summary> /// 根据Excel物理路径获取Excel文件中所有表名 /// </summary> /// <param name="excelFile">Excel物理路径</param> /// <returns>String[]</returns> public static String[] GetExcelSheetNames(string excelFile) { System.Data.DataTable dt = null; try { dt = GetExcelSheetNames2DataTable(excelFile); if (dt == null) { return(null); } String[] excelSheets = new String[dt.Rows.Count]; int i = 0; foreach (DataRow row in dt.Rows) { excelSheets[i] = row["TABLE_NAME"].ToString(); i++; } return(excelSheets); } catch (Exception ee) { throw new Exception(ee.Message); } finally { if (dt != null) { dt.Dispose(); } } }
//''' <summary> //''' 取得指定類別的類別名稱組(以逗號分開)忽略啟用 //''' </summary> //''' <param name="TopCdeS">TYP(前置三碼)</param> //''' <param name="TypTidStr">T001,T002,T003(可複選)</param> //''' <returns></returns> //''' <remarks></remarks> public String getTypSubA(String TopCdeS, String TypTidStr) { IZCls.WebFormBase WebFormBase = new IZCls.WebFormBase(); IZCls.DataAccess DataAccess = new IZCls.DataAccess(); TypTidStr = DataAccess.ClsSqlChr(TypTidStr); String TypTidSql = WebFormBase.DotToSqlStr(TypTidStr); IzDataSource IzDataSource = new IzDataSource(); IzDataSource.SelectString = "select tbTypSub from tbTyp where left(tbTypCde,3)=@TopCdeS and tbTypTid in (" + TypTidSql + ")"; IzDataSource.ParametersClear(); IzDataSource.ParametersAdd("TopCdeS", TopCdeS); System.Data.DataTable tb = IzDataSource.SelectDataTable(); String StrTmp = ""; for (int i = 0; i < tb.Rows.Count; i++) { if (StrTmp == "") { StrTmp = tb.Rows[i]["tbTypSub"].ToString(); } else { StrTmp += "," + tb.Rows[i]["tbTypSub"].ToString(); } } tb.Dispose(); IzDataSource.Dispose(); return(StrTmp); }
public static void setPlanBerorFastighetCache() { //string conStr = ConfigurationManager.AppSettings["OracleOleDBConString"].ToString(); string sql = string.Empty; sql = "SELECT TO_CHAR(plan_id) AS nyckel, fastighet_id AS nyckel_fastighet, UPPER(fastighet) AS fastighet " + "FROM gis_v_planberorfastighet"; DataTable dtPlanBerorFastighet = new DataTable(); //OleDbConnection con = new OleDbConnection(conStr); OleDbConnection con = UtilityDatabase.GetOleDbConncection(); OleDbCommand com = new OleDbCommand(sql, con); OleDbDataReader dr; com.Connection.Open(); dr = com.ExecuteReader(); dtPlanBerorFastighet.Load(dr); dr.Close(); dr.Dispose(); DateTime cacheExpiration = setCacheExpiration(); // Skapa cach av alla planer Cache cache = HttpRuntime.Cache; cache.Insert("PlanBerorFastighet", dtPlanBerorFastighet, null, cacheExpiration, Cache.NoSlidingExpiration); dtPlanBerorFastighet.Dispose(); con.Close(); con.Dispose(); }
public async Task<object> SelectWithDetail(oCurriculum_academic curriacadata) { DBConnector d = new DBConnector(); if (!d.SQLConnect()) return WebApiApplication.CONNECTDBERRSTRING; List<Questionare_set_detail> result = new List<Questionare_set_detail>(); curri_id = curriacadata.curri_id; aca_year = curriacadata.aca_year; d.iCommand.CommandText = getSelectByCurriculumAcademicCommand(); try { System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); if (res.HasRows) { DataTable data = new DataTable(); data.Load(res); foreach (DataRow item in data.Rows) { int qid = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.QUESTIONARE_SET_ID].Ordinal]); if (result.FirstOrDefault(t => t.questionare_set_id == qid) == null) { result.Add(new Questionare_set_detail { aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]), name = item.ItemArray[data.Columns[FieldName.NAME].Ordinal].ToString(), personnel_id = item.ItemArray[data.Columns[FieldName.PERSONNEL_ID].Ordinal].ToString() != "" ? Convert.ToInt32(item.ItemArray[data.Columns[FieldName.PERSONNEL_ID].Ordinal]) : 0, questionare_set_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.QUESTIONARE_SET_ID].Ordinal]), date = Convert.ToDateTime(item.ItemArray[data.Columns[FieldName.DATE].Ordinal].ToString(), System.Globalization.CultureInfo.CurrentCulture).GetDateTimeFormats()[3], curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(), t_name = item.ItemArray[data.Columns[User_list.FieldName.T_PRENAME].Ordinal].ToString() + item.ItemArray[data.Columns[Teacher.FieldName.T_NAME].Ordinal].ToString() }); } result.First(t => t.questionare_set_id == qid).target.Add(new User_type { user_type_id = Convert.ToInt32(item.ItemArray[data.Columns[Questionare_privilege.FieldName.PRIVILEGE_TYPE_ID].Ordinal]), user_type = item.ItemArray[data.Columns[User_type.FieldName.USER_TYPE_NAME].Ordinal].ToString() }); } data.Dispose(); } else { //Reserved for return error string } res.Close(); } catch (Exception ex) { //Handle error from sql execution return ex.Message; } finally { //Whether it success or not it must close connection in order to end block d.SQLDisconnect(); } return result; }
/// <summary> /// 获取excel文件里面的所有的工作表名称 /// </summary> /// <param name="filePath"></param> /// <returns></returns> static List <string> GetExcelSheetNames(string filePath) { OleDbConnection connection = null; System.Data.DataTable dt = null; try {//获取文件扩展名 string strExtension = System.IO.Path.GetExtension(filePath); switch (strExtension) { case ".xls": connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";" + "Extended Properties=\"Excel 8.0;HDR=yes;IMEX=1;\""); break; case ".xlsx": connection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";" + "Extended Properties=\"Excel 12.0;HDR=yes;IMEX=1;\""); //此连接可以操作.xls与.xlsx文件 (支持Excel2003 和 Excel2007 的连接字符串) //"HDR=yes;"是说Excel文件的第一行是列名而不是数,"HDR=No;"正好与前面的相反。"IMEX=1 "如果列中的数据类型不一致,使用"IMEX=1"可必免数据类型冲突。 break; default: connection = null; break; } connection.Open(); dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (dt == null) { return(new List <string>()); } String[] excelSheets = new String[dt.Rows.Count]; int i = 0; foreach (DataRow row in dt.Rows) { excelSheets[i] = row["TABLE_NAME"].ToString().Split('$')[0]; i++; } return(excelSheets.Distinct().ToList()); } catch (Exception ex) { Console.WriteLine(ex); return(new List <string>()); } finally { if (connection != null) { connection.Close(); connection.Dispose(); } if (dt != null) { dt.Dispose(); } } }
public string getAccountNo(string empID) { dTable = new DataTable(); dTable = getData(empID); string getName = dTable.Rows[0]["BankAccNo"].ToString(); dTable.Dispose(); return getName; }
public string getBloodGroup(string empID) { dTable = new DataTable(); dTable = getData(empID); string getName = dTable.Rows[0]["BloodGroupName"].ToString(); dTable.Dispose(); return getName; }
}//fim método #endregion #region Pega o nome das Folhas do Arquivo Excel, para aqueles casos que não localiza o nome das páginas internas do Excel // Utilize o namespace System.Data.OleDb; public String[] PegarNomesFolhasExcel(string caminhoExcel) { OleDbConnection objConn = null; System.Data.DataTable dt = null; try { // Configura a Connection String String connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=No;IMEX=1\";", caminhoExcel); // Cria o objeto de conexão usando a connection string objConn = new OleDbConnection(connString); // Abre a conexão com o banco de dados objConn.Open(); dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (dt == null) { return(null); } String[] excelSheets = new String[dt.Rows.Count]; int i = 0; // Adiciona os nomes na array foreach (DataRow row in dt.Rows) { excelSheets[i] = row["TABLE_NAME"].ToString(); i++; } // Loop através de todas as folhas se você quiser também.. for (int j = 0; j < excelSheets.Length; j++) { // Consultar cada folha de excel } return(excelSheets); } catch (Exception ex) { return(null); } finally { if (objConn != null) { objConn.Close(); objConn.Dispose(); } if (dt != null) { dt.Dispose(); } } }
//private void CreateActivationCode(string tableName, string filePath) //{ // XmlDataDocument xmlDoc = new XmlDataDocument(); // SqlCommand cmd = new SqlCommand(); // try // { // using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read)) // { // if (fs != null) // { // xmlDoc.Load(fs); // XmlNodeList NodeList = xmlDoc.GetElementsByTagName("Item"); // for (int i = 0; i < NodeList.Count; i++) // { // //string ID = NodeList[i]; // cmd = SetParameters(tableName, NodeList[i]); // ExecuteInsertSQL(cmd); // if (!string.IsNullOrEmpty(this.Error.Text.ToString())) // { // continue; // } // } // if (string.IsNullOrEmpty(this.Error.Text.ToString())) // { // MessageBox.Show("upload successfully..", "Upload Data", MessageBoxButton.OK); // } // } // } // } // catch (Exception ex) // { // this.Error.Text = ex.Message; // } // finally // { // cmd.Dispose(); // } //} private string CreateXml(string tableName, string path) { System.Data.DataTable dt = null; string sheetName = Constants.Activation; dt = InitializedtSalesEngineerDataTable(); if (dt != null) { //'Item' is tagName in xml dt.TableName = "Item"; } InitializeWorkbook(path); ISheet sheet = xssfworkbook.GetSheet(sheetName); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); if (dt == null) { return(null); } //start at line 2 rows.MoveNext(); while (rows.MoveNext()) { IRow row = (XSSFRow)rows.Current; DataRow dr = dt.NewRow(); for (int i = 0; i < row.LastCellNum; i++) { ICell cell = row.GetCell(i); if (cell == null) { dr[i] = null; } else { dr[i] = cell.ToString(); } } if (dr[0].ToString() != "") { dr[0] = dr[0].ToString().Trim().ToLower(); dr[3] = str2HexStr(dr[0].ToString().Trim().ToLower() + " " + dr[1].ToString().Trim() + " " + dr[2].ToString().Trim()); dt.Rows.Add(dr); } } string xmlPath = @"E:\ActivationCodeCreator\" + tableName + DateTime.Now.ToString("yyyyMMdd") + ".xml"; //create xml file to local dt.WriteXml(xmlPath); dt.Dispose(); return(xmlPath); }
public List<IncorporacionConsultaBE> obtenerPorId(int Id) { List<IncorporacionConsultaBE> listado = new List<IncorporacionConsultaBE>(); DataTable dt = new DataTable("ListadoIncorporacionesConsultoras"); SqlDataAdapter da = new SqlDataAdapter(); SqlConnection cn = new SqlConnection(connection.getConnectionString()); try { cn.Open(); da = new SqlDataAdapter("usp_bel_incorporacion_obtener_porID", cn); da.SelectCommand.CommandType = CommandType.StoredProcedure; da.SelectCommand.Parameters.Add("@incorporacionID", SqlDbType.Char, 2).Value = Id; da.Fill(dt); if (dt.Rows.Count > 0) { IncorporacionConsultaBE ic; for (int j = 0; j < dt.Rows.Count; j++) { ic = new IncorporacionConsultaBE(); ic.incorporacionID = Convert.ToInt32(dt.Rows[j]["incorporacionID"]); ic.consultoraID = (DBNull.Value.Equals(dt.Rows[j]["consultoraID"])) ? 0 : Convert.ToInt32(dt.Rows[j]["consultoraID"]); ic.regionCodigo = Convert.ToString(dt.Rows[j]["region"]).Trim(); ic.zonaCodigo = Convert.ToString(dt.Rows[j]["zona"]).Trim(); //ic.fechaRegistro = Convert.ToString(dt.Rows[j]["fechaIncorporacion"]).Trim(); ic.fechaRegistro = Convert.ToString(dt.Rows[j]["fechaIncorporacion"]); //DateFormatter.getShortTime(Convert.ToDateTime(dt.Rows[j]["fechaIncorporacion"])); ic.campanhaInscripcion = (DBNull.Value.Equals(dt.Rows[j]["campanhaInscripcion"])) ? "" : Convert.ToString(dt.Rows[j]["campanhaInscripcion"]).Trim(); ic.numeroDocumento = Convert.ToString(dt.Rows[j]["numeroDocumento"]).Trim(); ic.apellidoPaterno = Convert.ToString(dt.Rows[j]["apellidoPaterno"]).Trim(); ic.apellidoMaterno = Convert.ToString(dt.Rows[j]["apellidoMaterno"]).Trim(); ic.nombres = Convert.ToString(dt.Rows[j]["nombres"]).Trim(); ic.consultoraCodigo = (DBNull.Value.Equals(dt.Rows[j]["consultoraCodigo"])) ? "" : Convert.ToString(dt.Rows[j]["consultoraCodigo"]).Trim(); ic.estadoVerificado = Convert.ToBoolean(dt.Rows[j]["estadoVerificado"]); ic.estadoActivo = Convert.ToBoolean(dt.Rows[j]["estadoActivo"]); ic.tipo = Convert.ToChar(dt.Rows[j]["tipo"]); listado.Add(ic); } } } catch (Exception ex) { listado = null; } finally { da.Dispose(); dt.Dispose(); cn.Close(); cn.Dispose(); } return listado; }
public static bool ReadExcel(ref System.Data.DataTable table, String filePath, String fileType) { String connStr = String.Empty; System.Data.DataTable tables = new System.Data.DataTable(); if (fileType.Equals("xls")) { connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\""; } else { connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\""; } try { if (conn == null) { //类似于获取数据库连接 conn = new OleDbConnection(connStr); //打开连接 conn.Open(); //获取数据 tables = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] { }); //判断该文件中是否有sheet if (tables.Rows.Count == 0) { Console.WriteLine("文件中没有可用表"); MessageBox.Show("文件中没有可用表", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning); return(false); } } //将读取到的数据存入DataTable String firstTableName = tables.Rows[count]["TABLE_NAME"].ToString(); OleDbCommand cmd = new OleDbCommand("select * from [" + firstTableName + "]", conn); OleDbDataAdapter apt = new OleDbDataAdapter(cmd); table.Dispose(); apt.Fill(table); count++; if (count > table.Rows.Count) { conn.Close(); conn = null; count = 0; return(false); } return(true); } catch (Exception ex) { Console.WriteLine(ex.Message); return(false); } }
public void ClearData() { fields = null; if (tblSrc != null) { tblSrc.Dispose(); tblSrc = null; } }
private void GetExcelSheetNames(string excelFile, string connString) { OleDbConnection objConn = null; System.Data.DataTable dt = null; try { Dgrid.DataSource = null; cboSheet.Items.Clear(); cboSheet.Text = ""; objConn = new OleDbConnection(connString); objConn.Open(); dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (dt == null) { return; } String[] excelSheets = new String[dt.Rows.Count]; int i = 0; // Add the sheet name to the string array. foreach (DataRow row in dt.Rows) { cboSheet.Items.Add(row["TABLE_NAME"].ToString()); i++; } // Loop through all of the sheets if you want too... for (int j = 0; j < excelSheets.Length; j++) { // Query each excel sheet. } cboSheet.SelectedIndex = 0; return; } catch (Exception ex) { MessageBox.Show(ex.Message, MyModules.strApptitle, MessageBoxButtons.OK, MessageBoxIcon.Information); } finally { // Clean up. if (objConn != null) { objConn.Close(); objConn.Dispose(); } if (dt != null) { dt.Dispose(); } } }