从Datagrid视图将数据导出到SQL Server | 从Datagrid视图将数据导出到SQL

本文介绍了从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;
            }
        }

推荐答案

#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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！