May 7th, 2008, 06:37 PM
|
|
|
SqlDataAdapter.Update() and/or SqlDataAdapter.InsertCommand issue?
I'm creating a little web application to upload delimited text files to a SQL server (since my web host doesn't provide this service).
The relevant code in my web app that parses the file and updates the database:
PHP Code:
1 private void UpdateDatabase(string csvFilePath, string delimiterName, string tableName)
2 {
3 DataTable dataTable = _dataAccess.GetTableInfo("SELECT * FROM " + tableName, tableName, null);
4
5 string fileLine;
6 List<string> lines = new List<string>();
7 StringBuilder sql = new StringBuilder();
8 Dictionary<string, string> parameters = new Dictionary<string, string>();
9 string delimiter = (delimiterName == "Comma") ? "," : @"\t";
10 int count = 0;
11
12 // Open file
13 if (File.Exists(@csvFilePath))
14 {
15 // Open file
16 FileInfo csvFile = new FileInfo(@csvFilePath);
17
18 // Create resource stream
19 StreamReader reader = csvFile.OpenText();
20
21 // traverse each line of file
22 do
23 {
24 fileLine = reader.ReadLine().Trim();
25 if (!String.IsNullOrEmpty(fileLine) && !lines.Contains(fileLine))
26 {
27 string[] columnData = Regex.Split(fileLine, delimiter);
28 sql.Append("INSERT INTO ");
29 sql.Append(tableName);
30 sql.Append(" VALUES (");
31 for (int i = 0; i < columnData.Length; i++)
32 {
33 parameters.Add("Value" + count, columnData[i]);
34 sql.Append("@Value" + count);
35 count++;
36
37 if (i != columnData.Length - 1)
38 {
39 sql.Append(",");
40 }
41 else
42 {
43 sql.Append(")");
44 }
45 }
46
47 _dataAccess.UpdateTableInfo(sql.ToString(), tableName, dataTable, parameters);
48 sql.Length = 0;
49 lines.Add(fileLine);
50 }
51 } while (!reader.EndOfStream);
52
53 // Close stream
54 reader.Close();
55 }
56 }
...and then there is the relevant code in my referenced data access class:
PHP Code:
1 public class TableDataAccess
2 {
3 // Private Fields
4 private SqlDataAdapter _dataAdapter;
5 private readonly SqlConnection _dbConnection = new SqlConnection("My connection string");
6
7 // Constructor
8 public TableDataAccess()
9 {
10 _dataAdapter = new SqlDataAdapter();
11 }
12
13 public DataTable GetTableInfo(string command, string dbTableName, Dictionary<string, string> parameters)
14 {
15 _dataAdapter.SelectCommand = MakeDbCommand(command, parameters);
16 SqlConnection conn = _dbConnection;
17 _dataAdapter.SelectCommand.Connection = conn;
18 DataSet dataSet;
19 using (conn)
20 {
21 dataSet = new DataSet();
22 _dataAdapter.Fill(dataSet, dbTableName);
23 }
24 return dataSet.Tables[dbTableName];
25 }
26
27 public void UpdateTableInfo(string command, string dbTableName, DataTable dataTable, Dictionary<string, string> parameters)
28 {
29 SqlConnection conn = _dbConnection;
30 _dataAdapter.InsertCommand = MakeDbCommand(command, parameters);
31 _dataAdapter.InsertCommand.Connection = conn;
32 using (conn)
33 {
34 _dataAdapter.Update(dataTable.DataSet, dbTableName);
35 }
36 }
37
38 #region Private Methods
39 private SqlCommand MakeDbCommand(string command, Dictionary<string, string> parameters)
40 {
41 SqlCommand cmd = new SqlCommand(@command);
42
43 if (parameters != null)
44 {
45 foreach (KeyValuePair<string, string> parameter in parameters)
46 {
47 SqlParameter param = cmd.Parameters.AddWithValue("@" + parameter.Key, parameter.Value);
48 if (parameter.Value.Length <= 255)
49 {
50 param.SqlDbType = SqlDbType.VarChar;
51 }
52 else
53 {
54 param.SqlDbType = SqlDbType.NText;
55 }
56 }
57 }
58
59 return cmd;
60 }
When UpdateTableInfo() in my data access class is called, the INSERT queries constructed in my web app's UpdateDatabase() method are NOT updating the database. I'm having trouble figuring out why.
The database connection string is valid, although not posted here. In debug mode in VS, I can see that the INSERT queries are constructed correctly. The arguments passed to UpdateTableInfo() are OK.
I think I'm somehow implementing SqlDataAdapter.Update() and/or SqlDataAdapter.InsertCommand incorrectly. Also, since I am inserting rows into a manually created database table here, would a disparity between SqlDbType enumeration values (set in SqlParameter) and data types in my database table cause the INSERT query to flat out fail? Or does SQL Server attempt to convert the data to its columns' data types?
Finally, there may be a better way to create the T-SQL required to insert delimited data from a text file to a corresponding DB table, but my T-SQL knowledge is mostly limited to regular SQL queries.
Thanks for any help here.
|
Dev Shed Forums Sponsor:
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
