📄 excesql.cs
字号:
/// <summary>
/// DataGrid排序绑定
// /// </summary>
// /// <param name="dv">DataView</param>
// /// <param name="dg">要进行绑定的DataGrid</param>
// /// <param name="intColumn">列索引</param>
// /// <param name="bReSort">是不重新排序</param>
// /// <returns></returns>
// public bool DataGridSortBind(DataView dv,DataGrid dg,int intColumn,bool bReSort)
// {
// try
// {
// string strSortType="";//升序还是降序
// if (bReSort)
// {
// if (dg.Columns[intColumn].HeaderText.Substring(0,1) == "△")
// {
// strSortType="desc";
// }
// else
// {
// strSortType="asc";
// }
// dv.Sort = dg.Columns[intColumn].SortExpression + " " +strSortType;
// }
//
// for(int i=0;i<dg.Columns.Count;i++)
// {
// if((dg.Columns[i].HeaderText.Substring(0,1) == "△")||(dg.Columns[i].HeaderText.Substring(0,1) == "▽"))
// {
// if (bReSort)
// {
// dg.Columns[i].HeaderText = dg.Columns[i].HeaderText.Remove(0,1);
// }
// else
// {
// if (dg.Columns[i].HeaderText.Substring(0,1) == "△")
// {
// dv.Sort = dg.Columns[i].SortExpression + " asc";
// break;
// }
// else
// {
// dv.Sort = dg.Columns[i].SortExpression + " desc";
// break;
// }
// }
// }
//
// if (i==intColumn && bReSort)
// {
// if (strSortType=="asc")
// {
// dg.Columns[intColumn].HeaderText="△"+dg.Columns[intColumn].HeaderText;
// }
// else
// {
// dg.Columns[intColumn].HeaderText="▽"+dg.Columns[intColumn].HeaderText;
// }
// }
// }
//
// dg.DataSource=dv;
// dg.DataBind();
//
// return true;
// }
// catch(Exception ex)
// {
// ComC.ErrorShow("index.aspx",ex.Message);
// return false;
// }
// finally
// {
// dv.Dispose();
// }
// }
public Int64 PROC_GETBYOUTIDDS( Decimal fltPRMCUSTOMERID , ref DataSet OutCursorValue )
{
StringBuilder commandText = new StringBuilder("PKG_BASE_CUSTOMERS.PROC_GETBYOUTID");
DataSet ds = new DataSet();
try
{
OleDbConnection Conn = new OleDbConnection(strConn);
OleDbCommand DSCmd = new OleDbCommand(commandText.ToString(),Conn);
Conn.Open();
Int32 status = 0;
if(status == 0 )
{
DSCmd.CommandType = CommandType.StoredProcedure;
//Set in/out paramlist
DSCmd.Parameters.Add("PRMCUSTOMERID", OleDbType.Integer).Value = (Decimal)fltPRMCUSTOMERID;
DSCmd.Parameters["PRMCUSTOMERID"].Direction = ParameterDirection.Input;
//Set in/out paramlist
// DSCmd.Parameters.Add("IO_ALLREC", OleDbType.c).Direction = ParameterDirection.Output;
OleDbDataAdapter DBAdopter = new OleDbDataAdapter(DSCmd);
DBAdopter.Fill(ds);
OutCursorValue = ds;
if(status == 0 )
{
}
else
{
}
}
Conn.Close();
return 1;
}
catch(Exception ex)
{
throw new Exception(ex.Message);
}
}
//删除客户
public static void DelCust(string customer)
{
ExceSql.insertUpdate("update base_customers set last_date='"+System.DateTime.Today.ToShortDateString()+"',isDel=1 where Customer_ID in ('"+customer+"') ");
}
//再次激活客户
public static void ReDoCust(string customer)
{
ExceSql.insertUpdate("update base_customers set last_date='"+System.DateTime.Today.ToShortDateString()+"',Cust_jihuo='普通客户', Cust_jihuo_id=707,Cust_jihuo_all='普通客户,707' where Customer_ID in ('"+customer+"') ");
}
//填充下拉列表函数
// private int FillCountry(DropDownList ddl)//填充国籍,正常返回0，出错返回-1
// {
// ddl.Items.Clear();
// try
// {
// BASE_COUNTRY country = new BASE_COUNTRY();
// DataSet ds = country.GetAllDS();
//
// if(ds.Tables[0].Rows.Count>0)
// {
// foreach(DataRow dr in ds.Tables[0].Rows)
// {
// ListItem li = new ListItem(dr["COUNTRYNAME"].ToString(),dr["COUNTRYID"].ToString());
// ddl.Items.Add(li);
// }
// return 0;
// }
// else
// {
// return -1;
// }
// }
// catch(Exception ex)
// {
// throw ex;
// }
// }
//得到最大的号
public static int nextId(String pTableName)
{
OleDbDataReader dr=dataReader("SELECT NEXTID FROM Base_IdCouter WHERE TABLENAME ='"+pTableName+"'");
int nid=0;
if(dr.Read())
{
nid=Convert.ToInt32(dr["NEXTID"]);
}
insertUpdate("UPDATE Base_IdCouter SET NEXTID =nextId+1 WHERE TABLENAME ='"+pTableName+"' ");
return nid;
}
//得到展会名称
public static string geExhiName(string Exhi_Id)
{
string exhi_name="";
DataTable dpttable = GetBySqlToDS("select ex_name from tbl_exhibition where isdel=0 and id="+Exhi_Id+"").Tables[0];
foreach(DataRow dr in dpttable.Rows)
{
exhi_name=dr["ex_name"].ToString();
}
return exhi_name;
}
//得到传入的字段名名称
public static string getNameBySql(string sql,String orderid)
{
string exhi_name="";
DataTable dpttable = GetBySqlToDS(sql).Tables[0];
foreach(DataRow dr in dpttable.Rows)
{
exhi_name=dr[orderid].ToString();
}
return exhi_name;
}
//坚表转横表
public static string getListToDown(string sql,String orderid)
{
string exhi_name="";
DataTable dpttable = GetBySqlToDS(sql).Tables[0];
StringBuilder commandText = new StringBuilder();
foreach(DataRow dr in dpttable.Rows)
{
exhi_name=dr[orderid].ToString();
commandText.Append(exhi_name+",");
}
return commandText.ToString();
}
public static DataSet import(string path)
{
// string mystring="Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = 'F:/Documents and Settings/fzlhx/My Documents/Visual Studio Projects/stock Log/hq.xls';Extended Properties=Excel 8.0";
// OleDbConnection cnnxls = new OleDbConnection (mystring);
// OleDbDataAdapter myDa =new OleDbDataAdapter("select * from [Sheet1$]",cnnxls);
// DataSet myDs =new DataSet();
// myDa.Fill(myDs);
//cnnxls.Close();
// return myDs;
//string strConn1 = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + path+ ";Extended Properties=\"Excel 5.0;HDR=Yes;IMEX=1;\"";
string strConn1 = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn1);
conn.Open();
DataSet ds = new DataSet();
string strExcelSheet = string.Format("select * from [{0}$]","hq");
OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcelSheet, conn);
try
{
myCommand.Fill(ds, "hq");
}
catch(Exception e)
{
string a = "";
}
conn.Close();
return ds;
//
//
// string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\data\\data;Extended Properties=dBASE IV;User ID=Admin;Password=";
//
// OleDbConnection myConnection = new OleDbConnection(connString);
// OleDbCommand myCommand = new OleDbCommand(mySelectQuery,myConnection);
// myConnection.Open();
// OleDbDataAdapter Adapter = new OleDbDataAdapter();
// Adapter.SelectCommand=myCommand;
// DataSet myDs = new DataSet();
// Adapter.Fill(myDs);
// myConnection.Close();
// GridView1.DataSource = myDs.Tables[0].DefaultView;
// GridView1.DataBind();
// string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = ";
// strCon += path;
// strCon += ";Extended Properties=Excel 8.0" ;
//
// OleDbConnection myConn = new OleDbConnection(strCon) ;
// myConn.Open() ;
// string strCom = " Select * FROM [hq$] " ;
// OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom,myConn);
// DataSet dataSet1 = new DataSet();
// try
// {
// myCommand.Fill (dataSet1 , "hq");
// }
// catch(Exception e)
// {
// string a = "";
// }
// myConn.Close();
// return dataSet1;
}
public static DataTable ExcelToDataTable(string strExcelFileName, string strSheetName)
{
string strConn= "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + strExcelFileName+ ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1;\"";
//string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelFileName + ";" +"Extended Properties=Excel 5.0;";
string strExcel = string.Format("select * from [{0}$]", strSheetName);
DataSet ds = new DataSet();
using (OleDbConnection conn = new OleDbConnection(strConn))
{
conn.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);
adapter.Fill(ds, strSheetName);
conn.Close();
}
return ds.Tables[strSheetName];
}
/// <summary>
/// 将DataGrid中的数据导入Excel中,并显示Excel应用程序，
/// 注意调用该方法必须有安装Excel 2000应用程序,并且假定DataGrid中绑定的是一DataSet
/// </summary>
/// <param name="grid"></param>
/// <param name="ReportTitle"></param>
// public static void ExportDataGridToExcel(DataGrid grid,string ReportTitle)
// {
// DataTable myTable = ((DataSet)grid.DataSource).Tables[0];
//
// try
// {
// Excel.Application xlApp = new Excel.ApplicationClass();
//
// int rowIndex;
// int colIndex;
//
// rowIndex = 2;
// colIndex = 0;
//
// Excel.Workbook xlBook =xlApp.Workbooks.Add(true);
//
// if (grid.TableStyles.Count >0 )
// {
// Excel.Range range = xlApp.get_Range(xlApp.Cells[1,1],xlApp.Cells[1,grid.TableStyles[0].GridColumnStyles.Count]);
// range.MergeCells = true;
// xlApp.ActiveCell.FormulaR1C1 = ReportTitle;
// xlApp.ActiveCell.Font.Size = 18;
// xlApp.ActiveCell.Font.Bold = true;
//
// foreach(DataGridColumnStyle colu in grid.TableStyles[0].GridColumnStyles)
// {
// colIndex=colIndex +1;
// xlApp.Cells[2,colIndex] = colu.HeaderText ;
// }
//
// //得到的表所有行,赋值给单元格
// for (int row = 0;row < myTable.Rows.Count;row++)
// {
// rowIndex = rowIndex + 1;
// colIndex = 0;
// for (int col=0;col<grid.TableStyles[0].GridColumnStyles.Count;col++)
// {
// colIndex = colIndex + 1;
// xlApp.Cells[rowIndex, colIndex] = grid[row,col].ToString();
// }
// }
// }
// else
// {
// Excel.Range range = xlApp.get_Range(xlApp.Cells[1,1],xlApp.Cells[1,myTable.Columns.Count]);
// range.MergeCells = true;
// xlApp.ActiveCell.FormulaR1C1 = ReportTitle;
// xlApp.ActiveCell.Font.Size = 18;
// xlApp.ActiveCell.Font.Bold = true;
//
// //将表中的栏位名称填到Excel的第一行
// foreach(DataColumn Col in myTable.Columns)
// {
// colIndex = colIndex + 1;
// xlApp.Cells[2, colIndex] = Col.ColumnName;
// }
//
// //得到的表所有行,赋值给单元格
// for (int row = 0;row < myTable.Rows.Count;row++)
// {
// rowIndex = rowIndex + 1;
// colIndex = 0;
// for (int col=0;col<myTable.Columns.Count;col++)
// {
// colIndex = colIndex + 1;
// xlApp.Cells[rowIndex, colIndex] = grid[row,col].ToString();
// }
// }
// }
//
// xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, colIndex]).Font.Bold = true;
// xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[rowIndex, colIndex]).Borders.LineStyle = 1;
//
// xlApp.Cells.EntireColumn.AutoFit();
// xlApp.Cells.VerticalAlignment = Excel.Constants.xlCenter ;
// xlApp.Cells.HorizontalAlignment = Excel.Constants.xlCenter ;
//
// xlApp.Visible = true;
// }
// catch(Exception e)
// {
// throw e;
// }
//
//
//
// }
//FTP.ReportError("路径", DirNotFoundEx.ToString(), "返回 <a href='default.aspx'>根目录</a>.");
// FTP.ReportError("你没有权限查看", "", " 请点击<img src=pics/home_icon.gif>返回到根目录");
// ArrayList mList=new ArrayList();
//
// foreach(DataRow dRow in dSet.Tables[0].Rows)
// {
// KeyVal mObj=new KeyVal();
// mObj.strKey=dRow[0].ToString();
// mObj.strVal=dRow[1].ToString();
// mList.Add(mObj);
// }
}
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -