CODEWITHSUNDEEP
c#sql
peter
peter

Reputation: 23

c# get the DataType and Size of a column

public DataTable InsertToIncludeandReturnErrorTable(DataTable MappingTable, DataTable InsertTable, string TableName)
{
    //split data and insert data to datatable and validation
    var CS = Serenity.Data.SqlConnections.GetConnectionString("Northwind");
    String MyConString = CS.ConnectionString;
    SqlConnection con = new SqlConnection();
    con.ConnectionString = MyConString;
    DataTable returnDataTable = InsertTable.Clone();
    con.Open();
    foreach (DataRow InsertRow in InsertTable.Rows)
    {
        try
        {
            string InsertDBFileld = "";
            string DatarowField = "";

            foreach (DataRow row in MappingTable.Rows)
            {
                if (InsertDBFileld == "")
                    InsertDBFileld = InsertDBFileld + row["TableColumn"].ToString().Replace("\r\n", "");
                else
                    InsertDBFileld = InsertDBFileld + "," + row["TableColumn"].ToString().Replace("\r\n", "");

                if (DatarowField == "")
                    DatarowField = "'" + DatarowField + InsertRow[row["ExcelColumn"].ToString().Replace("\r\n", "")].ToString() + "'";
                else
                    DatarowField = DatarowField + ",'" + InsertRow[row["ExcelColumn"].ToString().Replace("\r\n", "")].ToString() + "'";
            }
            InsertDBFileld = InsertDBFileld + @",CreatedBy,CreatedDate,ModifiedBy,ModifiedDate";
            DatarowField = DatarowField + ",'" + User.Identity.Name + "'," + "'" + DateTime.Now + "'," + "'" + User.Identity.Name + "'," + "'" + DateTime.Now + "'";

            using (SqlCommand cmd = new SqlCommand(@"INSERT INTO dbo." + TableName + @"(
                                                     " + InsertDBFileld + @"
                                                    ) VALUES(" + DatarowField + ")", con))
            {

                cmd.ExecuteNonQuery();

            }


        }
        catch (Exception ex)
        {
            DataRow returnRow = InsertRow;
            returnDataTable.Rows.Add(InsertRow.ItemArray);
        }
    }
    if (con.State == System.Data.ConnectionState.Open)
        con.Close();
    return returnDataTable;

}
[HttpGet]
public FileContentResult DownLoadFile(string destFilePath)
{
    //Generate Excel file with data
    destFilePath = destFilePath.Replace("%5c", "\\").Replace("%3a", ":");
    byte[] fileBytes = System.IO.File.ReadAllBytes(destFilePath);
    string fileName = "ErrorList.xlsx";
    return File(fileBytes, System.Net.Mime.MediaTypeNames.Application.Octet, fileName);

}

Orginal Code can detect the column data length and output wrong data row.

How can I get the DataType and Size of a column?

If I upload a excel exceed the data length, output a excel file and fill in the red color in the wrong data cell.

Upvotes: 1

Views: 6259

Answers (1)

Sefe
Sefe

Reputation: 14007

You can check the data type and maximum length from the columns of your DataTable:

Type columnType = InsertTable.Columns["TableColumn"].DataType;
int maxLength = InsertTable.Columns["TableColumn"].MaxLength;

If your table does not include schema information (which I doubt), you can get the schema first from the database with a SqlDataAdapter. The FillSchema method is what you need.

Upvotes: 1

Related Questions