/// <summary>
/// 写数据到表
/// </summary>
/// <param name="table"></param>
public void Write(DataTable table)
{
if (table.Columns.Count == 0)
{
return;
}
if (table.Rows.Count == 0)
{
return;
}
//表不存在则创建
if (!TableExist(table.TableName))
{
CreateTableSchema(table.TableName, table);
}
using (SqlCeConnection conn = new SqlCeConnection(_connecitonString))
{
conn.Open();
IDbCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
ArrayList columns = GetColumnNames(table);
foreach (DataRow dr in table.Rows)
{
string insertSql = GenerateSQL.GenerateSqlInserts(columns, dr, table.TableName);
//Console.WriteLine(insertSql);
cmd.CommandText = insertSql;
cmd.ExecuteNonQuery();
}
}
}
public void Update(DataTable dt)
{
if (!DBExist(databaseName))
{
CreateDatabase(databaseName);
}
ArrayList columns = new ArrayList();
foreach (DataColumn c in dt.Columns)
{
columns.Add(c.ColumnName);
}
string sql;
if (!TableExist(dt.TableName))
{
sql = GenerateSQL.CreateSqlTableScheamFromDataTable(dt.TableName, dt);
try
{
sqlConn.Open();
sqlCmd.CommandText = sql;
sqlCmd.CommandType = CommandType.Text;
sqlCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally {
if (sqlConn.State == ConnectionState.Open)
{
sqlConn.Close();
}
}
}
sql = GenerateSQL.GenerateSqlInserts(columns, dt, dt.TableName);
try
{
sqlConn.Open();
sqlCmd.CommandText = sql;
sqlCmd.CommandType = CommandType.Text;
sqlCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlConn.State == ConnectionState.Open)
{
sqlConn.Close();
}
}
}
public int CreateTableSchema(string tableName, DataTable table)
{
string sql = GenerateSQL.CreateSqlTableScheamFromDataTable(tableName, table);
using (SqlCeConnection conn = new SqlCeConnection(_connecitonString))
{
conn.Open();
IDbCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
//Console.WriteLine(sql);
return(cmd.ExecuteNonQuery());
}
}