public bool AddUser(string username, string password) { bool isOk = false; OleDbConnection conn = DBHelper.getConn(); //得到连接对象 conn.Open(); try { String strCommand = "INSERT INTO TB_USER (username,pwd) VALUES (?,?)"; OleDbCommand command = new OleDbCommand(strCommand, conn); OleDbParameterCollection paramCollection = command.Parameters; OleDbParameter param1 = paramCollection.Add("username", OleDbType.VarChar); param1.Value = username; OleDbParameter param2 = paramCollection.Add("pwd", OleDbType.VarChar); param2.Value = password; command.ExecuteNonQuery(); conn.Close(); isOk = true; } catch (Exception e) { conn.Close(); throw new Exception(e.Message); } return(isOk); }
public bool ChangePwd(String username, String password) { bool isOk = false; OleDbConnection conn = DBHelper.getConn(); //得到连接对象 conn.Open(); try { String strCommand = "UPDATE TB_USER SET pwd = ? where username = ?"; OleDbCommand command = new OleDbCommand(strCommand, conn); OleDbParameterCollection paramCollection = command.Parameters; OleDbParameter param1 = paramCollection.Add("pwd", OleDbType.VarChar); param1.Value = password; OleDbParameter param2 = paramCollection.Add("username", OleDbType.VarChar); param2.Value = username; command.ExecuteNonQuery(); conn.Close(); isOk = true; } catch (Exception e) { conn.Close(); throw new Exception(e.Message); } return(isOk); }
protected override void setQueryParameters(OleDbParameterCollection parameters, QueryCondition queryCondition) { ApartmentQueryCondition qc = (ApartmentQueryCondition)queryCondition; if (!string.IsNullOrWhiteSpace(qc.unit_number)) { parameters.Add("@unit_number", OleDbType.VarWChar, 255).Value = "%" + qc.unit_number + "%"; } if (!string.IsNullOrWhiteSpace(qc.floorplan_id)) { parameters.Add("@floorplan_id", OleDbType.VarWChar, 255).Value = qc.floorplan_id; } if (!string.IsNullOrWhiteSpace(qc.building_id)) { parameters.Add("@building_id", OleDbType.VarWChar, 255).Value = qc.building_id; } else if (!string.IsNullOrWhiteSpace(qc.stage_id)) { parameters.Add("@stage_id", OleDbType.VarWChar, 255).Value = qc.stage_id; } else if (!string.IsNullOrWhiteSpace(qc.project_id)) { parameters.Add("@project_id", OleDbType.VarWChar, 255).Value = qc.project_id; } }
public DataSet FindAllInstance(int systemid, int instanceid) { OleDbConnection conn = DBHelper.getConn(); //得到连接对象 String strCommand = "SELECT a.indexid,a.pid,a.indexname,b.indexvalue FROM TB_INDEX a LEFT JOIN TB_INDEX_INSTANCE b ON a.systemid = b.systemid AND a.indexid = b.indexid WHERE a.systemid = ? AND b.instanceid =?"; OleDbCommand command = new OleDbCommand(strCommand, conn); OleDbParameterCollection paramCollection = command.Parameters; OleDbParameter param1 = paramCollection.Add("systemid", OleDbType.Integer); param1.Value = systemid; OleDbParameter param2 = paramCollection.Add("instanceid", OleDbType.Integer); param2.Value = instanceid; conn.Open(); try { OleDbDataAdapter adapter = new OleDbDataAdapter(command); DataSet sourceDataSet = new DataSet(); adapter.Fill(sourceDataSet); conn.Close(); return(sourceDataSet); } catch (Exception e) { conn.Close(); throw new Exception(e.Message); } }
public VfpParameter Add(VfpParameter value) { _vfpParamCollection.Add(value); _oleDbParameterCollection.Add(value.OleDbParameter); return(value); }
private void simpleButton1_Click(object sender, EventArgs e) { if (textEdit1.Text.Trim() == "") { MessageBox.Show("请输入对应名称", "错误", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } //添加进数据库 OleDbConnection conn = DBHelper.getConn(); //得到连接对象 conn.Open(); int indexid1; try { int tmp = int.Parse(MainForm.pCurrentWin.treeList1.FocusedNode.GetValue("indexid").ToString()); //根据指标体编号 String strCommand1 = "SELECT * FROM TB_INDEX t WHERE t.indexid = " + tmp; OleDbCommand myCommand1 = new OleDbCommand(strCommand1, conn); OleDbDataReader reader; reader = myCommand1.ExecuteReader(); //执行command并得到相应的DataReader reader.Read(); //int indexid = int.Parse(reader["indexid"].ToString());//指标编号 int indexsys = int.Parse(reader["systemid"].ToString());//指标体系编号 reader.Close(); //加入指标表 String strCommand = "INSERT INTO TB_INDEX (pid,indexname,systemid) VALUES (?,?,?)"; OleDbCommand command = new OleDbCommand(strCommand, conn); OleDbParameterCollection paramCollection = command.Parameters; OleDbParameter param1 = paramCollection.Add("pid", OleDbType.VarChar); param1.Value = tmp; OleDbParameter param2 = paramCollection.Add("indexname", OleDbType.VarChar); param2.Value = textEdit1.Text; OleDbParameter param3 = paramCollection.Add("systemid", OleDbType.VarChar); param3.Value = indexsys; command.ExecuteNonQuery(); //根据指标体系名查找指标体系编号 string strCommand2 = "select max(indexid) from TB_INDEX"; OleDbCommand myCommand2 = new OleDbCommand(strCommand2, conn); object o = myCommand2.ExecuteScalar(); indexid1 = int.Parse(o.ToString()); } catch (System.Exception ex) { conn.Close(); throw new Exception("[ERROR] 数据库操作出现异常:" + ex.Message); } MainForm.pCurrentWin.treeList1.BeginUnboundLoad(); MainForm.pCurrentWin.treeList1.AppendNode(new object[] { indexid1, MainForm.pCurrentWin.nodeid, textEdit1.Text }, MainForm.pCurrentWin.nodeid); MainForm.pCurrentWin.treeList1.EndUnboundLoad(); MainForm.pCurrentWin.SetImageIndex(MainForm.pCurrentWin.treeList1, null, 20, 21); conn.Close(); this.Close(); }
private OleDbCommand method_5(string string_1, OleDbConnection oleDbConnection_0) { string str = string.Concat("INSERT INTO [", string_1, "] ([Name], [Category], [Object]) Values (?, ?, ?)"); OleDbCommand oleDbCommand = new OleDbCommand(str, oleDbConnection_0); OleDbParameterCollection parameters = oleDbCommand.Parameters; parameters.Add("Name", OleDbType.Char); parameters.Add("Category", OleDbType.Char); parameters.Add("Object", OleDbType.Binary); return(oleDbCommand); }
private OleDbCommand method_6(string string_1, OleDbConnection oleDbConnection_0) { string str = string.Concat("Update [", string_1, "] Set [Name] = ?, [Category] = ?, [Object] = ? Where [ID] = ?"); OleDbCommand oleDbCommand = new OleDbCommand(str, oleDbConnection_0); OleDbParameterCollection parameters = oleDbCommand.Parameters; parameters.Add("Name", OleDbType.Char); parameters.Add("Category", OleDbType.Char); parameters.Add("Object", OleDbType.Binary); parameters.Add("ID", OleDbType.Integer); return(oleDbCommand); }
protected override void setQueryParameters(OleDbParameterCollection parameters, QueryCondition queryCondition) { BuildingQueryCondition qc = (BuildingQueryCondition)queryCondition; if (!string.IsNullOrWhiteSpace(qc.stage_id)) { parameters.Add("@stage_id", OleDbType.VarWChar, 255).Value = qc.stage_id; } else if (!string.IsNullOrWhiteSpace(qc.project_id)) { parameters.Add("@project_id", OleDbType.VarWChar, 255).Value = qc.project_id; } }
public virtual OleDbCommand CreateInsertCommand() { const string insertCmd = "INSERT INTO sampleLog " + "(logTime, event) " + "VALUES (?, ?)"; OleDbCommand cmd = new OleDbCommand(insertCmd); OleDbParameterCollection pc = cmd.Parameters; pc.Add("logTime", OleDbType.DBTimeStamp, 0, "logTime"); pc.Add("event", OleDbType.Char, 32, "event"); return(cmd); }
public bool DeleteUser(string username) { bool isOk = false; OleDbConnection conn = DBHelper.getConn(); //得到连接对象 conn.Open(); try { String strCommand = "DELETE FROM TB_USER where username = ?"; OleDbCommand command = new OleDbCommand(strCommand, conn); OleDbParameterCollection paramCollection = command.Parameters; OleDbParameter param1 = paramCollection.Add("username", OleDbType.VarChar); param1.Value = username; command.ExecuteNonQuery(); conn.Close(); isOk = true; } catch (Exception e) { conn.Close(); throw new Exception(e.Message); } return(isOk); }
/// <summary> /// Builds an OleDbParametersCollection for an INSERT command, according to the DbTypeParameters in this collection. /// </summary> /// <param name="a_oParams">The OleDbParameterCollection to be filled.</param> private void AddInsertCommandParameters(OleDbParameterCollection a_oParams) { foreach (DbTypeParameter l_oCurrent in this) { a_oParams.Add(l_oCurrent.ParameterName, l_oCurrent.Value); } }
// <Snippet1> public void CreateParamCollection(OleDbCommand command) { OleDbParameterCollection paramCollection = command.Parameters; paramCollection.Add("@CategoryName", OleDbType.Char); paramCollection.Add("@Description", OleDbType.Char); paramCollection.Add("@Picture", OleDbType.Binary); string parameterNames = ""; for (int i = 0; i < paramCollection.Count; i++) { parameterNames += paramCollection[i].ToString() + "\n"; } Console.WriteLine(parameterNames); paramCollection.Clear(); }
/// <summary> /// Creates insert command. /// </summary> /// <param name="tableName">Table name.</param> /// <param name="tableDescription">Table Description.</param> /// <param name="fields">Fields to export.</param> /// <returns>Insert command.</returns> private OleDbCommand _CreateInsertCommand(string tableName, TableDescription tableDescription, ICollection <string> fields) { var command = new OleDbCommand(); var valueNames = new StringBuilder(); var values = new StringBuilder(); OleDbParameterCollection parameters = command.Parameters; foreach (string field in fields) { if (!string.IsNullOrEmpty(valueNames.ToString())) { valueNames.Append(SQL_PARAM_SEPARATOR); values.Append(SQL_PARAM_SEPARATOR); } FieldInfo info = tableDescription.GetFieldInfo(field); Debug.Assert(null != info); string realName = _FormatFieldName(field); valueNames.Append(realName); values.Append(SQL_VALUE_SYMBOL); parameters.Add(field, info.Type, info.Size, field); } command.CommandText = string.Format(SQL_INSERT_COMMAND_FORMAT, tableName, valueNames.ToString(), values.ToString()); return(command); }
protected override void setParameters(OleDbParameterCollection parameters, Media entity) { parameters.Add("@project_id", OleDbType.VarWChar, 255).Value = entity.project_id; parameters.Add("@name", OleDbType.VarWChar, 255).Value = entity.name; parameters.Add("@small_url", OleDbType.VarWChar, 255).Value = entity.small_url; parameters.Add("@medium_url", OleDbType.VarWChar, 255).Value = entity.medium_url; parameters.Add("@large_url", OleDbType.VarWChar, 255).Value = entity.large_url; parameters.Add("@alt", OleDbType.VarWChar, 255).Value = entity.alt; parameters.Add("@media_type", OleDbType.UnsignedTinyInt).Value = entity.media_type; parameters.Add("@content_type", OleDbType.UnsignedTinyInt).Value = entity.content_type; parameters.Add("@removed", OleDbType.UnsignedTinyInt).Value = Utils.boolToByte(entity.removed); if (!string.IsNullOrWhiteSpace(entity.id)) { parameters.Add("@id", OleDbType.VarWChar, 255).Value = entity.id; } }
public List <IndexInstance> FindDistinctIndexInstance(int systemid) { OleDbConnection conn = DBHelper.getConn(); //得到连接对象 String strCommand = "SELECT DISTINCT(instanceid),instancename FROM TB_INDEX_INSTANCE where systemid = ?"; OleDbCommand command = new OleDbCommand(strCommand, conn); OleDbParameterCollection paramCollection = command.Parameters; OleDbParameter param1 = paramCollection.Add("systemid", OleDbType.Integer); param1.Value = systemid; conn.Open(); OleDbDataReader reader; reader = command.ExecuteReader(); //执行command并得到相应的DataReader List <IndexInstance> list = new List <IndexInstance>(); while (reader.Read()) { IndexInstance indexInstance = new IndexInstance(); //indexInstance.Indexid = int.Parse(reader["indexid"].ToString()); indexInstance.Instanceid = int.Parse(reader["instanceid"].ToString()); //indexInstance.Indexvalue = double.Parse(reader["indexvalue"].ToString()); indexInstance.Instancename = reader["instancename"].ToString(); list.Add(indexInstance); } reader.Close(); conn.Close(); return(list); }
public OleDbCommand CreateInsertCommand() { const string insertCmd = "INSERT INTO eventLog " + "(eventTime, system, category, message) " + "VALUES (?, ?, ?, ?)"; OleDbCommand cmd = new OleDbCommand(insertCmd); OleDbParameterCollection pc = cmd.Parameters; pc.Add("eventTime", OleDbType.DBTimeStamp, 0, "eventTime"); pc.Add("system", OleDbType.Char, 16, "system"); pc.Add("category", OleDbType.Char, 16, "category"); pc.Add("message", OleDbType.VarChar, 512, "message"); return(cmd); }
public DataSet FindAllIndex(int systemid) { OleDbConnection conn = DBHelper.getConn(); //得到连接对象 String strCommand = "SELECT * FROM TB_INDEX where systemid = ?"; OleDbCommand command = new OleDbCommand(strCommand, conn); OleDbParameterCollection paramCollection = command.Parameters; OleDbParameter param1 = paramCollection.Add("systemid", OleDbType.Integer); param1.Value = systemid; conn.Open(); try { OleDbDataAdapter adapter = new OleDbDataAdapter(command); DataSet sourceDataSet = new DataSet(); adapter.Fill(sourceDataSet); conn.Close(); return(sourceDataSet); } catch (Exception e) { conn.Close(); throw new Exception(e.Message); } }
// <Snippet1> public void CreateParameters(OleDbConnection connection) { OleDbCommand command = new OleDbCommand( "SELECT * FROM Customers WHERE CustomerID = ?", connection); OleDbParameterCollection paramCollection = command.Parameters; OleDbParameter parameter = paramCollection.Add( "CustomerID", OleDbType.VarChar, 5, "CustomerID"); }
protected override void setParameters(OleDbParameterCollection parameters, Building entity) { parameters.Add("@stage_id", OleDbType.VarWChar, 255).Value = entity.stage_id; parameters.Add("@building_num", OleDbType.VarWChar, 255).Value = entity.building_num; parameters.Add("@description", OleDbType.LongVarWChar).Value = entity.description; parameters.Add("@finish_date", OleDbType.Date).Value = entity.finish_date; parameters.Add("@floor_count", OleDbType.SmallInt).Value = entity.floor_count; parameters.Add("@orientation", OleDbType.SmallInt).Value = entity.orientation; parameters.Add("@ready_house", OleDbType.UnsignedTinyInt).Value = Utils.boolToByte(entity.ready_house); parameters.Add("@removed", OleDbType.UnsignedTinyInt).Value = Utils.boolToByte(entity.removed); if (!string.IsNullOrWhiteSpace(entity.id)) { parameters.Add("@id", OleDbType.VarWChar, 255).Value = entity.id; } }
private int GetCountOfSharedState(string path) { string getSharedStateCount = "SELECT COUNT(*)" + " FROM aspnet_PagePersonalizationAllUsers AllUsers, aspnet_Paths Paths" + " WHERE AllUsers.PathId = Paths.PathId AND Paths.ApplicationId = @ApplicationId"; AccessConnectionHolder connectionHolder = null; OleDbConnection connection = null; int count = 0; try { try { connectionHolder = GetConnectionHolder(); connection = connectionHolder.Connection; OleDbCommand command = new OleDbCommand(); command.Connection = connection; OleDbParameterCollection parameters = command.Parameters; int appId = GetApplicationID(connectionHolder); parameters.AddWithValue("ApplicationId", appId); if (path != null) { getSharedStateCount += " AND Paths.Path LIKE '%'+@Path+'%'"; OleDbParameter parameter = parameters.Add("Path", OleDbType.WChar); parameter.Value = path; } command.CommandText = getSharedStateCount; object result = command.ExecuteScalar(); if ((result != null) && (result is Int32)) { count = (Int32)result; } } finally { if (connectionHolder != null) { connectionHolder.Close(); connectionHolder = null; } } } catch { throw; } return(count); }
void SetParameterValue(OleDbParameterCollection Params, object value, OleDbType type) { if (value != null) { Params.Add("?", type).Value = value; } else { Params.AddWithValue("?", DBNull.Value); } }
/// <summary> /// Logs an error to the database. /// </summary> /// <remarks> /// Use the stored procedure called by this implementation to set a /// policy on how long errors are kept in the log. The default /// implementation stores all errors for an indefinite time. /// </remarks> public override string Log(Error error) { if (error == null) { throw new ArgumentNullException("error"); } string errorXml = ErrorXml.EncodeString(error); using (OleDbConnection connection = new OleDbConnection(this.ConnectionString)) using (OleDbCommand command = connection.CreateCommand()) { connection.Open(); command.CommandType = CommandType.Text; command.CommandText = @"INSERT INTO ELMAH_Error (Application, Host, Type, Source, Message, UserName, StatusCode, TimeUtc, AllXml) VALUES (@Application, @Host, @Type, @Source, @Message, @UserName, @StatusCode, @TimeUtc, @AllXml)"; command.CommandType = CommandType.Text; OleDbParameterCollection parameters = command.Parameters; parameters.Add("@Application", OleDbType.VarChar, _maxAppNameLength).Value = ApplicationName; parameters.Add("@Host", OleDbType.VarChar, 30).Value = error.HostName; parameters.Add("@Type", OleDbType.VarChar, 100).Value = error.Type; parameters.Add("@Source", OleDbType.VarChar, 60).Value = error.Source; parameters.Add("@Message", OleDbType.LongVarChar, error.Message.Length).Value = error.Message; parameters.Add("@User", OleDbType.VarChar, 50).Value = error.User; parameters.Add("@StatusCode", OleDbType.Integer).Value = error.StatusCode; parameters.Add("@TimeUtc", OleDbType.Date).Value = error.Time.ToUniversalTime(); parameters.Add("@AllXml", OleDbType.LongVarChar, errorXml.Length).Value = errorXml; command.ExecuteNonQuery(); using (OleDbCommand identityCommand = connection.CreateCommand()) { identityCommand.CommandType = CommandType.Text; identityCommand.CommandText = "SELECT @@IDENTITY"; return(Convert.ToString(identityCommand.ExecuteScalar(), CultureInfo.InvariantCulture)); } } }
/// <summary> /// Returns the specified error from the database, or null /// if it does not exist. /// </summary> public override ErrorLogEntry GetError(string id) { if (id == null) { throw new ArgumentNullException("id"); } if (id.Length == 0) { throw new ArgumentException(null, "id"); } int errorId; try { errorId = int.Parse(id, CultureInfo.InvariantCulture); } catch (FormatException e) { throw new ArgumentException(e.Message, "id", e); } catch (OverflowException e) { throw new ArgumentException(e.Message, "id", e); } string errorXml; using (OleDbConnection connection = new OleDbConnection(this.ConnectionString)) using (OleDbCommand command = connection.CreateCommand()) { command.CommandText = @"SELECT AllXml FROM ELMAH_Error WHERE ErrorId = @ErrorId"; command.CommandType = CommandType.Text; OleDbParameterCollection parameters = command.Parameters; parameters.Add("@ErrorId", OleDbType.Integer).Value = errorId; connection.Open(); errorXml = (string)command.ExecuteScalar(); } if (errorXml == null) { return(null); } Error error = ErrorXml.DecodeString(errorXml); return(new ErrorLogEntry(this, id, error)); }
protected override void setQueryParameters(OleDbParameterCollection parameters, QueryCondition queryCondition) { MediaQueryCondition qc = (MediaQueryCondition)queryCondition; if (!string.IsNullOrWhiteSpace(qc.url)) { parameters.Add("@url", OleDbType.VarWChar, 255).Value = "%" + qc.url + "%"; } if (!string.IsNullOrWhiteSpace(qc.floorplan_id)) { parameters.Add("@floorplan_id", OleDbType.VarWChar, 255).Value = qc.floorplan_id; } else if (!string.IsNullOrWhiteSpace(qc.project_id)) { if (!qc.project_id.Equals("-1")) { parameters.Add("@project_id", OleDbType.VarWChar, 255).Value = qc.project_id; } } if (!string.IsNullOrWhiteSpace(qc.overview_project_id)) { parameters.Add("@overview_project_id", OleDbType.VarWChar, 255).Value = qc.overview_project_id; } if (qc.media_type != 222) { parameters.Add("@media_type", OleDbType.UnsignedTinyInt).Value = qc.media_type; } if (qc.content_type != 222) { parameters.Add("@content_type", OleDbType.UnsignedTinyInt).Value = qc.content_type; } }
protected override void setParameters(OleDbParameterCollection parameters, Stage entity) { parameters.Add("@project_id", OleDbType.VarWChar, 255).Value = entity.project_id; parameters.Add("@name", OleDbType.VarWChar, 255).Value = entity.name; parameters.Add("@description", OleDbType.LongVarWChar).Value = entity.description; parameters.Add("@finish_date", OleDbType.Date).Value = entity.finish_date; parameters.Add("@removed", OleDbType.UnsignedTinyInt).Value = Utils.boolToByte(entity.removed); if (!string.IsNullOrWhiteSpace(entity.id)) { parameters.Add("@id", OleDbType.VarWChar, 255).Value = entity.id; } }
// ---------------------------------------------------------------- // ---------------------------------------------------------------- private OleDbCommand GetOrderLinesCommand(String strStoredProcName, ref OleDbConnection objConnect, String strCommandType) { // creates a new Command object and adds parameters, specifying the // column name and version from which values will be taken OleDbCommand objCommand = new OleDbCommand(strStoredProcName, objConnect); objCommand.CommandType = CommandType.StoredProcedure; OleDbParameterCollection colParams = objCommand.Parameters; OleDbParameter objParam; objParam = colParams.Add("OrderID", OleDbType.Integer, 4, "OrderID"); objParam.SourceVersion = DataRowVersion.Original; objParam = colParams.Add("ProductID", OleDbType.Integer, 4, "ProductID"); objParam.SourceVersion = DataRowVersion.Original; if (strCommandType != "INSERT") { // add "Original" parameter values for Delete and Update actions objParam = colParams.Add("OldUnitPrice", OleDbType.Decimal, 4, "UnitPrice"); objParam.SourceVersion = DataRowVersion.Original; objParam = colParams.Add("OldQuantity", OleDbType.SmallInt, 2, "Quantity"); objParam.SourceVersion = DataRowVersion.Original; objParam = colParams.Add("OldDiscount", OleDbType.Double, 2, "Discount"); objParam.SourceVersion = DataRowVersion.Original; } if (strCommandType != "DELETE") { // add "Current" parameter values for Update and Insert actions objParam = colParams.Add("NewUnitPrice", OleDbType.Decimal, 4, "UnitPrice"); objParam.SourceVersion = DataRowVersion.Current; objParam = colParams.Add("NewQuantity", OleDbType.SmallInt, 2, "Quantity"); objParam.SourceVersion = DataRowVersion.Current; objParam = colParams.Add("NewDiscount", OleDbType.Double, 2, "Discount"); objParam.SourceVersion = DataRowVersion.Current; } return(objCommand); }
protected void ApplyParameters() { if (_paramList == null) { return; } if (_dataAdpater == null) { return; } OleDbParameterCollection CommandParams = _dataAdpater.SelectCommand.Parameters; CommandParams.Clear(); int i; for (i = 0; i < _paramList.Count; i++) { CommandParams.Add(new OleDbParameter("@P" + i, _paramList[i])); } }
public static bool DataTableExportToExcel(DataTable dt, string filename, String tabName, ref String reMsg) { if (dt.Rows.Count <= 0) { reMsg = "目前无数据不需要导出"; return(false); } int rows = dt.Rows.Count; int cols = dt.Columns.Count; StringBuilder sb = new StringBuilder(); string connString = String.Empty; connString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;", filename); sb.Append("CREATE TABLE " + tabName + " ("); String colName = String.Empty; String colNames = String.Empty; String colNamePramas = String.Empty; String colType = String.Empty; for (int i = 0; i < cols; i++) { colName = dt.Columns[i].ColumnName.ToString(); colType = dt.Columns[i].DataType.ToString(); colType = NetDataTypeToDataBaseType(colType); if (i == 0) { sb.Append(colName + " " + colType); colNames += colName; colNamePramas += "@" + colName; } else { sb.Append(", " + colName + " " + colType); colNames += "," + colName; colNamePramas += ",@" + colName; } } sb.Append(" )"); if (colNames == String.Empty) { reMsg = "数据集的列数必须大于0"; return(false); } using (OleDbConnection objConn = new OleDbConnection(connString)) { OleDbCommand objCmd = new OleDbCommand(); objCmd.Connection = objConn; objCmd.CommandText = sb.ToString(); try { objConn.Open(); objCmd.ExecuteNonQuery(); } catch (Exception e) { reMsg = "在Excel中创建表失败,错误信息:" + e.Message; return(false); } sb.Remove(0, sb.Length); sb.Append(" insert into " + tabName + " (" + colNames + ") values(" + colNamePramas + " )"); objCmd.CommandText = sb.ToString(); OleDbParameterCollection param = objCmd.Parameters; for (int i = 0; i < cols; i++) { colType = dt.Columns[i].DataType.ToString(); colName = dt.Columns[i].ColumnName.ToString(); if (colType == "System.String") { param.Add(new OleDbParameter("@" + colName, OleDbType.VarChar)); } else if (colType == "System.DateTime") { param.Add(new OleDbParameter("@" + colName, OleDbType.Date)); } else if (colType == "System.Boolean") { param.Add(new OleDbParameter("@" + colName, OleDbType.Boolean)); } else if (colType == "System.Decimal") { param.Add(new OleDbParameter("@" + colName, OleDbType.Decimal)); } else if (colType == "System.Double") { param.Add(new OleDbParameter("@" + colName, OleDbType.Double)); } else if (colType == "System.Single") { param.Add(new OleDbParameter("@" + colName, OleDbType.Single)); } else if (colType == "System.Single") { param.Add(new OleDbParameter("@" + colName, OleDbType.Single)); } else { param.Add(new OleDbParameter("@" + colName, OleDbType.Integer)); } } foreach (DataRow row in dt.Rows) { for (int i = 0; i < param.Count; i++) { param[i].Value = row[i]; } objCmd.ExecuteNonQuery(); } } reMsg = "数据成功导出"; return(true); }
/// <summary> /// 写数据到Excel。 /// </summary> /// <param name="dtSource">数据源</param> /// <param name="filePath">Excel导出路径</param> /// <param name="excelVersion">excel版本,为ExcelVersion类型</param> /// <param name="pHDR">第一行是否标题,为HDRType类型</param> public static void SetData(DataTable dtSource, string filePath, string excelVersion, string pHDR) { //数据源为空 if (dtSource == null) { throw new Exception("无数据可导"); } //保存路径为空 if (string.IsNullOrEmpty(filePath)) { throw new Exception("未设置Excel保存路径"); } //删除文件 if (File.Exists(filePath)) { File.Delete(filePath); } //链接字符串 string connectionString = string.Format(m_excelVersion[excelVersion], filePath, pHDR); // 连接Excel using (OleDbConnection Connection = new OleDbConnection(connectionString)) { Connection.Open(); //导入数据 using (OleDbCommand command = new OleDbCommand()) { command.Connection = Connection; //构建列 格式如:Name VarChar,CreateDate Date string colList = CreateExcelColums(dtSource); //构建插入SQL语句 //格式如 "INSERT INTO TABLE [tablename](col1,col2,col3) VALUES(@col1,@col2,@col3)"; StringBuilder sbColumNames = new StringBuilder(); StringBuilder sbColumValues = new StringBuilder(); foreach (DataColumn dc in dtSource.Columns) { sbColumNames.AppendFormat(",[{0}]", dc.ColumnName); sbColumValues.AppendFormat(",@{0}", dc.ColumnName); } //去掉多余的逗号 sbColumNames.Remove(0, 1); sbColumValues.Remove(0, 1); //当数据量超过每页最大数据量时,自动分页 int totalRows = dtSource.Rows.Count;//总数据量 int pageIndex = 0; //开始插入数据 do...while循环是为了处理分页逻辑 do { //计算此轮插入的数据量 int insertRows = m_maxSheelSize - 1; //如果总数据量没有达到容量 if (totalRows < insertRows) { insertRows = totalRows; } string tableName = dtSource.TableName + pageIndex; if (pageIndex == 0) { tableName = "Sheet1"; } //创建表框架 StringBuilder sbCom = new StringBuilder(); sbCom.Append("CREATE TABLE ["); sbCom.Append(tableName); sbCom.Append("]("); sbCom.Append(colList); sbCom.Append(")"); command.CommandText = sbCom.ToString(); //try //{ command.ExecuteNonQuery(); //} //catch //{ // //如果使用Create语句创建失败则直接创建Excel文件 // CreateExcelFile(filePath, excelVersion, command.CommandText); //} //插入数据 sbCom = new StringBuilder(); sbCom.AppendFormat("INSERT INTO [{0}]({1}) VALUES({2})", tableName, sbColumNames.ToString(), sbColumValues.ToString()); int startIndex = pageIndex * (m_maxSheelSize - 1); int endIndex = pageIndex * (m_maxSheelSize - 1) + insertRows; for (int i = startIndex; i < endIndex; i++) { DataRow drData = dtSource.Rows[i]; OleDbParameterCollection dbParam = command.Parameters; dbParam.Clear(); foreach (DataColumn dc in dtSource.Columns) { dbParam.Add(new OleDbParameter("@" + dc.ColumnName, GetOleDbTypeByDataColumn(dc))); dbParam["@" + dc.ColumnName].Value = drData[dc.ColumnName]; } command.CommandText = sbCom.ToString(); command.ExecuteNonQuery(); } //计算剩余数据量 totalRows = totalRows - insertRows; pageIndex++; } while (totalRows > 0); }//end of using OleDbCommand }// end of using OleDbConnection }