DataTable 分页
/// <summary>
/// DataTable插入表
/// </summary>
/// <param name="selectSql">查询sql</param>
/// <param name="tableName"></param>
/// <param name="isOpen"></param>
/// <returns></returns>
public int InsertDataTableToTables(string selectSql, string tableName, bool isOpen)
{
int count = 0;
int spitCount = 2;
int totalRecords = Convert.ToInt32(oleDBOutport.GetDataSetToTableName("select count(*) as Records from " + tableName, tableName, isOpen).Tables[0].Rows[0][0].ToString());
if (totalRecords > spitCount)
{
int begin = 0, end = 0;
for (int i = 0; i <= totalRecords; i += spitCount)
{
begin = (end + 1) > totalRecords ? totalRecords : end == 0 ? 0 : end + 1;
end = i + spitCount;
end = (end > totalRecords) ? end = totalRecords : end;
try
{
DataTable dt = new DataTable();
dt = oleDBOutport.GetDataTable(selectSql, begin, end);
if (dt != null)
{
count += oleDBImport.SaveDataInsertOleDbParameter(dt, tableName, true, true);
}
}
catch { }
}
}
else
{
DataSet ds = new DataSet();
ds = oleDBOutport.GetDataSetToTableName(selectSql, tableName, isOpen);
if (ds != null)
{
count = oleDBImport.SaveDataInsertOleDbParameter(ds.Tables[0], tableName, true, true);
}
}
/// <summary>
/// SQL Insert 插入表
/// </summary>
/// <param name="dt">Import 导出数据</param>
/// <param name="tableName">导出数据表名</param>
/// <param name="isOpen">是否打开连接</param>
/// <param name="isDelete">是否表清空数据</param>
/// <returns></returns>
public int SaveDataInsertOleDbParameter(DataTable dt, string tableName, bool isOpen, bool isDelete)
{
DateTime begDate = DateTime.Now;
bool isError = false;
int count = 0;
string sql = "";
string fieldStr = "";
string valueStr = "";
int i = 0;
if (isOpen)
{
Open();
}
try
{
if (!isDelete)
{
try
{
int delCount = ExecuteSQL("delete " + tableName);
outLog(LogFile.InsertData, "删除 表名 【" + tableName + "】 记录数【" + delCount.ToString() + "】", begDate);
}
catch (Exception e)
{
LogManager.WriteLog(LogFile.InsertDataError, "删除 表 " + tableName + " 错误：" + e.Message);
isError = true;
}
}
if (!isError)
{
for (i = 0; i < dt.Columns.Count; i++)
{
fieldStr += dt.Columns[i].ColumnName + ",";
}
fieldStr = fieldStr.Substring(0, fieldStr.Length - 1);
for (i = 0; i < dt.Rows.Count; i++)
{
List<OleDbParameter> listOleDbParameter = new List<OleDbParameter>();
sql = "insert into {0}({1}) values({2})";
valueStr = "";
for (int j = 0; j < dt.Columns.Count; j++)
{
TypeCode t = System.Type.GetTypeCode(dt.Columns[j].DataType);
switch (t)
{
case System.TypeCode.Object:
{
int pIndex = listOleDbParameter.Count + 1;
OleDbParameter oleDbParameter = new OleDbParameter("@P" + pIndex, OleDbType.Binary);
valueStr += "?,";
byte[] bytes = (byte[])dt.Rows[i][j];
oleDbParameter.Value = bytes;
listOleDbParameter.Add(oleDbParameter);
break;
}
case System.TypeCode.Byte:
{
int pIndex = listOleDbParameter.Count + 1;
OleDbParameter oleDbParameter = new OleDbParameter("@P" + pIndex, OleDbType.Binary);
valueStr += "?,";
byte[] bytes = (byte[])dt.Rows[i][j];
oleDbParameter.Value = bytes;
listOleDbParameter.Add(oleDbParameter);
valueStr += (dt.Rows[i][j].ToString() == null || dt.Rows[i][j].ToString().Equals("")) ? "''" : dt.Rows[i][j].ToString() + ",";
break;
}
case System.TypeCode.Char:
{
int pIndex = listOleDbParameter.Count + 1;
OleDbParameter oleDbParameter = new OleDbParameter("@P" + pIndex, OleDbType.Char);
valueStr += "?,";
oleDbParameter.Value = (dt.Rows[i][j].ToString() == null || dt.Rows[i][j].ToString().Equals("")) ? "''" : dt.Rows[i][j].ToString();
listOleDbParameter.Add(oleDbParameter);
break;
}
case System.TypeCode.Decimal:
{
int pIndex = listOleDbParameter.Count + 1;
OleDbParameter oleDbParameter = new OleDbParameter("@P" + pIndex, OleDbType.Decimal);
valueStr += "?,";
oleDbParameter.Value = (dt.Rows[i][j].ToString() == null || dt.Rows[i][j].ToString().Equals("")) ? "0.0" : dt.Rows[i][j].ToString();
listOleDbParameter.Add(oleDbParameter);
break;
}
case System.TypeCode.Double:
{
int pIndex = listOleDbParameter.Count + 1;
OleDbParameter oleDbParameter = new OleDbParameter("@P" + pIndex, OleDbType.Double);
valueStr += "?,";
oleDbParameter.Value = (dt.Rows[i][j].ToString() == null || dt.Rows[i][j].ToString().Equals("")) ? "0.0" : dt.Rows[i][j].ToString();
listOleDbParameter.Add(oleDbParameter);
break;
}
case System.TypeCode.Int16:
{
int pIndex = listOleDbParameter.Count + 1;
OleDbParameter oleDbParameter = new OleDbParameter("@P" + pIndex, OleDbType.Integer);
valueStr += "?,";
oleDbParameter.Value = (dt.Rows[i][j].ToString() == null || dt.Rows[i][j].ToString().Equals("")) ? "0" : dt.Rows[i][j].ToString();
listOleDbParameter.Add(oleDbParameter);
break;
}
case System.TypeCode.Int32:
{
int pIndex = listOleDbParameter.Count + 1;
OleDbParameter oleDbParameter = new OleDbParameter("@P" + pIndex, OleDbType.Integer);
valueStr += "?,";
oleDbParameter.Value = (dt.Rows[i][j].ToString() == null || dt.Rows[i][j].ToString().Equals("")) ? "0" : dt.Rows[i][j].ToString();
listOleDbParameter.Add(oleDbParameter);
break;
}
case System.TypeCode.Int64:
{
int pIndex = listOleDbParameter.Count + 1;
OleDbParameter oleDbParameter = new OleDbParameter("@P" + pIndex, OleDbType.Integer);
valueStr += "?,";
oleDbParameter.Value = (dt.Rows[i][j].ToString() == null || dt.Rows[i][j].ToString().Equals("")) ? "0" : dt.Rows[i][j].ToString();
listOleDbParameter.Add(oleDbParameter);
break;
}
case System.TypeCode.SByte:
{
int pIndex = listOleDbParameter.Count + 1;
OleDbParameter oleDbParameter = new OleDbParameter("@P" + pIndex, OleDbType.Binary);
valueStr += "?,";
byte[] bytes = (byte[])dt.Rows[i][j];
oleDbParameter.Value = bytes;
listOleDbParameter.Add(oleDbParameter);
valueStr += (dt.Rows[i][j].ToString() == null || dt.Rows[i][j].ToString().Equals("")) ? "''" : dt.Rows[i][j].ToString() + ",";
break;
}
case System.TypeCode.Single:
{
int pIndex = listOleDbParameter.Count + 1;
OleDbParameter oleDbParameter = new OleDbParameter("@P" + pIndex, OleDbType.Single);
valueStr += "?,";
oleDbParameter.Value = (dt.Rows[i][j].ToString() == null || dt.Rows[i][j].ToString().Equals("")) ? "0.0" : dt.Rows[i][j].ToString();
listOleDbParameter.Add(oleDbParameter);
break;
}
case System.TypeCode.UInt16:
{
int pIndex = listOleDbParameter.Count + 1;
OleDbParameter oleDbParameter = new OleDbParameter("@P" + pIndex, OleDbType.Integer);
valueStr += "?,";
oleDbParameter.Value = (dt.Rows[i][j].ToString() == null || dt.Rows[i][j].ToString().Equals("")) ? "0" : dt.Rows[i][j].ToString();
listOleDbParameter.Add(oleDbParameter);
break;
}
case System.TypeCode.UInt32:
{
int pIndex = listOleDbParameter.Count + 1;
OleDbParameter oleDbParameter = new OleDbParameter("@P" + pIndex, OleDbType.Integer);
valueStr += "?,";
oleDbParameter.Value = (dt.Rows[i][j].ToString() == null || dt.Rows[i][j].ToString().Equals("")) ? "0" : dt.Rows[i][j].ToString();
listOleDbParameter.Add(oleDbParameter);
break;
}
case System.TypeCode.UInt64:
{
int pIndex = listOleDbParameter.Count + 1;
OleDbParameter oleDbParameter = new OleDbParameter("@P" + pIndex, OleDbType.Integer);
valueStr += "?,";
oleDbParameter.Value = (dt.Rows[i][j].ToString() == null || dt.Rows[i][j].ToString().Equals("")) ? "0" : dt.Rows[i][j].ToString();
listOleDbParameter.Add(oleDbParameter);
break;
}
case System.TypeCode.DateTime:
{
int pIndex = listOleDbParameter.Count + 1;
//OleDbParameter oleDbParameter = new OleDbParameter("@" + dt.Columns[j].ColumnName, OleDbType.Date);
OleDbParameter oleDbParameter = new OleDbParameter("@P" + pIndex, OleDbType.Date);
valueStr += "?,";
//valueStr += ":" + dt.Columns[j].ColumnName + ",";
//oleDbParameter.Value = "1900-01-01";
oleDbParameter.Value = (dt.Rows[i][j].ToString() == null || dt.Rows[i][j].ToString().Equals("")) ? "1900-01-01" : Utility.StringUtility.FormatDate(Convert.ToDateTime(dt.Rows[i][j].ToString()), "yyyy-MM-dd hh:mm:ss");
listOleDbParameter.Add(oleDbParameter);
break;
}
case System.TypeCode.String:
{
int pIndex = listOleDbParameter.Count + 1;
//OleDbParameter oleDbParameter = new OleDbParameter("@" + dt.Columns[j].ColumnName, OleDbType.WChar);
OleDbParameter oleDbParameter = new OleDbParameter("@P" + pIndex, OleDbType.WChar);
valueStr += "?,";
//valueStr += ":" + dt.Columns[j].ColumnName + ",";
oleDbParameter.Value = (dt.Rows[i][j].ToString() == null || dt.Rows[i][j].ToString().Equals("")) ? "''" : dt.Rows[i][j].ToString();
listOleDbParameter.Add(oleDbParameter);
break;
}
case System.TypeCode.Boolean:
{
int pIndex = listOleDbParameter.Count + 1;
//OleDbParameter oleDbParameter = new OleDbParameter("@" + dt.Columns[j].ColumnName, OleDbType.Integer);
OleDbParameter oleDbParameter = new OleDbParameter("@P" + pIndex, OleDbType.Integer);
valueStr += "?,";
//valueStr += ":" + dt.Columns[j].ColumnName + ",";
oleDbParameter.Value = (dt.Rows[i][j].ToString() == null || dt.Rows[i][j].ToString().Equals("")) ? "0" : dt.Rows[i][j].ToString() == "True" ? "0" : "1";
listOleDbParameter.Add(oleDbParameter);
break;
}
default:
{
int pIndex = listOleDbParameter.Count + 1;
//OleDbParameter oleDbParameter = new OleDbParameter("@" + dt.Columns[j].ColumnName, OleDbType.VarWChar);
OleDbParameter oleDbParameter = new OleDbParameter("@P" + pIndex, OleDbType.VarWChar);
valueStr += "?,";
//valueStr += ":" + dt.Columns[j].ColumnName + ",";
oleDbParameter.Value = (dt.Rows[i][j].ToString() == null || dt.Rows[i][j].ToString().Equals("")) ? "''" : dt.Rows[i][j].ToString();
listOleDbParameter.Add(oleDbParameter);
break;
}
}
}
valueStr = valueStr.Substring(0, valueStr.Length - 1);
sql = string.Format(sql, tableName, fieldStr, valueStr);
try
{
count += ExecuteCommand(sql, isOpen, listOleDbParameter);
FormImport.ShowInsertProgressBar showInsertProgressBar = new FormImport.ShowInsertProgressBar(formImpot.Show_InsertProgressBar);
showInsertProgressBar(dt.Rows.Count, i + 1);
//Show_InsertProgressBar(dt.Rows.Count, i + 1);
//FormImport.ShowInsertProgressBar = new FormImport.ShowInsertProgressBar(FormImport.Show_InsertProgressBar();
//LogManager.WriteLogs(LogFile.InsertData, " 表插入数据 " + tableName + " 正确：【 Insert SQL 】" + sql);
}
catch (Exception eInsertError)
{
LogManager.WriteLogs(LogFile.InsertDataError, " 表插入数据 " + tableName + " 错误：" + eInsertError.Message + " 【 Insert SQL 】" + sql);
}
}
outLog(LogFile.InsertData, "表名 【" + tableName + "】 记录数【" + count.ToString() + "】", begDate);
}
}
catch (Exception e)
{
LogManager.WriteLogs(LogFile.InsertDataError, " 表插入数据 " + tableName + " 错误：" + e.Message);
count = -1;
}
finally
{
if (isDelete)
{
Close();
}
}
return count;
}
/// <summary>
/// 执行带参数SQL
/// </summary>
/// <param name="CmdString"></param>
/// <param name="isOpen">是否打开 关闭连接</param>
/// <param name="oleDbParameter">参数集</param>
/// <returns></returns>
public int ExecuteCommand(string CmdString, bool isOpen, List<OleDbParameter> listOleDbParameter)
{
if (isOpen)
{
Open();
}
int Cmd = 0;
OleDbCommand myCmd = new OleDbCommand(CmdString, cnn);
for (int i = 0; i < listOleDbParameter.Count; i++)
{
myCmd.Parameters.Add(listOleDbParameter[i]);
}
try
{
Cmd = myCmd.ExecuteNonQuery();
}
catch { }
if (isOpen)
{
Close();
}
return Cmd;
}
return count;
}
/// <summary>
/// 取数据集
/// </summary>
/// <param name="sql">sql</param>
/// <param name="n1">要获取的起始记录号。小于 1 表示从第一条开始。</param>
/// <param name="n2">要获取的终止记录号。小于 1 表示取到结果集末尾。</param>
/// <returns></returns>
public DataTable GetDataTable(string sql,int n1, int n2)
{
Open();
DataTable dt = new DataTable();
OleDbCommand sqlcmd = new OleDbCommand(sql, cnn);
OleDbDataReader dr = sqlcmd.ExecuteReader();
dt = DataReaderToDataTable(dr, n1, n2);
Close();
return dt;
}
/// <summary>
/// 从结果集转换成记录数组。指针必须处于结果集头部。
/// </summary>
/// <param name="datareader">结果集</param>
/// <param name="n1">要获取的起始记录号。小于 1 表示从第一条开始。</param>
/// <param name="n2">要获取的终止记录号。小于 1 表示取到结果集末尾。</param>
/// <returns></returns>
public DataTable DataReaderToDataTable(OleDbDataReader datareader, int n1, int n2)
{
DataTable dt = new DataTable();
try
{
for (int i = 0; i < datareader.FieldCount; i++)
{
dt.Columns.Add(datareader.GetName(i), datareader.GetFieldType(i));
}
int k = 0;
dt.BeginLoadData();
while (datareader.Read())
{
k++;
if (k >= n1)
{
if (n2 < 1 || k <= n2)
{
object[] objValues = new object[datareader.FieldCount];
datareader.GetValues(objValues);
dt.LoadDataRow(objValues, true);
}
else
{
if (n2 > 0)
{
break;
}
}
}
}
dt.EndLoadData();
}
catch (Exception e) { Console.WriteLine("指针移动失败：" + e.Message); }
return dt;
}
