public HttpResponseBase GetBoilerRelationList() { List<boilerrelationQuery> store = new List<boilerrelationQuery>(); string json = string.Empty; try { boilerrelationQuery query = new boilerrelationQuery(); query.Start = Convert.ToInt32(Request.Params["start"] ?? "0"); if (!string.IsNullOrEmpty(Request.Params["limit"])) { query.Limit = Convert.ToInt32(Request.Params["limit"]); } if (!string.IsNullOrEmpty(Request.Params["boiler_type_describe"])) { query.Boiler_type_describe = Request.Params["boiler_type_describe"].Trim(); } if (!string.IsNullOrEmpty(Request.Params["out_boiler_type"])) { query.out_boiler_number = Request.Params["out_boiler_type"].Trim(); } if (!string.IsNullOrEmpty(Request.Params["innner_boiler_type"])) { query.inner_boiler_number = Request.Params["innner_boiler_type"].Trim(); } _boillationMgr = new BoilerrelationMgr(mySqlConnectionString); int totalCount = 0; store = _boillationMgr.QueryBoilerRelationAll(query, out totalCount); IsoDateTimeConverter timeConverter = new IsoDateTimeConverter(); //这里使用自定义日期格式,如果不使用的话,默认是ISO8601格式 timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss"; //listUser是准备转换的对象 json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(store, Formatting.Indented, timeConverter) + ",}";//返回json數據 } catch (Exception ex) { Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage(); logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message); logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name; log.Error(logMessage); json = "{success:false,totalCount:0,data:[]}"; } this.Response.Clear(); this.Response.Write(json); this.Response.End(); return this.Response; }
public int GetintoBoilerrelation(DataRow[] dr, out int total) { int result = 0; total = 0; boilerrelationQuery billation = new boilerrelationQuery(); billation.add_user = (System.Web.HttpContext.Current.Session["caller"] as Caller).user_id; billation.add_time=DateTime.Now; StringBuilder sb = new StringBuilder(); MySqlCommand mySqlCmd = new MySqlCommand(); MySqlConnection mySqlConn = new MySqlConnection(connStr); try { if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed) { mySqlConn.Open(); } mySqlCmd.Connection = mySqlConn; mySqlCmd.Transaction = mySqlConn.BeginTransaction(); mySqlCmd.CommandType = System.Data.CommandType.Text; sb.Append("SET sql_safe_updates = 0;delete from boiler_relation where 1>0 ; ");//在執行插入之前先進行刪除操作 for (int i = 1; i < dr.Length; i++) { string strstring = string.Empty; try { strstring = dr[i][4].ToString(); if (!string.IsNullOrEmpty(strstring)) { string[] str = strstring.Replace("\n", "@").Split('@'); billation.boiler_type = str[0];//這個是對應安康內鍋型號 billation.boiler_describe = str[1];//對應安康內鍋型號詳細信息 types = billation.boiler_type; describes = billation.boiler_describe; state = 1;//獲取到了值 } else { billation.boiler_type = types;//如果為空則保存原來的值 billation.boiler_describe = describes; state = 0;//未獲取到值 } billation.inner_boiler_number = dr[i][3].ToString();//安康內鍋型號和描述 可能會出現為空的情況 if (!string.IsNullOrEmpty(billation.inner_boiler_number.Trim())) { inner_boiler_numbers = billation.inner_boiler_number.Trim(); string[] strinnerbn = billation.inner_boiler_number.Trim('\n').Replace("\n", "@").Replace("/", "@").Replace("/", "@").Split('@'); for (int j = 0; j < strinnerbn.Length; j++) { billation.inner_boiler_number = strinnerbn[j];//獲取內鍋編號 可能會有多個 billation.out_boiler_number = dr[i][2].ToString();//安康外鍋型號和描述 billation.boiler_remark = dr[i][5].ToString(); if (!string.IsNullOrEmpty(billation.out_boiler_number.Trim()))//如果內鍋型號不為null,或讀取為"" { string[] stroutbn = billation.out_boiler_number.Trim('\n').Replace("\n", "@").Replace("/", "@").Replace("/", "@").Split('@'); for (int x = 0; x < stroutbn.Length; x++) { billation.out_boiler_number = stroutbn[x];//獲取外鍋編號 可能會有多個 sb.AppendFormat("INSERT INTO boiler_relation(boiler_type,boiler_describe,inner_boiler_number,out_boiler_number,add_user,add_time,boiler_remark)VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}');", billation.boiler_type, billation.boiler_describe, billation.inner_boiler_number, billation.out_boiler_number, billation.add_user, Common.CommonFunction.DateTimeToString(billation.add_time),billation.boiler_remark); // 计数器 total++; } } } } else//如果內鍋編號為空 { if (state == 0)//表示未獲取對應安康內鍋型號和詳細信息 { billation.inner_boiler_number = inner_boiler_numbers; string[] strinnerbn = billation.inner_boiler_number.Trim('\n').Replace("\n", "@").Replace("/", "@").Replace("/", "@").Split('@'); for (int j = 0; j < strinnerbn.Length; j++) { billation.inner_boiler_number = strinnerbn[j];//獲取內鍋編號 可能會有多個 billation.out_boiler_number = dr[i][2].ToString();//安康外鍋型號和描述 billation.boiler_remark = dr[i][5].ToString();//外鍋描述 if (!string.IsNullOrEmpty(billation.out_boiler_number.Trim()))//如果內鍋型號不為null,或讀取為"" { string[] stroutbn = billation.out_boiler_number.Trim('\n').Replace("\n", "@").Replace("/", "@").Replace("/", "@").Split('@'); for (int x = 0; x < stroutbn.Length; x++) { billation.out_boiler_number = stroutbn[x];//獲取外鍋編號 可能會有多個 sb.AppendFormat("INSERT INTO boiler_relation(boiler_type,boiler_describe,inner_boiler_number,out_boiler_number,add_user,add_time,boiler_remark)VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}');", billation.boiler_type, billation.boiler_describe, billation.inner_boiler_number, billation.out_boiler_number, billation.add_user, Common.CommonFunction.DateTimeToString(billation.add_time), billation.boiler_remark); // 计数器 total++; } } } } else { billation.out_boiler_number = dr[i][2].ToString();//安康外鍋型號和描述 billation.boiler_remark = dr[i][5].ToString();//外鍋描述 if (!string.IsNullOrEmpty(billation.out_boiler_number.Trim()))//如果內鍋型號不為null,或讀取為"" { //解決最後兩行的問題 string obn=billation.out_boiler_number.Trim(); if (obn[0] > 127)//如果大於127表示此為漢字 { sb.AppendFormat(" "); } else { string[] stroutbn = billation.out_boiler_number.Trim('\n').Replace("\n", "@").Replace("/", "@").Replace("/", "@").Split('@'); for (int x = 0; x < stroutbn.Length; x++) { billation.out_boiler_number = stroutbn[x];//獲取外鍋編號 可能會有多個 sb.AppendFormat("INSERT INTO boiler_relation(boiler_type,boiler_describe,inner_boiler_number,out_boiler_number,add_user,add_time,boiler_remark)VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}');", billation.boiler_type, billation.boiler_describe, billation.inner_boiler_number, billation.out_boiler_number, billation.add_user, Common.CommonFunction.DateTimeToString(billation.add_time), billation.boiler_remark); // 计数器 total++; } } } } } } catch (Exception ex) { continue; } } mySqlCmd.CommandText = sb.ToString(); result = mySqlCmd.ExecuteNonQuery(); mySqlCmd.Transaction.Commit(); } catch (Exception ex) { mySqlCmd.Transaction.Rollback(); throw new Exception("BoilerrelationDao.GetintoBoilerrelation-->" + ex.Message + sb.ToString(), ex); } finally { if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open) { mySqlConn.Close(); } } return result; }