private void WriteJson(string status1, string msg1, object data1 = null)
{
Response.ContentType = "application/json";
var obj = new { status = status1, msg = msg1, data = data1 };
string json = new JavaScriptSerializer().Serialize(obj);
Response.Write(json);
}
private ADOX.Column[] CreateAdoXColumn<T>()
{
var properties = typeof(T).GetProperties();
List<ADOX.Column> columns = new List<ADOX.Column>();
DataTypeEnum typeEnum = DataTypeEnum.adSingle;
properties.ToList().ForEach(p =>
{
if (p.PropertyType == typeof(String)) typeEnum = DataTypeEnum.adLongVarWChar;
else if (p.PropertyType == typeof(Int32)) typeEnum = DataTypeEnum.adInteger;
else if (p.PropertyType == typeof(DateTime)) typeEnum = DataTypeEnum.adDate;
else if (p.PropertyType == typeof(Boolean)) typeEnum = DataTypeEnum.adBoolean;
else if (p.PropertyType == typeof(Double)) typeEnum = DataTypeEnum.adNumeric;
columns.Add(new ADOX.Column() { Name = p.Name, Type = typeEnum });
});
return columns.ToArray();
}
private void InsertToMdb<T>(string fileNameWithPath, ADOX.Column[] columns, IList<T> list)
{
var con = new OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " + fileNameWithPath);
try
{
con.Open();
var cmd = new OleDbCommand();
cmd.Connection = con;
string columnString = "";
string fmtColumnString = "";
foreach (var col in columns)
{
columnString += "[" + col.Name + "],";
fmtColumnString += "@" + col.Name + ",";
}
columnString = columnString.Substring(0, columnString.Length - 1);
fmtColumnString = fmtColumnString.Substring(0, fmtColumnString.Length - 1);
string tableName = typeof(T).Name;
string formatStr = "insert into {0}({1}) values({2});";
foreach (var item in list)
{
cmd.CommandText = string.Format(formatStr, tableName, columnString, fmtColumnString);
var properties = item.GetType().GetProperties();
foreach (var p in properties)
{
var column = columns.FirstOrDefault(c => c.Name.ToLower() == p.Name.ToLower());
cmd.Parameters.AddWithValue("@" + p.Name, p.GetValue(item, null));
}
cmd.ExecuteNonQuery();
}
//cmd.Parameters.AddWithValue("@ID", 1);
//cmd.Parameters.AddWithValue("@Info", "toster.ru");
//cmd.Parameters.AddWithValue("@text", "blabla");
}
catch (Exception ex)
{
}
finally
{
con.Close();
}
}
/// 在access数据库中创建表
/// </summary>
/// <param name="filePath">数据库表文件全路径如D:\\NewDb.mdb 没有则创建 </param>
/// <param name="tableName">表名</param>
/// <param name="colums">ADOX.Column对象数组</param>
private void CreateAccessTable(string filePath, string tableName, params ADOX.Column[] colums)
{
ADOX.Catalog catalog = new Catalog();
//数据库文件不存在则创建
if (!System.IO.File.Exists(filePath))
{
try
{
catalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Jet OLEDB:Engine Type=5");
}
catch (System.Exception ex)
{
}
}
ADODB.Connection cn = new ADODB.Connection();
cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath, null, null, -1);
catalog.ActiveConnection = cn;
ADOX.Table table = new ADOX.Table();
table.Name = tableName;
foreach (var column in colums)
{
table.Columns.Append(column);
}
// column.ParentCatalog = catalog;
//column.Properties["AutoIncrement"].Value = true; //设置自动增长
//table.Keys.Append("FirstTablePrimaryKey", KeyTypeEnum.adKeyPrimary, column, null, null); //定义主键
catalog.Tables.Append(table);
cn.Close();
}
[HttpPost]
public void ExportMdb()
{
string folder = "~/Exports/";
string filename = DateTime.Now.ToString("yyyyMMddHHmmssffff") + ".mdb";
var path = Path.Combine(Server.MapPath(folder), filename);
try
{
ISession aSession = SessionHelper.GetSession();
//电缆井
ADOX.Column[] columnsPit = CreateAdoXColumn<mycablepit>();
CreateAccessTable(path, "mycablepit", columnsPit);
ImycablepitService caServicePit = new mycablepitService(aSession);
var allCablesPit = caServicePit.getAll();
InsertToMdb<mycablepit>(path, columnsPit, allCablesPit);
WriteJson("true", folder.Replace("~/","") + filename);
}
catch (Exception ex)
{
WriteJson("false", "");
}
}
function exportDatabase(_type) {
$("#btnExportMdb").button('loading');
switch (_type) {
case "mdb":
var url = "/ExportDb/ExportMdb";
$.post(url, "", function (data) {
if (data.status == "true") {
$("#btnExportMdb").button('reset');
alert("导出成功");
//var $eleForm = $("<form method='get'></form>");
//$eleForm.attr("action", data.msg);
//$(document.body).append($eleForm);
//$eleForm.submit();
}
});
break;
}
}