NK9gKf8n

1public void saveDBF()
2        {
3
4            //define the connections to the .dbf file
5            OleDbConnection oConn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source="+ Path.GetDirectoryName(tbFile.Text)+";Extended Properties=dBase III");
6
7            OleDbCommand command = new OleDbCommand("select * from " + Path.GetFileName(tbFile.Text), oConn);
8
9            //open the connection and read in all the airport data from .dbf file into a datatable
10
11            oConn.Open();
12            DataTable dt = new DataTable();
13
14            dt.Load(command.ExecuteReader());
15
16            oConn.Close();  //close connection to the .dbf file
17
18            //create a reader for the datatable
19            DataTableReader reader = dt.CreateDataReader();
20
21            myConnection = new SqlConnection(cString);
22            myConnection.Open();   ///this is my connection to the sql server
23            SqlBulkCopy sqlcpy = new SqlBulkCopy(myConnection);
24
25            sqlcpy.DestinationTableName = "TestDBF";  //copy the datatable to the sql table
26
27            sqlcpy.WriteToServer(dt);
28
29            myConnection.Close();
30
31            reader.Close();
32
33        } 
34     
35static void AutoSqlBulkCopy(DataSet dataSet)
36{
37    var sqlConnection = new SqlConnection("Data Source=sqlServer;Initial Catalog=mydatabase;user id=myuser;password=mypass;App=App");
38    sqlConnection.Open();
39    foreach (DataTable dataTable in dataSet.Tables)
40    {
41        // checking whether the table selected from the dataset exists in the database or not
42        var checkTableIfExistsCommand = new SqlCommand("IF EXISTS (SELECT 1 FROM sysobjects WHERE name =  '" + dataTable.TableName + "') SELECT 1 ELSE SELECT 0", sqlConnection);
43        var exists = checkTableIfExistsCommand.ExecuteScalar().ToString().Equals("1");
44
45        // if does not exist
46        if (!exists)
47        {
48            var createTableBuilder = new StringBuilder("CREATE TABLE [" + dataTable.TableName + "]");
49            createTableBuilder.AppendLine("(");
50
51            // selecting each column of the datatable to create a table in the database
52            foreach (DataColumn dc in dataTable.Columns)
53            {
54                createTableBuilder.AppendLine("  ["+ dc.ColumnName + "] VARCHAR(MAX),");
55            }
56
57            createTableBuilder.Remove(createTableBuilder.Length - 1, 1);
58            createTableBuilder.AppendLine(")");
59
60            var createTableCommand = new SqlCommand(createTableBuilder.ToString(), sqlcon);
61            createTableCommand.ExecuteNonQuery();
62        }
63
64        // if table exists, just copy the data to the destination table in the database
65        // copying the data from datatable to database table
66        using (var bulkCopy = new SqlBulkCopy(sqlConnection))
67        {
68            bulkCopy.DestinationTableName = dataTable.TableName;
69            bulkCopy.WriteToServer(dataTable);
70        }
71    }
72} 
73     
74var ds = new DataSet("MyDataSet");
75var dt = new DataTable("MyDataTable");
76dt.Columns.Add(new DataColumn("name", typeof(string)));
77dt.Columns.Add(new DataColumn("email", typeof(string)));
78dt.Columns.Add(new DataColumn("phone", typeof(string)));
79
80dt.Rows.Add("John","john@company.com","56765765");
81dt.Rows.Add("Tom","tom@company.com","8978987987");
82ds.Tables.Add(dt);
83AutoSqlBulkCopy(ds);