StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Your code looks ok, can you show us the INSERT statement. Also have you tried using SQL Profiler to see if your INSERT hits the database at all?</p> <p>Here is an SQL class I wrote a while back that you can use:</p> <pre><code>using System; using System.Data.SqlClient; namespace SAPCommonData { public class SQLClass : IDisposable { private String connString; private SqlCommand SQLCmd; private SqlConnection SQLConn; public void Dispose() { if (SQLCmd != null) { SQLCmd.Dispose(); SQLCmd=null; } if (SQLConn != null) { SQLConn.Dispose(); SQLConn = null; } } public String SQLConnString { get{ return connString; } set{ connString = value; } } private String GetSQLConnString() { String strConn; try { strConn = System.Configuration.ConfigurationSettings.AppSettings.Get("SQL_CONN"); } catch (Exception ex) { throw (new System.Exception(ex.Message.ToString())); } return strConn; } public void SQLExecuteNonQuery() { if (SQLCmd == null) { throw (new System.Exception("Must use SetSQLCommand to initialize SQLCommand object!")); } if (SQLConn == null) { OpenSQLDB(); } try { SQLCmd.ExecuteNonQuery(); } catch (Exception ex) { throw (new System.Exception(ex.Message.ToString())); } finally { SQLCmd.Dispose(); SQLCmd=null; } } public SQLClass() { try { connString = GetSQLConnString(); } catch (Exception e) { throw new System.Exception(e.Message.ToString()); } try { SQLConn = new SqlConnection(connString); SQLConn.Open(); } catch (Exception e) { throw (new System.Exception(e.Message.ToString())); } } public void OpenSQLDB() { if (IsOpen()) { //connection state open already } else { if (connString.Length == 0) { try { connString = GetSQLConnString(); } catch (Exception e) { throw new System.Exception(e.Message.ToString()); } } try { SQLConn = new SqlConnection(connString); SQLConn.Open(); } catch (Exception e) { throw (new System.Exception(e.Message.ToString())); } } } public bool IsOpen() { return (SQLConn.State == System.Data.ConnectionState.Open); } public void CloseSQLDB() { if (IsOpen()) { SQLConn.Dispose(); SQLConn.Close(); SQLConn=null; } } public String SQLDBUsed() { return SQLConn.Database; } public void SetSQLCommand(String proc) { if (proc.Length == 0) { throw new System.Exception("Procedure must be specified when calling SetSQLCommand!"); } if (SQLConn == null) { OpenSQLDB(); } SQLCmd = new SqlCommand(proc, SQLConn); SQLCmd.CommandType = System.Data.CommandType.StoredProcedure; } public void AddSQLCmdParameter(String pName, System.Data.SqlDbType pType, object pVal) { if (SQLCmd == null) { throw (new System.Exception("Must use SetSQLCommand to initialize SQLCommand object!")); } if (SQLConn == null) { OpenSQLDB(); } try { SQLCmd.Parameters.Add(pName, pType).Value = pVal; } catch (Exception ex) { throw (new System.Exception(ex.Message.ToString())); } } public void ClearSQLCmdParameters() { if (SQLCmd != null) { SQLCmd.Parameters.Clear(); } } public void DisposeSQLCmd() { if (SQLCmd != null) { SQLCmd.Dispose(); } } public System.Data.SqlClient.SqlDataReader SQLExecuteReader() { System.Data.SqlClient.SqlDataReader dr; if (SQLCmd == null) { throw (new System.Exception("Must use SetSQLCommand to initialize SQLCommand object!")); } if (SQLConn == null) { OpenSQLDB(); } try { dr = SQLCmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection); } catch (Exception ex) { throw (new System.Exception(ex.Message.ToString())); } finally { SQLCmd.Dispose(); SQLCmd=null; } return dr; } } } </code></pre> <p>Then you can reuse this object like so:</p> <pre><code>public void ProcessCustomerData(DataTable dt) { //we have a valid connection to the database if (dt.Rows.Count > 0) { try { s=new SQLClass(); } catch (Exception e) { throw new System.Exception(e.Message.ToString()); } foreach(DataRow dr in dt.Rows) { tw.WriteLine("Processing customer: " + dr["NAME1"].ToString()); Console.WriteLine("Processing customer: " + dr["NAME1"].ToString()); s.SetSQLCommand("insCustomer"); s.AddSQLCmdParameter("@ClientID", System.Data.SqlDbType.Int, dr["MANDT"]); s.AddSQLCmdParameter("@CustomerID", System.Data.SqlDbType.BigInt, dr["KUNNR"]); s.AddSQLCmdParameter("@CustomerName1", System.Data.SqlDbType.VarChar, ((string)dr["NAME1"]==String.Empty ? DBNull.Value : dr["NAME1"])); s.AddSQLCmdParameter("@CustomerName2", System.Data.SqlDbType.VarChar, ((string)dr["NAME2"]==String.Empty ? DBNull.Value : dr["NAME2"])); s.AddSQLCmdParameter("@Country", System.Data.SqlDbType.VarChar, ((string)dr["LAND1"]==String.Empty ? DBNull.Value : dr["LAND1"])); s.AddSQLCmdParameter("@Region", System.Data.SqlDbType.VarChar, ((string)dr["REGIO"]==String.Empty ? DBNull.Value : dr["REGIO"])); s.AddSQLCmdParameter("@City", System.Data.SqlDbType.VarChar, ((string)dr["ORT01"]==String.Empty ? DBNull.Value : dr["ORT01"])); s.AddSQLCmdParameter("@ZipCode", System.Data.SqlDbType.VarChar, ((string)dr["PSTLZ"]==String.Empty ? DBNull.Value : dr["PSTLZ"])); s.AddSQLCmdParameter("@Address", System.Data.SqlDbType.VarChar, ((string)dr["STRAS"]==String.Empty ? DBNull.Value : dr["STRAS"])); s.AddSQLCmdParameter("@Telephone", System.Data.SqlDbType.VarChar, ((string)dr["TELF1"]==String.Empty ? DBNull.Value : dr["TELF1"])); s.AddSQLCmdParameter("@Fax", System.Data.SqlDbType.VarChar, ((string)dr["TELFX"]==String.Empty ? DBNull.Value : dr["TELFX"])); s.AddSQLCmdParameter("@DateAdded", System.Data.SqlDbType.DateTime, System.DateTime.Today); s.AddSQLCmdParameter("@DateModified", System.Data.SqlDbType.DateTime, System.DateTime.Today); s.AddSQLCmdParameter("@AddedBy", System.Data.SqlDbType.VarChar, DBNull.Value); s.AddSQLCmdParameter("@ModifiedBy", System.Data.SqlDbType.VarChar, DBNull.Value); s.SQLExecuteNonQuery(); Console.WriteLine("Processed customer: " + dr["NAME1"].ToString()); tw.WriteLine("Processed customer: " + dr["NAME1"].ToString()); } s.CloseSQLDB(); s.Dispose(); } } </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload