public int GetExcelDistictBTNCOunt_Sales(string sFileName) { Int32 NORec; DataSet objDataset1 = new DataSet(); int count = 0; OleDbConnection objConn = new OleDbConnection(); try { string FileExt = System.IO.Path.GetExtension(sFileName); if (FileExt == ".xls") { //Excell connection string Xls_Con = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sFileName + ";Extended Properties=\"Excel 8.0;HDR=YES; IMEX=1;ImportMixedTypes=Text\""; objConn.ConnectionString = Xls_Con; //Dim objConn As New OleDbConnection(Xls_Con) objConn.Open(); DataTable ExcelSheets = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); string SpreadSheetName = "[" + ExcelSheets.Rows[0]["TABLE_NAME"].ToString() + "]"; OleDbDataAdapter objAdapter1 = new OleDbDataAdapter("SELECT distinct(Phoneno) FROM " + SpreadSheetName, objConn); objAdapter1.Fill(objDataset1, "XLData"); count = Convert.ToInt32(objDataset1.Tables[0].Rows.Count); } else if (FileExt == ".xlsx") { //Excell connection string Xls_Con = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sFileName + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\""; objConn.ConnectionString = Xls_Con; //Dim objConn As New OleDbConnection(Xls_Con) objConn.Open(); DataTable ExcelSheets = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); string SpreadSheetName = "[" + ExcelSheets.Rows[0]["TABLE_NAME"].ToString() + "]"; OleDbDataAdapter objAdapter1 = new OleDbDataAdapter("SELECT distinct(Phoneno) FROM " + SpreadSheetName, objConn); objAdapter1.Fill(objDataset1, "XLData"); count = Convert.ToInt32(objDataset1.Tables[0].Rows.Count); } objConn.Close(); } catch (Exception ex) { throw ex; //Redirecting to error message page // Redirect(ConstantClass.StrErrorPageURL); } return count; }
public static Dictionary<string, TableInfo> getSchema(OleDbConnection connection) { Dictionary<string, TableInfo> infos_dict = new Dictionary<string, TableInfo>(); DataTable dt; dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); foreach (DataRow row in dt.Rows) { if (row["TABLE_TYPE"] as string == "TABLE") { TableInfo info = new TableInfo { TableName = row["TABLE_NAME"] as string }; infos_dict[info.TableName] = info; } } int i = 0; dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, null); foreach (DataRow row in dt.Rows) { string table_name = row["TABLE_NAME"] as string; if (infos_dict.ContainsKey(table_name)) { i++; infos_dict[table_name].PrimaryKey = row["COLUMN_NAME"] as string; } } if (i != infos_dict.Count) throw new System.ApplicationException(); dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, null); foreach (DataRow row in dt.Rows) { string fk_table_name = row["FK_TABLE_NAME"] as string; if (infos_dict.ContainsKey(fk_table_name)) { FKey key = new FKey { Name = row["FK_COLUMN_NAME"] as string, Table = row["PK_TABLE_NAME"] as string, Column = row["PK_COLUMN_NAME"] as string }; infos_dict[fk_table_name].FKeys.Add(key); } } infos_dict["Users"] = infos_dict["USERS"]; infos_dict.Remove("USERS"); return infos_dict; }
private static DataTable GetExcelTable(string excelFilename) { string fileType = System.IO.Path.GetExtension(excelFilename); if (string.IsNullOrEmpty(fileType)) { return(null); } string connectionString; if (fileType == ".xls") { connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};" + "Extended Properties='Excel 8.0;HDR=YES;IMEX=1'", excelFilename); } else { connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};" + "Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1;'", excelFilename); } DataSet ds = new DataSet(); string tableName; using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(connectionString)) { connection.Open(); DataTable table = connection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null); tableName = table.Rows[0]["Table_Name"].ToString(); tableName = tableName.Replace("'", ""); string strExcel = "select * from " + "[" + tableName + "A3:D]"; OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, connectionString); adapter.Fill(ds, tableName); connection.Close(); } return(ds.Tables[tableName]); }
/// <summary> /// 读取Excel返回一个数据表 /// </summary> /// <param name="con">OleDb数据连接</param> public DataTable ExcelToDataTable(OleDbConnection conn) { System.Data.DataTable dt = null; try { //打开连接 if (conn.State == ConnectionState.Broken || conn.State == ConnectionState.Closed) { conn.Open(); } System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); //获取Excel的第一个Sheet名称 string sheetName = schemaTable.Rows[0]["TABLE_NAME"].ToString().Trim(); //查询sheet中的数据 string strSql = "select * from [" + sheetName + "]"; OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn); DataSet ds = new DataSet(); da.Fill(ds); dt = ds.Tables[0]; return dt; } catch (Exception exc) { throw exc; //MessageBox.Show(exc.Message); } finally { conn.Close(); conn.Dispose(); } }
public DataSet GetDataSet(string filepath, string excelFileExtension) { try { System.Data.OleDb.OleDbConnection oledbcon = null; string strConn = string.Empty; switch (excelFileExtension.Trim()) { case "xls": oledbcon = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=\"Excel 8.0;HDR=No;IMEX=1;MaxScanRows=0;\""); strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filepath + ";" + "Extended Properties=Excel 8.0;"; break; case "xlsx": oledbcon = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties='Excel 12.0;HDR=No;IMEX=1'"); strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties=Excel 12.0;"; break; } //excel OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); string sheetName = dtSheetName.Rows[0]["TABLE_NAME"].ToString(); System.Data.OleDb.OleDbDataAdapter oledbAdaptor = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [" + sheetName + "]", oledbcon); //select DataSet ds = new DataSet(); oledbAdaptor.Fill(ds); oledbcon.Close(); return ds; } catch (Exception ex) { throw ex; } }
public static void Main() { string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=..\..\ExcelDocs\trainers.xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES/'"; OleDbConnection dbConnection = new OleDbConnection(connectionString); dbConnection.Open(); using (dbConnection) { var excelSchema = dbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); var sheetName = excelSchema.Rows[0]["TABLE_NAME"].ToString(); OleDbCommand command = new OleDbCommand("INSERT INTO [" + sheetName + "] VALUES (@name, @scores)", dbConnection); command.Parameters.AddWithValue("@name", "Pesho Goshov"); command.Parameters.AddWithValue("@scores", "11"); try { for (var i = 0; i < 5; i++) { var queryResult = command.ExecuteNonQuery(); Console.WriteLine("({0} row(s) affected)", queryResult); } } catch (OleDbException exception) { Console.WriteLine("SQL Error occured: " + exception); } } }
public static DataSet ReadFile(string filepath) { var result = new DataSet(); var connectionstring = GetConnectionString((filepath.Contains("xlsx") ? ExcelFormat.Excel2007 : ExcelFormat.Excel2003), filepath); using (var connection = new OleDbConnection(connectionstring)) { try { connection.Open(); using (var datatable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null)) { if (datatable.Rows.Count == 0) throw new Exception("Excel file doesn't contain sheet"); var sheetname = datatable.Rows[0]["TABLE_NAME"].ToString(); /// Only read First Sheet using (var adapter = new OleDbDataAdapter(string.Format("select * from [{0}]", sheetname), connection)) { adapter.Fill(result); } } } catch (Exception err) { throw err; } finally { connection.Close(); } } return result; }
public static List<string> GetWorksheetList(string filePath) { List<string> result = new List<string>(); if (FileHelper.CheckFileExists(filePath)) { DataTable temp = null; using (OleDbConnection objOleDbConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" + filePath)) { objOleDbConnection.Open(); temp = objOleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); objOleDbConnection.Close(); } for (int i = 0; i < temp.Rows.Count; i++) { string name = temp.Rows[i][2].ToString().Trim().TrimEnd('$'); if (!result.Contains(name)) { result.Add(name); } } } return result; }
static void Main() { string excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=../../score_db.xlsx;Extended Properties='Excel 12.0 xml;HDR=Yes';"; var excelConnection = new OleDbConnection(excelConnectionString); excelConnection.Open(); DataTable excelSchema = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); string sheetName = excelSchema.Rows[0]["TABLE_NAME"].ToString(); Console.Write("Username: "******"Score: "); int score = int.Parse(Console.ReadLine()); OleDbCommand excelCommand = new OleDbCommand(@"INSERT INTO [" + sheetName + @"] VALUES (@name, @score)", excelConnection); excelCommand.Parameters.AddWithValue("@name", userName); excelCommand.Parameters.AddWithValue("@age", score); using (excelConnection) { Console.WriteLine("\nINSERTING INTO EXCEL FILE DATABASE"); Console.WriteLine("-----------------------------------\n"); var queryResult = excelCommand.ExecuteNonQuery(); Console.WriteLine("({0} row(s) affected)", queryResult); } }
public DataSet GetExcelDistictBTNCOunt_Sales(string sFileName) { Int32 NORec; DataSet objDataset1 = new DataSet(); int count = 0; OleDbConnection objConn = new OleDbConnection(); try { //Excell connection string Xls_Con = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sFileName + ";Extended Properties=\"Excel 8.0;HDR=YES; IMEX=1;ImportMixedTypes=Text\""; objConn.ConnectionString = Xls_Con; //Dim objConn As New OleDbConnection(Xls_Con) objConn.Open(); DataTable ExcelSheets = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); string SpreadSheetName = "[" + ExcelSheets.Rows[0]["TABLE_NAME"].ToString() + "]"; OleDbDataAdapter objAdapter1 = new OleDbDataAdapter("SELECT distinct([CPhone]),SaleDate FROM " + SpreadSheetName + "", objConn); objAdapter1.Fill(objDataset1, "XLData"); //count = Convert.ToInt32(objDataset1.Tables[0].Rows.Count); objConn.Close(); } catch (Exception ex) { } return objDataset1; }
public static IEnumerable<DataRow> GetRowsFromDataSheets(OleDbConnection connection) { if (connection == null) { throw new ArgumentNullException("Must provide a valid OleDbConnection object.", "connection"); } if (connection.State != ConnectionState.Open) { connection.Open(); } DataTable schema = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); var allDataRows = new List<DataRow>(); foreach (DataRow sheet in schema.Rows) { string sheetName = sheet.Field<string>("TABLE_NAME"); DataTable sheetData = new DataTable(); OleDbDataAdapter sheetAdapter = new OleDbDataAdapter(String.Format("select * from [{0}]", sheetName), connection); sheetAdapter.Fill(sheetData); var sheetDataRows = sheetData.AsEnumerable(); allDataRows.AddRange(sheetDataRows); } connection.Close(); return allDataRows; }
static void Main() { var connectionStringFor2007OrNewer = "Provider = Microsoft.ACE.OLEDB.12.0; Extended Properties = Excel 12.0 XML; Data Source = ../../../scores.xlsx;"; //var connectionStringForOlder = "Provider = Microsoft.Jet.OLEDB.4.0; Extended Properties = Excel 8.0; Data Source = ../../../scores.xlsx;"; using (var dbCon = new OleDbConnection(connectionStringFor2007OrNewer)) { dbCon.Open(); var docName = dbCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString(); var cmd = new OleDbCommand("SELECT * FROM [" + docName + "]", dbCon); using (var oleDbAdapter = new OleDbDataAdapter(cmd)) { var dataSet = new DataSet(); oleDbAdapter.Fill(dataSet); using (var reader = dataSet.CreateDataReader()) { while (reader.Read()) { var name = reader["Name"]; var score = reader["Score"]; Console.WriteLine("{0}: {1}", name, score); } } } } }
public List<Destination> SelectExcelFilesFromZip(string path) { var destinations = new List<Destination>(); using (ZipArchive archive = ZipFile.Open(path, ZipArchiveMode.Update)) { foreach (ZipArchiveEntry entry in archive.Entries) { if (entry.FullName.EndsWith(".xlsx")) { entry.ExtractToFile(Path.Combine(extractPath, entry.Name)); string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path.Combine(extractPath, entry.Name) + ";Extended Properties='Excel 12.0 xml;HDR=Yes';"; OleDbConnection connection = new OleDbConnection(connectionString); using (connection) { connection.Open(); var excelSchema = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); var sheetName = excelSchema.Rows[0]["TABLE_NAME"].ToString(); destinations = this.ReadExcelData(connection, sheetName); } } } return destinations; } }
public void ReadExcelSheet(string fileName, string sheetName, Action<DataTableReader> actionForEachRow) { var connectionString = string.Format(ExcelSettings.Default.ExcelConnectionString, fileName); using (var excelConnection = new OleDbConnection(connectionString)) { excelConnection.Open(); if (sheetName == null) { var excelSchema = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (excelSchema != null) { sheetName = excelSchema.Rows[0]["TABLE_NAME"].ToString(); } } var excelDbCommand = new OleDbCommand(@"SELECT * FROM [" + sheetName + "]", excelConnection); using (var oleDbDataAdapter = new OleDbDataAdapter(excelDbCommand)) { var dataSet = new DataSet(); oleDbDataAdapter.Fill(dataSet); using (var reader = dataSet.CreateDataReader()) { while (reader.Read()) { actionForEachRow(reader); } } } } }
public static void WriteInExcel(string testerName, string placeName, string placeArea, float slope, string date, string testResult) { OleDbConnection excelConnection = new OleDbConnection(OutputFileConnectionString); using (excelConnection) { excelConnection.Open(); DataTable tableSchema = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); string sheetName = tableSchema.Rows[0]["TABLE_NAME"].ToString(); OleDbCommand excelDbCommand = new OleDbCommand( "INSERT INTO [" + sheetName + "] VALUES (@TesterName, @PlaceName, @PlaceArea, @Slope, @Date, @TestResult)", excelConnection); excelDbCommand.Parameters.AddWithValue("@TesterName", testerName); excelDbCommand.Parameters.AddWithValue("@PlaceName", placeName); excelDbCommand.Parameters.AddWithValue("@PlaceArea", placeArea); excelDbCommand.Parameters.AddWithValue("@Slope", slope); excelDbCommand.Parameters.AddWithValue("@Date", date); excelDbCommand.Parameters.AddWithValue("@testResult", testResult); excelDbCommand.ExecuteNonQuery(); Console.WriteLine("Added new row in Excel output file!"); } }
/// <summary> /// 读取Excel文件的所有Sheet /// </summary> /// <param name="filepath">文件路径</param> /// <param name="filename">文件名称</param> /// <returns>DataTable</returns> public static DataTable ReadExcelSheets(string fileName) { DataTable dt = new DataTable(); System.Data.DataSet itemDS = new DataSet(); if (fileName.Trim().ToUpper().EndsWith("XLS") || fileName.Trim().ToUpper().EndsWith("XLSX")) { string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;HdR=NO;\""; System.Data.OleDb.OleDbConnection conn = null; try { conn = new System.Data.OleDb.OleDbConnection(); conn.ConnectionString = connStr; conn.Open(); dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); } catch (Exception ex) { } finally { //释放 conn.Close(); } //创建连接 } return(dt); }
/// <summary> /// 解析Excel到DataTable /// </summary> /// <param name="filePath"></param> /// <returns></returns> public static DataTable GetDataTabelExcelContent(string filePath) { //excel2007,兼容2003 string strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1';"; System.Data.OleDb.OleDbConnection myConn = new System.Data.OleDb.OleDbConnection(strCon); myConn.Open(); //获取excel第一标签名 DataTable dt = new DataTable(); DataTable schemaTable = myConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null); string tableName = schemaTable.Rows[0][2].ToString().Trim(); //标签名 string strCom = "SELECT * FROM [" + tableName + "]"; //查询语句 OleDbCommand ocmd = null; try { ocmd = new OleDbCommand(strCom, myConn); OleDbDataAdapter oda = new OleDbDataAdapter(ocmd); oda.Fill(dt); } catch (OleDbException oex) { dt = null; throw oex; } finally { myConn.Close(); ocmd.Dispose(); } return(dt); }
/// <summary> /// 取出sheet /// </summary> /// <param name="filePath"></param> /// <returns></returns> public string getFirstSheetName(string filePath) { string ret = ""; string strConn = sConnstring; //"Extended Properties=Excel 8.0;"; strConn = string.Format(strConn, filePath); System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn); conn.Open(); using (DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null)) { ////取得工作表數量,法一 //foreach (DataRow dr in dt.Rows) //{ // Console.WriteLine((String)dr["TABLE_NAME"]); //} //取得工作表數量,法二 int TableCount = dt.Rows.Count; for (int i = 0; i < TableCount; i++) { ret = dt.Rows[i][2].ToString().Trim().TrimEnd('$'); conn.Close(); return(ret); } } conn.Close(); return(ret); }
/// <summary> /// 获取Excel文件数据表列表 /// </summary> public static ArrayList GetExcelTables(string ExcelFileName) { DataTable dt = new DataTable(); ArrayList TablesList = new ArrayList(); if (File.Exists(ExcelFileName)) { using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" + ExcelFileName)) { try { conn.Open(); dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); } catch (Exception exp) { throw exp; } //获取数据表个数 int tablecount = dt.Rows.Count; for (int i = 0; i < tablecount; i++) { string tablename = dt.Rows[i][2].ToString().Trim().TrimEnd('$'); if (TablesList.IndexOf(tablename) < 0) { TablesList.Add(tablename); } } } } return TablesList; }
/// <summary> /// 连接ACCESS数据库 /// </summary> /// <param name="filePath"></param> /// <returns>数据表表名</returns> public string[] ACSconection(string filePath) { string[] strTable = null; try { acsCon = new OleDbConnection(@"Provider=Microsoft.Jet.OleDb.4.0; Data Source= " + filePath); acsCon.Open(); DataTable shemaTable = acsCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); int n = shemaTable.Rows.Count; strTable = new string[n]; int m = shemaTable.Columns.IndexOf("TABLE_NAME"); for (int i = 0; i < n; i++) { DataRow m_DataRow = shemaTable.Rows[i]; strTable[i] = m_DataRow.ItemArray.GetValue(m).ToString(); } return strTable; } catch { return strTable; } }
public DataView ExceltoDataView(string strFilePath) { DataView dv; try { OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + strFilePath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'"); conn.Open(); object[] CSs0s0001 = new object[4]; CSs0s0001[3] = "TABLE"; DataTable tblSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, CSs0s0001); string tableName = Convert.ToString(tblSchema.Rows[0]["TABLE_NAME"]); if (tblSchema.Rows.Count > 1) { tableName = "sheet1$"; } string sql_F = "SELECT * FROM [{0}]"; OleDbDataAdapter adp = new OleDbDataAdapter(string.Format(sql_F, tableName), conn); DataSet ds = new DataSet(); adp.Fill(ds, "Excel"); dv = ds.Tables[0].DefaultView; conn.Close(); } catch (Exception) { Exception strEx = new Exception("請確認是否使用模板上傳(上傳的Excel中第一個工作表名稱是否為Sheet1)"); throw strEx; } return dv; }
public void loads(string SlnoAbbreviation) { string path = System.IO.Path.GetFullPath(@SlnoAbbreviation); oledbConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';"); oledbConn.Open(); OleDbCommand cmd = new OleDbCommand(); OleDbDataAdapter oleda = new OleDbDataAdapter(); DataSet ds = new DataSet(); cmd.Connection = oledbConn; cmd.CommandType = CommandType.Text; dt = oledbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (dt == null) { } String[] excelSheets = new String[dt.Rows.Count]; int i = 0; foreach (DataRow row in dt.Rows) { excelSheets[i] = row["TABLE_NAME"].ToString(); i++; } for (int j = 0; j < excelSheets.Length; j++) { drfile.Items.Add(excelSheets[j]); } drfile.Items.Insert(0, new ListItem("~Select~", "0")); }
public static DataTable ExcelToTable(string excelFilename, bool isTitleOrDataOfFirstRow) { string HDR = string.Empty;//如果第一行是数据而不是标题的话, 应该写: "HDR=No;" if (isTitleOrDataOfFirstRow) { HDR = "YES";//第一行是标题 } else { HDR = "NO";//第一行是数据 } string connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Jet OLEDB:Engine Type=35;Extended Properties=Excel 8.0;HDR=\"{1}\";Persist Security Info=False", excelFilename, HDR); DataSet ds = new DataSet(); string tableName; using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(connectionString)) { connection.Open(); DataTable table = connection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null); tableName = table.Rows[0]["Table_Name"].ToString(); string strExcel = "select * from " + "[" + tableName + "]"; OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, connectionString); adapter.Fill(ds, tableName); connection.Close(); } return(ds.Tables[tableName]); }
static void Main() { string excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=../../score_db.xlsx;Extended Properties='Excel 12.0 xml;HDR=Yes';"; var excelConnection = new OleDbConnection(excelConnectionString); excelConnection.Open(); DataTable excelSchema = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); string sheetName = excelSchema.Rows[0]["TABLE_NAME"].ToString(); OleDbCommand excelCommand = new OleDbCommand("SELECT * FROM [" + sheetName + "]", excelConnection); using (excelConnection) { using (OleDbDataAdapter adapter = new OleDbDataAdapter(excelCommand)) { DataSet dataSet = new DataSet(); adapter.Fill(dataSet); using (DataTableReader reader = dataSet.CreateDataReader()) { while (reader.Read()) { var userName = reader["Name"]; var score = reader["Score"]; Console.WriteLine(userName + " -> " + score); } } } } }
private static async Task<DataSet> SetDataSet(string path) { var ds = new DataSet(); var connectString = ""; //connection strings for excel files from and rest code of this method based on //http://www.aspsnippets.com/Articles/Read-and-Import-Excel-File-into-DataSet-or-DataTable-using-C-and-VBNet-in-ASPNet.aspx if (Path.GetExtension(path).Equals(".xlsx")) connectString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;\""; else if (Path.GetExtension(path).Equals(".xls")) connectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0 Xml;HDR=YES;IMEX=1;\""; using (var oleDbConnection = new OleDbConnection(connectString)) { await oleDbConnection.OpenAsync(); var dt = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (dt == null) return null; var excelSheets = new String[dt.Rows.Count]; var t = 0; foreach (DataRow row in dt.Rows) { excelSheets[t] = row["TABLE_NAME"].ToString(); t++; } var query = string.Format("Select * from [{0}]", excelSheets[0]); using (var dataAdapter = new OleDbDataAdapter(query, oleDbConnection)) { dataAdapter.Fill(ds); } oleDbConnection.Close(); } return ds; }
public List<List<object>> Parse(string filename) { const string fileType = ".xlsx"; var fileFullName = HttpContext.Current.Server.MapPath("~/Storage/" + filename + fileType); var connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileFullName + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text\""; var adapter = new OleDbDataAdapter(); var conn = new OleDbConnection(connectionString); conn.Open(); DataTable excelSheets = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); const int workSheetNumber = 0; string spreadSheetName = excelSheets.Rows[workSheetNumber]["TABLE_NAME"].ToString(); string strQuery = "select * from [" + spreadSheetName + "] "; adapter.SelectCommand = new OleDbCommand(strQuery, conn); var dsExcel = new DataSet(); adapter.Fill(dsExcel); conn.Close(); var result = new List<List<object>>(); var listDataRow = dsExcel.Tables[0].Rows.Cast<DataRow>().ToList(); foreach (var item in listDataRow) { var row = new List<object>(); for (var i = 0; i < item.ItemArray.Count(); i++) { var value = (item.ItemArray[i] == DBNull.Value) ? null : item.ItemArray[i]; row.Add(value); } result.Add(row); } return result; }
private static DataSet ImportExcelXLS(string FileName, bool hasHeaders) { string HDR = hasHeaders ? "Yes" : "No"; string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=1\""; DataSet output = new DataSet(); using (OleDbConnection conn = new OleDbConnection(strConn)) { conn.Open(); DataTable schemaTable = conn.GetOleDbSchemaTable( OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); foreach (DataRow schemaRow in schemaTable.Rows) { string sheet = schemaRow["TABLE_NAME"].ToString(); OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheet + "]", conn); cmd.CommandType = CommandType.Text; DataTable outputTable = new DataTable(sheet); output.Tables.Add(outputTable); new OleDbDataAdapter(cmd).Fill(outputTable); } } return output; }
/// <summary> /// 从EXCEL中获取数据(放入dataset中) /// </summary> /// <param name="postedFile"></param> /// <param name="context"></param> /// <param name="tableName"></param> /// <returns></returns> public static DataSet GetDataFromUploadFile(this HttpPostedFile postedFile, HttpContext context, string tableName) { string directory = context.Server.MapPath(POSTED_FILE_ROOT_DIRECTORY); if (!Directory.Exists(directory)) Directory.CreateDirectory(directory); string filename = postedFile.FileName; //将文件上传至服务器 postedFile.SaveAs(context.Server.MapPath(POSTED_FILE_ROOT_DIRECTORY) + filename); string conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + context.Server.MapPath(POSTED_FILE_ROOT_DIRECTORY) + filename + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'"; conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + context.Server.MapPath(POSTED_FILE_ROOT_DIRECTORY) + filename + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'"; //string sqlin = "SELECT * FROM [" + "ConstructPlanDevice" + "$]"; //OleDbCommand oleCommand = new OleDbCommand(sqlin, new OleDbConnection(conn)); //OleDbDataAdapter adapterIn = new OleDbDataAdapter(oleCommand); //DataSet dsIn = new DataSet(); //adapterIn.Fill(dsIn, tableName); OleDbConnection conn1 = new OleDbConnection(conn); conn1.Open(); string name = conn1.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0][2].ToString().Trim(); OleDbDataAdapter odda = new OleDbDataAdapter("select * from ["+name+"]", conn1); DataSet dsIn1 = new DataSet(); odda.Fill(dsIn1, tableName); conn1.Close(); return dsIn1; }
public static List<string> GetWorksheetColumnList(string filePath, string worksheetName) { List<string> result = new List<string>(); if (FileHelper.CheckFileExists(filePath)) { DataTable temp = null; using (OleDbConnection objOleDbConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" + filePath)) { objOleDbConnection.Open(); temp = objOleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, worksheetName, null }); objOleDbConnection.Close(); } for (int i = 0; i < temp.Rows.Count; i++) { result.Add(temp.Rows[i]["Column_Name"].ToString().Trim()); } } return result; }
/// <summary> /// Get Metadata information about the tables in a schema in the current database /// </summary> /// <param name="schema">Name of the schema in the database.</param> /// <returns></returns> public override SchemaTablesMetaData QuerySchemaDefinition(string schema) { SchemaTablesMetaData result = new SchemaTablesMetaData(); result.schemaName = schema; try { using (OleDbConnection connector = new OleDbConnection(connectionString)) { connector.Open(); using (DataTable dt = connector.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, schema, null, "TABLE" })) { foreach (DataRow row in dt.Rows) { TableMetaData table = new TableMetaData(); table.tableName = row[2].ToString(); result.AddTable(table); } } connector.Close(); } } catch (OleDbException ex) { Console.Out.WriteLine("Exception fetching schema metadata: {0}", ex.Message); } return result; }
//加载Excel public static DataSet LoadExcel(string filePath) { try { string strConn; strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'"; OleDbConnection OleConn = new OleDbConnection(strConn); OleConn.Open(); DataTable OleTables = OleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,new object[] {null, null, null, "TABLE"}); DataSet OleDsExcle = new DataSet(); foreach (DataRow dr in OleTables.Rows) { String TableName = (String)dr["TABLE_NAME"]; String sql = String.Format("SELECT * FROM [{0}]",TableName); OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn); OleDaExcel.Fill(OleDsExcle, TableName); } OleConn.Close(); return OleDsExcle; } catch { return null; } }
public List<SheetInfo> GetSheetlist() { if (excelpath == null||excelpath.Length<=0) return null; List<SheetInfo> list = new List<SheetInfo>(); string connStr = ""; string sql_F = "Select * FROM [{0}]"; string fileType = System.IO.Path.GetExtension(excelpath); if (fileType == ".xls") connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelpath + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\""; else connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + excelpath + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\""; OleDbConnection conn = new OleDbConnection(connStr); OleDbDataAdapter da = null; try { conn.Open(); string sheetname = ""; DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); da = new OleDbDataAdapter(); for (int i = 0; i < dtSheetName.Rows.Count;i++ ) { sheetname = (string)dtSheetName.Rows[i]["TABLE_NAME"]; if (sheetname.Contains("$") ) { SheetInfo info = new SheetInfo(); info.SheetName = sheetname.Replace("$", ""); da.SelectCommand = new OleDbCommand(String.Format(sql_F, sheetname), conn); DataSet dsItem = new DataSet(); da.Fill(dsItem, sheetname); int cnum = dsItem.Tables[0].Columns.Count; int rnum = dsItem.Tables[0].Rows.Count; info.StartRange = "A1"; char c = (char)('A' + cnum - 1); info.EndRange = c + Convert.ToString(rnum); list.Add(info); } } } catch (System.Exception ex) { MessageBox.Show(ex.ToString(), "错误消息"); return null; } finally { if (conn.State == ConnectionState.Open) { conn.Close(); if(da!=null) da.Dispose(); conn.Dispose(); } } return list; }
public static DataTable LoadExcelToDataTable(string filename) { DataTable dtResult = new DataTable(); //连接字符串 string sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties=Excel 12.0;"; //string sConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + @";Extended Properties=""Excel 12.0;HDR=YES;"""; //String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filename + ";" + "Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\""; OleDbConnection myConn = new OleDbConnection(sConnectionString); myConn.Open(); DataTable sheetNames = myConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); string sheetName = (sheetNames.Rows.Count > 0) ? sheetNames.Rows[0][2].ToString() : ""; if (String.IsNullOrEmpty(sheetName)) { return dtResult; } string strCom = " SELECT * FROM [" + sheetName + "]"; OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn); myCommand.Fill(dtResult); myConn.Close(); return dtResult; }
/// <summary> /// Read the specified Excel file and returns the content /// </summary> /// <param name="excelFile"></param> /// <returns></returns> public DataTable Read(string excelFile) { string connectionString = string.Empty; string fileExtension = Path.GetExtension(excelFile); if (fileExtension == ".xls") { connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + ";" + "Extended Properties='Excel 8.0;HDR=YES;'"; } else if (fileExtension == ".xlsx") { connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFile + ";" + "Extended Properties='Excel 12.0 Xml;HDR=YES;'"; } using (var conn = new OleDbConnection(connectionString)) { conn.Open(); using (OleDbCommand command = conn.CreateCommand()) { DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); string sheetName = dtSheet.Rows[0]["TABLE_NAME"].ToString(); command.CommandText = string.Format("SELECT * FROM [{0}]", sheetName); using (OleDbDataAdapter da = new OleDbDataAdapter(command)) { var dt = new DataTable(); da.Fill(dt); return dt; } } } }
/// <summary> /// 取出sheet /// </summary> /// <param name="filePath"></param> /// <returns></returns> public string getFirstSheetName(string filePath) { string ret = ""; string strConn = "Driver={Microsoft Text Driver (*.txt; *.csv)}; " + "Dbq= " + filePath + ";Extensions=csv,txt "; strConn = string.Format(strConn, filePath); System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn); conn.Open(); using (DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null)) { ////取得工作表數量,法一 //foreach (DataRow dr in dt.Rows) //{ // Console.WriteLine((String)dr["TABLE_NAME"]); //} //取得工作表數量,法二 int TableCount = dt.Rows.Count; for (int i = 0; i < TableCount; i++) { ret = dt.Rows[i][2].ToString().Trim().TrimEnd('$'); conn.Close(); return(ret); } } conn.Close(); return(ret); }
//读取数据 private string ReadFile(string filePath, ref DataTable dtSource) { string strCon = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;IMEX=1'", filePath); System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon); try { Conn.Open(); var Sheet1 = "Sheet1"; string sSheetName = ""; if (String.IsNullOrEmpty(Sheet1)) { List <string> lstSheetNames = new List <string>(); DataTable dtSheets = new DataTable(); dtSheets = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); for (int i = 0; i < dtSheets.Rows.Count; i++) { if (!dtSheets.Rows[i]["TABLE_NAME"].ToString().Contains("_")) { lstSheetNames.Add(dtSheets.Rows[i]["TABLE_NAME"].ToString()); } } sSheetName = lstSheetNames[0]; } else { sSheetName = Sheet1 + "$"; } DataSet ds = new DataSet(); string strCom = String.Format("SELECT * FROM [{0}]", sSheetName); System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, Conn); myCommand.Fill(ds, "ExcelTable"); dtSource = ds.Tables[0]; } catch (Exception e) { return("读取文件数据时出错:" + e.Message); } finally { Conn.Close(); try { FileInfo fi = new FileInfo(filePath); //删除临时文件 fi.Delete(); } catch {} } return(""); }
public DataTable ReadTableExcel(string strFileName) { string ConnectionString = ""; System.Data.OleDb.OleDbDataAdapter adapter; System.Data.DataSet ds = new System.Data.DataSet(); try { if (strFileName.Trim().EndsWith(".xlsx")) { ConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", strFileName); } else if (strFileName.Trim().EndsWith(".xls")) { try { ConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";", strFileName); } catch (Exception) { ConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", strFileName); } } System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(ConnectionString); conn.Open(); DataTable sheets = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); foreach (DataRow dr in sheets.Rows) { string sht = dr[2].ToString().Replace("'", ""); adapter = new OleDbDataAdapter("select * from [" + sht + "]", conn); adapter.Fill(ds); if (conn != null) { adapter.Dispose(); conn.Close(); } break; } } catch (Exception ex) { Console.WriteLine("Error: " + ex.Message); } return(ds.Tables[0]); }
public static string[] GetExcelWorksheets(string sFilePath) { System.Data.OleDb.OleDbConnection oleConnection = null; string[] sWorksheets = null; try { oleConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + sFilePath + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\""); oleConnection.Open(); //select just TABLE in the Object array of restrictions. //Remove TABLE and insert Null to see tables, views, and other objects. object[] objArrRestrict = new object[] { null, null, null, "TABLE" }; DataTable schemaTbl = oleConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, objArrRestrict); ArrayList aryTableNames = new ArrayList(); // Display the table name from each row in the schema foreach (DataRow row in schemaTbl.Rows) { string sTable = Convert.ToString(row["TABLE_NAME"]).Trim(); if (sTable.IndexOf("$_") < 0) { aryTableNames.Add(sTable); } } oleConnection.Close(); if (aryTableNames.Count <= 0) { throw new Exception("Excel spreadsheet contains no valid worksheets!"); } sWorksheets = (string[])aryTableNames.ToArray(typeof(string)); } catch (Exception ex) { string sErrorMessage = ex.Message; if (ex.InnerException != null) { sErrorMessage = sErrorMessage + " Details: " + ex.InnerException.Message; } throw new Exception(sErrorMessage); } finally { oleConnection.Close(); } return(sWorksheets); }
private object GetPrimaryKeyContrantName(SqlHelper sqlHelper, string dataBaseName, string tableName) { var con = new System.Data.OleDb.OleDbConnection(sqlHelper.DbConnectionString); con.Open(); var schemaTable = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Primary_Keys, new Object[] { null, null, tableName }); con.Close(); object pk = null; pk = schemaTable == null ? null : schemaTable.Rows[0].ItemArray[7].ToString(); return(pk ?? ""); }
/// <summary> /// 将线路站点数据导入导入到系统中 /// </summary> /// <param name="FileName"></param> public static void importTrainLineDataToSystem(String FileName) { String FileName1 = HttpContext.Current.Server.MapPath("~/Attachment/" + FileName); if (File.Exists(FileName1)) { //得到线路站点的数据 JTable tab1 = new JTable("TrainLine"); tab1.OrderBy = "LineName"; DataTable dt0 = tab1.SearchData(null, -1, "*").Tables[0]; string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName1 + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\""; System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon); Conn.Open(); DataTable dt = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); for (int i = 0; i < dt.Rows.Count; i++) { String tableName = dt.Rows[i][2].ToString().Trim(); foreach (DataRow dr0 in dt0.Rows) { String AStation = dr0["AStation"].ToString(); String BStation = dr0["BStation"].ToString(); int miles0 = int.Parse(dr0["Miles"].ToString()); DataTable dt1 = GetLineStationTable(Conn, tableName, dr0["LineName"].ToString(), out AStation, out BStation, out miles0); if (dt1.Rows.Count > 0) //表示有数据 { dr0["Astation"] = AStation; dr0["Bstation"] = BStation; dr0["miles"] = miles0; //更新线路中的数据 Line.SetMiddleStation(dr0["LineID"].ToString(), dt1); } } tab1.Update(dt0); //更新老站点中的相关数据 } Conn.Close(); //得到线路的数据 tab1.Close(); } }
/// <summary> /// Get all available table names from .xls data source /// </summary> public List <String> RetrieveAllTables() { // clear the old tables list firstly m_tables.Clear(); // get all table names from data source DataTable schemaTable = m_objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); for (int i = 0; i < schemaTable.Rows.Count; i++) { m_tables.Add(schemaTable.Rows[i].ItemArray[2].ToString().TrimEnd('$')); } return(m_tables); }
/// <summary> /// 读取Excel文件 /// </summary> /// <param name="filepath">文件路径</param> /// <returns>DataTable</returns> public static DataTable ReadExcel(string filename, bool isHdR) { string hdr = "N0"; if (isHdR) { hdr = "YEX"; } System.Data.DataSet itemDS = new DataSet(); if (filename.Trim().ToUpper().EndsWith("XLS") || filename.Trim().ToUpper().EndsWith("XLSX")) { string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties=\"Excel 12.0;HdR=" + hdr + ";\""; System.Data.OleDb.OleDbConnection conn = null; System.Data.OleDb.OleDbCommand oledbCommd = null; try { conn = new System.Data.OleDb.OleDbConnection(); conn.ConnectionString = connStr; conn.Open(); DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); //判断连接Excel sheet名 for (int i = 0; i < dt.Rows.Count; i++) { DataRow dr = dt.Rows[i]; string sqlText = "select * from [" + dr["TABLE_NAME"] + "]"; oledbCommd = new System.Data.OleDb.OleDbCommand(sqlText, conn); oledbCommd.CommandTimeout = 100000; //执行 System.Data.OleDb.OleDbDataAdapter oledbDA = new System.Data.OleDb.OleDbDataAdapter(oledbCommd); oledbDA.Fill(itemDS); } } catch { } finally { //释放 oledbCommd.Dispose(); conn.Close(); } //创建连接 } return(itemDS.Tables[0]); }
//加载Excel public static DataTable getExcel(string filePos) { string connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Jet OLEDB:Engine Type=35;Extended Properties=Excel 8.0;Persist Security Info=False", filePos); DataSet ds = new DataSet(); string tableName; using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(connectionString)) { connection.Open(); DataTable table = connection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null); tableName = table.Rows[0]["Table_Name"].ToString(); string strExcel = "select * from " + "[" + tableName + "]"; OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, connectionString); adapter.Fill(ds, tableName); connection.Close(); } return(ds.Tables[tableName]); }
public object GetPrimaryKey(SqlHelper sqlHelper, string dataBaseName, string tableName) { var con = new System.Data.OleDb.OleDbConnection(sqlHelper.DbConnectionString); con.Open(); var schemaTable = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Primary_Keys, new Object[] { null, null, tableName }); con.Close(); if (schemaTable == null || schemaTable.Rows.Count == 0) { return(""); } object pk = null; pk = schemaTable.Rows[0].ItemArray[3].ToString().FirstLetterToUpper(); return(pk); }
private void LoadExcelFile() { if (String.IsNullOrEmpty(FileName)) { throw new Exception("Select file"); } OleDbConnection connExcel = new OleDbConnection(); OleDbCommand cmdExcel = new OleDbCommand(); OleDbDataAdapter oda = new OleDbDataAdapter(); connExcel = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + FileName + ";Extended Properties=Excel 12.0;"); cmdExcel.Connection = connExcel; connExcel.Open(); System.Data.DataTable dt = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (dt == null) { throw new Exception("File read error"); } foreach (DataRow row in dt.Rows) { string t = row["TABLE_NAME"].ToString(); EI.Add(new ExcelInfo { ID = t, Sheet = CleanUpSheetName(t) }); } CleanUpSheetName(EI[0].ID); connExcel.Close(); GridControl1.BeginUpdate(); GridControl1.DataSource = null; GridView1.Columns.Clear(); GridControl1.DataSource = Ext.ToDataTable(EI); GridView1.Columns["ID"].Visible = false; GridView1.ClearSelection(); GridControl1.EndUpdate(); }
public void GetTable(string Apath, ComboBox ComBox) { string connstr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Apath + ";Persist Security Info=True"; System.Data.OleDb.OleDbConnection tem_OleConn = new System.Data.OleDb.OleDbConnection(connstr); tem_OleConn.Open(); DataTable tem_DataTable = tem_OleConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); tem_OleConn.Close(); ComBox.Items.Clear(); for (int i = 0; i < tem_DataTable.Rows.Count; i++) { ComBox.Items.Add(tem_DataTable.Rows[i][2]); } if (ComBox.Items.Count > 0) { ComBox.SelectedIndex = 0; } }
public static DataTable import(string arquivo) { string ext = string.Empty; string aspas = "******""; string Conexao = string.Empty; for (int i = arquivo.Length - 1; i < arquivo.Length; i--) { if (arquivo[i] != '.') { ext += arquivo[i]; } else { ext += "."; break; } } ext = Reverse(ext); if (ext == ".xls") { Conexao = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + arquivo + ";" + "Extended Properties=" + aspas + "Excel 8.0;HDR=YES" + aspas; } if (ext == ".xlsx") { Conexao = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + arquivo + ";" + "Extended Properties=" + aspas + "Excel 12.0;HDR=YES" + aspas; } System.Data.OleDb.OleDbConnection Cn = new System.Data.OleDb.OleDbConnection(); Cn.ConnectionString = Conexao; Cn.Open(); object[] Restricoes = { null, null, null, "TABLE" }; DataTable DTSchema = Cn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, Restricoes); if (DTSchema.Rows.Count > 0) { string Sheet = DTSchema.Rows[0]["TABLE_NAME"].ToString(); System.Data.OleDb.OleDbCommand Comando = new System.Data.OleDb.OleDbCommand("SELECT * FROM [" + Sheet + "]", Cn); DataTable Dados = new DataTable(); System.Data.OleDb.OleDbDataAdapter DA = new System.Data.OleDb.OleDbDataAdapter(Comando); DA.Fill(Dados); Cn.Close(); return(Dados); } return(null); }
public bool IsHasTable(string sTableName) { bool b = false; Open(); DataTable schemaTable = Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] { null, null, null, "TABLE" }); if (schemaTable != null) { for (int i = 0; i < schemaTable.Rows.Count; i++) { string sName = schemaTable.Rows[i]["TABLE_NAME"].ToString(); if (sName == sTableName) { b = true; } } } return(b); }
/// <summary> /// 读取XLS数据到数据库 /// </summary> /// <param name="filepath"></param> /// <returns></returns> public static DataSet xsldata(string filepath, String SheetName) { string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\""; System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon); Conn.Open(); DataTable dt = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); String tableName = dt.Rows[0][2].ToString().Trim(); string strCom = String.Format("SELECT * FROM [{0}]", tableName); System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, Conn); DataSet ds = new DataSet(); myCommand.Fill(ds); Conn.Close(); return(ds); }
public static DataTable ExcelToTable(string excelFilename, bool isTitleOrDataOfFirstRow) { if (!File.Exists(excelFilename)) { return(null); } string HDR = string.Empty;//如果第一行是数据而不是标题的话, 应该写: "HDR=No;" string connectionString = string.Empty; if (isTitleOrDataOfFirstRow) { HDR = "YES";//第一行是标题 } else { HDR = "NO";//第一行是数据 } if (Is2007Excel(excelFilename)) { connectionString = connstring07.Replace("{path}", excelFilename).Replace("{HDR}", HDR); } else { connectionString = connstring03.Replace("{path}", excelFilename).Replace("{HDR}", HDR); } DataSet ds = new DataSet(); string tableName; using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(connectionString)) { connection.Open(); DataTable table = connection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null); tableName = table.Rows[0]["Table_Name"].ToString(); string strExcel = "select * from " + "[" + tableName + "]"; OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, connectionString); adapter.Fill(ds, tableName); connection.Close(); } return(ds.Tables[tableName]); }
private DataSet GetData() { String filepath = Server.MapPath("/TrainWeb/Train/cjl.xls"); string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\""; System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon); Conn.Open(); DataTable dt = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); String tableName = dt.Rows[0][2].ToString().Trim(); tableName = "客运长交路机车牵引费单价表$"; string strCom = String.Format("SELECT * FROM [{0}]", tableName); System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, Conn); DataSet ds = new DataSet(); myCommand.Fill(ds); Conn.Close(); return(ds); }
public static string connectionString = "";//数据库连接字符串 #region OleDb读取Excel /// <summary> /// 将 Excel 文件转成 DataTable 后,再把 DataTable中的数据写入表Products /// </summary> /// <param name="serverMapPathExcelAndFileName"></param> /// <param name="excelFileRid"></param> /// <returns></returns> public static int WriteExcelToDataBase(string excelFileName) { int rowsCount = 0; OleDbConnection objConn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFileName + ";" + "Extended Properties=Excel 8.0;"); objConn.Open(); try { DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null); string sheetName = string.Empty; for (int j = 0; j < schemaTable.Rows.Count; j++) { sheetName = schemaTable.Rows[j][2].ToString().Trim();//获取 Excel 的表名,默认值是sheet1 DataTable excelDataTable = ExcelToDataTable(excelFileName, sheetName, true); if (excelDataTable.Columns.Count > 1) { SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction); sqlbulkcopy.DestinationTableName = "Products";//数据库中的表名 sqlbulkcopy.WriteToServer(excelDataTable); sqlbulkcopy.Close(); } } } catch (SqlException ex) { throw ex; } finally { objConn.Close(); objConn.Dispose(); } return(rowsCount); }
/// <summary> /// 获取Excel工作表名 /// </summary> /// <param name="excelFilename">Excel文件绝对路径</param> /// <returns></returns> public List <string> GetExcelTableName(string excelFilename) { List <string> lstName = new List <string>(); string connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Jet OLEDB:Engine Type=35;Extended Properties=Excel 8.0;Persist Security Info=False", excelFilename); DataSet ds = new DataSet(); using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(connectionString)) { connection.Open(); DataTable table = connection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null); connection.Close(); if (table != null && table.Rows.Count > 0) { foreach (DataRow dr in table.Rows) { if (dr["Table_Name"] != DBNull.Value) { lstName.Add(dr["Table_Name"].ToString()); } } } } return(lstName); }
public static IEnumerable <T> LoadFromFile <T>(string fullFileName, GetObjectFromReader <T> getObjectFromReaderFunc, string sheetNameFirst = "") { string conString = FormatXlsConnectionString(fullFileName); IEnumerable <T> result = null; using (var con = new System.Data.OleDb.OleDbConnection(conString)) { using (var cmd = new System.Data.OleDb.OleDbCommand()) { cmd.Connection = con; if (string.IsNullOrEmpty(sheetNameFirst)) { con.Open(); var dtExcelSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); sheetNameFirst = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString(); con.Close(); } cmd.CommandType = CommandType.TableDirect; cmd.CommandText = sheetNameFirst; con.Open(); using (var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { if (reader != null) { result = GetObjectListFromReader <T>(reader, getObjectFromReaderFunc); } } } } return(result); }
// Constructor. // The fields of this class is set up according to the type of the connection public QuoteUtil(DbConnection connection) { // default is double quotes. // currently only oledb provide quotes information, others will use default prefix = "\""; suffix = "\""; separator = "."; // connection must be open in order to initialize this class STrace.Assert(connection.State == ConnectionState.Open, "the connection is not open"); System.Data.OleDb.OleDbConnection oleDbConnection = connection as System.Data.OleDb.OleDbConnection; if (oleDbConnection != null) { // special case SQL Server string connectionString = connection.ConnectionString; string providerToken = string.Empty; string[] tokens = connectionString.Split(new char[] { ';' }); foreach (string token in tokens) { // navigate to "Provider = " if (token.Trim().StartsWith("Provider", StringComparison.OrdinalIgnoreCase)) { if (token.IndexOf("SQLOLEDB", StringComparison.OrdinalIgnoreCase) != -1 || token.IndexOf("SQLNCLI", StringComparison.OrdinalIgnoreCase) != -1) { prefix = "["; suffix = "]"; escapedPrefix = "\\["; escapedSuffix = "\\]"; escapedSeparator = "\\."; SpeicalWordID = SpecialWordID_HardBrackets; return; } } } DataTable literalTable = oleDbConnection.GetOleDbSchemaTable( OleDbSchemaGuid.DbInfoLiterals, null); if (literalTable != null) { DataRow[] rows = literalTable.Select("LiteralName = 'Quote_Prefix'"); if (rows.Length > 0) { prefix = rows[0]["LiteralValue"].ToString(); if (prefix == null) { prefix = string.Empty; } } else { prefix = string.Empty; } rows = literalTable.Select("LiteralName = 'Quote_Suffix'"); if (rows.Length > 0) { suffix = rows[0]["LiteralValue"].ToString(); if (suffix == null) { suffix = string.Empty; } } else { suffix = string.Empty; } rows = literalTable.Select("LiteralName = 'Schema_Separator'"); if (rows.Length > 0) { separator = rows[0]["LiteralValue"].ToString(); if (separator == null) { separator = string.Empty; } } else { separator = string.Empty; } } } // escape those symbols which are meaningful in regular expression, such as []. escapedPrefix = prefix; escapedSuffix = suffix; escapedSeparator = separator; if (prefix == "[") { escapedPrefix = "\\["; escapedSuffix = "\\]"; SpeicalWordID = SpecialWordID_HardBrackets; } else { SpeicalWordID = SpecialWordID_DoubleQuotes; } if (separator == ".") { escapedSeparator = "\\."; } }
private void button1_Click(object sender, EventArgs e) { checkedListBox1.Items.Clear(); OpenFileDialog dialog = new OpenFileDialog(); dialog.Filter = "Excel文件|*.xls|Excel文件|*.xlsx"; List <string> combobox_list = new List <string>(); if (dialog.ShowDialog() == DialogResult.OK) { richTextBox1.Text = dialog.FileName; filePath = richTextBox1.Text; } if (filePath != "") { string postfix = richTextBox1.Text.Substring(richTextBox1.Text.LastIndexOf(".")); if (postfix == ".xls") { strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + richTextBox1.Text + ";" + "Extended Properties=Excel 8.0;"; } else { strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'", richTextBox1.Text); } jilu(strConn); using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(strConn)) { jilu("47-"); try { connection.Open(); } catch (Exception ex) { jilu("54-" + ex.Message); throw; } //connection.Open(); jilu("48-"); DataTable table = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); jilu("49-"); string[] strTableNames = new string[table.Rows.Count]; string excel_useless = ""; for (int k = 0; k < table.Rows.Count; k++) { strTableNames[k] = table.Rows[k]["TABLE_NAME"].ToString(); if (strTableNames[k].ToString().Trim().Substring(strTableNames[k].Length - 1, 1) == "$") { excel_useless = strTableNames[k].Substring(0, strTableNames[k].Length - 1); if (excel_useless != "基础数据" && excel_useless != "修订记录表" && excel_useless != "填写说明表" && excel_useless != "教材目录" && excel_useless != "通用设置") { combobox_list.Add(strTableNames[k].ToString().Trim().Substring(0, strTableNames[k].Length - 1).ToString().Trim()); } combobox_list.Sort(); } else if (strTableNames[k].Substring(strTableNames[k].Length - 2, 2) == "$'") { excel_useless = strTableNames[k].Substring(1, strTableNames[k].Length - 3); if (excel_useless != "基础数据" && excel_useless != "修订记录表" && excel_useless != "填写说明表" && excel_useless != "教材目录" && excel_useless != "通用设置") { combobox_list.Add(strTableNames[k].Substring(1, strTableNames[k].Length - 3).ToString().Trim()); } } } this.checkedListBox1.Items.AddRange(combobox_list.ToArray()); } } }
/// <summary> /// 是否存在表或视图 /// </summary> /// <param name="type">"U"或"V"</param> /// <param name="name">表名或视图名</param> public static bool Exists(string type, string name, ref DbBase helper) { if (type == "U" && tableCache.Count > 0) { string key = "TableCache:" + helper.dalType + "." + helper.DataBase; if (tableCache.ContainsKey(key)) { return(tableCache[key].ContainsKey(name)); } } int result = 0; string exist = string.Empty; helper.IsAllowRecordSql = false; DalType dalType = helper.dalType; name = SqlFormat.Keyword(name, helper.dalType); switch (dalType) { case DalType.Access: try { System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(helper.Con.ConnectionString); con.Open(); result = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, SqlFormat.NotKeyword(name), "Table" }).Rows.Count; con.Close(); } catch (Exception err) { Log.WriteLogToTxt(err); } break; case DalType.MySql: if (type != "V" || (type == "V" && name.ToLower().StartsWith("v_"))) //视图必须v_开头 { exist = string.Format(ExistMySql, SqlFormat.NotKeyword(name), helper.DataBase); } break; case DalType.Oracle: exist = string.Format(ExistOracle, (type == "U" ? "TABLE" : "VIEW"), name); break; case DalType.MsSql: exist = string.Format(helper.Version.StartsWith("08") ? Exist2000 : Exist2005, name, type); break; case DalType.SQLite: exist = string.Format(ExistSqlite, (type == "U" ? "table" : "view"), SqlFormat.NotKeyword(name)); break; case DalType.Sybase: exist = string.Format(ExistSybase, SqlFormat.NotKeyword(name), type); break; case DalType.Txt: case DalType.Xml: string folder = helper.Con.DataSource + Path.GetFileNameWithoutExtension(name); FileInfo info = new FileInfo(folder + ".ts"); result = (info.Exists && info.Length > 10) ? 1 : 0; if (result == 0) { info = new FileInfo(folder + (dalType == DalType.Txt ? ".txt" : ".xml")); result = (info.Exists && info.Length > 10) ? 1 : 0; } break; } if (exist != string.Empty) { helper.IsAllowRecordSql = false; result = Convert.ToInt32(helper.ExeScalar(exist, false)); } return(result > 0); }
public Int32 exportexcel(string filename, string filePath, string tablename, long cmpcode) { string conStr = ""; string extension; extension = Path.GetExtension(filename); switch (extension) { case ".xls": //Excel 97-03 conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString; break; case ".xlsx": //Excel 07 conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString; break; } try { conStr = String.Format(conStr, filePath, "Yes"); System.Data.OleDb.OleDbConnection connExcel = new System.Data.OleDb.OleDbConnection(conStr); System.Data.OleDb.OleDbCommand cmdExcel = new System.Data.OleDb.OleDbCommand(); System.Data.OleDb.OleDbDataAdapter oda = new System.Data.OleDb.OleDbDataAdapter(); DataTable dt = new DataTable(); cmdExcel.Connection = connExcel; //Get the name of First Sheet connExcel.Open(); DataTable dtExcelSchema; dtExcelSchema = connExcel.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null); string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString(); connExcel.Close(); string myexceldataquery = "SELECT * From [" + SheetName + "]"; string ssqlconnectionstring = ConfigManager.GetNewSqlConnection.ConnectionString; OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery, connExcel); connExcel.Open(); cmdExcel.CommandText = "SELECT * From [" + SheetName + "]"; oda.SelectCommand = cmdExcel; oda.Fill(dt); dt.Columns.Add("CmpCode", typeof(System.Int64)); foreach (DataRow row in dt.Rows) { row["CmpCode"] = cmpcode; } // OleDbDataReader dr = oledbcmd.ExecuteReader(); SqlBulkCopy bulkcopy = new SqlBulkCopy(ssqlconnectionstring); bulkcopy.DestinationTableName = tablename; bulkcopy.WriteToServer(dt); //while (dt) //{ // bulkcopy.WriteToServer(dr); //} // dr.Close(); connExcel.Close(); return(1); } catch (Exception e) { return(2); } }
// [CustomAuthentication("ReportUploader", "Create,Edit,Delete")] public ActionResult Import(HttpPostedFileBase excelfile) { string Value = string.Empty; // Excelimport (excelfile); if (excelfile == null || excelfile.ContentLength == 0) { ViewBag.Error = "Upload the excel file !...."; return(View("Index")); } else { if (excelfile.FileName.EndsWith("xsl") || excelfile.FileName.EndsWith("xlsx")) { string path = ""; // string path = Server.MapPath("~/Content/" + excelfile.FileName + DateTime.Now.ToString("MM.dd.yyyy-hh.mm.ss")); try { //path = Server.MapPath("~/UploadSheet/" + excelfile.FileName + DateTime.Now.ToString("MM.dd.yyyy")); path = Server.MapPath("~/UploadSheet/"); if (System.IO.File.Exists(path)) { System.IO.File.Delete(path); } excelfile.SaveAs(path + Path.GetFileName(excelfile.FileName)); path = path + Path.GetFileName(excelfile.FileName); // excelfile.SaveAs(path); } catch (Exception ex) { } System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; " + "data source='" + path + "';" + "Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\" "); try { myConnection.Open(); DataTable mySheets = myConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); DataSet ds = new DataSet(); DataTable dt; for (int i = 0; i < mySheets.Rows.Count; i++) { dt = makeDataTableFromSheetName(path, mySheets.Rows[i]["TABLE_NAME"].ToString()); ds.Tables.Add(dt); } if (ds.Tables.Count > 0) { CreateReport(ds); return(View()); } //StatusLabel.Text += "<br />Report status: File processed!"; } catch (Exception ex) { //btnDownloadReport.Visible = false; //grdReport.DataSource = null; //grdReport.DataBind(); var x = "<br />Report status: The file could not be processed. The following error occured: " + ex.Message; } finally { myConnection.Close(); } } } return(View()); }
private static int WriteToExcelFile(string connExcel, OleDbConnection dbCon) { var excelSchema = dbCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); var sheetName = excelSchema.Rows[0]["TABLE_NAME"].ToString(); OleDbCommand command = new OleDbCommand("INSERT INTO [" + sheetName + "] VALUES(@name, @score)", dbCon); string name = "Evlogy Christov"; int score = 34; command.Parameters.AddWithValue("@name", name); command.Parameters.AddWithValue("@score", score); int queryResult = command.ExecuteNonQuery(); command.Parameters.Clear(); name = "Christian Zaklev"; score = 32; command.Parameters.AddWithValue("@name", name); command.Parameters.AddWithValue("@score", score); queryResult += command.ExecuteNonQuery(); // a ready-made way to "invent" new parameters: for (int i = 5; i < 10; i++) { command.Parameters.Clear(); command.Parameters.AddWithValue("@name", "Student" + i); command.Parameters.AddWithValue("@score", i); queryResult += command.ExecuteNonQuery(); } return queryResult; }