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