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