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
}
