从Datagrid视图将数据导出到SQL Server [英] Export data to SQL Server from Datagrid view
本文介绍了从Datagrid视图将数据导出到SQL Server的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个Windows窗体，在此Windows窗体中，我有一个导入程序，该程序将.csv数据导入到datagridview，现在我有一个保存并上传的按钮，我想将数据从datagrid视图保存到表在我的SQL Server数据库中.这是我显示从.csv文件到datagridview的数据的代码.有人可以帮我吗?
I have a windows form which in this windows form i have an import programm which import a .csv data to a datagridview, now i have a button which is save and upload i want to save the data from the datagrid view to the table in my database in sql server. This is the code by which i am showing the data from .csv file to datagridview. Can anybody help me please?
#region declerations
string strCSVFile = "";
//System.Data.Odbc.OdbcDataAdapter obj_oledb_da;
System.Data.SqlClient.SqlDataAdapter obj_sql_da;
char[] splitArray1 = { ',', '\n', '\r' };
char[] splitArray2 = { ' ' };
Int64[] SearchList = new long[1];
string CSVDataSource;
string FileName;
string FileSize;
string TempFileName;
string[] DataResult1 = { "", "", "", "", "" };// Use to populate the grid.
string[] Titles = { "DEPARTURE_AIRPORT", "ARRIVAL_AIRPORT", "FLIGHT_NO", "AIRCRAFT_TYPE_CODE", "SCHEDULED_DATETIME", "SCHEDULED_ARRIVAL_DATETIME", "ECONOMY_SEAT_AVAILABILITY", "BUSINESS_SEAT_AVAILABILITY", "FIRST_SEAT_AVAILABILITY", "PRICE_ECONOMY", "PRICE_BUSINESS", "PRICE_FIRST" };
//Create a dataset
DataSet dataset = new DataSet("My Dataset");
//Create a table
DataTable datatable = new DataTable("Temp.CSV");
SqlConnection connection = new SqlConnection("Data Source=AMA;Initial Catalog=KRS;Integrated Security=True");
string connection2 = "Data Source=AMA;Initial Catalog=KRS;Integrated Security=True";
SqlCommand cmd = new SqlCommand();
SqlCommand cmd1 = new SqlCommand();
#endregion
#region Constructor
public ImportProgram()
{
InitializeComponent();
TempFileName = @"C:\Temp.csv";
lblLoading.Visible = false;
CreateTable();
dataset.Tables.Add(datatable);
}
#endregion
private void btnBrowse_Click(object sender, EventArgs e)
{
OpenFileDialog openfiledialog = new OpenFileDialog();
if (openfiledialog.ShowDialog() == DialogResult.OK)
{
txtCSVFilePath.Text = openfiledialog.FileName;
}
}
private void btnImport_Click(object sender, EventArgs e)
{
try
{
if (txtCSVFilePath.Text == "")
{
MessageBox.Show("The File Path TextBox cannot be empty.", "Warning");
return;
}
else
{
// Clear datagrid contents
dGridCSVdata.SelectAll();
dGridCSVdata.ClearSelection();
// Set file name
FileName = txtCSVFilePath.Text;
CSVDataSource = FileName;
if (File.Exists(TempFileName))
{
File.Delete(TempFileName);
}
StreamReader sr = new StreamReader(FileName);
StreamWriter sw = new StreamWriter(TempFileName);
// Read & delete header
string junk = sr.ReadLine();
// Read file into string
string FileData = sr.ReadToEnd();
FileSize = FileData.Length.ToString("N");
FileSize = FileSize.Substring(0, FileSize.IndexOf("."));
lblLoading.Text = "Loading " + FileSize + " bytes.\nPlease wait a moment or two.";
lblLoading.Visible = true;
lblLoading.Update();
sw.WriteLine(" DEPARTURE_AIRPORT, ARRIVAL_AIRPORT, FLIGHT_NO, AIRCRAFT_TYPE_CODE, SCHEDULED_DATETIME, SCHEDULED_ARRIVAL_DATETIME, ECONOMY_SEAT_AVAILABILITY, BUSINESS_SEAT_AVAILABILITY, FIRST_SEAT_AVAILABILITY, PRICE_ECONOMY, PRICE_BUSINESS, PRICE_FIRST");
sw.Write(FileData);
sr.Close();
sw.Close();
ReadData();
lblLoading.Visible = false;
dGridCSVdata.Update();
if (File.Exists(TempFileName))
{
File.Delete(TempFileName);
}
btnSaveandUpload.Enabled = true;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void CreateTable()
{
for (int i = 0; i < 12; i++)
{
datatable.Columns.Add(Titles[i]);
}
}
private void ReadData()
{
string tempPath = "C:";
string strConn = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + tempPath + @"\;Extensions=asc,csv,tab,txt";
OdbcConnection conn = new OdbcConnection(strConn);
// SqlConnection sql = new SqlConnection(strConn);
// SqlDataAdapter da = new SqlDataAdapter ("Select DEPARTURE_AIRPORT, ARRIVAL_AIRPORT, FLIGHT_NO, AIRCRAFT_TYPE_CODE, SCHEDULED_DATETIME, SCHEDULED_ARRIVAL_DATETIME, ECONOMY_SEAT_AVAILABILITY, BUSINESS_SEAT_AVAILABILITY, FIRST_SEAT_AVAILABILITY from temp.csv", sql);
OdbcDataAdapter da = new OdbcDataAdapter("Select DEPARTURE_AIRPORT, ARRIVAL_AIRPORT, FLIGHT_NO, AIRCRAFT_TYPE_CODE, SCHEDULED_DATETIME, SCHEDULED_ARRIVAL_DATETIME, ECONOMY_SEAT_AVAILABILITY, BUSINESS_SEAT_AVAILABILITY, FIRST_SEAT_AVAILABILITY, PRICE_ECONOMY, PRICE_BUSINESS, PRICE_FIRST from temp.csv", conn);
DataTable dt = new DataTable();
da.Fill(dt);
dGridCSVdata.DataSource = dt;
dGridCSVdata.Columns[1].DefaultCellStyle.Format = "T";
foreach (DataGridViewColumn col in dGridCSVdata.Columns)
{
col.SortMode = DataGridViewColumnSortMode.NotSortable;
}
}
推荐答案
我有一个Windows窗体，在此Windows窗体中，我有一个导入程序，该程序将.csv数据导入到datagridview，现在我有一个保存并上传的按钮，我想将数据从datagrid视图保存到表在我的SQL Server数据库中.这是我显示从.csv文件到datagridview的数据的代码.有人可以帮我吗?
I have a windows form which in this windows form i have an import programm which import a .csv data to a datagridview, now i have a button which is save and upload i want to save the data from the datagrid view to the table in my database in sql server. This is the code by which i am showing the data from .csv file to datagridview. Can anybody help me please?
#region declerations
string strCSVFile = "";
//System.Data.Odbc.OdbcDataAdapter obj_oledb_da;
System.Data.SqlClient.SqlDataAdapter obj_sql_da;
char[] splitArray1 = { ',', '\n', '\r' };
char[] splitArray2 = { ' ' };
Int64[] SearchList = new long[1];
string CSVDataSource;
string FileName;
string FileSize;
string TempFileName;
string[] DataResult1 = { "", "", "", "", "" };// Use to populate the grid.
string[] Titles = { "DEPARTURE_AIRPORT", "ARRIVAL_AIRPORT", "FLIGHT_NO", "AIRCRAFT_TYPE_CODE", "SCHEDULED_DATETIME", "SCHEDULED_ARRIVAL_DATETIME", "ECONOMY_SEAT_AVAILABILITY", "BUSINESS_SEAT_AVAILABILITY", "FIRST_SEAT_AVAILABILITY", "PRICE_ECONOMY", "PRICE_BUSINESS", "PRICE_FIRST" };
//Create a dataset
DataSet dataset = new DataSet("My Dataset");
//Create a table
DataTable datatable = new DataTable("Temp.CSV");
SqlConnection connection = new SqlConnection("Data Source=AMA;Initial Catalog=KRS;Integrated Security=True");
string connection2 = "Data Source=AMA;Initial Catalog=KRS;Integrated Security=True";
SqlCommand cmd = new SqlCommand();
SqlCommand cmd1 = new SqlCommand();
#endregion
#region Constructor
public ImportProgram()
{
InitializeComponent();
TempFileName = @"C:\Temp.csv";
lblLoading.Visible = false;
CreateTable();
dataset.Tables.Add(datatable);
}
#endregion
private void btnBrowse_Click(object sender, EventArgs e)
{
OpenFileDialog openfiledialog = new OpenFileDialog();
if (openfiledialog.ShowDialog() == DialogResult.OK)
{
txtCSVFilePath.Text = openfiledialog.FileName;
}
}
private void btnImport_Click(object sender, EventArgs e)
{
try
{
if (txtCSVFilePath.Text == "")
{
MessageBox.Show("The File Path TextBox cannot be empty.", "Warning");
return;
}
else
{
// Clear datagrid contents
dGridCSVdata.SelectAll();
dGridCSVdata.ClearSelection();
// Set file name
FileName = txtCSVFilePath.Text;
CSVDataSource = FileName;
if (File.Exists(TempFileName))
{
File.Delete(TempFileName);
}
StreamReader sr = new StreamReader(FileName);
StreamWriter sw = new StreamWriter(TempFileName);
// Read & delete header
string junk = sr.ReadLine();
// Read file into string
string FileData = sr.ReadToEnd();
FileSize = FileData.Length.ToString("N");
FileSize = FileSize.Substring(0, FileSize.IndexOf("."));
lblLoading.Text = "Loading " + FileSize + " bytes.\nPlease wait a moment or two.";
lblLoading.Visible = true;
lblLoading.Update();
sw.WriteLine(" DEPARTURE_AIRPORT, ARRIVAL_AIRPORT, FLIGHT_NO, AIRCRAFT_TYPE_CODE, SCHEDULED_DATETIME, SCHEDULED_ARRIVAL_DATETIME, ECONOMY_SEAT_AVAILABILITY, BUSINESS_SEAT_AVAILABILITY, FIRST_SEAT_AVAILABILITY, PRICE_ECONOMY, PRICE_BUSINESS, PRICE_FIRST");
sw.Write(FileData);
sr.Close();
sw.Close();
ReadData();
lblLoading.Visible = false;
dGridCSVdata.Update();
if (File.Exists(TempFileName))
{
File.Delete(TempFileName);
}
btnSaveandUpload.Enabled = true;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void CreateTable()
{
for (int i = 0; i < 12; i++)
{
datatable.Columns.Add(Titles[i]);
}
}
private void ReadData()
{
string tempPath = "C:";
string strConn = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + tempPath + @"\;Extensions=asc,csv,tab,txt";
OdbcConnection conn = new OdbcConnection(strConn);
// SqlConnection sql = new SqlConnection(strConn);
// SqlDataAdapter da = new SqlDataAdapter ("Select DEPARTURE_AIRPORT, ARRIVAL_AIRPORT, FLIGHT_NO, AIRCRAFT_TYPE_CODE, SCHEDULED_DATETIME, SCHEDULED_ARRIVAL_DATETIME, ECONOMY_SEAT_AVAILABILITY, BUSINESS_SEAT_AVAILABILITY, FIRST_SEAT_AVAILABILITY from temp.csv", sql);
OdbcDataAdapter da = new OdbcDataAdapter("Select DEPARTURE_AIRPORT, ARRIVAL_AIRPORT, FLIGHT_NO, AIRCRAFT_TYPE_CODE, SCHEDULED_DATETIME, SCHEDULED_ARRIVAL_DATETIME, ECONOMY_SEAT_AVAILABILITY, BUSINESS_SEAT_AVAILABILITY, FIRST_SEAT_AVAILABILITY, PRICE_ECONOMY, PRICE_BUSINESS, PRICE_FIRST from temp.csv", conn);
DataTable dt = new DataTable();
da.Fill(dt);
dGridCSVdata.DataSource = dt;
dGridCSVdata.Columns[1].DefaultCellStyle.Format = "T";
foreach (DataGridViewColumn col in dGridCSVdata.Columns)
{
col.SortMode = DataGridViewColumnSortMode.NotSortable;
}
}谢谢
Keyaa
这篇关于从Datagrid视图将数据导出到SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
