Tagged: SqlTransaction

C# 自动导入数据至数据库

最后在写一个项目,数据源自NC接口,需要将NC接口返回的数据处理后导入Sqlite数据库。

在实际操作中需要直接写入Datatable的对象进数据库,或直接将一行数据库写入数据,但每次构建SQL这些太麻烦。因此才有了这样一个处理类。

构建SQL

/// <summary>
/// 根据DataTable构建插入语句
/// </summary>
/// <param name="dataTable"></param>
/// <returns></returns>
public static string builderSql(DataTable dataTable)
{
    StringBuilder sb = new StringBuilder("insert into "+ dataTable.TableName + "(");
    StringBuilder head = new StringBuilder();
    StringBuilder values = new StringBuilder();
    foreach (DataColumn col in dataTable.Columns)
    {
        head.Append(col.ColumnName + ",");
        values.Append("@" + col.ColumnName + ",");
    }
    head.Remove(head.Length - 1, 1);
    values.Remove(values.Length - 1, 1);
    sb.Append(head).Append(") VALUES (").Append(values).Append(");");
    sb.Append("select last_insert_rowid();");
    return sb.ToString();
}

插入行

/// <summary>
/// 插入行
/// </summary>
/// <param name="row"></param>
/// <returns>数据库ID</returns>
public static int InsertDataRow(DataRow row) 
{
    using (SQLiteConnection connection = new SQLiteConnection(connectionString))
    {
        using (SQLiteCommand cmd = new SQLiteCommand())
        {
            try
            {
                string SQLString = builderSql(row.Table);

                List<SQLiteParameter> sqlitePars = new List<SQLiteParameter>();
                foreach (DataColumn col in row.Table.Columns)
                {
                    SQLiteParameter pars = new SQLiteParameter();
                    pars.ParameterName = string.Format("@{0}", col.ColumnName);
                    pars.Value = row[col.ColumnName];
                    pars.DbType = GetDbType(col.DataType);
                    sqlitePars.Add(pars);
                }

                PrepareCommand(cmd, connection, null, SQLString, sqlitePars.ToArray());

                int rows = Convert.ToInt32(cmd.ExecuteScalar());

                cmd.Parameters.Clear();
                return rows;
            }
            catch (System.Data.SQLite.SQLiteException sqlex)
            {
                throw sqlex;
            }
        }
    }
}

批量写入

/// <summary>
/// 批量插入
/// </summary>
/// <param name="dataTable">需插入数据库的表</param>
/// <param name="DataBaseName">数据库名称</param>
/// <returns></returns>
public static void InsertDataTable(DataTable dataTable)
{
    using (SQLiteConnection conn = new SQLiteConnection(connectionString))
    {
        conn.Open();
        using (SQLiteTransaction trans = conn.BeginTransaction())
        {
            SQLiteCommand cmd = new SQLiteCommand();
            try
            {
                // 构建插入语句
                string cmdText = builderSql(dataTable);

                //循环
                foreach (DataRow row in dataTable.Rows)
                {
                    string BaseName = dataTable.TableName;

                    // 构建插入值
                    List<SQLiteParameter> sqlitePars = new List<SQLiteParameter>();
                    foreach (DataColumn col in dataTable.Columns)
                    {
                        SQLiteParameter pars = new SQLiteParameter();
                        pars.ParameterName = string.Format("@{0}", col.ColumnName);
                        pars.Value = row[col.ColumnName];
                        pars.DbType = GetDbType(col.DataType);
                        sqlitePars.Add(pars);
                    }
                    PrepareCommand(cmd, conn, trans, cmdText, sqlitePars.ToArray());
                    int val = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                }
                trans.Commit();
            }
            catch
            {
                trans.Rollback();
                throw;
            }
        }
    }
}

自动获取对象数据类型

private static Dictionary typeMap;

#region 类型对应
static SQLite()
{
    typeMap = new Dictionary();

    typeMap[typeof(string)] = DbType.String;
    typeMap[typeof(byte)] = DbType.Byte;
    typeMap[typeof(int)] = DbType.Int32;
    typeMap[typeof(Int16)] = DbType.Int16;
    typeMap[typeof(Int32)] = DbType.Int32;
    typeMap[typeof(Int64)] = DbType.Int64;
    typeMap[typeof(DateTime)] = DbType.DateTime;
    typeMap[typeof(decimal)] = DbType.Decimal;
    typeMap[typeof(float)] = DbType.Double;
    typeMap[typeof(double)] = DbType.Double;
}

public static DbType GetDbType(Type giveType)
{
    giveType = Nullable.GetUnderlyingType(giveType) ?? giveType;

    if (typeMap.ContainsKey(giveType))
    {
        return typeMap[giveType];
    }

    throw new ArgumentException(string.Format("{0} is not a supported .NET class", giveType.FullName));
}

public static DbType GetDbType()
{
    return GetDbType(typeof(T));
}

#endregion

C# SqlBulkCopy 批量写入数据

最近在写 一个项目,数据来自WebService,然后插入数据库。但在写入库的时候总是报错,所以记录下!

XMl转DataTable

/// <summary>
/// XMLNode转DataTable
/// </summary>
/// <param name="Data">XMLNode节点</param>
/// <param name="xpath">Xpath</param>
/// <returns></returns>
public static DataTable GetDataTable(XmlNode Data, string xpath)
{
	XmlNodeList xmlRows = Data.SelectNodes(xpath);
	if ((xmlRows == null) || (xmlRows.Count == 0)) return null;

	DataTable dt = new DataTable();
	foreach (XmlNode xmlColumn in xmlRows[0].ChildNodes)
	{
		if (xmlColumn.Name.ToLower() != "locators")
		{
			dt.Columns.Add(xmlColumn.Name);
		}
		else
		{
			foreach (XmlNode loc in xmlColumn.SelectSingleNode("locator").ChildNodes)
			{
				dt.Columns.Add(loc.Name);
			}
		}
	}

	foreach (XmlNode xmlRow in xmlRows)
	{
		DataRow dr = dt.NewRow();
		foreach (XmlNode xmlColumn in xmlRow.ChildNodes)
		{
			if (xmlColumn.Name.ToLower() != "locators")
			{
				dr[xmlColumn.Name] = xmlRow[xmlColumn.Name].InnerText;
			}
			else
			{
				foreach (XmlNode loc in xmlColumn.SelectSingleNode("locator").ChildNodes)
				{
					dr[loc.Name] = xmlRow.SelectSingleNode(@"locators/locator/" + loc.Name).InnerText;
				}
			}
		}
		dt.Rows.Add(dr);
	}
	dt.AcceptChanges();

	return dt;
}

获取XML所有的单据行封装成DataTable

/// <summary>
/// XMLNodeList 转 DataTable
/// </summary>
/// <param name="xmlNodeList">xmlNodeList</param>
/// <param name="xpath">xpath</param>
/// <returns></returns>
public static DataTable GetDataTable(XmlNodeList xmlNodeList,string xpath)
{
	if (xmlNodeList.Count == 0 || string.IsNullOrEmpty(xpath))
		return null;

	DataTable dataTable = new DataTable();
	foreach (XmlElement item in xmlNodeList[0].ChildNodes)
	{
		dataTable.Columns.Add(item.Name.ToLower());
	}

	foreach (XmlNode item in xmlNodeList)
	{
		DataRow dataRow = dataTable.NewRow();
		foreach (XmlElement xmlelement in item.ChildNodes)
		{
			dataRow[xmlelement.Name] = xmlelement.InnerText;
		}
		dataTable.Rows.Add(dataRow);
	}
	dataTable.AcceptChanges();
	return dataTable;
}

批量插入数据

使用SqlBulkCopy的时候,它不是按照DataColumn的名称来对照数据库列名称的,它是按照DataTable的顺序来对应数据库列。所以一定要注意。若DataTable的列与数据库列不对应,可以使用ColumnMappings来建立对应关系。

public static void Insert(DataTable dataTable,string TableName)
{
	try
	{
		using (SqlConnection Connection = new SqlConnection(connectionString))
		{
			if (Connection.State != ConnectionState.Open)
				Connection.Open();

			using(SqlTransaction transaction = Connection.BeginTransaction())
			{
				using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(Connection, SqlBulkCopyOptions.Default, transaction))
				{
					sqlBulkCopy.BatchSize = dataTable.Rows.Count;
					sqlBulkCopy.DestinationTableName = TableName;

					// 映射表关系
					foreach (DataColumn column in dataTable.Columns)
					{
						sqlBulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(column.ColumnName, column.ColumnName));
					}

					try
					{
						sqlBulkCopy.WriteToServer(dataTable);
						transaction.Commit();
					}
					catch (Exception)
					{
						transaction.Rollback();
						throw;
					}
					finally
					{
						Connection.Close();
					}
				}
			}
		}
	}
	catch (Exception)
	{
		throw;
	}
}