/// <summary> /// Return the scalar value from an SQL query /// </summary> /// <param name="sql"></param> /// <param name="trans"></param> /// <returns></returns> public object ExecuteScalar(string sql, DbTransaction trans) { Log.Debug("\tSQL: " + sql); try { if (trans != null) { return(Db.ExecuteScalar(trans, CommandType.Text, sql)); } else { object ret = ODHR.Utils.Caching.CallStackCaching.GetFromCache <object>(sql); if (ret != null) { return(ret); } ret = Db.ExecuteScalar(CommandType.Text, sql); ODHR.Utils.Caching.CallStackCaching.SaveToCache(sql, ret); return(ret); } } catch (Exception e) { Log.Fatal("ExecuteScalar - " + e.Message + " SQL:[" + (sql ?? "Null") + "]", e); throw; } }
/// <summary> /// Returns an object by executing a scalar function /// </summary> /// <param name="command"></param> /// <returns>Object</returns> public static Object ExecuteScalar(DbCommand command) { object obj = new object(); try { Microsoft.Practices.EnterpriseLibrary.Data.Database db = DBSelector.Instance.GetDataBase; obj = db.ExecuteScalar(command); } catch (Exception ex) { throw ex; } return obj; }
private void InsertDataBase(object ods) { #region init DataSet ds = ods as DataSet; if (ds == null) { MessageBox.Show("没有数据可以导入数据库!"); return; } DataTable NewDataTable = ds.Tables[0].Copy(); for (int i = 1; i < ds.Tables.Count; i++) { //添加DataTable2的数据 foreach (DataRow dr in ds.Tables[i].Rows) { NewDataTable.ImportRow(dr); } } if (NewDataTable.Rows.Count <= 0) { MessageBox.Show("没有数据可以导入数据库!"); return; } DataView dataView = NewDataTable.DefaultView; DataTable project = dataView.ToTable(true, "Project", "PartNo", "PartName"); if (project.Rows.Count != 1) { MessageBox.Show("Excel文件不正确"); return; } else { var row = project.Rows[0]; SqlParameter[] p = new SqlParameter[] { new SqlParameter("@ProjectNo", row["Project"]), new SqlParameter("@PartNo", row["PartNo"]) }; var sql = ("select ProjectNo,PartNo from MeasurementReports where ProjectNo=@ProjectNo and PartNo=@PartNo"); var dbc = db.GetSqlStringCommand(sql); dbc.Parameters.AddRange(p); var temp = db.ExecuteDataSet(dbc); if (null != temp && temp.Tables[0].Rows.Count > 0) { MessageBox.Show("已经存在 此零件号"); return; } } #endregion MethodInvoker mi = new MethodInvoker(ShowProgressBar); this.BeginInvoke(mi); StringBuilder sb = new StringBuilder(); string strProjectNo = string.Empty; #region Project foreach (DataRow row in project.Rows) { strProjectNo = row["Project"].ToString(); sb.AppendFormat("insert into MeasurementReports(ProjectNo,PartNo,PartName,CreateTime,Creator) values('{0}','{1}','{2}','{3}','{4}');" , row["Project"] , row["PartNo"] , row["PartName"] , DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") , "管理员" ); } db.ExecuteNonQuery(CommandType.Text, sb.ToString()); this.Invoke(this.myHandle, new object[] { (10 * 100 / 100), strProjectNo }); var ProjectId = 0; ProjectId = int.Parse(db.ExecuteScalar(CommandType.Text, "select top 1 * from MeasurementReports order by MeasReportID desc").ToString()); #endregion #region item sb.Clear(); DataTable SheetName = dataView.ToTable(true, "SheetName"); foreach (DataRow row in SheetName.Rows) { sb.AppendFormat("INSERT INTO [MeasurementItems]([MeasReportID],[MeasItemNO],[MeasItemName]) VALUES ({0},'{1}','{1}');" , ProjectId , row["SheetName"] ); } db.ExecuteNonQuery(CommandType.Text, sb.ToString()); #endregion this.Invoke(this.myHandle, new object[] { (30 * 100 / 100), strProjectNo }); #region page sb.Clear(); DataTable Pages = dataView.ToTable(true, "SheetName", "Pages"); foreach (DataRow row in Pages.Rows) { var ItemId = 0; ItemId = int.Parse(db.ExecuteScalar(CommandType.Text, string.Format("select top 1 * from MeasurementItems where [MeasReportID]={0} and [MeasItemNO]='{1}'", ProjectId, row["SheetName"])).ToString()); sb.AppendFormat("INSERT INTO [dbo].[MeasurementPages]([MeasItemID],[MeasPageNo]) VALUES ({0},'{1}');" , ItemId , row["Pages"] ); } db.ExecuteNonQuery(CommandType.Text, sb.ToString()); #endregion this.Invoke(this.myHandle, new object[] { (50 * 100 / 100), strProjectNo }); #region point sb.Clear(); DataTable point = dataView.ToTable(true, "SheetName", "Pages"); foreach (DataRow row in point.Rows) { var PageId = 0; PageId = int.Parse(db.ExecuteScalar(CommandType.Text, string.Format("select [MeasPageID] from [MeasurementPages] a left join [dbo].[MeasurementItems] b on a.[MeasItemID]=b.MeasItemID where b.MeasItemNO='{0}' and [MeasPageNo]={1} and [MeasReportID]={2}", row["SheetName"], row["Pages"], ProjectId ) ).ToString()); string filter = "Pages = '" + row["Pages"].ToString() + "' and SheetName = '" + row["SheetName"].ToString() + "'"; DataRow[] drArr = NewDataTable.Select(filter); foreach (var item in drArr) { string sql = @"INSERT INTO [dbo].[MeasurementPoints] ([MeasPageID],[PointNo],[PointType],[XAxis] ,[YAxis],[ZAxis],[UpperTol],[LowerTol] ,[Direct],[AVG] ,[CorrectDirect]) VALUES ({0},'{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}');"; sb.AppendFormat(sql , PageId , item["PointNo"] , item["PointType"] , item["XAxis"] , item["YAxis"] , item["ZAxis"] , item["UpperTol"] , item["LowerTol"] , item["Direct"] , item["AVG"] , item["CorrectDirect"] ); } } db.ExecuteNonQuery(CommandType.Text, sb.ToString()); #endregion this.Invoke(this.myHandle, new object[] { (100 * 100 / 100), strProjectNo }); }