static void Main(string[] args)
{
Console.Title = "增加性别字段";
string connectionString = //数据库连接字串
"Data Source=.\\SQLExpress;Database=student;Trusted_Connection=true;";
SqlConnection connection = new SqlConnection(connectionString);//创建数据库连接实例
connection.Open(); //打开数据库连接
Console.WriteLine("数据库student连接成功！");
SqlCommand cmd = new SqlCommand(); //创建数据查询类实例
cmd.Connection = connection;
cmd.CommandText = "ALTER TABLE student_info ADD sex varchar(2)";
cmd.ExecuteNonQuery(); //执行添加sex字段SQL语句
cmd.Dispose();
SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM student_info",
"Data Source=.\\SQLExpress;Database=student;Trusted_Connection=true;");
DataSet dataSet = new DataSet(); //创建数据集
adapter.Fill(dataSet); //填充数据集
for (int i = 0; i < dataSet.Tables[0].Rows.Count; i++)
{
dataSet.Tables[0].Rows[i][5] = random.Next(2) == 0 ? "男" : "女";//修改性别值
}
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);//将数据集更新与数据库协调
adapter.Update(dataSet); //更新数据集到数据库
builder.Dispose();
dataSet.Dispose();
adapter.Dispose();
connection.Close(); //关闭数据库连接
Console.ReadLine();
}
public static bool AppendDataTable(ref DataTable dsNewTable, string strSQL)
{
if (!OpenConnection())
{
return(false);
}
try
{
DataTable oDataSet = new DataTable();
System.Data.SqlClient.SqlDataAdapter DataAdapter;
DataAdapter = new System.Data.SqlClient.SqlDataAdapter(strSQL, lo_Connection);
System.Data.SqlClient.SqlCommandBuilder myDataRowsCommandBuilder = new System.Data.SqlClient.SqlCommandBuilder(DataAdapter);
DataAdapter.Update(dsNewTable);
DataAdapter.Dispose();
myDataRowsCommandBuilder.Dispose();
return(true);
}
catch
{
return(false);
}
finally
{
lo_Connection.Close();
}
}
/// <summary>
/// 根据sql语句更新ds；
/// Update ds
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static void UpdateDataSet(DataSet ds, string sql, string tableName)
{
SqlDataAdapter da = new SqlDataAdapter();
SqlCommandBuilder cb = new SqlCommandBuilder();
try
{
da.SelectCommand = new SqlCommand(sql, myConn);
cb.DataAdapter = da;
//da.InsertCommand = cb.GetInsertCommand();
//da.UpdateCommand = cb.GetUpdateCommand();
//da.DeleteCommand = cb.GetDeleteCommand();
da.Update(ds.Tables[tableName]);
}
catch (Exception ex)
{
throw ex;
}
finally
{
da.Dispose();
cb.Dispose();
}
}
/// <summary>
/// ��������DataTable
/// </summary>
/// <param name="strSelectSQL">����ѡ���dt��¼��ͬ��SQL���</param>
/// <param name="dt">Ҫ�������µ�DataTable</param>
public static void UpdateTable(string strSelectSQL, DataTable dt)
{
try
{
using (SqlDataAdapter sda = new SqlDataAdapter(strSelectSQL, ConnectionString))
{
SqlCommandBuilder scb = new SqlCommandBuilder(sda);
sda.Update(dt);
scb.Dispose();
}
}
catch (SqlException e)
{
throw e;
}
}
public bool UpdateTable(DataTable table, string tableName)
{
try
{
TableHelper.SetDefaultColumnValues(table);
var con = CONNECTION.OpenCon();
var adapter = new SqlDataAdapter(string.Format(@"SELECT * FROM {0}", tableName), con);
var cmd = new SqlCommandBuilder(adapter);
adapter.Update(table);
cmd.Dispose();
adapter.Dispose();
CONNECTION.CloseCon(con);
return true;
}
catch (DBConcurrencyException cex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "UpdateTable DBConcurrencyError!",
Ex = cex,
});
return false;
}
catch (Exception ex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "UpdateTable Error!",
Ex = ex,
});
return false;
}
}
// 1- ListView Control
// 2- Total Columns
// 3- Select Query
// 4- Field List
// 5- Column Width
// 6- bool CheckBox Column
// 7- connection string
public static void FillListView(
ListView pLV,
int pCol,
string pSQL,
string pFL,
string pFLen,
bool pChkBox = false,
string pCon = clsGVar.ConString1)
{
// 1- ListView Control
// 2- Columns
// 3- Query
// 4- Field List
// 5- Del: Title List
// 6- Field Length
// 7- Optional: pChkBox bool
// 8- Optional: Connection string
// ListView
//
// ListView Headers
string[] cFL = pFL.Split(',');
if (pFL.Count() < pCol)
{
pCol = pFL.Count();
}
//
SqlConnection CurrCon = new SqlConnection(pCon);
DataTable table = new DataTable();
try
{
CurrCon.Open();
SqlDataAdapter dataAdaptor = new SqlDataAdapter(pSQL, CurrCon);
SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdaptor);
table.Locale = System.Globalization.CultureInfo.InvariantCulture;
dataAdaptor.Fill(table);
//pLV.DataBindings.Add("User a", table, "usr_name");
//pLV.DataBindings.Add("User b", table, "usr_fullname");
if (pLV.Items.Count > 0)
{
pLV.Items.Clear();
}
for (int i = 0; i < table.Rows.Count; i++)
{
DataRow drow = table.Rows[i];
// Only row that have not been deleted
if (drow.RowState != DataRowState.Deleted)
{
// Define the list items
ListViewItem lvi = new ListViewItem(drow[cFL[0]].ToString());
for (int j = 1; j < pCol; j++)
{
lvi.SubItems.Add(drow[cFL[j]].ToString());
}
// Add the list items to the ListView
pLV.Items.Add(lvi);
}
}
// pLV.DataBindings = table;
//
//if (pReadOnly) { pLV.ReadOnly = true; }
//else { pLV.ReadOnly = false; }
//
commandBuilder.Dispose();
dataAdaptor.Dispose();
CurrCon.Close();
}
catch (Exception exp)
{
if (CurrCon.State != ConnectionState.Closed) { CurrCon.Close(); }
MessageBox.Show("Exception: " + exp.Message, "Fill List View ");
}
}
// =========================================================
// Fill Grid Start
// 1- Grid Control
// 2- Query
// 3- Connection
// 4- Readonly
public static void FillGrid(
DataGridView pDGV1,
string pSQL,
string pCon = clsGVar.ConString1,
bool pReadOnly = true
)
{
SqlConnection CurrCon = new SqlConnection(pCon);
DataTable table = new DataTable();
try
{
CurrCon.Open();
SqlDataAdapter dataAdaptor = new SqlDataAdapter(pSQL, CurrCon);
SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdaptor);
table.Locale = System.Globalization.CultureInfo.InvariantCulture;
dataAdaptor.Fill(table);
pDGV1.DataSource = table;
//
if (pReadOnly) { pDGV1.ReadOnly = true; }
else { pDGV1.ReadOnly = false; }
//
commandBuilder.Dispose();
dataAdaptor.Dispose();
CurrCon.Close();
}
catch (Exception exp)
{
if (CurrCon.State != ConnectionState.Closed) { CurrCon.Close(); }
MessageBox.Show("Exception: " + exp.Message, "Fill Grid ");
}
}
public bool SaveData(DataTable DataTable)
{
bool success = false;
try
{
SqlConnection dbConnection = new SqlConnection(_connectionString);
SqlDataAdapter dbDataAdapter = new SqlDataAdapter("SELECT * FROM " + DataTable.TableName, dbConnection);
SqlCommandBuilder dbCommandBuilder = new SqlCommandBuilder(dbDataAdapter);
dbDataAdapter.Update(DataTable);
dbCommandBuilder.Dispose();
dbCommandBuilder = null;
dbDataAdapter.Dispose();
dbDataAdapter = null;
dbConnection.Close();
dbConnection.Dispose();
dbConnection = null;
success = true;
}
catch
{
success = false;
}
return success;
}
protected void btnUpload_Click(object sender, EventArgs e)
{
if (fuDonorFile.HasFile)
{
string strFilePath = HttpContext.Current.Request.PhysicalApplicationPath + @"Uploads\" + Request["eid"].ToString();
if (!Directory.Exists(strFilePath))
Directory.CreateDirectory(strFilePath);
string strFileName = Guid.NewGuid().ToString() + ".csv";
fuDonorFile.SaveAs(strFilePath + @"\" + strFileName);
try
{
try
{
SqlConnection ConnT = new SqlConnection(_ConnStr);
ConnT.Open();
SqlCommand cmd = new SqlCommand("DELETE DonorListStage", ConnT);
cmd.ExecuteNonQuery();
cmd.Dispose();
ConnT.Close();
}
catch(Exception EX)
{
throw new Exception("T Error: " + EX.Message);
}
SqlConnection Conn = new SqlConnection(_ConnStr);
Conn.Open();
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM DonorListStage", Conn);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
try { da.Fill(ds); }
catch (Exception EX) { throw new Exception("DA Error: " + EX.Message); }
// Read file
string line = "";
string[] arrLine;
StreamReader file = new StreamReader(strFilePath + @"\" + strFileName);
line = file.ReadLine(); // Skip the first line of column headers
int intRowCount = 1;
while((line = file.ReadLine()) != null)
{
int intCount = line.Split((char)34).Length;
string[] arrComma = line.Split((char)34);
if (intCount > 2)
line = ReplaceComma(line);
if(intCount > 4)
line = ReplaceComma(line);
if (intCount > 6)
line = ReplaceComma(line);
if (intCount > 8)
line = ReplaceComma(line);
if (intCount > 10)
line = ReplaceComma(line);
if (intCount > 12)
line = ReplaceComma(line);
arrLine = line.Split(",".ToCharArray());
DataRow dr = ds.Tables[0].NewRow();
if (arrLine.Count() != 31)
{
dr["pk_DonorList"] = intRowCount.ToString();
dr["UploadNotes"] = "Error: Data not in the correct format.";
}
else
{
try
{
dr["pk_DonorList"] = arrLine[28].ToString().Replace(";",",");
dr["DonorType"] = arrLine[2].ToString().Replace(";", ",");
dr["AccountType"] = arrLine[3].ToString().Replace(";", ",");
dr["KeyName"] = arrLine[4].ToString().Replace(";", ",");
dr["AccountID"] = arrLine[5].ToString().Replace(";", ",");
dr["InsideSal"] = arrLine[6].ToString().Replace(";", ",");
dr["OutSideSal"] = arrLine[7].ToString().Replace(";", ",");
dr["HHOutsideSal"] = arrLine[8].ToString().Replace(";", ",");
dr["AccountName"] = arrLine[9].ToString().Replace(";", ",");
dr["AddressLine1"] = arrLine[10].ToString().Replace(";", ",");
dr["AddressLine2"] = arrLine[11].ToString().Replace(";", ",");
dr["AddressLine3"] = arrLine[12].ToString().Replace(";", ",");
dr["AddressLine4"] = arrLine[13].ToString().Replace(";", ",");
dr["AddressLine5"] = arrLine[14].ToString().Replace(";", ",");
dr["City"] = arrLine[15].ToString().Replace(";", ",");
dr["State"] = arrLine[16].ToString().Replace(";", ",");
dr["PostCode"] = arrLine[17].ToString().Replace(";", ",");
dr["CountryIDTrans"] = arrLine[18].ToString().Replace(";", ",");
dr["StateDescription"] = arrLine[19].ToString().Replace(";", ",");
dr["EmailAddress"] = arrLine[20].ToString().Replace(";", ",");
dr["PhoneNumber"] = arrLine[21].ToString().Replace(";", ",");
dr["SourceCode"] = arrLine[29].ToString().Replace(";", ",");
if (!arrLine[22].ToString().Equals(""))
dr["SPLCLeadCouncil"] = arrLine[22].ToString();
if (arrLine[23].ToString().Equals(""))
dr["MembershipYear"] = 0;
else
{
string[] arrMYear = arrLine[23].Split("/".ToCharArray());
dr["MembershipYear"] = int.Parse(arrMYear[2].ToString());
}
if(arrLine[24].ToString().Equals(""))
dr["YearsSince"] = 0;
else
dr["YearsSince"] = int.Parse(arrLine[24].ToString());
if (arrLine[25].ToString().Equals(""))
dr["HPC"] = 0;
else
dr["HPC"] = float.Parse(arrLine[25].ToString().Replace("$", "").Replace(";", ","));
if (!arrLine[26].ToString().Equals(""))
dr["LastPaymentDate"] = DateTime.Parse(arrLine[26].ToString());
if (arrLine[27].ToString().Equals(""))
dr["LastPaymentAmount"] = 0;
else
dr["LastPaymentAmount"] = float.Parse(arrLine[27].ToString().Replace("$", "").Replace(";", ","));
//dr["SourceCode"] = arrLine[29].ToString();
//dr["UploadNotes"] = intRowCount.ToString();
}
catch (Exception EX)
{
dr["pk_DonorList"] = intRowCount.ToString();
dr["UploadNotes"] = "Error: " + EX.Message;
}
}
ds.Tables[0].Rows.Add(dr);
intRowCount += 1;
}
da.Update(ds);
cb.Dispose();
da.Dispose();
SqlDataAdapter daSel = new SqlDataAdapter("SELECT * FROM DonorListStage WHERE UploadNotes IS NOT NULL", Conn);
DataSet dsSel = new DataSet();
daSel.Fill(dsSel);
if (dsSel.Tables[0].Rows.Count > 0)
{
pnlGrid.Visible = true;
gvErrors.DataSource = dsSel.Tables[0];
gvErrors.DataBind();
}
else
{
pnlGrid.Visible = false;
}
//Conn.Close();
pnlProcess.Visible = true;
pnlUpload.Visible = false;
pnlGrid.Visible = false;
}
catch(Exception EX)
{
lblErrorMessage.Text = EX.Message;
}
}
}