I want to check if the SQL Server Compact column's datatype is Int, and if it is, change it to Float.

However I am just receiving this message that type is String, whereas the Column Number 7 "Amount" Type is INT.

I have not specified SQL query at the moment to change from INT to Float, I will add once the condition it picks up true and gets the right data type of that "Amount" Column which is INT right now.

private void CheckDataType()
{
    MessageBox.Show("Checking Data Type");
    string sqlcon = @"Data Source = .\Records.sdf;Persist Security Info=False";

    using (SqlCeConnection conn = new SqlCeConnection(sqlcon))
    {
        conn.Open();

        SqlCeCommand cmd = new SqlCeCommand(@"Select data_type
    from information_schema.columns
    where table_name = 'OutgoingChequeRecords' and column_name = 'Amount'", conn);

        SqlCeDataReader reader = cmd.ExecuteReader();
                  
        while (reader.Read())
        {
            MessageBox.Show("Reading Started");

            for (int i = 0; i < reader.FieldCount; i  )
            {
                Type dataType = reader.GetFieldType(i);
                string columnName = reader.GetName(i); 

                if (dataType == typeof(int))
                {
                    // Do for integers (INT, SMALLINT, BIGINT)
                    MessageBox.Show("Type is INT");
                }
                else if (dataType == typeof(double))
                {
                    // Do for doubles (DOUBLE, DECIMAL)
                    MessageBox.Show("Type is Decimal");
                }
                else if (dataType == typeof(string))
                {
                    // Do for Strings (VARCHAR, NVARCHAR).
                    MessageBox.Show("Type is String");
                }
                else if (dataType == typeof(DateTime))
                {
                    // Do for DateTime (DATETIME)
                    MessageBox.Show("Type is DateTime");
                }
                else if (dataType == typeof(byte[]))
                {
                    // Do for Binary (BINARY, VARBINARY, NVARBINARY, IMAGE)
                    MessageBox.Show(columnName);
                }
               
                else if (dataType == typeof(float))
                {
                    MessageBox.Show("Type is Float");
                }    
            }
        }
        MessageBox.Show("Reading Stopped, Connection Closed");

        conn.Close();
    }   
}

Type is String

CodePudding user response：

Can you please check this way

Type type = reader.GetFieldType(i);

switch (Type.GetTypeCode(type))
{
    case TypeCode.DateTime:
        break;
    case TypeCode.String:
        break;
    default: break;
}

CodePudding user response：

you can simply run (string)cmd.ExecuteScalar(); and check its value is "int". I am assuming there is only one column with the same table name across all schemas. If there are more then you can add the scheme as well in condition TABLE_SCHEMA = 'yourSchema'