//public string ExcelFile {private get; set; } public static DataTable ReadData(string excelFile) { if (!System.IO.File.Exists(excelFile)) return null; OleDbConnection excelConnection = new OleDbConnection(); excelConnection.ConnectionString = string.Format("Provider=Microsoft.Jet.OleDb.4.0;Data Source='{0}';Extended Properties='Excel 8.0;HDR=YES'", excelFile); excelConnection.Open(); DataTable dtSchema = excelConnection.GetSchema("Tables"); if (dtSchema.Rows.Count == 0) return null; string strTableName = dtSchema.Rows[0]["Table_Name"] as string; string strSQL = string.Format("select * from [{0}]", strTableName); OleDbCommand cmdSelect = excelConnection.CreateCommand(); cmdSelect.CommandText = strSQL; OleDbDataAdapter dbAdapter = new OleDbDataAdapter(cmdSelect); DataTable dtResult=new DataTable(); dbAdapter.Fill(dtResult); dbAdapter.Dispose(); excelConnection.Close(); excelConnection.Dispose(); return dtResult; }
public String[] GetFirstnames() { List<string> items = new List<string>(); string strSQL = ""; OleDbDataReader objReader = null; OleDbConnection objConn = null; OleDbCommand objCommand = null; strSQL = "SELECT firstname from Employees ORDER BY firstname"; objConn = new OleDbConnection(GetconnectstringLocal()); objConn.Open(); objCommand = new OleDbCommand(strSQL, objConn); objReader = objCommand.ExecuteReader(); while (objReader.Read()) { items.Add(objReader["firstname"].ToString()); } objReader.Close(); objReader.Dispose(); objReader = null; objCommand.Dispose(); objCommand = null; objConn.Close(); objConn.Dispose(); objConn = null; return items.ToArray(); }
protected void btnlogout_Click(object sender, EventArgs e) { conlogout = i.GetOledbDbConnection(); cmdlogout = i.GetOledbDbCommand(); try { conlogout = DBConnection.GetConnection(); cmdlogout.Connection = conlogout; cmdlogout.CommandType = CommandType.StoredProcedure; cmdlogout.CommandText = "Logout"; cmdlogout.Parameters.AddWithValue("@USERNAME",s); int result = cmdlogout.ExecuteNonQuery(); if (result == 1) { // Response.Write("logout successfull"); Response.Redirect("Login.aspx"); Session.Clear(); } else Response.Write("logout unsuccessfull"); } catch (Exception ex) { Response.Write("logout unsuccessfull"); } finally { cmdlogout.Dispose(); conlogout.Dispose(); } }
public void AccessGuideJoinExcel(string Access, string AccTable, string Excel) { try { string tem_sql = ""; //定义字符串 string connstr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Access + ";Persist Security Info=True"; //记录连接Access的语句 System.Data.OleDb.OleDbConnection tem_conn = new System.Data.OleDb.OleDbConnection(connstr); //连接Access数据库 System.Data.OleDb.OleDbCommand tem_comm; //定义OleDbCommand类 tem_conn.Open(); //打开连接的Access数据库 tem_sql = "select Count(*) From " + AccTable; //设置SQL语句,获取记录个数 tem_comm = new System.Data.OleDb.OleDbCommand(tem_sql, tem_conn); //实例化OleDbCommand类 int RecordCount = (int)tem_comm.ExecuteScalar(); //执行SQL语句,并返回结果 //每个Sheet只能最多保存65536条记录。 tem_sql = @"select top 65535 * into [Excel 8.0;database=" + Excel + @".xls].[Sheet2] from 帐目"; //记录连接Excel的语句 tem_comm = new System.Data.OleDb.OleDbCommand(tem_sql, tem_conn); //实例化OleDbCommand类 tem_comm.ExecuteNonQuery(); //执行SQL语句,将数据表的内容导入到Excel中 tem_conn.Close(); //关闭连接 tem_conn.Dispose(); //释放资源 tem_conn = null; MessageBox.Show("导入完成"); } catch (Exception ex) { MessageBox.Show(ex.Message, "提示!"); } }
//导入excel代码 private void button1_Click(object sender, EventArgs e) { OpenFileDialog of = new OpenFileDialog(); if (of.ShowDialog() == DialogResult.OK) { string filepath = of.FileName; FileInfo fi = new FileInfo(filepath); string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'"; DataSet ds = new DataSet(); using (OleDbConnection con = new OleDbConnection(strCon)) { con.Open(); OleDbDataAdapter adapter = new OleDbDataAdapter("select * from [Sheet1$]", con); adapter.Fill(ds, "dtSheet1"); con.Close(); con.Dispose(); } for (int i = 0; i < ds.Tables[0].Rows.Count;i++ ) { string number = ds.Tables[0].Rows[i]["编号"].ToString(); string name = ds.Tables[0].Rows[i]["姓名"].ToString(); string attendance = ds.Tables[0].Rows[i]["出勤次数"].ToString(); string absence = ds.Tables[0].Rows[i]["缺勤次数"].ToString(); string sql = "insert into teacher(number,name,chu,que) values ('" + number + "','" + name + "','" + attendance + "','" + absence + "')"; int j=DBConnection.OperateData(sql); if (j==0) { MessageBox.Show("插入数据失败"); return; } } MessageBox.Show("插入数据成功!"); } }
public void AccessGuideJoinExcel(string Access, string AccTable, string Excel) { try { string tem_sql = "";//定义字符串 string connstr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Access + ";Persist Security Info=True";//记录连接Access的语句 System.Data.OleDb.OleDbConnection tem_conn = new System.Data.OleDb.OleDbConnection(connstr);//连接Access数据库 System.Data.OleDb.OleDbCommand tem_comm;//定义OleDbCommand类 tem_conn.Open();//打开连接的Access数据库 tem_sql = "select Count(*) From " + AccTable;//设置SQL语句,获取记录个数 tem_comm = new System.Data.OleDb.OleDbCommand(tem_sql, tem_conn);//实例化OleDbCommand类 int RecordCount = (int)tem_comm.ExecuteScalar();//执行SQL语句,并返回结果 //每个Sheet只能最多保存65536条记录。 tem_sql = @"select top 65535 * into [Excel 8.0;database=" + Excel + @".xls].[Sheet2] from 帐目";//记录连接Excel的语句 tem_comm = new System.Data.OleDb.OleDbCommand(tem_sql, tem_conn);//实例化OleDbCommand类 tem_comm.ExecuteNonQuery();//执行SQL语句,将数据表的内容导入到Excel中 tem_conn.Close();//关闭连接 tem_conn.Dispose();//释放资源 tem_conn = null; MessageBox.Show("导入完成"); } catch(Exception ex) { MessageBox.Show(ex.Message,"提示!"); } }
// TODO : Gestion du fichier Excel // - Import du fichier Excel en entier dans la base de données MERCURE // - Lecture du fichier Excel // - Accéder à un élément précis public static void ReadExcel(string path, string sheet, int nbColumns) { String strExcelConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + path + ";" + "Extended Properties='Excel 8.0;HDR=YES'"; OleDbConnection connExcel = new OleDbConnection(strExcelConn); OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheet + "$]", connExcel); try { connExcel.Open(); OleDbDataReader dataReader = cmd.ExecuteReader(); while (dataReader != null && dataReader.Read()) { for (int i = 0; i < nbColumns && i < dataReader.FieldCount-1; i++) { Console.Write("{0} || ", dataReader.GetString(i)); } Console.WriteLine(""); } } catch (Exception ex) { Console.WriteLine(ex.Message); } finally { connExcel.Dispose(); } }
/// <summary> /// 建立DataSet对象,用记录填充或构架(如果必要)DataSet对象,DataSet即是数据在内存的缓存 /// </summary> /// <param name="str_Sql">打开表Sql语句</param> public string Fill(string str_Sql) { string errorstring = Open(); if (errorstring != "OK") { return(errorstring); } //myAdapter = new SqlDataAdapter(str_Sql,myConnection) myAdapter = new System.Data.OleDb.OleDbDataAdapter(str_Sql, myConnection); myAdapter.TableMappings.Add("Table", "TableIn"); ds = new DataSet(); try { myAdapter.Fill(ds); } catch (SqlException e) { string errorMessage = e.Message; return(errorMessage); } finally { myConnection.Dispose(); } return("OK"); }
public DataTable ExecuteQuery(string sql) { conn = new OleDbConnection(connection); var results = new DataTable(); try { conn.Open(); OleDbDataAdapter adapter = null; using (adapter = new OleDbDataAdapter(CreateCommand(sql))) { adapter.Fill(results); } } catch (OleDbException ex) { throw ex; } finally { conn.Close(); conn.Dispose(); } return results; }
private DataTable LoadWorksheet(string worksheetName) { OleDbConnection connection = new System.Data.OleDb.OleDbConnection(_ConnectionString); OleDbDataAdapter cmd = null; try { cmd = new System.Data.OleDb.OleDbDataAdapter(string.Format("SELECT * FROM [{0}$]", worksheetName), connection); connection.Open(); DataTable dt = new DataTable(); cmd.Fill(dt); return(dt); } catch { return(null); } finally { if (connection != null) { try { connection.Dispose(); } catch { } } if (cmd != null) { try { cmd.Dispose(); } catch { } } } }
/// <summary> /// Executes an inline SQL statement and returns a data table. /// </summary> /// <param name="dbStatement">Inline SQL</param> /// <param name="connectionString">Connection string</param> /// <returns>Data table containing the return data</returns> public static DataTable RunQuery(string dbStatement, string connectionString) { OleDbConnection dbConnection = null; OleDbCommand dbCommand = null; OleDbDataAdapter adapter = null; DataTable dt = null; try { dbConnection = new OleDbConnection(connectionString); dbCommand = new OleDbCommand(dbStatement, dbConnection); dbCommand.CommandType = CommandType.Text; dbCommand.CommandTimeout = 600; adapter = new OleDbDataAdapter(dbCommand); dt = new DataTable(); dbConnection.Open(); adapter.Fill(dt); return dt; } finally { if (adapter != null) adapter.Dispose(); if (dbCommand != null) dbCommand.Dispose(); if (dbConnection != null) dbConnection.Dispose(); } }
private void button1_Click(object sender, EventArgs e) { string query = string.Empty; string strDbPath = Application.StartupPath + @"\CodeChildren1Offical.mdb"; string strCnn = "Provider = Microsoft.Jet.OleDb.4.0; Data Source = " + strDbPath; OleDbConnection conn = new OleDbConnection(strCnn); try { conn.Open(); folderBrowserDialog1.ShowDialog(); query = "INSERT INTO BlockFolder(LinkFolder, Status) VALUES('" + folderBrowserDialog1.SelectedPath + "','bat')"; Directory.Move(folderBrowserDialog1.SelectedPath, folderBrowserDialog1.SelectedPath + ".{20D04FE0-3AEA-1069-A2D8-08002B30309D}"); // tren day là lệnh khóa máy folder à ? dung r a //truyền vào là cái gì ? truyen vao day ky tu do do'a OleDbCommand sqlCommand = new OleDbCommand(query, conn); int i = sqlCommand.ExecuteNonQuery(); listView1.Items.Add(folderBrowserDialog1.SelectedPath); MessageBox.Show("Đã Nhập Vào Folder Cần Chặn !!!"); } catch (Exception ex) { MessageBox.Show(ex.StackTrace); } finally { conn.Close(); conn.Dispose(); } }
private void button2_Click(object sender, EventArgs e) { string query = string.Empty; string strDbPath = Application.StartupPath + @"\CodeChildren1Offical.mdb"; string strCnn = "Provider = Microsoft.Jet.OleDb.4.0; Data Source = " + strDbPath; OleDbConnection conn = new OleDbConnection(strCnn); try { conn.Open(); query = "INSERT INTO NameChildren(FirstName, LastName , PhoneNumber) VALUES('" + textBox3.Text + "','" + textBox2.Text + "','" + textBox1.Text + "')"; OleDbCommand sqlCommand = new OleDbCommand(query,conn); int i = sqlCommand.ExecuteNonQuery(); MessageBox.Show("Đã insert [" + i.ToString() + "] dữ liệu"); } catch (Exception ex) { MessageBox.Show(ex.StackTrace); } finally { conn.Close(); conn.Dispose(); } }
public bool DeleteContentType(string id) { String strCmd1 = string.Format("DELETE FROM ContentType WHERE ID = {0}" , id); String strCmd2 = string.Format("DELETE FROM Contents WHERE TypeCode = (SELECT TypeCode FROM ContentType WHERE ID = {0})",id); OleDbConnection conn = new OleDbConnection(StrConn); OleDbTransaction transaction = null; bool flag = false; try { conn.Open(); transaction = conn.BeginTransaction(); OleDbCommand contentCommand = new OleDbCommand(strCmd2, conn, transaction); contentCommand.ExecuteNonQuery(); OleDbCommand typeCommand = new OleDbCommand(strCmd1, conn,transaction); typeCommand.ExecuteNonQuery(); transaction.Commit(); flag = true; } catch (Exception ex) { flag = false; transaction.Rollback(); } finally { conn.Close(); conn.Dispose(); } return flag; }
protected void Button1_Click(object sender, EventArgs e) { string b = RID;// Request["QUERY_STRING"]; string a = Session["mzj"].ToString().Trim(); if (a == "没中奖") { string url = Request.Url.ToString(); Response.Redirect(url); } else { string PathDataBase = System.IO.Path.Combine(Server.MapPath("~"), "USER_DIR\\SYSUSER\\LotteryTicket\\db.dll");//"USER_DIR\\SYSUSER\\SYSSET\\" + String source = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + PathDataBase; OleDbConnection con = new OleDbConnection(source); //2、打开连接 C#操作Access之按列读取mdb con.Open(); DbCommand com = new OleDbCommand(); com.Connection = con; com.CommandText = "select jp from wx_ggk_jp where name='" + b + "'"; DbDataReader reader = com.ExecuteReader(); if (reader.Read()) { string jp = reader["jp"].ToString(); bd.Style["Display"] = "None";//转盘层 csyw.Style["Display"] = "None";//次数用完次数 zjl.Style["Display"] = "Block";//中奖页面 zjjs.Style["Display"] = "None";//中奖结束页面 Label3.Text = "恭喜你以中奖" + jp + "请填写相关信息"; con.Close(); } else { OleDbCommand comd = new OleDbCommand(); //连接字符串 comd.CommandText = "insert into wx_ggk_jp (name,jp,sjh) values ('" + b + "','" + a + "','手机号')"; comd.Connection = con; //执行命令,将结果返回 int sm = comd.ExecuteNonQuery(); con.Close(); //释放资源 con.Dispose(); bd.Style["Display"] = "None";//转盘层 csyw.Style["Display"] = "None";//次数用完次数 zjl.Style["Display"] = "Block";//中奖页面 zjjs.Style["Display"] = "None";//中奖结束页面 Label3.Text = "恭喜你以中奖" + a + "请填写相关信息"; } } }
private void CreateAttemptedSheet(String buildingName) { try { //Fetches sheet with master list data using (OLEDB.OleDbConnection conn = returnConnection()) { try { conn.Open(); OLEDB.OleDbCommand cmd = new OLEDB.OleDbCommand(); cmd.Connection = conn; cmd.CommandText = @"Create Table " + buildingName.Replace(' ', '_').Replace('-', '_') + "_Attempted(RfidTagId varchar, Location varchar,BU varchar, BUStaging varchar, RequestedDate varchar,RequestedModifyDate varchar,LocType varchar,PackageType varchar);"; cmd.ExecuteNonQuery(); cmd.CommandText = @"Insert Into [" + buildingName.Replace(' ', '_').Replace('-', '_') + "_Attempted$] Select RfidTagId,Location,BU,BUStaging,RequestedDate,RequestedModifyDate,LocType,PackageType From [MasterData$] Where Status<>'Missing' and PackageType NOT IN " + SizeListString() + " and (LocType<>'CUSTOMER STAGING' and LocType<>'delivered') and (Location NOT LIKE '%versum%' and Location NOT LIKE '%MarkGruver%' and (Location LIKE '%" + buildingName + "%attempt%' OR (Location LIKE '%" + buildingName + "%pallet%' AND LocType LIKE '%ATTEMPT%') OR (BUStaging LIKE '%" + buildingName + "%' AND LocType Like '%ATTEMPT%'))) Order By BU;"; cmd.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine(ex.ToString()); } finally { conn.Close(); conn.Dispose(); } } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
private void button2_Click(object sender, EventArgs e) { string strDbPath = Application.StartupPath + @"\CodeChildren1Offical.mdb"; string strCnn = "Provider = Microsoft.Jet.OleDb.4.0; Data Source = " + strDbPath; OleDbConnection conn = new OleDbConnection(strCnn); if (MessageBox.Show("Bạn có muốn xóa bản ghi này không?", "Thông báo", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes) { try { conn.Open(); string query = " Delete From LimitTime Where Hours ='" + listView2.SelectedItems[0].Text + "'"; MessageBox.Show("Đã xóa bản ghi thành công", "Thông báo", MessageBoxButtons.OK); OleDbCommand sqlCommand = new OleDbCommand(query, conn); sqlCommand.ExecuteNonQuery(); listView2.Items.RemoveAt(listView1.SelectedIndices[0]); } catch (Exception ex) { MessageBox.Show("Không thể xóa bản ghi này!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information); MessageBox.Show(ex.Message); conn.Close(); conn.Dispose(); return; } } return; }
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; }
private void button1_Click(object sender, EventArgs e) { string query = string.Empty; string strDbPath = Application.StartupPath + @"\CodeChildren1Offical.mdb"; string strCnn = "Provider = Microsoft.Jet.OleDb.4.0; Data Source = " + strDbPath; OleDbConnection conn = new OleDbConnection(strCnn); try { conn.Open(); query = "INSERT INTO About(Name, Version , FromDate , ToDate ,IdeaFr , Email) VALUES('" + textBox1.Text + "','1.0.0.2','unlimited', 'unlimited','" + textBox2.Text + "')"; OleDbCommand sqlCommand = new OleDbCommand(query, conn); int i = sqlCommand.ExecuteNonQuery(); //button1.Items.Add(textBox1); // button1.Items.Add(textBox2); MessageBox.Show("Cám ơn bạn đã góp ý cho chúng tôi"); MessageBox.Show("Mọi ý kiến của bạn sẽ được lắng nghe giúp phần mềm phát triển "); } catch (Exception ex) { MessageBox.Show(ex.StackTrace); } finally { conn.Close(); conn.Dispose(); } }
private void CreateLargeAttemptedSheet(String roomName) { try { using (OLEDB.OleDbConnection conn = returnConnection()) { try { conn.Open(); OLEDB.OleDbCommand cmd = new OLEDB.OleDbCommand(); cmd.Connection = conn; cmd.CommandText = @"Create Table " + roomName.Replace(' ', '_').Replace('-', '_') + "_Large_Attempted(RfidTagId varchar, Location varchar,BU varchar, BUStaging varchar, RequestedDate varchar,RequestedModifyDate varchar,LocType varchar,PackageType varchar);"; cmd.ExecuteNonQuery(); cmd.CommandText = @"Insert Into [" + roomName.Replace(' ', '_').Replace('-', '_') + "_Large_Attempted$] Select RfidTagId,Location,BU,BUStaging,RequestedDate,RequestedModifyDate,LocType,PackageType From [MasterData$] Where Status<>'Missing' and (LocType<>'CUSTOMER STAGING' and LocType<>'delivered') and PackageType IN " + SizeListString() + " and (Location NOT LIKE '%RVN%' and Location NOT LIKE '%RCV-Stag%' and Location NOT LIKE '%versum%' and (Location LIKE '%attempt%' OR LocType LIKE '%ATTEMPT%') and Location NOT LIKE '%B72%' and BUStaging NOT LIKE '%B72%') and BUStaging='" + roomName + "' Order By BU;"; cmd.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine(ex.ToString()); } finally { conn.Close(); conn.Dispose(); } } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
private void button5_Click(object sender, EventArgs e) { SaveFileDialog saveFileDialog1 = new SaveFileDialog(); saveFileDialog1.Filter = "xls files (*.xls)|*.xls"; saveFileDialog1.FilterIndex = 2; saveFileDialog1.RestoreDirectory = true; if (saveFileDialog1.ShowDialog() == DialogResult.OK) { DataTable dt = dataSet.Tables["student"].Copy(); //連線字串 System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection( "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + saveFileDialog1.FileName + ";" + "Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=0\""); connection.Open(); //建立工作表 try { OleDbCommand cmd = new OleDbCommand(); cmd.Connection = connection; DataTable cdt = connection.GetSchema("tables"); DataRow[] cdrs = cdt.Select("Table_Name = 'Sheet1'"); if (cdrs.Length == 0) { cmd.CommandText = "CREATE TABLE [Sheet1] ([學號] INTEGER,[班級] INTEGER,[姓名] VarChar)"; //新增Excel工作表 cmd.ExecuteNonQuery(); } //增加資料 for (int i = 0; i < dt.Rows.Count; i++) { if (dt.Rows[i]["學號"] == null || dt.Rows[i]["班級"] == null || dt.Rows[i]["姓名"] == null) { MessageBox.Show(string.Format("有部分資料錯誤,請檢查 (學號:{0},班級:{1},姓名{2})", dt.Rows[i]["學號"], dt.Rows[i]["班級"], dt.Rows[i]["姓名"])); } else { cmd.CommandText = string.Format("INSERT INTO [Sheet1$] VALUES({0},{1},'{2}')", dt.Rows[i]["學號"], dt.Rows[i]["班級"], dt.Rows[i]["姓名"]); cmd.ExecuteNonQuery(); } } MessageBox.Show(string.Format("學生資料成功匯出到 {0}", saveFileDialog1.FileName)); tssMessage.Text = "學生資料匯出成功!"; } catch (Exception ex) { MessageBox.Show(ex.ToString()); } connection.Close(); connection.Dispose(); } }
private void button9_Click(object sender, EventArgs e) { SaveFileDialog saveFileDialog1 = new SaveFileDialog(); saveFileDialog1.Filter = "xls files (*.xls)|*.xls"; saveFileDialog1.FilterIndex = 2; saveFileDialog1.RestoreDirectory = true; if (saveFileDialog1.ShowDialog() == DialogResult.OK) { DataTable dt = dataSet.Tables["pointlog"].Copy(); //連線字串 System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection( "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + saveFileDialog1.FileName + ";" + "Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=0\""); connection.Open(); try { //建立工作表 //dataGridView2.Columns["number"].HeaderText = "學號"; //dataGridView2.Columns["stu_class"].HeaderText = "班級"; //dataGridView2.Columns["name"].HeaderText = "姓名"; //dataGridView2.Columns["date"].HeaderText = "日期"; //dataGridView2.Columns["pt_case"].HeaderText = "獎勵事由"; //dataGridView2.Columns["point"].HeaderText = "獎勵點數"; OleDbCommand cmd = new OleDbCommand(); cmd.Connection = connection; DataTable cdt = connection.GetSchema("tables"); DataRow[] cdrs = cdt.Select("Table_Name = 'Sheet1'"); if (cdrs.Length == 0) { cmd.CommandText = "CREATE TABLE [Sheet1] ([班級] VarChar,[學號] INTEGER,[姓名] VarChar,[日期] VarChar,[獎勵事由] VarChar,[獎勵點數] INTEGER)"; //新增Excel工作表 cmd.ExecuteNonQuery(); } //增加資料 for (int i = 0; i < dt.Rows.Count; i++) { cmd.CommandText = string.Format("INSERT INTO [Sheet1$] VALUES('{0}',{1},'{2}','{3}','{4}',{5})", dt.Rows[i]["stu_class"], dt.Rows[i]["number"], dt.Rows[i]["name"], dt.Rows[i]["date"], dt.Rows[i]["pt_case"], dt.Rows[i]["point"]); cmd.ExecuteNonQuery(); } MessageBox.Show(string.Format("點數清單成功匯出到 {0}", saveFileDialog1.FileName)); tssMessage.Text = "點數清單匯出成功!"; } catch (Exception ex) { MessageBox.Show(ex.ToString()); } connection.Close(); connection.Dispose(); } }
void IDisposable.Dispose() { //释放托管资源 if (_connection != null) { _connection.Dispose(); } }
/// <summary> /// 关闭数据库 /// </summary> private void closeConnection(OleDbConnection conn) { if (conn.State == ConnectionState.Open) { conn.Close(); conn.Dispose(); } }
public static DataTable ReadExcelData(string file, string sheetName) { DataTable ret = null; string fileExtension = Path.GetExtension(file); fileExtension = fileExtension.ToLower(); string connectionString = string.Empty; if (fileExtension.Equals(".xls")) { connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + @";Extended Properties=Excel 8.0"; } else if (fileExtension.Equals(".xlsx")) { connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file + @";Extended Properties=Excel 12.0"; } else { throw new InvalidDataException("File '" + file + "' is NOT a valid MS Excel file"); } // Create the connection object OleDbConnection oledbConn = new OleDbConnection(connectionString); try { // Open connection oledbConn.Open(); // Create OleDbCommand object and select data from worksheet Sheet1 OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheetName + "$]", oledbConn); // Create new OleDbDataAdapter OleDbDataAdapter oleda = new OleDbDataAdapter(); oleda.SelectCommand = cmd; // Create a DataSet which will hold the data extracted from the worksheet. ret = new DataTable(); // Fill the DataSet from the data extracted from the worksheet. oleda.Fill(ret); } catch (Exception ex) { } finally { if ((oledbConn != null) && (oledbConn.State != ConnectionState.Closed)) { oledbConn.Close(); oledbConn.Dispose(); } } return ret; }
/// <summary> /// 公有方法,释放资源。 /// </summary> public void Dispose() { // 确保连接被关闭 if (Connection != null) { Connection.Dispose(); Connection = null; } }
protected void btnSil_Click(object sender, EventArgs e) { OleDbConnection baglanti = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("Uyeler\\Uyeler.accdb")); baglanti.Open(); OleDbCommand sil = new OleDbCommand("DELETE FROM UyelikSistemi WHERE UyeID=" + txtKullaniciID.Text, baglanti); sil.ExecuteNonQuery(); lblsil.Text = "Silme İşlemi Gerçekleştirildi."; baglanti.Close(); baglanti.Dispose(); }
protected void btnKaydet_Click(object sender, EventArgs e) { OleDbConnection baglanti = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+Server.MapPath("Uyeler\\Uyeler.accdb")); baglanti.Open(); OleDbCommand sorguKaydet=new OleDbCommand("INSERT INTO UyelikSistemi(AdiSoyadi,KullaniciAdi,Sifre,Eposta) values('" + txtAdi.Text + "','" + txtKullaniciAdi.Text + "', '" + txtSifre.Text + "','" + txtEposta.Text + "')",baglanti); sorguKaydet.ExecuteNonQuery(); Response.Redirect("Giris.aspx"); baglanti.Close(); baglanti.Dispose(); }
public void ExecuteStatement(string strSql) { OleDbConnection myConnection = new OleDbConnection(ConfigurationSettings.AppSettings["DBCONN_OARPT_OLE"]); OleDbCommand myCommand = new OleDbCommand(strSql, myConnection); myCommand.Connection.Open(); myCommand.ExecuteNonQuery(); myConnection.Close(); myConnection.Dispose(); myCommand.Dispose(); }
private DataTable ExecuteDataTable(string sql) { OleDbConnection con = new OleDbConnection(this._connstring); con.Open(); OleDbCommand cmd = new OleDbCommand(sql, con); OleDbDataAdapter adapter = new OleDbDataAdapter(cmd); DataTable tbl = new DataTable(); adapter.Fill(tbl); con.Close(); con.Dispose(); return tbl; }
public DataSet Import(string message, bool error) { DataSet dataSet = new DataSet(); string text = Constants.Config.ExcelConnectionString + "Data Source=" + this.FileName + ";"; OleDbConnection oleDbConnection = new OleDbConnection(text); string text2 = "Select * From [" + this.SheetName + "$]"; OleDbCommand oleDbCommand = new OleDbCommand(text2, oleDbConnection); OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter(oleDbCommand); oleDbDataAdapter.Fill(dataSet); oleDbConnection.Dispose(); return dataSet; }
public static long fcnAddWebReg(long _lngGGCCRegWebID, long _lngRecordID) { //add event reg based on web record--adding based on passed recordid, event id, and date registered //return new event reg id OleDbConnection objConn; OleDbCommand objCommand; string strSQL; long lngRes = 0; try { objConn = new OleDbConnection(clsAppSettings.GetAppSettings().strCTConn); objConn.Open(); strSQL = "INSERT INTO tblGGCCRegistrations " + "( blnCustomGGCCRegFlag1, blnCustomGGCCRegFlag2, blnCustomGGCCRegFlag3, blnCustomGGCCRegFlag4, blnCustomGGCCRegFlag5, blnCustomGGCCRegFlag6, blnCustomGGCCRegFlag7, blnCustomGGCCRegFlag8, blnCustomGGCCRegFlag9, blnCustomGGCCRegFlag10, blnCustomGGCCRegFlag11, blnCustomGGCCRegFlag12, blnCustomGGCCRegFlag13, blnCustomGGCCRegFlag14, blnCustomGGCCRegFlag15, " + "lngGGCCID, lngRecordID, lngGGCCRegistrationWebID, " + "dblDiscount, " + "dteDateRegistered ) " + "SELECT tblWebGGCCRegistrations.blnCustomGGCCRegFlag1, tblWebGGCCRegistrations.blnCustomGGCCRegFlag2, tblWebGGCCRegistrations.blnCustomGGCCRegFlag3, tblWebGGCCRegistrations.blnCustomGGCCRegFlag4, tblWebGGCCRegistrations.blnCustomGGCCRegFlag5, tblWebGGCCRegistrations.blnCustomGGCCRegFlag6, tblWebGGCCRegistrations.blnCustomGGCCRegFlag7, tblWebGGCCRegistrations.blnCustomGGCCRegFlag8, tblWebGGCCRegistrations.blnCustomGGCCRegFlag9, tblWebGGCCRegistrations.blnCustomGGCCRegFlag10, tblWebGGCCRegistrations.blnCustomGGCCRegFlag11, tblWebGGCCRegistrations.blnCustomGGCCRegFlag12, tblWebGGCCRegistrations.blnCustomGGCCRegFlag13, tblWebGGCCRegistrations.blnCustomGGCCRegFlag14, tblWebGGCCRegistrations.blnCustomGGCCRegFlag15, " + "lngGGCCID, " + _lngRecordID + ", " + _lngGGCCRegWebID + ", " + "dblDiscount, " + "dteDateRegistered " + "FROM tblWebGGCCRegistrations " + "WHERE lngGGCCRegistrationWebID=" + _lngGGCCRegWebID + ";"; objCommand = new OleDbCommand(strSQL, objConn); if (objCommand.ExecuteNonQuery() > 0) { objCommand.CommandText = "SELECT @@IDENTITY;"; lngRes = (long)(int)objCommand.ExecuteScalar(); } else lngRes = 0; objConn.Close(); objCommand.Dispose(); objConn.Dispose(); } catch (Exception ex) { clsErr.subLogErr("cslGGCCCRUD.fcnAddWebReg()", ex); } return lngRes; }
/// <summary> /// 关闭SQL连接 /// </summary> public override void Close() { try { _conn.Close(); _conn.Dispose(); _conn = null; } catch { } }
public int OutExcel() { DataTable dt = GetDataTable(); string FileName = Rand.Number(5, true) + ".xlsx"; //Guid.NewGuid().ToString()+ ".xlsx"; string sNewFullFile = "C:\\" + FileName; string strConn = GetConnectionString(sNewFullFile); System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn); OleDbCommand cmd = null; try { conn.Open(); cmd = new OleDbCommand("create table [Sheet1]([通知单号] Text,[所属机组/机泵] Text,[设备编号] Text,[设备名称] Text,[故障名称] Text,[故障部位] Text,[故障发生时间] Text,[处理完成时间] Text,[停机时间] Text,[故障处理方法] Text)", conn); cmd.ExecuteNonQuery(); for (int i = 0; i < dt.Rows.Count; i++) { var sRows = "INSERT INTO [Sheet1$] ([通知单号], [所属机组/机泵],[设备编号],[设备名称],[故障名称],[故障部位],[故障发生时间],[处理完成时间],[停机时间],[故障处理方法]) VALUES ("; for (int j = 1; j < dt.Columns.Count; j++) { sRows += "'" + dt.Rows[i][j] + "',"; } sRows = sRows.TrimEnd(','); string strSQL = sRows + ")"; cmd = new OleDbCommand(strSQL, conn); cmd.ExecuteNonQuery(); } return(1); } catch (Exception er) { throw er; } finally { if (cmd != null) { cmd.Dispose(); } conn.Dispose(); } }
// 查询教师名单 private void button1_Click(object sender, EventArgs e) { string ConStr = "Provider=Microsoft.Jet.OleDb.4.0;"; ConStr += @"Data Source=People.mdb"; OleDbConnection oleCon = new OleDbConnection(ConStr); OleDbDataAdapter oleDap = new OleDbDataAdapter("select * from t_teacher", oleCon); DataSet ds = new DataSet(); oleDap.Fill(ds, "t_teacher"); this.dataGridView1.DataSource = ds.Tables[0].DefaultView; oleCon.Close(); oleCon.Dispose(); dataGridView1Style(); //调用Gridview方法 }
private void btnContinue_Click(object sender, EventArgs e) { //add record, get id, close form OleDbConnection objConn; OleDbCommand objCommand; string strSQL; try { objConn = new OleDbConnection(clsAppSettings.GetAppSettings().strCTConn); objConn.Open(); strSQL = "INSERT INTO tblRecords " + "( blnCamper, " + "lngStateID, " + "strFirstName, strLastCoName, strCompanyName, strAddress, strCity, strZip, strHomePhone, strWorkPhone, strCellPhone, strEmail ) " + "SELECT 1 AS blnCamper, " + ((clsCboItem)cboState.SelectedItem).ID + ", " + "\"" + txtFName.Text + "\", \"" + txtLName.Text + "\", \"" + txtCompany.Text + "\", \"" + txtAddress.Text + "\", \"" + txtCity.Text + "\", \"" + txtZip.Text + "\", \"" + txtHomePhone.Text + "\", \"" + txtWorkPhone.Text + "\", @strCellPhone, \"" + txtEMail.Text + "\";"; objCommand = new OleDbCommand(strSQL, objConn); objCommand.Parameters.AddWithValue("@strCellPhone", txtCellPhone.Text); if (objCommand.ExecuteNonQuery() > 0) { objCommand.CommandText = "SELECT @@IDENTITY;"; objCommand.Parameters.Clear(); lngRecordID = int.Parse(objCommand.ExecuteScalar().ToString()); } else { lngRecordID = 0; } objConn.Close(); objCommand.Dispose(); objConn.Dispose(); } catch (Exception ex) { clsErr.subLogErr("frmAddIRForGGCCWebReg.btnContinue", ex); lngRecordID = 0; } this.Close(); }
public string FillLegal() { string tbl = "admin"; string sqlStr = "SELECT * FROM " + tbl; string legal = string.Empty; try { OleDbConnection cnn = new OleDbConnection(Base.cnnStr); OleDbDataAdapter oda = new OleDbDataAdapter(sqlStr, cnn); OleDbCommand cmd = new OleDbCommand(sqlStr, cnn); cnn.Open(); OleDbDataReader drr = cmd.ExecuteReader(); DataSet ds = new DataSet(); oda.Fill(ds, "admin"); while (drr.Read()) { legal = EncDec.Decrypt(drr["legal"].ToString(), Base.hashKey); break; } drr.Close(); cnn.Close(); cmd.Dispose(); drr.Dispose(); ds.Dispose(); oda.Dispose(); cnn.Dispose(); cmd = null; drr = null; ds = null; oda = null; cnn = null; } catch { legal = errInvalidLegal; } finally { tbl = null; sqlStr = null; } return legal; }
/// <summary> /// 读取excel文件数据 /// </summary> /// <param name="filepath">文件物理路径</param> /// <param name="fields">字段映射</param> /// <returns></returns> public DataTable CallExcel(string filepath, string Worksheet, Dictionary<string, string> fields) { string strConn = GetExcelConnectionString(filepath); OleDbConnection objConn = new OleDbConnection(strConn); objConn.Open(); string sql = "select * from ["+Worksheet+"$]"; OleDbDataAdapter adapter = new OleDbDataAdapter(sql, objConn); DataTable dt = new DataTable(); adapter.Fill(dt); dt = ExchangeColName(dt, fields); objConn.Close(); objConn.Dispose(); return dt; }
private void AccessToExcel(string P_str_Access,string P_str_Table,string P_str_Excel) { string P_str_Con = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + P_str_Access + ";Persist Security Info=True";//记录连接Access的语句 string P_str_Sql = "";//存储要执行的SQL语句 OleDbConnection oledbcon = new OleDbConnection(P_str_Con);//实例化OLEDB连接对象 OleDbCommand oledbcom;//定义OleDbCommand对象 oledbcon.Open();//打开数据库连接 //向Excel工作表中导入数据 P_str_Sql = @"select * into [Excel 12.0;database=" + P_str_Excel + "]." + "[Sheet1]" + " from " + P_str_Table + "";//记录连接Excel的语句 oledbcom = new System.Data.OleDb.OleDbCommand(P_str_Sql, oledbcon);//实例化OleDbCommand对象 oledbcom.ExecuteNonQuery();//执行SQL语句,将数据表的内容导入到Excel中 oledbcon.Close();//关闭数据库连接 oledbcon.Dispose();//释放资源 }
private void Frm_Main_Load(object sender, EventArgs e) { string ConStr = string.Format(//设置数据库连接字符串 @"Provider=Microsoft.Jet.OLEDB.4.0;Data source='{0}\test.mdb'", Application.StartupPath); OleDbConnection oleCon = new OleDbConnection(ConStr);//创建数据库连接对象 OleDbDataAdapter oleDap = new OleDbDataAdapter(//创建数据适配器对象 "select * from 帐目", oleCon); DataSet ds = new DataSet();//创建数据集 oleDap.Fill(ds, "帐目");//填充数据集 this.dataGridView1.DataSource =//设置数据源 ds.Tables[0].DefaultView; oleCon.Close();//关闭数据库连接 oleCon.Dispose();//释放连接资源 }
//파일에서 불러오기 private void buttonAddByFile_Click(object sender, EventArgs e) { //xls파일 불러와서 테이블에 저장 string strConnection = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Data.xls;Extended Properties='Excel 8.0;HDR=No'"; DataTable dTable = new DataTable(); int rCnt=0; //SQL문을 통한 OleDb로 자료 읽기 try { OleDbConnection oleDbCon = new OleDbConnection(strConnection); oleDbCon.Open(); string strSQL = "SELECT * FROM [Course$]"; OleDbCommand dbCommand = new OleDbCommand(strSQL, oleDbCon); OleDbDataAdapter dataAdapter = new OleDbDataAdapter(dbCommand); dataAdapter.Fill(dTable); dTable.Dispose(); dataAdapter.Dispose(); dbCommand.Dispose(); oleDbCon.Close(); oleDbCon.Dispose(); } catch (SystemException ex) { MessageBox.Show(ex.Message); } //데이터그리드뷰에 데이터 입력 try { foreach (DataRow row in dTable.Rows) { int cnt = 0; pRO1COURSESBindingSource.AddNew(); foreach (DataColumn col in dTable.Columns) { dataGridViewMakeCrs.Rows[rCnt].Cells[cnt].Value = dTable.Rows[rCnt].ItemArray[cnt]; cnt++; } rCnt++; } } catch (NoNullAllowedException ex) { MessageBox.Show("완성되지 않은 행이 있습니다. 삭제 후 다시 시도해주세요."); } }
public int OutExcel() { DataTable dt = GetDataTable(); string FileName = Guid.NewGuid().ToString().Substring(8) + ".xlsx"; string sNewFullFile = "C:\\" + FileName; string strConn = GetConnectionString(sNewFullFile); System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn); OleDbCommand cmd = null; try { conn.Open(); cmd = new OleDbCommand("create table [Sheet1]( [位置编码] Text,[关联位置] Text,[公司编号] Text,[计划工厂] Text,[维护工厂] Text,[工厂区域] Text,[计划员组] Text,[成本中心] Text,[位置类型] Text,[位置状态] Text,[开始日期] Text,[修改日期] Text,[业务范围] Text,[是否安装] Text,[包含删除] Text,[维护班组] Text)", conn); cmd.ExecuteNonQuery(); for (int i = 0; i < dt.Rows.Count; i++) { var sRows = "INSERT INTO [Sheet1$] ([位置编码], [关联位置],[公司编号],[计划工厂],[维护工厂],[工厂区域],[计划员组],[成本中心],[位置类型],[位置状态],[开始日期],[修改日期],[业务范围],[是否安装],[包含删除],[维护班组]) VALUES ("; for (int j = 1; j < dt.Columns.Count; j++) { sRows += "'" + dt.Rows[i][j] + "',"; } sRows = sRows.TrimEnd(','); string strSQL = sRows + ")"; cmd = new OleDbCommand(strSQL, conn); cmd.ExecuteNonQuery(); } return(1); } catch (Exception er) { throw er; } finally { if (cmd != null) { cmd.Dispose(); } conn.Dispose(); } }
/// <summary> /// Se encarga de cerrar la conexión con la base de datos objetivo /// No necesita parámetros de entrada o salida /// </summary> public void Stop() { try { _cnn.Close(); _cnn.Dispose(); try { _cmd.Connection.Close(); } catch { Console.WriteLine("SpinDataBase::If you are working with MYSQL, this object isn´t necessary or it is null"); } } catch (Exception ex) { throw SpinException.GetException("SpinDatabase:: " + ex.Message, ex); } }
private string GetDBImage(int Index, Accusoft.ImagXpressSdk.ImageXView iXView) { string ImageName; long retter; int bufferSize = 16777216; byte[] outByte = new byte[bufferSize - 1]; string stringConnection = (strProvider + strDataSource); string stringSQL = "SELECT * FROM [tblImages]"; dbConnection.ConnectionString = (strProvider + strDataSource); dbConnection.Open(); System.Data.OleDb.OleDbCommand dbCommandGet = new OleDbCommand(stringSQL, dbConnection); System.Data.OleDb.OleDbDataReader dbReader = dbCommandGet.ExecuteReader(CommandBehavior.Default); int i = 0; while ((i <= Index)) { dbReader.Read(); i = (i + 1); } retter = dbReader.GetBytes(2, 0, outByte, 0, bufferSize); MemoryStream stmBLOBData = new MemoryStream(outByte); ImageName = dbReader["ImageName"].ToString(); dbReader.Close(); try { iXView.Image = Accusoft.ImagXpressSdk.ImageX.FromStream(imagXpress1, stmBLOBData, loLoadOptions); } catch (Accusoft.ImagXpressSdk.ImagXpressException ex) { AccusoftError(ex, lblError); } if ((dbConnection.State == ConnectionState.Open)) { dbConnection.Close(); dbConnection.Dispose(); } return(ImageName); }
public List <dynamic> GetColumns(SqlHelper sqlHelper, string dataBaseName, string tableName) { var list = new List <dynamic>(); var con = new System.Data.OleDb.OleDbConnection(sqlHelper.DbConnectionString); con.Open(); DataTable dt = con.GetSchema("columns", new string[] { null, null, tableName }); foreach (DataRow dr in dt.Rows) { dynamic eo = new ExpandoObject(); eo.name = dr["COLUMN_NAME"].ToString().FirstLetterToUpper(); eo.type = dr["data_type"].ToString(); eo.length = dr["CHARACTER_MAXIMUM_LENGTH"].ToString(); eo.isnullable = dr["is_nullable"].ToString(); list.Add(eo); } con.Close(); con.Dispose(); return(list); }
public List <dynamic> GetTables(SqlHelper sqlHelper, string dataBaseName) { var list = new List <dynamic>(); var con = new System.Data.OleDb.OleDbConnection(sqlHelper.DbConnectionString); con.Open(); DataTable dt = con.GetSchema("tables"); foreach (DataRow dr in dt.Rows) { if (dr["TABLE_TYPE"].ToString() == "TABLE") { dynamic eo = new ExpandoObject(); eo.name = dr["TABLE_NAME"].ToString().FirstLetterToUpper(); list.Add(eo); } } con.Close(); con.Dispose(); return(list); }
public bool IsConnected(string serverName, string userId, string password, string dbName, ref string outputConnectString) { string connectString = "provider=SQLOLEDB;DATA SOURCE=" + serverName + ";Initial Catalog=" + dbName + ";USER ID=" + userId + ";Password="******";"; try { System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection(); cn.ConnectionString = connectString; cn.Open(); cn.Close(); cn.Dispose(); } catch (System.Exception) { return(false); } outputConnectString = connectString; return(true); }
//打开恢复数据库专用的数据连接,用master数据库 public string DBRestore(string str_Sql) { string myConnectionStr = "Provider=SQLOLEDB.1;Persist Security Info=False;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=DH;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=master;Data Source=" + ConfigurationSettings.AppSettings["Database"] + ";User ID=" + ConfigurationSettings.AppSettings["DatabaseUser"] + ";Password="******"DatabasePassword"]; myConnection = new System.Data.OleDb.OleDbConnection(myConnectionStr); try { myConnection.Open(); } catch (SqlException e) { string errorMessage = e.Message; return(errorMessage); } finally { myConnection.Dispose(); } //myCommand = new SqlCommand(str_Sql,myConnection); //断开SchoolManage的一切连接 string str_Sql_DisConnect = "declare hcforeach cursor global for select 'kill '+rtrim(spid) from master.dbo.sysprocesses where dbid=db_id('" + ConfigurationSettings.AppSettings["Database"].ToString() + "') exec sp_msforeach_worker '?'"; myCommand = new System.Data.OleDb.OleDbCommand(str_Sql_DisConnect, myConnection); myCommand.ExecuteNonQuery(); myCommand.Dispose(); myCommand = new System.Data.OleDb.OleDbCommand(str_Sql, myConnection); myCommand.ExecuteNonQuery(); myCommand.Dispose(); return("OK"); }
/// <summary> /// 导出中奖结果 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void 导出结果ToolStripMenuItem_Click(object sender, EventArgs e) { string sql; string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + System.Environment.CurrentDirectory + "\\data\\LD.mdb;Persist Security Info=False;Jet OLEDB:Database Password=shiyanexperiment;"; System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection(ConnectionString); System.Data.OleDb.OleDbCommand cmd; cn.Open(); String fileName = "抽奖结果" + System.DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls"; sql = @"select stuID as 员工编号, stuName as 员工姓名,awaGrade as 获奖等级,awaGood as 奖品 into [Excel 8.0;database=" + System.Environment.CurrentDirectory + @"\" + fileName + "].[Sheet1] from AwardsInfo"; cmd = new System.Data.OleDb.OleDbCommand(sql, cn); try { int result = cmd.ExecuteNonQuery(); if (result == 0) { MessageBox.Show("没有中奖结果,您可能尚未开奖!"); } else { MessageBox.Show("中奖结果已经导入" + System.Environment.CurrentDirectory + "\\" + fileName + "中"); } } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { cn.Close(); cn.Dispose(); cn = null; } }
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); }
public DataSet ImportexcelData1(string FilePath) { OleDbConnection dbConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties=\"Excel 12.0 xml;IMEX=1\";"); dbConnection.Open(); DataSet ds = new DataSet("Data"); String sql = "Select * from [Sheet1$]"; OleDbCommand dbCommand = new System.Data.OleDb.OleDbCommand(sql, dbConnection); OleDbDataAdapter dbAdapter = new OleDbDataAdapter(dbCommand); DataTable dt = new DataTable("Sheet1"); dbAdapter.Fill(dt); ds.Tables.Add(dt); dbConnection.Close(); dbConnection.Dispose(); // CheckExcellProcesses(); // KillExcel(); return(ds); }
private void button4_Click(object sender, EventArgs e) { OpenFileDialog openFileDialog1 = new OpenFileDialog(); openFileDialog1.InitialDirectory = System.Environment.CurrentDirectory; openFileDialog1.Filter = "xls files (*.xls)|*.xls"; openFileDialog1.FilterIndex = 2; openFileDialog1.RestoreDirectory = true; if (openFileDialog1.ShowDialog() == DialogResult.OK) { DataTable dt = dataSet.Tables["student"].Clone(); //連線字串 System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection( "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + openFileDialog1.FileName + ";" + "Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\""); connection.Open(); try { string query = "select 學號,班級,姓名 from [Sheet1$]"; System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(query, connection); adapter.Fill(dt); connection.Close(); connection.Dispose(); //dt.PrimaryKey = new DataColumn[] { dt.Columns["學號"] }; string TMPMSG = ""; for (int i = 0; i < dt.Rows.Count; i++) { if (!dataSet.Tables["student"].Rows.Contains(dt.Rows[i]["學號"])) { DataRow newRow = dataSet.Tables["student"].NewRow(); newRow["學號"] = dt.Rows[i]["學號"]; newRow["班級"] = dt.Rows[i]["班級"]; newRow["姓名"] = dt.Rows[i]["姓名"]; dataSet.Tables["student"].Rows.Add(newRow); TMPMSG += string.Format("添加 {0},{1},{2}\r\n", newRow["班級"], newRow["學號"], newRow["姓名"]); } else { DataRow oldRow = dataSet.Tables["student"].Rows.Find(dt.Rows[i]["學號"]); oldRow["班級"] = dt.Rows[i]["班級"]; oldRow["姓名"] = dt.Rows[i]["姓名"]; TMPMSG += string.Format("更新 {0},{1},{2}\r\n", oldRow["班級"], oldRow["學號"], oldRow["姓名"]); } } TMPMSG += string.Format("匯入工作一共處理{0}筆資料", dt.Rows.Count); MessageBox.Show(TMPMSG); dataAdapter3.Update(dataSet, "student"); dataSet.AcceptChanges(); GetNewData(); tssMessage.Text = "學生資料匯入成功!"; } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } }
public void ProcessRequest(HttpContext context) { HttpResponse Response = context.Response; HttpRequest Request = context.Request; System.IO.Stream iStream = null; byte[] buffer = new Byte[10240]; int length; long dataToRead; System.Data.OleDb.OleDbConnection dbConnection = null; string sFileName = Request["fn"]; string sFilePath = HttpContext.Current.Server.MapPath("~/") + sFileName; //待下载的文件路径 try { if (sFileName != null)//按文件名下载指定文件 { iStream = new System.IO.FileStream(sFilePath, System.IO.FileMode.Open, System.IO.FileAccess.Read, System.IO.FileShare.Read); } else//下载debug报表 { ExcelPackage pck = new ExcelPackage(); BuildExcelReport(ref pck, ref dbConnection, ref context, ref sFileName); iStream = new System.IO.MemoryStream(pck.GetAsByteArray()); } Response.Clear(); dataToRead = iStream.Length; long p = 0; if (Request.Headers["Range"] != null) { Response.StatusCode = 206; p = long.Parse(Request.Headers["Range"].Replace("bytes=", "").Replace("-", "")); } if (p != 0) { Response.AddHeader("Content-Range", "bytes " + p.ToString() + "-" + ((long)(dataToRead - 1)).ToString() + "/" + dataToRead.ToString()); } Response.AddHeader("Content-Length", ((long)(dataToRead - p)).ToString()); Response.ContentType = "application/octet-stream"; Response.AddHeader("Content-Disposition", "attachment; filename=" + System.Web.HttpUtility.UrlEncode( System.Text.Encoding.GetEncoding(65001).GetBytes(System.IO.Path.GetFileName(sFileName)))); iStream.Position = p; dataToRead = dataToRead - p; while (dataToRead > 0) { if (Response.IsClientConnected) { length = iStream.Read(buffer, 0, 10240); Response.OutputStream.Write(buffer, 0, length); Response.Flush(); buffer = new Byte[10240]; dataToRead = dataToRead - length; } else { dataToRead = -1; } } //Response.End(); HttpContext.Current.ApplicationInstance.CompleteRequest(); } catch (Exception ex) { Response.Write("Error : " + context.Server.HtmlEncode(ex.Message)); string sHtml = "<script type=\"text/javascript\">" + "if(parent.lblError) parent.lblError.innerHTML=unescape(\"" + Microsoft.JScript.GlobalObject.escape(ex.Message.Replace("\n", "<br>")) + "\");" + "</script>"; Response.Write(sHtml); } finally { if (iStream != null) { iStream.Close(); } if (dbConnection != null) { dbConnection.Dispose(); } } }
public static bool ExportToXLSX(string sheetToCreate, List <DataRow> selectedRows, System.Data.DataTable origDataTable, string tableName) { bool status = false; System.Data.OleDb.OleDbConnection cn = new OleDbConnection(); try { char Space = ' '; string dest = sheetToCreate; if (File.Exists(dest)) { File.Delete(dest); } sheetToCreate = dest; if (tableName == null) { tableName = string.Empty; } tableName = tableName.Trim().Replace(Space, '_'); if (tableName.Length == 0) { tableName = origDataTable.TableName.Replace(Space, '_'); } if (tableName.Length == 0) { tableName = "NoTableName"; } if (tableName.Length > 30) { tableName = tableName.Substring(0, 30); } //Excel names are less than 31 chars string queryCreateExcelTable = "CREATE TABLE [" + tableName + "] ("; Dictionary <string, string> colNames = new Dictionary <string, string>(); foreach (DataColumn dc in origDataTable.Columns) { //Cause the query to name each of the columns to be created. string modifiedcolName = dc.ColumnName;//.Replace(Space, '_').Replace('.', '#'); string origColName = dc.ColumnName; colNames.Add(modifiedcolName, origColName); queryCreateExcelTable += "[" + modifiedcolName + "]" + " text,"; } queryCreateExcelTable = queryCreateExcelTable.TrimEnd(new char[] { Convert.ToChar(",") }) + ")"; //adds the closing parentheses to the query string if (selectedRows.Count > 65000 && sheetToCreate.ToLower().EndsWith(".xls")) { //use Excel 2007 for large sheets. sheetToCreate = sheetToCreate.ToLower().Replace(".xls", string.Empty) + ".xlsx"; } string strCn = string.Empty; string ext = System.IO.Path.GetExtension(sheetToCreate).ToLower(); if (ext == ".xls") { strCn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sheetToCreate + "; Extended Properties='Excel 8.0;HDR=YES'"; } if (ext == ".xlsx") { strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sheetToCreate + ";Extended Properties='Excel 12.0 Xml;HDR=YES' "; } if (ext == ".xlsb") { strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sheetToCreate + ";Extended Properties='Excel 12.0;HDR=YES' "; } if (ext == ".xlsm") { strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sheetToCreate + ";Extended Properties='Excel 12.0 Macro;HDR=YES' "; } cn = new System.Data.OleDb.OleDbConnection(strCn); System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(queryCreateExcelTable, cn); cn.Open(); cmd.ExecuteNonQuery(); System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [" + tableName + "]", cn); System.Data.OleDb.OleDbCommandBuilder cb = new System.Data.OleDb.OleDbCommandBuilder(da); //creates the INSERT INTO command cb.QuotePrefix = "["; cb.QuoteSuffix = "]"; cmd = cb.GetInsertCommand(); //gets a hold of the INSERT INTO command. foreach (DataRow row in selectedRows) { foreach (System.Data.OleDb.OleDbParameter param in cmd.Parameters) { param.Value = row[colNames[param.SourceColumn.Replace('#', '.')]]; } cmd.ExecuteNonQuery(); //INSERT INTO command. } cn.Close(); cn.Dispose(); da.Dispose(); GC.Collect(); GC.WaitForPendingFinalizers(); status = true; } catch (Exception ex) { status = false; if (cn.State == ConnectionState.Open) { cn.Close(); } } return(status); }
protected void ImportVisitDocument(object sender, EventArgs e) { System.Data.OleDb.OleDbConnection cnnDest = new System.Data.OleDb.OleDbConnection(); System.Data.OleDb.OleDbCommand cmdDest; Char[] delimiter = { '\\' }; String[] SrcFileArr; String SrcFileID = ""; String SrcFileName = ""; String SrcDocumentName = ""; String SrcUploadDate = ""; String SrcPatientID = ""; String SrcEventID = "0"; //0 for baseline, consultid for visit String SrcEventLink = "V"; //V, B or O String SrcEventDate = "0"; String SrcFullFilePath = ""; String SrcFolderPath = ""; String DestFileName = ""; strLapdataPath = ""; strLapdataPath = txtFolder.Text; String startRecord = txtStartRecord.Text; String endRecord = txtEndRecord.Text; startRecord = startRecord.Trim(); endRecord = endRecord.Trim(); int startNum = 0; int endNum = 0; int count = 0; String strDocID = ""; //notes int fileNum = 0; int fileSuccessNum = 0; int fileFailNum = 0; String fileFail = ""; String baselineNotes = ""; txtErrNotes.InnerHtml = ""; String strMDBPath = strLapdataPath + "\\Lapdata.mdb"; //String strMDBPath = "C:\\Lapdata\\Lapdata.mdb;"; if (startRecord == "" || startRecord == "0") { txtErrNotes.InnerHtml = "Warning: Please enter the Starting record number on visit file"; } else if (endRecord == "" || endRecord == "0") { txtErrNotes.InnerHtml = "Warning: Please enter the Ending record number on visit file"; } else { startNum = Convert.ToInt32(startRecord); endNum = Convert.ToInt32(endRecord); if (endNum > startNum) { try { if (strLapdataPath == "") { txtErrNotes.InnerHtml = "Warning: Please enter the Lapdata folder path"; } cnnDest.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" + strMDBPath; cnnDest.Open(); cmdDest = cnnDest.CreateCommand(); cmdDest.CommandType = CommandType.Text; cmdDest.CommandText = strSqlConsult; OleDbDataReader drSource = cmdDest.ExecuteReader(CommandBehavior.Default); while (drSource.Read()) { count++; if (count >= startNum && count <= endNum) { FileSize = 0; strDocumentName = ""; SrcFileType = 0; SrcPatientID = drSource["Patient Id"].ToString(); SrcEventID = drSource["ConsultId"].ToString(); SrcEventDate = drSource["DateSeen"].ToString(); SrcUploadDate = drSource["VideoDate"].ToString(); SrcFileName = drSource["VideoLocation"].ToString(); SrcDocumentName = drSource["VideoName"].ToString(); SrcEventLink = "V"; //call function to copy file DestFileName = copyFile(SrcFileName, "IMAGES"); //save to db strDocID = SaveVisitDocumentData(SrcPatientID, SrcEventID, SrcEventDate, SrcEventLink, SrcUploadDate, SrcFileType, DestFileName, SrcDocumentName); if (strDocID != "") { gClass.AddDocumentEventLog(gClass.OrganizationCode, Convert.ToInt32(strDocID), 1, Convert.ToInt64(Request.Cookies["UserPracticeCode"].Value), Request.Url.Host, "Upload visit file"); } //to be diplayed on notes fileNum++; if (strDocID != "") { fileSuccessNum++; } else { fileFailNum++; fileFail += SrcEventID + " - "; } } } baselineNotes = "Finish Uploading Visit Video File\n"; baselineNotes += "-------------------------------------\n"; baselineNotes += "Record number: " + startNum.ToString() + " - " + endNum.ToString() + "\n"; baselineNotes += "Total file: " + fileNum + "\n"; baselineNotes += "Success: " + fileSuccessNum + "\n"; baselineNotes += "Failed: " + fileFailNum + "\n"; baselineNotes += "Failed File (Consult ID): " + fileFail + "\n\n\n"; //update notes txtNotes.Value = txtNotes.Value + baselineNotes; } catch (Exception err) { ExportFlag = false; gClass.AddErrorLogData(Request.Cookies["UserPracticeCode"].Value, Request.Url.Host, Request.Cookies["Logon_UserName"].Value, "Import/Export data Form", "ExportFromSqlServerToMDB function", strSqlConsult + " ... " + err.ToString()); } finally { cnnDest.Close(); cnnDest.Dispose(); } } else { txtErrNotes.InnerHtml = "Warning: Please enter the proper range"; } } return; }
protected void ImportVisitImageDocument(object sender, EventArgs e) { Char[] delimiter = { '\\' }; String[] SrcFileArr; String SrcFileID = ""; String SrcFileName = ""; String SrcDocumentName = ""; String SrcUploadDate = ""; String SrcPatientID = ""; String SrcEventID = "0"; //0 for baseline, consultid for visit String SrcEventLink = "V"; //V, B or O String SrcEventDate = "0"; String SrcFullFilePath = ""; String SrcFolderPath = ""; String DestFileName = ""; strLapdataPath = ""; strLapdataPath = txtFolder.Text; int count = 0; String strDocID = ""; //notes int fileNum = 0; int fileSuccessNum = 0; int fileFailNum = 0; String fileFail = ""; String baselineNotes = ""; txtErrNotes.InnerHtml = ""; String strMDBPath = strLapdataPath + "\\Lapdata.mdb"; try { if (strLapdataPath == "") { txtErrNotes.InnerHtml = "Warning: Please enter the Lapdata folder path"; } for (int imageCount = 1; imageCount <= 3; imageCount++) { System.Data.OleDb.OleDbConnection cnnDest = new System.Data.OleDb.OleDbConnection(); System.Data.OleDb.OleDbCommand cmdDest; cnnDest.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" + strMDBPath; cnnDest.Open(); cmdDest = cnnDest.CreateCommand(); cmdDest.CommandType = CommandType.Text; switch (imageCount) { case 1: cmdDest.CommandText = strSqlConsultImage; break; case 2: cmdDest.CommandText = strSqlConsultImage1; break; case 3: cmdDest.CommandText = strSqlConsultImage2; break; } OleDbDataReader drSource = cmdDest.ExecuteReader(CommandBehavior.Default); while (drSource.Read()) { count++; FileSize = 0; strDocumentName = ""; SrcFileType = 0; SrcPatientID = drSource["Patient Id"].ToString(); SrcEventID = drSource["ConsultId"].ToString(); SrcEventDate = drSource["DateSeen"].ToString(); SrcUploadDate = drSource["ImageDate"].ToString(); SrcFileName = drSource["ImageLocation"].ToString(); SrcDocumentName = drSource["ImageName"].ToString(); SrcEventLink = "V"; //call function to copy file DestFileName = copyFile(SrcFileName, "PHOTOS"); //save to db strDocID = SaveVisitDocumentData(SrcPatientID, SrcEventID, SrcEventDate, SrcEventLink, SrcUploadDate, SrcFileType, DestFileName, SrcDocumentName); if (strDocID != "") { gClass.AddDocumentEventLog(gClass.OrganizationCode, Convert.ToInt32(strDocID), 1, Convert.ToInt64(Request.Cookies["UserPracticeCode"].Value), Request.Url.Host, "Upload visit file"); } //to be diplayed on notes fileNum++; if (strDocID != "") { fileSuccessNum++; } else { fileFailNum++; fileFail += SrcEventID + " - "; } } cnnDest.Close(); cnnDest.Dispose(); } baselineNotes = "Finish Uploading Visit Image File\n"; baselineNotes += "-------------------------------------\n"; baselineNotes += "Total file: " + fileNum + "\n"; baselineNotes += "Success: " + fileSuccessNum + "\n"; baselineNotes += "Failed: " + fileFailNum + "\n"; baselineNotes += "Failed File (Consult ID): " + fileFail + "\n\n\n"; //update notes txtNotes.Value = txtNotes.Value + baselineNotes; } catch (Exception err) { ExportFlag = false; gClass.AddErrorLogData(Request.Cookies["UserPracticeCode"].Value, Request.Url.Host, Request.Cookies["Logon_UserName"].Value, "Import/Export data Form", "ImportVisitImageDocument function", strSqlConsult + " ... " + err.ToString()); } return; }
private void button7_Click(object sender, EventArgs e) { OpenFileDialog openFileDialog1 = new OpenFileDialog(); openFileDialog1.InitialDirectory = System.Environment.CurrentDirectory; openFileDialog1.Filter = "xls files (*.xls)|*.xls"; openFileDialog1.FilterIndex = 2; openFileDialog1.RestoreDirectory = true; if (openFileDialog1.ShowDialog() == DialogResult.OK) { DataTable dt = dataSet.Tables["pointlog"].Clone(); //連線字串 System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection( "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + openFileDialog1.FileName + ";" + "Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\""); connection.Open(); try { //dataGridView2.Columns["number"].HeaderText = "學號"; //dataGridView2.Columns["stu_class"].HeaderText = "班級"; //dataGridView2.Columns["name"].HeaderText = "姓名"; //dataGridView2.Columns["date"].HeaderText = "日期"; //dataGridView2.Columns["pt_case"].HeaderText = "獎勵事由"; //dataGridView2.Columns["point"].HeaderText = "獎勵點數"; string query = "select 學號,班級,姓名,日期,獎勵事由,獎勵點數 from [Sheet1$]"; System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(query, connection); adapter.Fill(dt); connection.Close(); connection.Dispose(); //dt.PrimaryKey = new DataColumn[] { dt.Columns["學號"] }; string TMPMSG = ""; for (int i = 0; i < dt.Rows.Count; i++) { DataRow newRow = dataSet.Tables["pointlog"].NewRow(); newRow["number"] = dt.Rows[i]["學號"]; newRow["stu_class"] = dt.Rows[i]["班級"]; newRow["name"] = dt.Rows[i]["姓名"]; newRow["date"] = dt.Rows[i]["日期"]; newRow["pt_case"] = dt.Rows[i]["獎勵事由"]; newRow["point"] = dt.Rows[i]["獎勵點數"]; dataSet.Tables["pointlog"].Rows.Add(newRow); if (i < 30) { TMPMSG += string.Format("添加 {0},{1},{2},{3},{4},{5}\r\n", newRow["stu_class"], newRow["number"], newRow["name"], newRow["date"], newRow["pt_case"], newRow["point"]); } } TMPMSG += string.Format("匯入工作一共處理{0}筆資料", dt.Rows.Count); MessageBox.Show(TMPMSG); dataAdapter2.Update(dataSet, "pointlog"); dataSet.AcceptChanges(); GetNewData(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } }
protected void ExportFromSqlServerToMDB(object sender, EventArgs e) { System.Data.OleDb.OleDbConnection cnnDest = new System.Data.OleDb.OleDbConnection(); System.Data.OleDb.OleDbCommand cmdDest; Char[] delimiter = { '\\' }; String[] SrcFileArr; String strSql = ""; String SrcFileID = ""; String SrcFileName = ""; String SrcDocumentName = ""; String SrcUploadDate = ""; String SrcPatientID = ""; String SrcEventID = "0"; //0 for baseline, consultid for visit String SrcEventLink = "B"; //V, B or O String SrcFullFilePath = ""; String SrcFolderPath = ""; String DestFileName = ""; strLapdataPath = ""; strLapdataPath = txtFolder.Text; String strDocID = ""; //notes int fileNum = 0; int fileSuccessNum = 0; int fileFailNum = 0; String fileFail = ""; String baselineNotes = ""; txtErrNotes.InnerHtml = ""; //need to escape " String strMDBPath = strLapdataPath + "\\Lapdata.mdb"; //String strMDBPath = "C:\\Lapdata\\Lapdata.mdb;"; try { if (strLapdataPath == "") { txtErrNotes.InnerHtml = "Warning: Please enter the Lapdata folder path"; } cnnDest.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" + strMDBPath; cnnDest.Open(); cmdDest = cnnDest.CreateCommand(); cmdDest.CommandType = CommandType.Text; strSql = "Select * from tblDocuments where LinkName is not null and LinkName <> ''"; cmdDest.CommandText = strSql; OleDbDataReader drSource = cmdDest.ExecuteReader(CommandBehavior.Default); while (drSource.Read()) { FileSize = 0; strDocumentName = ""; SrcFileType = 0; //for every data on tblDocuments in mdb file, upload file SrcFileID = drSource["LinikId"].ToString(); SrcFullFilePath = drSource["LinkName"].ToString(); SrcDocumentName = drSource["Description"].ToString(); SrcUploadDate = drSource["Date"].ToString(); SrcPatientID = drSource["PatientId"].ToString(); SrcEventID = "0"; SrcEventLink = "B"; //remove # at the end of filename if (SrcFullFilePath.Substring((SrcFullFilePath.Length) - 1) == "#") { SrcFullFilePath = SrcFullFilePath.Replace("#", ""); } //split file path SrcFileArr = SrcFullFilePath.Split(delimiter); //get filename and folder path SrcFileName = SrcFileArr[(SrcFileArr.Length) - 1]; SrcFolderPath = SrcFileArr[(SrcFileArr.Length) - 2]; //call function to copy file DestFileName = copyFile(SrcFileName, SrcFolderPath); //save to db strDocID = SaveVisitDocumentData(SrcPatientID, SrcEventID, SrcUploadDate, SrcEventLink, SrcUploadDate, SrcFileType, DestFileName, SrcDocumentName); if (strDocID != "") { gClass.AddDocumentEventLog(gClass.OrganizationCode, Convert.ToInt32(strDocID), 1, Convert.ToInt64(Request.Cookies["UserPracticeCode"].Value), Request.Url.Host, "Upload file"); } //to be diplayed on notes fileNum++; if (strDocID != "") { fileSuccessNum++; } else { fileFailNum++; fileFail += SrcFileID + " - "; } } baselineNotes = "Finish Uploading baseline file\n"; baselineNotes += "-------------------------------------\n"; baselineNotes += "Total file: " + fileNum + "\n"; baselineNotes += "Success: " + fileSuccessNum + "\n"; baselineNotes += "Failed: " + fileFailNum + "\n"; baselineNotes += "Failed File (Link ID): " + fileFail + "\n\n\n"; //update notes txtNotes.Value = txtNotes.Value + baselineNotes; } catch (Exception err) { ExportFlag = false; gClass.AddErrorLogData(Request.Cookies["UserPracticeCode"].Value, Request.Url.Host, Request.Cookies["Logon_UserName"].Value, "Import/Export data Form", "ExportFromSqlServerToMDB function", strSql + " ... " + err.ToString()); } finally { cnnDest.Close(); cnnDest.Dispose(); } return; }
private DataTable ReadData() { string ConnectionString = ""; if (this.Context.FileType == ImportFileType.excelx || this.Context.FileType == ImportFileType.excel) { System.Data.OleDb.OleDbConnectionStringBuilder builder = new System.Data.OleDb.OleDbConnectionStringBuilder(); builder.DataSource = this.Context.DataLocation + this.Context.DataFilePath; builder.Provider = "Microsoft.ACE.OLEDB.12.0"; if (this.Context.FileType == ImportFileType.excel) { builder.Add("Extended Properties", "Excel 8.0;HDR=Yes;IMEX=1"); } else { builder.Add("Extended Properties", "Excel 12.0;HDR=Yes;IMEX=1"); } ConnectionString = builder.ConnectionString; var con = new System.Data.OleDb.OleDbConnection(ConnectionString); System.Data.DataSet ds = new System.Data.DataSet(); System.Data.DataTable dt = new DataTable(); con.Open(); var dtSheets = new System.Data.DataTable(); dtSheets = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (dtSheets.Rows.Count == 0) { throw new Exception("No Sheet exist in the excel file"); } else if (dtSheets.Rows[0].IsNull(2)) { throw new Exception("No Data Avaiable"); } System.Data.OleDb.OleDbDataAdapter MyCommand; MyCommand = new System.Data.OleDb.OleDbDataAdapter("Select * from " + "[" + dtSheets.Rows[0][2] + "]", con); // MyCommand.TableMappings.Add("Table", "TestTable"); MyCommand.Fill(ds); dt = ds.Tables[0]; List <DataColumn> dc = new List <DataColumn>(); foreach (DataColumn c in dt.Columns) { if (c.DataType == typeof(string)) { dc.Add(c); } } con.Close(); con.Dispose(); foreach (DataColumn c in dc) { foreach (DataRow dr in dt.Rows) { if (dr[c.ColumnName] != null) { dr[c.ColumnName] = dr[c.ColumnName].ToString().Trim(); } } } return(dt); } else if (this.Context.FileType == ImportFileType.csv) { ConnectionString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + this.Context.DataLocation + this.Context.DataFilePath + ";Extensions=asc,csv,tab,txt;"; } return(new DataTable()); }
private void ExportFromSqlServerToMDB(string strMDBPath) { System.Data.OleDb.OleDbConnection cnnDest = new System.Data.OleDb.OleDbConnection(); System.Data.SqlClient.SqlConnection cnnSource = new System.Data.SqlClient.SqlConnection(); System.Data.OleDb.OleDbCommand cmdDest; System.Data.SqlClient.SqlCommand cmdSource; System.Data.SqlClient.SqlDataReader drSource; string[,] strTablesName = { { "tblCodes", "Codes" }, { "tblDoctors", "Doctors" }, { "tblIdealWeights", "IdealWeights" }, { "tblSystemCodes", "SystemCodes" }, { "tblSystemDetails", "SystemDetails" }, { "tblSystemNormals", "tblSystemNormals" }, { "tblHospitals", "Hospitals" }, { "tblReferringDoctors", "ReferringDoctors" }, { "tblPatients", "Patients" }, { "tblPatientConsult", "PatientConsult" }, { "tblOpEvents", "OpEvents" }, { "tblPatientWeightData", "PatientWeightData" }, { "tblComplications", "tblComplications" } }; String strSql = "", strSql_Source = String.Empty; String selectCodes = " UserPracticeCode, Code, CategoryCode, Description, Field2 "; String selectDoctors = " OrganizationCode, UserPracticeCode, DoctorID, Surname, Firstname, Initial, Title, DoctorName, Address1, Address2, Suburb, Postcode, State, Country, Telephone, Fax, Degrees, Speciality, UseOwnLetterHead, PrefSurgeryType, PrefApproach, PrefCategory, CountryCode, LapBandCode, OtherType, IsSurgeon, Hide "; String selectIdealWeights = " OrganizationCode, UserPracticeCode, Height, IdealWeight, IdealWeightFemale "; String selectSystemCodes = " distinct 0 as UserPracticeCode, Code, CategoryCode, Score, Description, Description2 "; String selectSystemDetails = " SystemId, SystemName, SystemType, DateInstalled, UpdateDate, CountryCode, SystemCode, Imperial, WOScale, TargetBMI, ReferenceBMI, BackUpLocation, MFU3, MFU6, MFU12, FU1Y, FU2Y, FU3Y, FU4Y, FU1, FU2, FU3, RD1, RD2, CV, UseRace, EWPerCent, DateCreated, CreatedByUser, CreatedByComputer, LastModified, ModifiedByUser, ModifiedByComputer, ComordityVisitMonths, IdealOnBMI, FUPNotes, FUCom, FUinv, PatCOM, PatInv, UserPracticeCode, VisitWeeksFlag, OrganizationCode "; String selectSystemNormals = " UserPracticeCode, Code, TestType, ImperialLow, ImperialLow_F, ImperialHigh, ImperialHigh_F, ImperialUnits, MetricLow, MetricLow_F, MetricHigh, MetricHigh_F, MetricUnits, ConversionImpToMetric, Description "; String selectHospitals = " OrganizationCode, UserPracticeCode, [Hospital Id], [Hospital Name], Street, Suburb, PostCode, Phone, Fax "; String selectReferringDoctors = " OrganizationCode, UserPracticeCode, RefDrId, Surname, FirstName, Title, UseFirst, Address1, Address2, Suburb, PostalCode, State, Phone, Fax "; String selectPatient = " OrganizationCode, UserPracticeCode, CASE WHEN dbo.fn_GetPatientCustomID([Patient Id], OrganizationCode) IS NULL OR dbo.fn_GetPatientCustomID([Patient Id], OrganizationCode) = '' OR dbo.fn_GetPatientCustomID([Patient Id], OrganizationCode) = '0' THEN cast([Patient ID] as varchar(50)) ELSE dbo.fn_GetPatientCustomID([Patient Id], OrganizationCode) END AS [Patient Id], [Name Id], [Reference Id], Surname, Firstname, Title, Street, Suburb, State, Postcode, [Home Phone], [Work Phone], MobilePhone, EmailAddress, Birthdate, Sex, Race, Insurance, [Doctor Id], [Date First Visit], [Date Last Visit], RefDrId1, RefDrId2, RefDrId3, [Select], Select2, Select3, [Date Next Visit], DateCreated, CreatedByUser, CreatedByComputer, LastModified, ModifiedByUser, ModifiedByComputer, RemoteDrId, CreatedByWindowsUser, ModifiedByWindowsUser, WebExport, [Marital status], ReferralDate, ReferralDuration, [Patient MD Id], SocialHistory "; String selectPatientConsult = " A.ConsultID, A.OrganizationCode, A.UserPracticeCode, CASE WHEN dbo.fn_GetPatientCustomID(A.[Patient Id], A.OrganizationCode) IS NULL OR dbo.fn_GetPatientCustomID(A.[Patient Id], A.OrganizationCode) = '' OR dbo.fn_GetPatientCustomID(A.[Patient Id], A.OrganizationCode) = '0' THEN cast(A.[Patient ID] as varchar(50)) ELSE dbo.fn_GetPatientCustomID(A.[Patient Id], A.OrganizationCode) END AS [Patient Id], A.ConsultType, A.DateSeen, A.VisitType, A.CoMorbidityVisit, A.Seenby, A.Height, A.Weight, A.WaistCircumference, A.HipCircumference, A.SagittalDiameter, A.ReportSent, A.ReservoirVolume, A.UpdateD, A.Months, A.BMIWeight, A.DateNextVisit, A.BloodPressure, A.Neck, A.Hip, A.Waist, A.ImageName, A.Image, A.ImageLocation, A.ImageDate, A.Image1, A.ImageLocation1, A.ImageName1, A.ImageDate1, A.Image2, A.ImageLocation2, A.ImageName2, A.ImageDate2, A.Video, A.VideoLocation, A.VideoName, A.VideoResult, A.VideoDate, A.WeightLastVisit, A.WeeksLastVisit, A.BMR, A.Impedance, A.FatPerCent, A.FreeFatMass, A.TotalBodyWater, A.FirstVisitWeight, A.SystolicBP, A.DiastolicBP, A.BPRxDetails, A.Triglycerides, A.TotalCholesterol, A.HDLCholesterol, A.LDLCholesterol, A.LipidRxDetails, A.HBA1C, A.FSerumInsulin, A.FBloodGlucose, A.DiabetesRxDetails, A.Hemoglobin, A.Platelets, A.WCC, A.Iron, A.Ferritin, A.Transferrin, A.IBC, A.Folate, A.B12, A.Sodium, A.Potassium, A.Chloride, A.Bicarbonate, A.Urea, A.Creatinine, A.Homocysteine, A.TSH, A.T4, A.T3, A.Albumin, A.Calcium, A.Phosphate, A.VitD, A.Bilirubin, A.AlkPhos, A.ALT, A.AST, A.GGT, A.TProtein, A.UserField1, A.UserField2, A.UserField3, A.UserField4, A.UserField5, A.AsthmaLevel, A.RefluxLevel, A.SleepLevel, A.FertilityLevel, A.IncontinenceLevel, A.BackLevel, A.ArthritisLevel, A.CVDLevel, A.DateCreated, A.CreatedByUser, A.CreatedByComputer, A.LastModified, A.ModifiedByUser, A.ModifiedByComputer, A.CreatedByWindowsUser, A.ModifiedByWindowsUser, A.OtherRxDetails, A.HypertensionProblems, A.LipidRx, A.DiabetesRx, A.BloodPressureRx, A.OtherRx, A.LipidProblems, A.AsthmaProblems, A.SleepProblems, A.IncontinenceProblems, A.BackProblems, A.ArthritisProblems, A.FertilityProblems, A.RefluxProblems, A.DiabetesProblems, A.UserMemoField1, A.UserMemoField2, A.Notes2, A.Notes, A.DateDeleted, A.DeletedByUser, A.PulseRate, A.RespiratoryRate, A.BloodPressureUpper, A.BloodPressureLower, A.GeneralReview, A.CardiovascularReview, A.RespiratoryReview, A.GastroReview, A.GenitoReview, A.ExtremitiesReview, A.NeurologicalReview, A.SatietyStaging, A.ChiefComplaint, A.MusculoskeletalReview, A.SkinReview, A.PsychiatricReview, A.EndocrineReview, A.HematologicReview, A.ENTReview, A.EyesReview, A.PFSHReview, A.MedicationsReview, A.LapbandAdjustment, A.MedicalProvider, A.AdjConsent, A.AdjAntiseptic, A.AdjAnesthesia, A.AdjAnesthesiaVol, A.AdjNeedle, A.AdjVolume, A.AdjInitialVol, A.AdjAddVol, A.AdjRemoveVol, A.AdjTolerate "; String selectOpEvents = " A.OrganizationCode, A.AdmitId, A.UserPracticeCode, CASE WHEN dbo.fn_GetPatientCustomID([PatientId], A.OrganizationCode) IS NULL OR dbo.fn_GetPatientCustomID([PatientId], A.OrganizationCode) = '' OR dbo.fn_GetPatientCustomID([PatientId], A.OrganizationCode) = '0' THEN cast([Patient ID] as varchar(50)) ELSE dbo.fn_GetPatientCustomID([PatientId], A.OrganizationCode) END AS PatientId, A.AdmitDate, A.OpWeight, A.HospitalCode, A.SurgeonId, A.OperationDate, A.Duration, A.DaysInHospital, A.SurgeryType, A.Approach, A.Category, A.[Group], A.StartNeck, A.StartWaist, A.StartHip, A.BandSize, A.ReservoirSite, A.BalloonVolume, A.Pathway, A.Indication, A.[Procedure], A.Findings, A.Closure, A.BloodLoss, A.RouxLimbLength, A.RouxColic, A.RouxGastric, A.RouxEnterostomy, A.Banded, A.VBGStomaSize, A.VBGStomaWrap, A.BPDStomachSize, A.BPDIlealLength, A.BPDChannelLength, A.BPDDuodenalSwitch, A.TubeSize, A.PreviousSurgery, A.PrevAbdoSurgery1, A.PrevAbdoSurgery2, A.PrevAbdoSurgery3, A.PrevAbdoSurgeryNotes, A.PrevPelvicSurgery, A.PrevPelvicSurgery1, A.PrevPelvicSurgery2, A.PrevPelvicSurgery3, A.PrevPelvicSurgeryNotes, A.ComcomitantSurgery, A.ComcomitantSurgery1, A.ComcomitantSurgery2, A.ComcomitantSurgery3, A.ComcomitantSurgeryNotes, A.DateCreated, A.CreatedByUser, A.CreatedByComputer, A.LastModified, A.ModifiedByUser, A.ModifiedByComputer, A.CreatedByWindowsUser, A.ModifiedByWindowsUser, A.GeneralNotes, A.DateDeleted, A.DeletedByUser "; String selectPatientWeightData = " A.OrganizationCode, A.UserPracticeCode, CASE WHEN dbo.fn_GetPatientCustomID(A.[Patient Id], A.OrganizationCode) IS NULL OR dbo.fn_GetPatientCustomID(A.[Patient Id], A.OrganizationCode) = '' OR dbo.fn_GetPatientCustomID(A.[Patient Id], A.OrganizationCode) = '0' THEN cast(A.[Patient ID] as varchar(50)) ELSE dbo.fn_GetPatientCustomID(A.[Patient Id], A.OrganizationCode) END AS [Patient Id], A.LapBandDate, A.Notes, A.Height, A.StartWeight, A.StartWeightDate, A.IdealWeight, A.CurrentWeight, A.OpWeight, A.TargetWeight, A.Excluded, A.LastReservoirVol, A.losttofollowup, A.StartBMIWeight, A.BMIHeight, A.LastImageLocation, A.LastImageDate, A.SurgeryType, A.Approach, A.Category, A.[Group], A.BaseAssessmentDate, A.BaseOtherDetails, A.BaseHypertensionProblems, A.BaseBloodPressureRxDetails, A.BaseSystolicBP, A.BaseDiastolicBP, A.HypertensionResolved, A.HypertensionResolvedDate, A.HypertensionResolvedSystolic, A.HypertensionResolvedDiastolic, A.BaseLipidProblems, A.BaseLipidRxDetails, A.BaseTriglycerides, A.BaseTotalCholesterol, A.BaseHDLCholesterol, A.LipidsResolved, A.LipidsResolvedDate, A.BaseDiabetesProblems, A.BaseDiabetesRxDetails, A.DiabetesResolved, A.DiabetesResolvedDate, A.DiabetesResolvedFBglucose, A.BaseAsthmaProblems, A.BaseAsthmaLevel, A.BaseAsthmaDetails, A.AsthmaResolved, A.AsthmaResolvedDate, A.AsthmaResolvedLevel, A.AsthmaCurrentLevel, A.BaseRefluxProblems, A.BaseRefluxDetails, A.BaseRefluxLevel, A.RefluxResolved, A.RefluxResolvedDate, A.RefluxResolvedLevel, A.RefluxCurrentLevel, A.BaseOtherRx, A.BaseOtherRxDetails, A.BaseSleepProblems, A.BaseSleepDetails, A.BaseSleepLevel, A.SleepResolved, A.SleepResolvedDate, A.SleepResolvedLevel, A.SleepCurrentLevel, A.BaseFertilityProblems, A.BaseFertilityLevel, A.BaseFertilityDetails, A.FertilityResolved, A.FertilityResolvedDate, A.FertilityResolvedLevel, A.FertilityCurrentLevel, A.BaseArthritisProblems, A.BaseArthritisDetails, A.BaseArthritisLevel, A.ArthritisResolved, A.ArthritisResolvedDate, A.ArthritisResolvedLevel, A.ArthritisCurrentLevel, A.BaseIncontinenceProblems, A.BaseIncontinenceDetails, A.BaseIncontinenceLevel, A.IncontinenceResolved, A.IncontinenceResolvedDate, A.IncontinenceResolvedLevel, A.IncontinenceCurrentLevel, A.BaseBackProblems, A.BaseBackDetails, A.BaseBackPainLevel, A.BackResolved, A.BackResolvedDate, A.BackResolvedLevel, A.BackCurrentLevel, A.BaseCVDProblems, A.BaseCVDDetails, A.BaseCVDLevel, A.CVDLevelResolved, A.CVDLevelResolvedDate, A.CVDLevelCurrentLevel, A.CVDLevelResolvedLevel, A.StartNeck, A.StartWaist, A.StartHip, A.BaseBodyFat, A.BaseFatMass, A.BaseBMR, A.BaseImpedance, A.BaseFatPerCent, A.BaseFreeFatMass, A.BaseTotalBodyWater, A.BaseHomocysteine, A.BaseTSH, A.BaseT4, A.BaseT3, A.BaseHBA1C, A.BaseFSerumInsulin, A.BaseFBloodGlucose, A.BaseIron, A.BaseFerritin, A.BaseTransferrin, A.BaseIBC, A.BaseFolate, A.BaseB12, A.BaseHemoglobin, A.BasePlatelets, A.BaseWCC, A.BaseCalcium, A.BasePhosphate, A.BaseVitD, A.BaseBilirubin, A.BaseTProtein, A.BaseAlkPhos, A.BaseALT, A.BaseAST, A.BaseGGT, A.BaseCPK, A.BaseAlbumin, A.BaseSodium, A.BasePotassium, A.BaseChloride, A.BaseBicarbonate, A.BaseUrea, A.BaseCreatinine, A.BaseUserField1, A.BaseUserField2, A.BaseUserField3, A.BaseUserField4, A.BaseUserField5, A.DateCreated, A.NextComorbidVisit, A.ComorbidityMonths, A.ComorbidtyOnFile, A.OpBMIWeight, A.BaseLDLCholesterol, A.BaseUserMemoField1, A.BaseUserMemoField2, A.BaseReason7, A.BaseReason8, A.BMI, A.BaseHDLCholesterolProblems, A.BaseTriglycerideProblems, A.BaseBaselineHStatus, A.BaseCholesterolProblems, A.BaseOtherProblems, A.BaseDiabetesRx, A.BaseLipidRx, A.BaseBloodPressureRx, A.BaseBloodGlucoseLevel, A.FirstVisitWeight, A.BaseBMC, A.BaseLeanBodyMass, A.BaseBodyFatPC, A.BaseBMIPercentile, A.BaseZScore, A.COMData, A.Status, A.MaxWeightYr, A.MaxWeight, A.Updated, A.PatientReport, A.ProcedureReport, A.TempFlag, StartPR, StartRR, StartBP1, StartBP2, A.ZeroDate "; String selectComplications = " A.ComplicationNum, A.OrganizationCode, A.UserPracticeCode, CASE WHEN dbo.fn_GetPatientCustomID(A.[PatientID], A.OrganizationCode) IS NULL OR dbo.fn_GetPatientCustomID(A.[PatientID], A.OrganizationCode) = '' OR dbo.fn_GetPatientCustomID(A.[PatientID], A.OrganizationCode) = '0' THEN cast([Patient ID] as varchar(50)) ELSE dbo.fn_GetPatientCustomID(A.[PatientID], A.OrganizationCode) END AS PatientID, A.ComplicationDate, A.ComplicationCode, A.TypeCode, A.Readmitted, A.DateCreated, A.CreatedByUser, A.CreatedByWindowsUser, A.CreatedByComputer, A.LastModified, A.ModifiedByUser, A.ModifiedByWindowsUser, A.ModifiedByComputer, A.ReOperation, A.Notes, A.Complication, A.FacilityCode, A.Facility_Other, A.AdverseSurgery, A.DoctorID "; try { cnnSource.ConnectionString = GlobalClass.strSqlCnnString; cnnDest.ConnectionString = @"PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" + strMDBPath; cnnSource.Open(); cnnDest.Open(); cmdDest = cnnDest.CreateCommand(); cmdSource = cnnSource.CreateCommand(); cmdSource.CommandType = CommandType.Text; cmdDest.CommandType = CommandType.Text; for (int Xh = 0; Xh < strTablesName.Length / strTablesName.Rank; Xh++) { switch (strTablesName[Xh, 0]) { case "tblPatients": cmdSource.CommandText = "Select " + selectPatient + " from " + strTablesName[Xh, 0]; cmdSource.CommandText += " with(nowait, nolock) where DateDeleted is null AND OrganizationCode = " + gClass.OrganizationCode; break; case "tblOpEvents": cmdSource.CommandText = "Select " + selectOpEvents + " from " + strTablesName[Xh, 0] + " A with(nowait, nolock) "; cmdSource.CommandText += "inner join tblPatients B with(nowait, nolock) on A.[PatientID] = B.[Patient ID] and (A.OrganizationCode = B.OrganizationCode) "; cmdSource.CommandText += "where A.DateDeleted is NULL AND B.DateDeleted is NULL AND B.OrganizationCode = " + gClass.OrganizationCode; break; case "tblComplications": cmdSource.CommandText = "Select " + selectComplications + " from " + strTablesName[Xh, 0] + " A with(nowait, nolock) "; cmdSource.CommandText += "inner join tblPatients B with(nowait, nolock) on A.[PatientID] = B.[Patient ID] and (A.OrganizationCode = B.OrganizationCode) "; cmdSource.CommandText += "where B.DateDeleted is NULL AND B.OrganizationCode = " + gClass.OrganizationCode; break; case "tblPatientConsult": cmdSource.CommandText = "Select " + selectPatientConsult + " from " + strTablesName[Xh, 0] + " A with(nowait, nolock) "; cmdSource.CommandText += "inner join tblPatients B with(nowait, nolock) on (A.[Patient ID] = B.[Patient ID]) and (A.OrganizationCode = B.OrganizationCode) "; cmdSource.CommandText += "where A.DateDeleted is NULL AND B.DateDeleted is NULL AND B.OrganizationCode = " + gClass.OrganizationCode; break; case "tblPatientWeightData": cmdSource.CommandText = "Select " + selectPatientWeightData + " from " + strTablesName[Xh, 0] + " A with(nowait, nolock) "; cmdSource.CommandText += "inner join tblPatients B with(nowait, nolock) on (A.[Patient ID] = B.[Patient ID]) and (A.OrganizationCode = B.OrganizationCode) "; cmdSource.CommandText += "where B.DateDeleted is NULL AND B.OrganizationCode = " + gClass.OrganizationCode; break; case "tblCodes": cmdSource.CommandText = "Select " + selectCodes + " from " + strTablesName[Xh, 0] + " where UserPracticeCode = " + Request.Cookies["UserPracticeCode"].Value; break; case "tblSystemNormals": cmdSource.CommandText = "Select " + selectSystemNormals + " from " + strTablesName[Xh, 0] + " where UserPracticeCode = " + Request.Cookies["UserPracticeCode"].Value; break; case "tblSystemDetails": cmdSource.CommandText = "Select " + selectSystemDetails + " from " + strTablesName[Xh, 0] + " where OrganizationCode = " + gClass.OrganizationCode; break; case "tblSystemCodes": cmdSource.CommandText = "Select " + selectSystemCodes + " from " + strTablesName[Xh, 0]; break; case "tblDoctors": cmdSource.CommandText = "Select " + selectDoctors + " from " + strTablesName[Xh, 0] + " where OrganizationCode = " + gClass.OrganizationCode; break; case "tblIdealWeights": cmdSource.CommandText = "Select " + selectIdealWeights + " from " + strTablesName[Xh, 0] + " where OrganizationCode = " + gClass.OrganizationCode; break; case "tblHospitals": cmdSource.CommandText = "Select " + selectHospitals + " from " + strTablesName[Xh, 0] + " where OrganizationCode = " + gClass.OrganizationCode; break; case "tblReferringDoctors": cmdSource.CommandText = "Select " + selectReferringDoctors + " from " + strTablesName[Xh, 0] + " where OrganizationCode = " + gClass.OrganizationCode; break; } strSql_Source = cmdSource.CommandText; drSource = cmdSource.ExecuteReader(CommandBehavior.Default); while (drSource.Read()) { strSql = "insert into " + strTablesName[Xh, 1] + "( "; for (int Idx = 0; Idx < drSource.FieldCount; Idx++) { strSql += "[" + drSource.GetName(Idx) + "] , "; } strSql = strSql.Substring(0, strSql.Length - 2) + ") values ( "; for (int Idx = 0; Idx < drSource.FieldCount; Idx++) { switch (drSource.GetFieldType(Idx).ToString().ToUpper()) { case "SYSTEM.DATETIME": if (drSource.IsDBNull(Idx)) { strSql += "null, "; } else { string strDate = drSource.GetValue(Idx).ToString(); strDate = strDate.Substring(0, strDate.IndexOf(" ")); string[] tempDate = strDate.Split('/'); strSql += "#" + tempDate[1] + "/" + tempDate[0] + "/" + tempDate[2] + "#, "; } break; case "SYSTEM.STRING": if (drSource.IsDBNull(Idx)) { strSql += "'', "; } else { strSql += "'" + drSource.GetValue(Idx).ToString().Replace("'", "`") + "', "; } break; case "SYSTEM.BOOLEAN": strSql += (drSource.GetValue(Idx).ToString().Equals("TRUE") ? "-1" : "0") + ", "; break; default: if (drSource.IsDBNull(Idx)) { strSql += "0, "; } else { strSql += drSource.GetValue(Idx) + ", "; } break; } } strSql = strSql.Substring(0, strSql.Length - 2) + ")"; cmdDest.CommandText = strSql; cmdDest.ExecuteNonQuery(); } drSource.Close(); } ExportFlag = true; } catch (Exception err) { ExportFlag = false; gClass.AddErrorLogData(Request.Cookies["UserPracticeCode"].Value, Request.Url.Host, Request.Cookies["Logon_UserName"].Value, "Import/Export data Form", "ExportFromSqlServerToMDB function", strSql_Source + " " + strSql + " ... " + err.ToString()); } finally { cnnDest.Close(); cnnSource.Close(); cnnSource.Dispose(); cnnDest.Dispose(); } return; }