本文整理汇总了C#中SqlDatabase.CreateCommand方法的典型用法代码示例。如果您正苦于以下问题：C# SqlDatabase.CreateCommand方法的具体用法？C# SqlDatabase.CreateCommand怎么用？C# SqlDatabase.CreateCommand使用的例子？那么恭喜您, 这里精选的方法代码示例或许可以为您提供帮助。您也可以进一步了解该方法所在类SqlDatabase的用法示例。
在下文中一共展示了SqlDatabase.CreateCommand方法的15个代码示例，这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞，您的评价将有助于系统推荐出更棒的C#代码示例。
示例1: btnExport_Click
protected void btnExport_Click(object sender, EventArgs e)
{
string yearmonth = drpYear.SelectedValue;
string building = Func.ParseString(Session["__BUILDINGID__"]);
DataSet ds = new DataSet();
string sql = string.Empty;
sql = " SELECT *";
sql += " FROM BD_BudgetSchedule ";
sql += " WHERE BuildingId = '" + building + "' ";
sql += " and YearMonth = '" + yearmonth + "' ";
sql += drpBudgetExport.SelectedValue.Equals("") ? "" : " and id ='" + drpBudgetExport.SelectedValue + "'";
sql += " and DelFlag = 0 Order by id";
using (SqlDatabase db = new SqlDatabase())
{
C1XLBook xlbBook = new C1XLBook();
string fileName = HttpContext.Current.Server.MapPath(@"~\Report\Template\NganSach.xlsx");
if (!Directory.Exists(@"~\Report\Building\" + Func.ParseString(Session["__BUILDINGID__"])))
{
Directory.CreateDirectory(HttpContext.Current.Server.MapPath(@"~\Report\Building\" + Func.ParseString(Session["__BUILDINGID__"])));
}
string strDT = DateTime.Now.ToString("yyyyMMddHHmmss");
string strFilePath = @"~\Report\Building\" + Func.ParseString(Session["__BUILDINGID__"]) + @"\NganSach" + strDT + ".xlsx";
string strFilePathExport = @"../../Report/Building/" + Func.ParseString(Session["__BUILDINGID__"]) + @"/NganSach" + strDT + ".xlsx";
string fileNameDes = HttpContext.Current.Server.MapPath(strFilePath);
//string fileNameDes = HttpContext.Current.Server.MapPath(@"~\Report\Building\" + Func.ParseString(Session["__BUILDINGID__"]) + @"\TongHopDienTich" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx");
File.Copy(fileName, fileNameDes);
xlbBook.Load(fileNameDes);
string sheet = "NganSach";
XLSheet xlsSheet = xlbBook.Sheets[sheet];
string IDs = "";
using (SqlCommand cm = db.CreateCommand(sql))
{
SqlDataAdapter da = new SqlDataAdapter(cm);
da.Fill(ds);
if (ds != null)
{
xlsSheet[0, 2].Value = xlsSheet[0, 2].Value.ToString().Replace("{%TOA_NHA%}", DbHelper.GetScalar("Select Name From Mst_Building Where BuildingId = '" + building + "'"));
xlsSheet[0, 2].Value = xlsSheet[0, 2].Value.ToString().Replace("{%NAM_THANG%}", "NĂM " + yearmonth);
int j = 7;
DataTable dtReport = ds.Tables[0];
foreach (DataRow rowType in dtReport.Rows)
{
string Budget = rowType["Budget"].ToString();
string id = rowType["id"].ToString();
IDs += ",'" + id + "'";
xlsSheet[2, j].Value = id;
xlsSheet[3, j].Value = Budget;
j++;
}
for (int i = j; i < j * 12; i++)
{
XLColumn col = new XLColumn();
col = xlsSheet.Columns[j];
xlsSheet.Columns.Remove(col);
}
}
}
if (String.IsNullOrEmpty(IDs))
{
mvMessage.AddError("Hiện tại chưa có Kỳ ngân sách nào được tạo");
return;
}
string buildingId = Func.ParseString(Session["__BUILDINGID__"]);
string sessionId = Session.SessionID;
DbHelper.ExecuteNonQuery("Delete From BD_BudgetScheduleDetailReport where SessionId = '" + sessionId + "'");
string[] idList = IDs.Substring(1).Split(',');
for (int m = 0; m < idList.Length; m++)
{
string sqlTmp = "Select * from BD_BudgetScheduleDetail where BuggetScheduleId in (" + idList[m] + ") and delFlag = 0 Order by Id";
DataTable dtTable = new DataTable();
dtTable.Columns.Add("SessionId", Type.GetType("System.String"));
dtTable.Columns.Add("BuggetScheduleId", Type.GetType("System.Int32"));
dtTable.Columns.Add("PaymentType", Type.GetType("System.String"));
dtTable.Columns.Add("PaymentId", Type.GetType("System.Int32"));
dtTable.Columns.Add("ParentId", Type.GetType("System.Int32"));
dtTable.Columns.Add("InVND", Type.GetType("System.Double"));
dtTable.Columns.Add("InUSD", Type.GetType("System.Decimal"));
dtTable.Columns.Add("OutVND", Type.GetType("System.Double"));
dtTable.Columns.Add("OutUSD", Type.GetType("System.Decimal"));
dtTable.Columns.Add("ItemLevel", Type.GetType("System.String"));
DataTable dt = DbHelper.GetDataTable(sqlTmp);
foreach (DataRow dr in dt.Rows)
//.........这里部分代码省略.........
示例2: btnExport_Click
protected void btnExport_Click(object sender, EventArgs e)
{
DataSet ds = new DataSet();
string sql = string.Empty;
sql = " SELECT *";
sql += " FROM v_GuiXeThang";
sql += " WHERE BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "' ";
sql += " AND ((NgayKetThuc is null) OR ";
sql += " (NgayKetThuc is not null and substring(NgayKetThuc,1,6) >= '" + drpYear.SelectedValue + drpMonth.SelectedValue + "'))";
using (SqlDatabase db = new SqlDatabase())
{
using (SqlCommand cm = db.CreateCommand(sql))
{
SqlDataAdapter da = new SqlDataAdapter(cm);
da.Fill(ds);
db.Close();
if (ds != null)
{
C1XLBook xlbBook = new C1XLBook();
string fileName = HttpContext.Current.Server.MapPath(@"~\Report\Template\GuixeThang.xls");
if (!Directory.Exists(@"~\Report\Building\" + Func.ParseString(Session["__BUILDINGID__"])))
{
Directory.CreateDirectory(HttpContext.Current.Server.MapPath(@"~\Report\Building\" + Func.ParseString(Session["__BUILDINGID__"])));
}
string strDT = DateTime.Now.ToString("yyyyMMddHHmmss");
string strFilePath = @"~\Report\Building\" + Func.ParseString(Session["__BUILDINGID__"]) + @"\GuiXeThang" + strDT + ".xls";
string strFilePathExport = "Report/Building/" + Func.ParseString(Session["__BUILDINGID__"]) + "/GuiXeThang" + strDT + ".xls";
string fileNameDes = HttpContext.Current.Server.MapPath(strFilePath);
//string fileNameDes = HttpContext.Current.Server.MapPath(@"~\Report\Building\" + Func.ParseString(Session["__BUILDINGID__"]) + @"\TongHopDienTich" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
File.Copy(fileName, fileNameDes);
xlbBook.Load(fileNameDes);
XLSheet xlsSheet = xlbBook.Sheets["GuiXeThang"];
//xlsSheet.Name = drpMonth.SelectedValue + "_" + drpYear.SelectedValue;
int i = 4;
XLCellRange mrCell = new XLCellRange(0, 0, 0, 2);
xlsSheet.MergedCells.Add(mrCell);
XLStyle xlstStyle = new XLStyle(xlbBook);
xlstStyle.AlignHorz = XLAlignHorzEnum.Center;
xlstStyle.WordWrap = true;
xlstStyle.Font = new Font("", 8, FontStyle.Regular);
xlstStyle.SetBorderColor(Color.Black);
xlstStyle.BorderBottom = XLLineStyleEnum.Thin;
xlstStyle.BorderTop = XLLineStyleEnum.Thin;
xlstStyle.BorderLeft = XLLineStyleEnum.Thin;
xlstStyle.BorderRight = XLLineStyleEnum.Thin;
XLStyle xlstStyle01 = new XLStyle(xlbBook);
xlstStyle01.AlignHorz = XLAlignHorzEnum.Center;
xlstStyle01.Font = new Font("", 10, FontStyle.Bold);
xlstStyle.SetBorderColor(Color.Black);
xlsSheet[1, 0].Value = xlsSheet[1, 0].Value.ToString().Replace("{%TOA_NHA%}", DbHelper.GetScalar("Select Name From Mst_Building Where BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "'"));
xlsSheet[0, 0].Value = xlsSheet[0, 0].Value.ToString().Replace("{%THANG%}", drpMonth.SelectedValue+"/"+drpYear.SelectedValue);
int stt = 0;
DataTable dt = ds.Tables[0];
foreach (DataRow rowType in dt.Rows)
{
string col01 = rowType[0].ToString();
string col02 = rowType[1].ToString();
string col03 = rowType[2].ToString();
string col04 = rowType[3].ToString();
string col05 = rowType[4].ToString();
string col06 = rowType[5].ToString();
string col07 = Func.FormatDMY(rowType[6].ToString());
string col08 = Func.FormatDMY(rowType[7].ToString());
string col09 = rowType[8].ToString();
xlsSheet[i, 0].Value = ++stt;
xlsSheet[i, 1].Value = col03;
xlsSheet[i, 2].Value = col04;
xlsSheet[i, 3].Value = col05;
xlsSheet[i, 4].Value = col06;
xlsSheet[i, 5].Value = col07;
xlsSheet[i, 6].Value = col08;
xlsSheet[i, 7].Value = col09;
xlsSheet[i, 0].Style = xlstStyle;
xlsSheet[i, 1].Style = xlstStyle;
xlsSheet[i, 2].Style = xlstStyle;
xlsSheet[i, 3].Style = xlstStyle;
xlsSheet[i, 4].Style = xlstStyle;
xlsSheet[i, 5].Style = xlstStyle;
xlsSheet[i, 6].Style = xlstStyle;
xlsSheet[i, 7].Style = xlstStyle;
++i;
}
////ScriptManager.RegisterStartupScript(Page, this.GetType(), "", "PopUp('/CSV/DownloadZipFile.aspx'," + PopupWidth + "," + PopupHeight + ",'EditFlat', true);", true);
////xlsSheet[i++, 0].Value = "Ghi chú:";
//.........这里部分代码省略.........
示例3: btnExport_Click
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnExport_Click(object sender, EventArgs e)
{
string job = "";
switch (hidJobType.Value)
{
case "1":
job = "BV";
break;
case "2":
job = "VS";
break;
case "3":
job = "KT";
break;
case "4":
job = "QL";
break;
default:
break;
}
string buildingId = Func.ParseString(Session["__BUILDINGID__"]);
Hashtable staffIdRow = new Hashtable();
string[] dateOfWeekVN = { "T2", "T3", "T4", "T5", "T6", "T7", "CN" };
string[] dateOfWeekEN = { "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Sartuday", "Sunday" };
Dictionary<string, string> dictionary = new Dictionary<string, string>();
dictionary.Add("monday", "T2");
dictionary.Add("tuesday", "T3");
dictionary.Add("wednesday", "T4");
dictionary.Add("thursday", "T5");
dictionary.Add("friday", "T6");
dictionary.Add("saturday", "T7");
dictionary.Add("sunday", "CN");
DataSet ds = new DataSet();
string sql = string.Empty;
sql = " SELECT *";
sql += " FROM BD_WorkingWorkedInfo";
sql += " WHERE BuildingId = '" + buildingId + "' and DelFlag = 0 and jobtypeid = '" + hidJobType.Value + "'";
sql += " and YearMonth = '" + drpYear.SelectedValue + drpMonth.SelectedValue + "'";
Hashtable scheduleLst = new Hashtable();
using (SqlDatabase db = new SqlDatabase())
{
using (SqlCommand cm = db.CreateCommand(sql))
{
SqlDataAdapter da = new SqlDataAdapter(cm);
da.Fill(ds);
db.Close();
if (ds != null)
{
DataTable dt = ds.Tables[0];
foreach (DataRow rowType in dt.Rows)
{
string StaffId = rowType["StaffId"].ToString();
string WorkingHourId = rowType["WorkingHourId"].ToString();
string WorkingDate = rowType["WorkingDate"].ToString().Substring(6, 2);
if (!String.IsNullOrEmpty(WorkingHourId) && scheduleLst.ContainsKey(StaffId + WorkingDate))
{
scheduleLst.Add(StaffId + WorkingDate, WorkingHourId);
}
}
}
}
}
ds = new DataSet();
sql = " SELECT *";
sql += " FROM BD_Staff";
sql += " WHERE BuildingId = '" + buildingId + "' and DelFlag = 0 and jobtypeid = '" + hidJobType.Value + "' and SUBSTRING(JobBegin,0,7) <= '" + drpYear.SelectedValue + drpMonth.SelectedValue + "' and (JobEnd = '' or JobEnd is Null or SUBSTRING(JobEnd,0,7) >= '" + drpYear.SelectedValue + drpMonth.SelectedValue + "')";
sql += " Order By Name";
using (SqlDatabase db = new SqlDatabase())
{
using (SqlCommand cm = db.CreateCommand(sql))
{
SqlDataAdapter da = new SqlDataAdapter(cm);
da.Fill(ds);
db.Close();
if (ds != null)
{
mvMessage.SetCompleteMessage("File CSV đã xuất thành công.");
C1XLBook xlbBook = new C1XLBook();
string fileName = HttpContext.Current.Server.MapPath(@"~\Report\Template\LichLamViec.xls");
if (!Directory.Exists(@"~\Report\Building\" + Func.ParseString(Session["__BUILDINGID__"])))
{
Directory.CreateDirectory(HttpContext.Current.Server.MapPath(@"~\Report\Building\" + buildingId));
//.........这里部分代码省略.........
示例4: FullNewViewMultiBoth
//.........这里部分代码省略.........
string strDT = DateTime.Now.ToString("yyyyMMddHHmmss");
string strFilePath = @"~\Report\Building\" + Func.ParseString(Session["__BUILDINGID__"]) + @"\Bill\Bill_" + lblCustomerId.Text + "_" + strDT + ".xlsx";
string strFilePathExport = @"../../Report/Building/" + Func.ParseString(Session["__BUILDINGID__"]) + @"/Bill/Bill_" + lblCustomerId.Text + "_" + strDT + ".xlsx";
string fileNameDes = HttpContext.Current.Server.MapPath(strFilePath);
File.Copy(fileName, fileNameDes);
xlbBook.Load(fileNameDes);
xlsSheet = xlbBook.Sheets["TongHop"];
xlsSheetEn = xlbBook.Sheets["TongHop_En"];
//Bill No
setValReplace(rBillNo, cBillNo, "{%BILL_NO%}", txtBillNo.Text);
//Ngay Thang Nam
DateTime dtime = DateTime.Today;
setValReplace(rBillDate, cBillDate, "{%NGAY%}", dtime.ToString("dd"));
setValReplace(rBillDate, cBillDate, "{%THANG%}", dtime.ToString("MM"));
setValReplace(rBillDate, cBillDate, "{%NAM%}", dtime.ToString("yyyy"));
//Nam
setValReplace(rBillMonth, cBillMonth, "{%NAM_THANG%}", drpMonth.SelectedValue + "/" + drpYear.SelectedValue);
using (SqlDatabase db = new SqlDatabase())
{
DataSet ds = new DataSet();
string sql = string.Empty;
sql = " SELECT Name, ContactName";
sql += " FROM Customer";
sql += " WHERE CustomerId = '" + lblCustomerId.Text + "' ";
using (SqlCommand cm = db.CreateCommand(sql))
{
SqlDataAdapter da = new SqlDataAdapter(cm);
da.Fill(ds);
if (ds != null)
{
DataTable dt = ds.Tables[0];
foreach (DataRow rowType in dt.Rows)
{
string Name = rowType[0].ToString();
string ContactName = rowType[1].ToString();
//Customer
setValReplace(rCustomer, cCustomer, "{%TEN_CONG_TY%}", Name);
//Contact
setValReplace(rContact, cContact, "{%NGUOI_DAI_DIEN%}", ContactName);
}
}
}
Hashtable contractIdLst = new Hashtable();
string contract = "";
ds = new DataSet();
sql = " SELECT Bank,Account,AccountName,Office,OfficeAddress,OfficePhone";
sql += " FROM Mst_Building";
sql += " WHERE BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "' ";
using (SqlCommand cm = db.CreateCommand(sql))
{
SqlDataAdapter da = new SqlDataAdapter(cm);
da.Fill(ds);
if (ds != null)
{示例5: ShowData
/// <summary>
///
/// </summary>
private void ShowData()
{
PopupWidth = 850;
PopupName = "ListSyncSong";
SqlDatabase db = new SqlDatabase();
string sql = string.Empty;
if (!Func.IsValid(ListSortExpression))
{
ListSortExpression = "SongId";
ListSortDirection = SortDirection.Descending;
}
try
{
//件数を数える
sql += " SELECT COUNT(SongId) ";
sql += " FROM SongImport ";
sql += " WHERE (SongId IS NOT NULL) and SessionId = '" + Session.SessionID + "' and ImportType = '" + importType + "'";
int count = db.ExecuteCount(sql);
sql = string.Empty;
string sort = ListSortExpression + " " + (ListSortDirection == SortDirection.Ascending ? " asc " : " desc ");
//検索条件で取得情報のSQL文を作成する
sql = " SELECT *,ROW_NUMBER() OVER(ORDER BY " + sort + ") as RowNum FROM SongImport ";
sql += " WHERE (SongId IS NOT NULL) and SessionId = '" + Session.SessionID + "' and ImportType = '" + importType + "'";
//ページによるレコーダを取得する
sql = " SELECT *,RowNum FROM (" + sql + ") AS TMP ";
sql += " WHERE RowNum BETWEEN @PageIndex*@PageSize + 1 AND (@PageIndex+1)*@PageSize ORDER BY TMP.RowNum ";
//SQL文を実行する
SqlCommand cm = db.CreateCommand(sql);
cm.Parameters.AddWithValue("@PageIndex", pager.CurrentPageIndex);
cm.Parameters.AddWithValue("@PageSize", pager.PageSize);
SqlDataAdapter da = new SqlDataAdapter(cm);
DataSet ds = new DataSet();
da.Fill(ds);
db.Close();
rptList.DataSource = ds.Tables[0].DefaultView;
rptList.DataBind();
pager.Count = count;
}
catch (Exception ex)
{
ApplicationLog.WriteError(ex);
}
}示例6: btnExport_Click
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnExport_Click(object sender, EventArgs e)
{
string[] dateOfWeekVN = { "T2", "T3", "T4", "T5", "T6", "T7", "CN" };
string[] dateOfWeekEN = { "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Sartuday", "Sunday" };
Dictionary<string, string> dictionary = new Dictionary<string, string>();
dictionary.Add("monday", "T2");
dictionary.Add("tuesday", "T3");
dictionary.Add("wednesday", "T4");
dictionary.Add("thursday", "T5");
dictionary.Add("friday", "T6");
dictionary.Add("saturday", "T7");
dictionary.Add("sunday", "CN");
DataSet ds = new DataSet();
string sql = string.Empty;
sql = " SELECT *";
sql += " FROM BD_Staff";
sql += " WHERE BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "' and DelFlag <> 1 and jobtypeid = '"+ hidJobType.Value +"'";
sql += " Order By Name";
using (SqlDatabase db = new SqlDatabase())
{
using (SqlCommand cm = db.CreateCommand(sql))
{
SqlDataAdapter da = new SqlDataAdapter(cm);
da.Fill(ds);
db.Close();
if (ds != null)
{
mvMessage.SetCompleteMessage("File CSV đã xuất thành công.");
C1XLBook xlbBook = new C1XLBook();
XLSheet xlsSheet = xlbBook.Sheets[0];
xlsSheet.Name = drpMonth.SelectedValue + "_" + drpYear.SelectedValue;
int i = 0;
XLCellRange mrCell = new XLCellRange(0, 0, 0, 2);
xlsSheet.MergedCells.Add(mrCell);
XLStyle xlstStyle = new XLStyle(xlbBook);
xlstStyle.AlignHorz = XLAlignHorzEnum.Center;
xlstStyle.Font = new Font("", 12, FontStyle.Bold);
xlstStyle.SetBorderColor(Color.Black);
xlsSheet[i, 0].Value = "Tháng " + drpMonth.SelectedValue + "/" + drpYear.SelectedValue;
xlsSheet[i, 0].Style = xlstStyle;
xlsSheet[i + 1, 0].Value = "STT";
xlsSheet[i + 1, 1].Value = "Mã Nhân Viên";
xlsSheet[i + 1, 2].Value = "Họ và Tên";
XLStyle xlstStyle01 = new XLStyle(xlbBook);
xlstStyle01.AlignHorz = XLAlignHorzEnum.Center;
xlstStyle01.Font = new Font("", 10, FontStyle.Bold);
xlstStyle.SetBorderColor(Color.Black);
for (int j = 1; j <= 31; j++)
{
xlsSheet[i, 2 + j].Value = j;
DateTime date = new DateTime(Func.ParseInt(drpYear.SelectedValue), Func.ParseInt(drpMonth.SelectedValue), j);
xlsSheet[i + 1, 2 + j].Value = dictionary[date.DayOfWeek.ToString().ToLower()];
xlsSheet[i, 2 + j].Style = xlstStyle01;
xlsSheet[i + 1, 2 + j].Style = xlstStyle01;
if (j == DateTime.DaysInMonth(Func.ParseInt(drpYear.SelectedValue), Func.ParseInt(drpMonth.SelectedValue)))
{
break;
}
}
i++;
DataTable dt = ds.Tables[0];
foreach (DataRow rowType in dt.Rows)
{
int No = i;
i++;
string StaffId = rowType["StaffId"].ToString();
string Name = rowType["Name"].ToString();
xlsSheet[i, 0].Value = No;
xlsSheet[i, 1].Value = StaffId;
xlsSheet[i, 2].Value = Name;
xlsSheet[i, 0].Style = xlstStyle01;
xlsSheet[i, 1].Style = xlstStyle01;
xlsSheet[i, 2].Style = xlstStyle01;
}
//ScriptManager.RegisterStartupScript(Page, this.GetType(), "", "PopUp('/CSV/DownloadZipFile.aspx'," + PopupWidth + "," + PopupHeight + ",'EditFlat', true);", true);
//xlsSheet[i++, 0].Value = "Ghi chú:";
//.........这里部分代码省略.........
示例7: ShowData
/// <summary>
/// List data
/// </summary>
private void ShowData()
{
SqlDatabase db = new SqlDatabase();
string sql = string.Empty;
if (!Func.IsValid(ListSortExpression))
{
ListSortExpression = "isnull(B.Modified,A.Modified)";
ListSortDirection = SortDirection.Descending;
}
try
{
//Đếm số lượng record
sql += " Select COUNT(ID) ";
sql += " FROM BD_Supplies";
sql += " WHERE (Id IS NOT NULL) and DelFlag <> 1 and BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "' and SuppliesType = '" + hidSuppliesType.Value + "'";
sql += GetWhere();
int count = db.ExecuteCount(sql);
sql = string.Empty;
string sort = ListSortExpression + " " + (ListSortDirection == SortDirection.Ascending ? " asc " : " desc ");
//Xuất ra toàn bộ nội dung theo Trang
sql += " Select id,CreatedId,BuildingId,JobType,SuppliesType,ItemId,Name,Description,ProductOf,Comment,Created,CreatedBy,isnull(B.Modified,A.Modified) as Modified,isnull(A.ModifiedBy,B.ModifiedBy) as ModifiedBy,DelFlag,Model,Label,Regional, ROW_NUMBER() OVER(ORDER BY " + sort + ") as RowNum ";
sql += " FROM BD_Supplies A left outer join (SELECT t.SuppliesId,Modified,ModifiedBy ";
sql += " FROM ( SELECT SuppliesId , MAX(id) AS max_votes FROM BD_SuppliesExim GROUP BY SuppliesId) AS m ";
sql += " INNER JOIN BD_SuppliesExim AS t ";
sql += " ON t.SuppliesId = m.SuppliesId ";
sql += " AND t.id = m.max_votes) B on A.id = B.SuppliesId";
sql += " WHERE Id IS NOT NULL and DelFlag <> 1 and BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "' and SuppliesType = '" + hidSuppliesType.Value + "'";
sql += GetWhere();
//Phân trang
sql = " Select * FROM (" + sql + ") AS TMP ";
sql += " WHERE RowNum BETWEEN @PageIndex*@PageSize + 1 AND (@PageIndex+1)*@PageSize ORDER BY RowNum ";
//Thực hiện câu SQL
SqlCommand cm = db.CreateCommand(sql);
cm.Parameters.AddWithValue("@PageIndex", pager.CurrentPageIndex);
cm.Parameters.AddWithValue("@PageSize", pager.PageSize);
SqlDataAdapter da = new SqlDataAdapter(cm);
DataSet ds = new DataSet();
da.Fill(ds);
db.Close();
rptList.DataSource = ds.Tables[0].DefaultView;
rptList.DataBind();
pager.Count = count;
}
catch (Exception ex)
{
ApplicationLog.WriteError(ex);
}
}示例8: btnView_Click
protected void btnView_Click(object sender, EventArgs e)
{
C1XLBook xlbBook = new C1XLBook();
string fileName = HttpContext.Current.Server.MapPath(@"~\Report\Template\BillPhongHop.xlsx");
if (!Directory.Exists(@"~\Report\Building\" + Func.ParseString(Session["__BUILDINGID__"])))
{
Directory.CreateDirectory(HttpContext.Current.Server.MapPath(@"~\Report\Building\" + Func.ParseString(Session["__BUILDINGID__"])));
}
decimal LastSumPriceVND = 0;
decimal LastSumPriceUSD = 0;
XLStyle xlstStyle = new XLStyle(xlbBook);
xlstStyle.AlignHorz = XLAlignHorzEnum.Center;
xlstStyle.WordWrap = true;
xlstStyle.Font = new Font("", 8, FontStyle.Regular);
xlstStyle.SetBorderColor(Color.Black);
xlstStyle.BorderBottom = XLLineStyleEnum.Thin;
xlstStyle.BorderTop = XLLineStyleEnum.Thin;
xlstStyle.BorderLeft = XLLineStyleEnum.Thin;
xlstStyle.BorderRight = XLLineStyleEnum.Thin;
string strDT = DateTime.Now.ToString("yyyyMMddHHmmss");
string strFilePath = @"~\Report\Building\" + Func.ParseString(Session["__BUILDINGID__"]) + @"\BillPhongHop" + strDT + ".xlsx";
string strFilePathExport = @"../../Report/Building/" + Func.ParseString(Session["__BUILDINGID__"]) + @"/BillPhongHop" + strDT + ".xlsx";
string fileNameDes = HttpContext.Current.Server.MapPath(strFilePath);
//string fileNameDes = HttpContext.Current.Server.MapPath(@"~\Report\Building\" + Func.ParseString(Session["__BUILDINGID__"]) + @"\TongHopDienTich" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx");
File.Copy(fileName, fileNameDes);
xlbBook.Load(fileNameDes);
XLSheet xlsSheet = xlbBook.Sheets["HoaDon"];
int k = 2;
xlsSheet[1, 6 + k].Value = xlsSheet[1, 6 + k].Value.ToString().Replace("{%NGAY%}", DateTime.Today.ToString("dd"));
xlsSheet[1, 6 + k].Value = xlsSheet[1, 6 + k].Value.ToString().Replace("{%THANG%}", DateTime.Today.ToString("MM"));
xlsSheet[1, 6 + k].Value = xlsSheet[1, 6 + k].Value.ToString().Replace("{%NAM%}", DateTime.Today.ToString("yyyy"));
using (SqlDatabase db = new SqlDatabase())
{
DataSet ds = new DataSet();
string sql = string.Empty;
sql = " SELECT Name, ContactName";
sql += " FROM Customer";
sql += " WHERE CustomerId = '" + lnbCustomerId.Text + "' ";
using (SqlCommand cm = db.CreateCommand(sql))
{
SqlDataAdapter da = new SqlDataAdapter(cm);
da.Fill(ds);
if (ds != null)
{
DataTable dt = ds.Tables[0];
foreach (DataRow rowType in dt.Rows)
{
string Name = rowType[0].ToString();
string ContactName = rowType[1].ToString();
xlsSheet[6, 0].Value = xlsSheet[6, 0].Value.ToString().Replace("{%TEN_CONG_TY%}", Name);
xlsSheet[7, 0].Value = xlsSheet[7, 0].Value.ToString().Replace("{%NGUOI_DAI_DIEN%}", ContactName);
xlsSheet[9, 0].Value = xlsSheet[9, 0].Value.ToString().Replace("{%NGAY_HOP_DONG%}", lblBookingDate.Text);
xlsSheet[11, 0].Value = xlsSheet[11, 0].Value.ToString().Replace("{%GIO_TU%}", drpHourFrom.SelectedValue);
xlsSheet[11, 0].Value = xlsSheet[11, 0].Value.ToString().Replace("{%PHUT_TU%}", drpMinuteFrom.Value);
xlsSheet[11, 0].Value = xlsSheet[11, 0].Value.ToString().Replace("{%GIO_DEN%}", drpHourTo.SelectedValue);
xlsSheet[11, 0].Value = xlsSheet[11, 0].Value.ToString().Replace("{%PHUT_DEN%}", drpMinuteTo.Value);
xlsSheet[11, 0].Value = xlsSheet[11, 0].Value.ToString().Replace("{%NGAY_THUE%}", lblBookingDate.Text);
}
}
}
ds = new DataSet();
sql = " SELECT Bank,Account,AccountName,Office,OfficeAddress,OfficePhone";
sql += " FROM Mst_Building";
sql += " WHERE BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "' ";
using (SqlCommand cm = db.CreateCommand(sql))
{
SqlDataAdapter da = new SqlDataAdapter(cm);
da.Fill(ds);
if (ds != null)
{
int tmp = 2;
DataTable dt = ds.Tables[0];
foreach (DataRow rowType in dt.Rows)
{
string Bank = rowType["Bank"].ToString();
string Account = rowType["Account"].ToString();
string AccountName = rowType["AccountName"].ToString();
string Office = rowType["Office"].ToString();
string OfficeAddress = rowType["OfficeAddress"].ToString();
string OfficePhone = rowType["OfficePhone"].ToString();
xlsSheet[30 + tmp, 0].Value = xlsSheet[30 + tmp, 0].Value.ToString().Replace("{%VAN_PHONG%}", Office);
xlsSheet[31 + tmp, 0].Value = xlsSheet[31 + tmp, 0].Value.ToString().Replace("{%DIEN_THOAI%}", OfficePhone);
//.........这里部分代码省略.........
示例9: ShowData
private void ShowData(string YearMonth)
{
SqlDatabase db = new SqlDatabase();
string sql = string.Empty;
if (!Func.IsValid(ListSortExpression))
{
ListSortExpression = "Name";
ListSortDirection = SortDirection.Descending;
}
try
{
//Rent And Manager Price
sql = string.Empty;
string sort = ListSortExpression + " " + (ListSortDirection == SortDirection.Ascending ? " asc " : " desc ");
//Xuất ra toàn bộ nội dung theo Trang
sql += " Select *";
sql += " FROM PaymentRoom";
sql += " WHERE BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "' and CustomerId = '" + hidId.Value + "' and YearMonth in (" + YearMonth + ")";
SqlCommand cm = db.CreateCommand(sql);
SqlDataAdapter da = new SqlDataAdapter(cm);
DataSet ds = new DataSet();
da.Fill(ds);
db.Close();
rptRoomRent.DataSource = ds.Tables[0].DefaultView;
rptRoomRent.DataBind();
rptRoomManager.DataSource = ds.Tables[0].DefaultView;
rptRoomManager.DataBind();
//Parking
sql = string.Empty;
sort = "TariffsParkingName" + " " + (ListSortDirection == SortDirection.Ascending ? " asc " : " desc ");
//Xuất ra toàn bộ nội dung theo Trang
sql += " SELECT COUNT(*) AS Num, YearMonth, TariffsParkingName, PriceVND, PriceUSD, SUM(VatVND) AS VatVND,SUM(VatUSD) AS VatUSD, SUM(SumVND) AS SumVND, SUM(SumUSD) AS SumUSD, SUM(LastPriceVND) AS LastPriceVND";
sql += " , SUM(LastPriceUSD) AS LastPriceUSD";
sql += " FROM dbo.PaymentParking";
sql += " WHERE BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "' and CustomerId = '" + hidId.Value + "' and YearMonth in (" + YearMonth + ")";
sql += " GROUP BY YearMonth, TariffsParkingName, PriceVND, PriceUSD, Vat, daysParking";
cm = db.CreateCommand(sql);
da = new SqlDataAdapter(cm);
ds = new DataSet();
da.Fill(ds);
db.Close();
rptParking.DataSource = ds.Tables[0].DefaultView;
rptParking.DataBind();
//Extra Time
sql = string.Empty;
sort = "WorkingDate" + " " + (ListSortDirection == SortDirection.Ascending ? " asc " : " desc ");
//Xuất ra toàn bộ nội dung theo Trang
sql += " SELECT * ";
sql += " FROM PaymentExtraTime";
sql += " WHERE BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "' and CustomerId = '" + hidId.Value + "' and YearMonth in (" + YearMonth + ")";
cm = db.CreateCommand(sql);
da = new SqlDataAdapter(cm);
ds = new DataSet();
da.Fill(ds);
db.Close();
rptExtraTime.DataSource = ds.Tables[0].DefaultView;
rptExtraTime.DataBind();
//Elec
ListSortDirection = SortDirection.Ascending;
sql = string.Empty;
sort = "B.FromIndex" + " " + (ListSortDirection == SortDirection.Ascending ? " asc " : " desc ");
//Xuất ra toàn bộ nội dung theo Trang
sql += " SELECT BD_Room.Name as RoomName, A.DateFrom, A.DateTo, A.Vat, B.id, B.UsedElecWaterId, B.FromIndex, B.ToIndex, B.OtherFee01, B.OtherFee02, B.Mount, B.PriceVND, B.PriceUSD, B.SumVND, B.SumUSD, ";
sql += " B.VatVND, B.VatUSD, B.LastPriceVND, B.LastPriceUSD, B.Name,B.WaterPricePercent,B.ElecPricePercent ";
sql += " FROM PaymentElecWater AS A INNER JOIN ";
sql += " PaymentElecWaterDetail AS B ON A.UsedElecWaterId = B.UsedElecWaterId INNER JOIN";
sql += " BD_Room ON A.RoomId = BD_Room.id";
sql += " WHERE A.BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "' and A.CustomerId = '" + hidId.Value + "' and TarrifsOfWaterId = 0 and A.YearMonth in (" + YearMonth + ")";
sql += " Order by " + sort;
cm = db.CreateCommand(sql);
da = new SqlDataAdapter(cm);
ds = new DataSet();
da.Fill(ds);
db.Close();
rptElec.DataSource = ds.Tables[0].DefaultView;
rptElec.DataBind();
//Water
sql = string.Empty;
//Xuất ra toàn bộ nội dung theo Trang
sql += " SELECT BD_Room.Name as RoomName, A.DateFrom, A.DateTo, A.Vat, B.id, B.UsedElecWaterId, B.FromIndex, B.ToIndex, B.OtherFee01, B.OtherFee02, B.Mount, B.PriceVND, B.PriceUSD, B.SumVND, B.SumUSD, ";
sql += " B.VatVND, B.VatUSD, B.LastPriceVND, B.LastPriceUSD, B.Name,B.WaterPricePercent,B.ElecPricePercent ";
sql += " FROM PaymentElecWater AS A INNER JOIN ";
sql += " PaymentElecWaterDetail AS B ON A.UsedElecWaterId = B.UsedElecWaterId INNER JOIN";
sql += " BD_Room ON A.RoomId = BD_Room.id";
sql += " WHERE A.BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "' and A.CustomerId = '" + hidId.Value + "' and TarrifsOfElecId = 0 and A.YearMonth in (" + YearMonth + ")";
sql += " Order by " + sort;
//.........这里部分代码省略.........
示例10: ShowData
/// <summary>
/// List data
/// </summary>
private void ShowData()
{
SqlDatabase db = new SqlDatabase();
string sql = string.Empty;
if (!Func.IsValid(ListSortExpression))
{
ListSortExpression = "A.BookingHourFrom";
ListSortDirection = SortDirection.Descending;
}
try
{
string sqlWhere = "";
if (!String.IsNullOrEmpty(txtBookingDate.Text.Trim()))
{
sqlWhere = "and BookingDate = '" + Func.FormatYYYYmmdd(txtBookingDate.Text.Trim()) + "'";
}
//Đếm số lượng record
sql += " Select COUNT(ID) ";
sql += " FROM BD_RoomBooking A";
sql += " Where Status in ('0','1') and DelFlag <> 1 and RoomId = '" + hidId.Value + "'" + sqlWhere;
int count = db.ExecuteCount(sql);
sql = string.Empty;
string sort = ListSortExpression + " " + (ListSortDirection == SortDirection.Ascending ? " asc " : " desc ");
//Xuất ra toàn bộ nội dung theo Trang
sql += " Select A.*, ROW_NUMBER() OVER(ORDER BY " + sort + ") as RowNum ";
sql += " FROM BD_RoomBooking A";
sql += " Where Status in ('0','1') and DelFlag <> 1 and RoomId = '" + hidId.Value + "'" + sqlWhere;
//Phân trang
sql = " Select * FROM (" + sql + ") AS TMP ";
sql += " WHERE RowNum BETWEEN @PageIndex*@PageSize + 1 AND (@PageIndex+1)*@PageSize ORDER BY RowNum ";
//Thực hiện câu SQL
SqlCommand cm = db.CreateCommand(sql);
cm.Parameters.AddWithValue("@PageIndex", pager.CurrentPageIndex);
cm.Parameters.AddWithValue("@PageSize", pager.PageSize);
SqlDataAdapter da = new SqlDataAdapter(cm);
DataSet ds = new DataSet();
da.Fill(ds);
db.Close();
rptList.DataSource = ds.Tables[0].DefaultView;
rptList.DataBind();
pager.Count = count;
}
catch (Exception ex)
{
ApplicationLog.WriteError(ex);
}
}示例11: ViewMultiBoth
//.........这里部分代码省略.........
xlbBook.Load(fileNameDes);
XLSheet xlsSheet = xlbBook.Sheets["TongHop"];
XLSheet xlsSheetEn = xlbBook.Sheets["TongHop_En"];
//Bill No
xlsSheet[rBillNo, cBillNo].Value = xlsSheet[rBillNo, cBillNo].Value.ToString().Replace("{%BILL_NO%}", txtBillNo.Text);
xlsSheetEn[rBillNo, cBillNo].Value = xlsSheetEn[rBillNo, cBillNo].Value.ToString().Replace("{%BILL_NO%}", txtBillNo.Text);
//Ngay Thang Nam
DateTime dtime = DateTime.Today;
string strTmp = xlsSheet[rBillDate, cBillDate].Value.ToString().Replace("{%NGAY%}", dtime.ToString("dd"));
strTmp = strTmp.Replace("{%THANG%}", dtime.ToString("MM"));
xlsSheet[rBillDate, cBillDate].Value = strTmp.Replace("{%NAM%}", dtime.ToString("yyyy"));
strTmp = xlsSheetEn[rBillDate, cBillDate].Value.ToString().Replace("{%NGAY%}", dtime.ToString("dd"));
strTmp = strTmp.Replace("{%THANG%}", dtime.ToString("MM"));
xlsSheetEn[rBillDate, cBillDate].Value = strTmp.Replace("{%NAM%}", dtime.ToString("yyyy"));
//Nam
xlsSheet[rBillMonth, cBillMonth].Value = xlsSheet[rBillMonth, cBillMonth].Value.ToString().Replace("{%NAM_THANG%}", drpMonth.SelectedValue + "/" + drpYear.SelectedValue);
xlsSheetEn[rBillMonth, cBillMonth].Value = xlsSheetEn[rBillMonth, cBillMonth].Value.ToString().Replace("{%NAM_THANG%}", drpMonth.SelectedValue + "/" + drpYear.SelectedValue);
using (SqlDatabase db = new SqlDatabase())
{
DataSet ds = new DataSet();
string sql = string.Empty;
sql = " SELECT Name, ContactName";
sql += " FROM Customer";
sql += " WHERE CustomerId = '" + lblCustomerId.Text + "' ";
using (SqlCommand cm = db.CreateCommand(sql))
{
SqlDataAdapter da = new SqlDataAdapter(cm);
da.Fill(ds);
if (ds != null)
{
DataTable dt = ds.Tables[0];
foreach (DataRow rowType in dt.Rows)
{
string Name = rowType[0].ToString();
string ContactName = rowType[1].ToString();
//Customer
xlsSheet[rCustomer, cCustomer].Value = xlsSheet[rCustomer, cCustomer].Value.ToString().Replace("{%TEN_CONG_TY%}", Name);
xlsSheetEn[rCustomer, cCustomer].Value = xlsSheetEn[rCustomer, cCustomer].Value.ToString().Replace("{%TEN_CONG_TY%}", Name);
//Contact
xlsSheet[rContact, cContact].Value = xlsSheet[rContact, cContact].Value.ToString().Replace("{%NGUOI_DAI_DIEN%}", ContactName);
xlsSheetEn[rContact, cContact].Value = xlsSheetEn[rContact, cContact].Value.ToString().Replace("{%NGUOI_DAI_DIEN%}", ContactName);
}
}
}
Hashtable contractIdLst = new Hashtable();
string contract = "";
ds = new DataSet();
sql = " SELECT Bank,Account,AccountName,Office,OfficeAddress,OfficePhone";
sql += " FROM Mst_Building";
sql += " WHERE BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "' ";
using (SqlCommand cm = db.CreateCommand(sql))
{
SqlDataAdapter da = new SqlDataAdapter(cm);
da.Fill(ds);
示例12: DoInit
/// <summary>
/// Init values
/// </summary>
protected override void DoInit()
{
try
{
Hashtable tmp = new Hashtable();
for (int i = 0; i < 24; i++)
{
drpHourFrom.Items.Add(new ListItem(Func.ParseString(i).PadLeft(2, '0'), Func.ParseString(i).PadLeft(2, '0')));
drpHourTo.Items.Add(new ListItem(Func.ParseString(i).PadLeft(2, '0'), Func.ParseString(i).PadLeft(2, '0')));
}
drpHourFrom.Items.FindByValue(DateTime.Now.AddHours(-1).ToString("hh")).Selected = true;
drpHourTo.Items.FindByValue(DateTime.Now.AddHours(1).ToString("hh")).Selected = true;
if (!String.IsNullOrEmpty(txtBookingDate.Text.Trim()))
{
DataSet ds = new DataSet();
string sql = string.Empty;
sql = " SELECT *";
sql += " FROM BD_RoomBooking ";
sql += " Where Status in ('0','1') and DelFlag <> 1 and RoomId = '" + hidId.Value + "' and BookingDate = '" + Func.FormatYYYYmmdd(txtBookingDate.Text.Trim()) + "' Order By BookingHourFrom";
using (SqlDatabase db = new SqlDatabase())
{
using (SqlCommand cm = db.CreateCommand(sql))
{
SqlDataAdapter da = new SqlDataAdapter(cm);
da.Fill(ds);
db.Close();
if (ds != null)
{
DataTable dt = ds.Tables[0];
foreach (DataRow rowType in dt.Rows)
{
int id = Func.ParseInt(rowType["Id"].ToString());
float BookingHourFrom = Func.ParseFloat(rowType["BookingHourFrom"].ToString() + "00");
float BookingMinuteFrom = Func.ParseFloat(rowType["BookingMinuteFrom"].ToString());
float BookingHourTo = Func.ParseFloat(rowType["BookingHourTo"].ToString() + "00");
float BookingMinuteTo = Func.ParseFloat(rowType["BookingMinuteTo"].ToString());
BookingRoomInfo bkf = new BookingRoomInfo(BookingHourFrom, BookingMinuteFrom, BookingHourTo, BookingMinuteTo);
tmp.Add(id, bkf);
}
}
}
}
int tblRows = 3;
int tblCols = 16;
// Create a Table and set its properties
Table tbl = new Table();
// Add the table to the placeholder control
PlaceHolder1.Controls.Add(tbl);
// Now iterate through the table and add your controls
for (int i = 0; i < tblRows; i++)
{
TableRow tr = new TableRow();
for (int j = 0; j < tblCols; j++)
{
TableCell tc = new TableCell();
TextBox txtBox = new TextBox();
txtBox.Width = 50;
string minute = j % 2 == 0 ? "00" : "30";
txtBox.BackColor = System.Drawing.Color.LightBlue;
string hour = (i * 8 + j / 2) + "00";
int hourOdd = Func.ParseInt(hour) + Func.ParseInt(minute);
foreach (int key in tmp.Keys)
{
BookingRoomInfo bkf = (BookingRoomInfo)tmp[key];
if (bkf.checkBooked(hourOdd))
{
txtBox.BackColor = System.Drawing.Color.Red;
break;
}
}
txtBox.Text = (minute == "00" ? (i * 8 + j / 2) + ":00" : (i * 8 + j / 2) + ":" + minute);
tc.Controls.Add(txtBox);
tr.Cells.Add(tc);
}
// Add the TableRow to the Table
tbl.Rows.Add(tr);
}
}
}
catch (Exception ex)
{
Console.Write(ex);
}
//return tmp;
}示例13: ShowData
/// <summary>
/// List data
/// </summary>
private void ShowData()
{
string sqlWhere = GetWhere();
SqlDatabase db = new SqlDatabase();
string sql = string.Empty;
if (!Func.IsValid(ListSortExpression))
{
ListSortExpression = "A.Modified";
ListSortDirection = SortDirection.Descending;
}
try
{
string condition = "";
switch (hidDocType.Value)
{
case "1":
break;
case "2":
condition = " And Dept = '2'";
break;
case "3":
condition = " And Dept = '3'";
break;
case "4":
condition = " And Dept = '4'";
break;
//case "5":
// condition = " And ContractId = '" + hidId.Value + "'";
// break;
//case "6":
// condition = " And CustomerId = '" + hidId.Value + "'";
// break;
//case "7":
// condition = " And StaffId = '" + hidId.Value + "'";
// break;
//case "8":
// condition = " And SuppliesId = '" + hidId.Value + "'";
// break;
//case "9":
// condition = " And EquipmentId = '" + hidId.Value + "'";
// break;
case "5":
case "6":
case "7":
case "8":
case "9":
condition = " And DocTypeId = '" + hidId.Value + "'";
break;
default:
break;
}
//Đếm số lượng record
sql += " SELECT COUNT(ID) ";
sql += " FROM [BD_Document] A";
sql += " WHERE (ID IS NOT NULL) and DelFlag <> 1 and BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "' and DocType = '" + hidDocType.Value + "'" + condition;
sql += sqlWhere;
int count = db.ExecuteCount(sql);
sql = string.Empty;
string sort = ListSortExpression + " " + (ListSortDirection == SortDirection.Ascending ? " asc " : " desc ");
//Xuất ra toàn bộ nội dung theo Trang
sql += " SELECT A.*,B.DocSubject Subject, ROW_NUMBER() OVER(ORDER BY " + sort + ") as RowNum ";
sql += " FROM BD_Document A left outer join BD_DocSubject B on A.DocSubject = B.id ";
sql += " WHERE (A.ID IS NOT NULL) and A.DelFlag <> 1 and A.BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "' and A.DocType = '" + hidDocType.Value + "'" + condition;
sql += sqlWhere;
//Phân trang
sql = " SELECT * FROM (" + sql + ") AS TMP ";
sql += " WHERE RowNum BETWEEN @PageIndex*@PageSize + 1 AND (@PageIndex+1)*@PageSize ORDER BY RowNum ";
//Thực hiện câu SQL
SqlCommand cm = db.CreateCommand(sql);
cm.Parameters.AddWithValue("@PageIndex", pager.CurrentPageIndex);
cm.Parameters.AddWithValue("@PageSize", pager.PageSize);
SqlDataAdapter da = new SqlDataAdapter(cm);
DataSet ds = new DataSet();
da.Fill(ds);
db.Close();
rptList.DataSource = ds.Tables[0].DefaultView;
rptList.DataBind();
pager.Count = count;
pager1.Count = count;
}
catch (Exception ex)
{
ApplicationLog.WriteError(ex);
}
}示例14: btnRegister_Click
protected void btnRegister_Click(object sender, EventArgs e)
{
try
{
//string delete = " Delete PaymentMonthWaterFee From PaymentMonthWaterFee A, PaymentMonthWaterFeeTmp B Where A.FlatID = B.TenementID + B.FlatID and A.YearMonth = B.YearMonth and SessionId = '" + Session.SessionID + "';";
//delete += " Delete PaymentMonthWaterFeeDetail From PaymentMonthWaterFeeDetail A, PaymentMonthWaterFeeTmp B Where A.FlatID = B.TenementID + B.FlatID and A.YearMonth = B.YearMonth and SessionId = '" + Session.SessionID + "'";
string delete = " Delete PaymentMonthWaterFee Where FlatID like '" + hidTenementID.Value + "%' and YearMonth = '"+ hidYearmonth.Value +"';";
DbHelper.ExecuteNonQuery(delete);
delete = " Delete PaymentMonthWaterFeeDetail Where FlatID like '" + hidTenementID.Value + "%' and YearMonth = '" + hidYearmonth.Value + "'";
DbHelper.ExecuteNonQuery(delete);
DataSet ds = new DataSet();
string select = "Select A.*,C.LossAvg from PaymentMonthWaterFeeTmp A, MST_Flat B, MST_Tenement C Where C.TenementId = B.TenementId and A.TenementID + A.FlatId = B.FlatID and SessionId = '" + Session.SessionID + "'";
using (SqlDatabase db = new SqlDatabase())
{
using (SqlCommand cm = db.CreateCommand(select))
{
SqlDataAdapter da = new SqlDataAdapter(cm);
da.Fill(ds);
db.Close();
if (ds != null)
{
DataTable dt = ds.Tables[0];
using (SqlConnection rConn = new SqlConnection(Gnt.Configuration.ApplicationConfiguration.ConnectionString))
{
rConn.Open();
foreach (DataRow rowType in dt.Rows)
{
string FlatID = rowType["FlatID"].ToString();
string YearMonth = rowType["YearMonth"].ToString();
string OldIndex = rowType["OldIndex"].ToString();
string NewIndex = rowType["NewIndex"].ToString();
string Created = rowType["Created"].ToString();
string Creator = rowType["Creator"].ToString();
string Modified = rowType["Modified"].ToString();
string Updator = rowType["Updator"].ToString();
string TenementID = rowType["TenementID"].ToString();
string LossAvg = rowType["LossAvg"].ToString();
string insert = " INSERT INTO PaymentMonthWaterFee (FlatID,YearMonth,OldIndex,NewIndex,DelFlag,Created,Creator,Modified,Updator,TenementID,LossAvg)" +
" Values (@FlatID,@YearMonth,@OldIndex,@NewIndex,0,@Created,@Creator,@Modified,@Updator,@TenementID,@LossAvg)";
using (SqlCommand command = new SqlCommand(insert, rConn))
{
command.CommandType = CommandType.Text;
command.Parameters.Add(new SqlParameter("@FlatID", TenementID + FlatID));
command.Parameters.Add(new SqlParameter("@YearMonth", YearMonth));
command.Parameters.Add(new SqlParameter("@OldIndex", OldIndex));
command.Parameters.Add(new SqlParameter("@NewIndex", NewIndex));
command.Parameters.Add(new SqlParameter("@Created", Created));
command.Parameters.Add(new SqlParameter("@Creator", Creator));
command.Parameters.Add(new SqlParameter("@Modified", Modified));
command.Parameters.Add(new SqlParameter("@Updator", Updator));
command.Parameters.Add(new SqlParameter("@TenementID", TenementID));
command.Parameters.Add(new SqlParameter("@LossAvg", LossAvg));
command.ExecuteNonQuery();
}
}
rConn.Close();
}
}
}
}
DbHelper.ExecuteNonQuery("Delete From PaymentMonthWaterFeeTmp where SessionId = '" + Session.SessionID + "'");
ShowData();
importDiv.Visible = false;
mvldMessage.SetCompleteMessage("Dữ liệu đã lưu, hãy kiểm tra dữ liệu.");
}
catch (Exception exc)
{
mvldMessage.AddError("Lỗi Phát Sinh：" + exc.Message);
}
}示例15: btnView_Click
//.........这里部分代码省略.........
string strFilePath = @"~\Report\Building\" + Func.ParseString(Session["__BUILDINGID__"]) + @"\BillTongQuat" + strDT + ".xlsx";
string strFilePathExport = @"../../Report/Building/" + Func.ParseString(Session["__BUILDINGID__"]) + @"/BillTongQuat" + strDT + ".xlsx";
string fileNameDes = HttpContext.Current.Server.MapPath(strFilePath);
//string fileNameDes = HttpContext.Current.Server.MapPath(@"~\Report\Building\" + Func.ParseString(Session["__BUILDINGID__"]) + @"\TongHopDienTich" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
File.Copy(fileName, fileNameDes);
xlbBook.Load(fileNameDes);
XLSheet xlsSheet = xlbBook.Sheets["TongHop"];
//Bill No
xlsSheet[rBillNo, cBillNo].Value = xlsSheet[rBillNo, cBillNo].Value.ToString().Replace("{%BILL_NO%}", txtBillNo.Text);
//Ngay Thang Nam
DateTime dtime = DateTime.Today;
string strTmp = xlsSheet[rBillDate, cBillDate].Value.ToString().Replace("{%NGAY%}", dtime.ToString("dd"));
strTmp = strTmp.Replace("{%THANG%}", dtime.ToString("MM"));
xlsSheet[rBillDate, cBillDate].Value = strTmp.Replace("{%NAM%}", dtime.ToString("yyyy"));
//Nam
xlsSheet[rBillMonth, cBillMonth].Value = xlsSheet[rBillMonth, cBillMonth].Value.ToString().Replace("{%NAM_THANG%}", drpMonth.SelectedValue + "/" + drpYear.SelectedValue);
using (SqlDatabase db = new SqlDatabase())
{
DataSet ds = new DataSet();
string sql = string.Empty;
sql = " SELECT Name, ContactName";
sql += " FROM Customer";
sql += " WHERE CustomerId = '" + lblCustomerId.Text + "' ";
using (SqlCommand cm = db.CreateCommand(sql))
{
SqlDataAdapter da = new SqlDataAdapter(cm);
da.Fill(ds);
if (ds != null)
{
DataTable dt = ds.Tables[0];
foreach (DataRow rowType in dt.Rows)
{
string Name = rowType[0].ToString();
string ContactName = rowType[1].ToString();
//Customer
xlsSheet[rCustomer, cCustomer].Value = xlsSheet[rCustomer, cCustomer].Value.ToString().Replace("{%TEN_CONG_TY%}", Name);
//Contact
xlsSheet[rContact, cContact].Value = xlsSheet[rContact, cContact].Value.ToString().Replace("{%NGUOI_DAI_DIEN%}", ContactName);
}
}
}
Hashtable contractIdLst = new Hashtable();
string contract = "";
ds = new DataSet();
sql = " SELECT Bank,Account,AccountName,Office,OfficeAddress,OfficePhone";
sql += " FROM Mst_Building";
sql += " WHERE BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "' ";
using (SqlCommand cm = db.CreateCommand(sql))
{
SqlDataAdapter da = new SqlDataAdapter(cm);
da.Fill(ds);
if (ds != null)
{