/// <summary> /// SQLのパラメータを追加します。サニタイジング(SQLインジェクション対策)をします /// </summary> /// <param name="cmd"></param> /// <param name="direction"></param> /// <param name="paramName"></param> /// <param name="type"></param> /// <param name="value">値がnullの場合はDBNull.Valueを挿入します</param> public static void AddSqlParameter(SqlCommand cmd, ParameterDirection direction, string paramName, SqlDbType type, Object value) { if (cmd == null) { return; } if (string.IsNullOrEmpty(paramName) || string.IsNullOrWhiteSpace(paramName)) { return; } if (value == null) { value = DBNull.Value; } SqlParameter param = cmd.CreateParameter(); param.ParameterName = paramName; param.SqlDbType = type; param.Direction = direction; param.Value = value; cmd.Parameters.Add(param); }
/// <summary> /// 매개 변수를 사용하여 <c>SqlParameter</c>클래스의 새 인스턴스를 반환합니다. /// </summary> /// <param name="parameterName">매핑할 매개 변수의 이름</param> /// <param name="dbType"><c>SqlDbType</c>값 중 하나</param> /// <param name="size">매개 변수의 길이</param> /// <param name="sourceColumn">소스 열의 이름</param> /// <returns><c>SqlParameter</c>클래스의 새 인스턴스</returns> public static SqlParameter CreateParameter(string parameterName, SqlDbType dbType, int size, string sourceColumn) { SqlParameter ret = CreateParameter(parameterName, dbType); ret.Size = size; ret.SourceColumn = sourceColumn; return ret; }
public static Type SqlType2CsharpType(SqlDbType sqlType) { switch (sqlType) { case SqlDbType.BigInt: return typeof(Int64); case SqlDbType.Binary: return typeof(Object); case SqlDbType.Bit: return typeof(Boolean); case SqlDbType.Char: return typeof(String); case SqlDbType.DateTime: return typeof(DateTime); case SqlDbType.Decimal: return typeof(Decimal); case SqlDbType.Float: return typeof(Double); case SqlDbType.Image: return typeof(Object); case SqlDbType.Int: return typeof(Int32); case SqlDbType.Money: return typeof(Decimal); case SqlDbType.NChar: return typeof(String); case SqlDbType.NText: return typeof(String); case SqlDbType.NVarChar: return typeof(String); case SqlDbType.Real: return typeof(Single); case SqlDbType.SmallDateTime: return typeof(DateTime); case SqlDbType.SmallInt: return typeof(Int16); case SqlDbType.SmallMoney: return typeof(Decimal); case SqlDbType.Text: return typeof(String); case SqlDbType.Timestamp: return typeof(Object); case SqlDbType.TinyInt: return typeof(Byte); case SqlDbType.Udt: return typeof(Object); case SqlDbType.UniqueIdentifier: return typeof(Guid); case SqlDbType.VarBinary: return typeof(Object); case SqlDbType.VarChar: return typeof(String); case SqlDbType.Variant: return typeof(Object); case SqlDbType.Xml: return typeof(Object); default: return null; } }
/// <summary> /// Metodo constructor con todos los campos asignados /// </summary> /// <param name="etiqueta">etiqueta del parametro ejemplo: @nombreDeUsuario</param> /// <param name="tipoDato">SqlDbType con el tipo de dato del parametro /// ejemplo: SqlDbType.VarChar</param> /// <param name="valor">valor: string con el valor que se le asigno al /// parametro ejemplo: Pepe</param> /// <param name="esOutput">esOutput: si es un parametro de output: true, si no: false</param> public Parametro(string etiqueta, SqlDbType tipoDato, string valor, bool esOutput) { this.etiqueta = etiqueta; this.tipoDato = tipoDato; this.valor = valor; this.esOutput = esOutput; }
/// <summary> /// 매개 변수를 사용하여 <c>SqlParameter</c>클래스의 새 인스턴스를 반환합니다. /// </summary> /// <param name="parameterName">매핑할 매개 변수의 이름</param> /// <param name="dbType"><c>SqlDbType</c>값 중 하나</param> /// <param name="size">매개 변수의 길이</param> /// <param name="direction"><c>ParameterDirection</c>값 중 하나</param> /// <returns><c>SqlParameter</c>클래스의 새 인스턴스</returns> public static SqlParameter CreateParameter(string parameterName, SqlDbType dbType, int size, ParameterDirection direction) { SqlParameter ret = CreateParameter(parameterName, dbType); ret.Direction = direction; ret.Size = size; return ret; }
public void AddParameter(string paramName, SqlDbType paramType, object paramValue) { SqlParameter prm = new SqlParameter(paramName, paramType); prm.Direction = ParameterDirection.Input; prm.Value = paramValue; m_Cmd.Parameters.Add(prm); }
public static object SqlValue(this string strVal, SqlDbType type) { if (type == SqlDbType.DateTime) return DateTime.Parse(strVal); return strVal; }
private SqlDataType(SqlDbType type, int? length, int? precision, int? scale) { _Type = type; _Length = length; _Precision = precision; _Scale = scale; }
public void SetOutParam(string ParamName, SqlDbType Sqltype) { //Initialize Sql parameter m_Param = new SqlParameter(); //Set parameter name m_Param.ParameterName = ParamName; //Set parameter datatype m_Param.SqlDbType = Sqltype; //Check Out param type switch (Sqltype) { //If outparam type is varchar case SqlDbType.VarChar: case SqlDbType.NVarChar: { //Set default size m_Param.Size = 1000; break; } } //Set parameter direction as output m_Param.Direction = ParameterDirection.Output; //Add parameter in hashtable SetParam(m_Param); }
public SqlParam(string paramName, object paramValue, ParameterDirection paramDirection, SqlDbType type) { Name = paramName; Direction = paramDirection; Value = paramValue; Type = type; }
public SqlField(int tableIndex, string fieldName, SqlDbType dataType, bool visible) { this.tabIndex = tableIndex; this.fldName = fieldName; this.dbType = dataType; this.visible = visible; }
public StoredProcedureParameterAttribute(SqlDbType dataType, StoredProcedureParameterOptions options, int size) { DataType = dataType; Options = options; Direction = ParameterDirection.Input; Size = size; }
/// <summary> /// Create new Store proc parameter. /// </summary> /// <param name="paramName">Sql parameter name.</param> /// <param name="paramType"><see cref="SqlDbType"/> parameter type.</param> /// <param name="size"><see cref="Int32"/> sql parameter size - use with string.</param> /// <param name="usedFor">Array of <see cref="SqlStatementType"/> values to determine the SP usage.</param> internal StoredProcParameter(string paramName, SqlDbType paramType, int size, SqlStatementType[] usedFor) : this(new SqlParameter(paramName, paramType, size), usedFor) { }
/// <summary> /// Create new Store proc parameter. /// </summary> /// <param name="paramName">Sql parameter name.</param> /// <param name="paramType"><see cref="SqlDbType"/> parameter type.</param> /// <param name="paramSize"><see cref="Int32"/> sql parameter size - use with string.</param> /// <param name="usedFor1">Identifies the purpose of the parameter.</param> internal StoredProcParameter(string paramName, SqlDbType paramType, int paramSize, SqlStatementType usedFor1) : this(paramName, paramType, paramSize, new[] {usedFor1}) { }
public string Create(string name, SqlDbType sqlDbType, long? length = null) { var lStr = length.HasValue ? string.Format("({0})", length.Value) : string.Empty; return string.Format("\"{0}\" {1}{2}", name.Trim(), sqlDbType.ToString().ToLower(), lStr); }
/// <summary> /// Output Ÿ���� Parameter /// </summary> /// <param name="paramName">�Ķ���� �̸�</param> /// <param name="type">SqlDbType</param> /// <param name="size">ũ��</param> /// <param name="val">�Ķ���� ��</param> /// <returns></returns> protected static SqlParameter CreateOutParam(string paramName, SqlDbType type, int size) { SqlParameter param = new SqlParameter( paramName, type, size ); param.Direction = ParameterDirection.Output; return param; }
public SqlMetaData (string name, SqlDbType type, byte precision, byte scale) { this.name = name; this.precision = precision; this.scale = scale; this.sqlDbType = type; }
public SqlCeParameter AddInParameter(string name, object val, SqlDbType type) { SqlCeParameter sp = new SqlCeParameter(name, val); sp.SqlDbType = type; return sp; }
///<summary> ///</summary> ///<param name="paramName"></param> ///<param name="dbType"></param> ///<param name="size"></param> ///<param name="direction"></param> ///<param name="value"></param> ///<returns></returns> ///<exception cref="ArgumentOutOfRangeException"></exception> public static SqlParameter MakeParam(string paramName, SqlDbType dbType, int size, ParameterDirection direction, object value) { SqlParameter sqlParameter = null; try { paramName = paramName ?? string.Empty; //modify reason:验证花时比较大 //if (!MathUtils.IsMachVarName(paramName)) //{ // throw new ArgumentOutOfRangeException("paramName", "参数名格式不正确"); //} if (size > 0) { sqlParameter = new SqlParameter(FormatParamName(paramName), dbType, size); } else { sqlParameter = new SqlParameter(FormatParamName(paramName), dbType); } sqlParameter.Direction = direction; if (direction != ParameterDirection.Output || value != null) { sqlParameter.Value = value; } } catch (Exception ex) { TraceLog.WriteError("{0}", ex); } return sqlParameter; }
/// <summary> /// ����� ���� Parameter /// </summary> /// <param name="paramName">�Ķ���� �̸�</param> /// <param name="type">SqlDbType</param> /// <param name="size">ũ��</param> /// <param name="val">�Ķ���� ��</param> /// <returns></returns> protected static SqlParameter CreateInParam(string paramName, SqlDbType type, int size, object val) { SqlParameter param = new SqlParameter( paramName, type, size ); param.Value = val; return param; }
public SqlFieldAttribute(string fieldName, int fieldLength, SqlDbType sqlDbType, bool needsSpellCheck) { this.m_FieldName = fieldName; this.m_FieldLength = fieldLength; this.m_SqlDbType = sqlDbType; this.m_NeedsSpellCheck = needsSpellCheck; }
internal SmiExtendedMetaData(SqlDbType dbType, long maxLength, byte precision, byte scale, long localeId, SqlCompareOptions compareOptions, Type userDefinedType, string udtAssemblyQualifiedName, bool isMultiValued, IList<SmiExtendedMetaData> fieldMetaData, SmiMetaDataPropertyCollection extendedProperties, string name, string typeSpecificNamePart1, string typeSpecificNamePart2, string typeSpecificNamePart3) : base(dbType, maxLength, precision, scale, localeId, compareOptions, userDefinedType, udtAssemblyQualifiedName, isMultiValued, fieldMetaData, extendedProperties) { this._name = name; this._typeSpecificNamePart1 = typeSpecificNamePart1; this._typeSpecificNamePart2 = typeSpecificNamePart2; this._typeSpecificNamePart3 = typeSpecificNamePart3; }
protected SqlParameter genSqlParameter(String paramName, SqlDbType type, int size, Object value) { if (value == null) value = DBNull.Value; SqlParameter param = null; if (type == SqlDbType.DateTime) { if (value == DBNull.Value) { param = new SqlParameter(paramName, value); } else { param = new SqlParameter(paramName, ((Nullable<DateTime>)value).Value); } } else { param = new SqlParameter(paramName, type, size); param.Value = value; } return param; }
/// <summary> /// Método genérico que chama os métodos correspondentes ao Tipo do Banco para execução da Procedure. /// </summary> /// <param name="query"></param> /// <param name="tipoBanco"></param> /// <returns></returns> public bool executaProc(string nameProc, TipoBanco tipoBanco, object[] values, string[] parametros, SqlDbType[] sqlDBType) { bool bRet = false; try { switch (tipoBanco) { case TipoBanco.SQLServer: bRet = executaProcSQL(nameProc, values, parametros, sqlDBType); break; case TipoBanco.Oracle: break; case TipoBanco.OLDB: break; default: break; } } catch (Exception ex) { bRet = false; throw ex; } finally { } return bRet; }
/// <summary> /// Sets up a parameter for the query /// </summary> /// <param name="id">The ID of the parameter</param> /// <param name="type">The Sql type of the parameter</param> /// <param name="Value">The value of the parameter</param> public static void AddParameter(this List<SqlParameter> collection, string parameterName, SqlDbType type, Object Value) { SqlParameter parameter = new SqlParameter(); parameter.ParameterName = parameterName; parameter.SqlDbType = type; if (Value == null) { parameter.Value = Convert.DBNull; } else if (Value.ToString() == "" && type != SqlDbType.VarChar) { // must convert the empty string to a DBNull parameter.Value = Convert.DBNull; } else if (Value.ToString() == "" && (type == SqlDbType.Float || type == SqlDbType.Int || type == SqlDbType.Money)) { parameter.Value = 0; } else { // set the value of the parameter parameter.Value = Value; } collection.Add(parameter); }
public MetaType(byte precision, byte scale, int fixedLength, bool isFixed, bool isLong, bool isPlp, byte tdsType, byte nullableTdsType, string typeName, Type classType, Type sqlType, SqlDbType sqldbType, DbType dbType, byte propBytes) { this.Precision = precision; this.Scale = scale; this.FixedLength = fixedLength; this.IsFixed = isFixed; this.IsLong = isLong; this.IsPlp = isPlp; // can we get rid of this (?just have a mapping?) this.TDSType = tdsType; this.NullableType = nullableTdsType; this.TypeName = typeName; this.SqlDbType = sqldbType; this.DbType = dbType; this.ClassType = classType; this.SqlType = sqlType; this.PropBytes = propBytes; IsAnsiType = _IsAnsiType(sqldbType); IsBinType = _IsBinType(sqldbType); IsCharType = _IsCharType(sqldbType); IsNCharType = _IsNCharType(sqldbType); IsSizeInCharacters = _IsSizeInCharacters(sqldbType); IsNewKatmaiType = _IsNewKatmaiType(sqldbType); IsVarTime = _IsVarTime(sqldbType); Is70Supported = _Is70Supported(SqlDbType); Is80Supported = _Is80Supported(SqlDbType); Is90Supported = _Is90Supported(SqlDbType); Is100Supported = _Is100Supported(SqlDbType); }
public DbParam(String paramName, Object paramValue, SqlDbType paramType) { ParamName = paramName; ParamValue = paramValue; ParamType = paramType; ParamDirection = ParameterDirection.Input; }
public static DbParameter CreateParameter(SqlDbType dbType) { return new SqlParameter { SqlDbType = dbType }; }
//thoai Add sql parameter protected static SqlParameter AddSqlPrameter(string parameterName, SqlDbType dbType, int size, ParameterDirection direction, object value) { SqlParameter parameter = new SqlParameter(parameterName, dbType, size); parameter.Direction = direction; parameter.Value = value; return parameter; }
public static string ConvertToSystemDataType(SqlDbType sqlDbType) { switch (sqlDbType) { case SqlDbType.BigInt: return "System.Int64"; case SqlDbType.Bit: return "System.Boolean"; case SqlDbType.DateTime: case SqlDbType.SmallDateTime: return "System.DateTime"; case SqlDbType.Decimal: case SqlDbType.Float: case SqlDbType.Money: case SqlDbType.Real: case SqlDbType.SmallMoney: return "System.Decimal"; case SqlDbType.Int: return "System.Int32"; case SqlDbType.UniqueIdentifier: return "System.Guid"; case SqlDbType.SmallInt: return "System.Int16"; case SqlDbType.TinyInt: return "System.Byte"; } return "System.String"; }
public abstract DbParameter CreateParameter(string parameterName, SqlDbType type, string udtTypeName, bool nullable, object value);
public bool ExecuteNonQuery(string sNombre_SP, DataTable dtParametros, ref string sMsjError) { cls_BD_DAL obj_BD_DAL = new cls_BD_DAL(); try { obj_BD_DAL.sCadena_Conexion = ConfigurationManager.ConnectionStrings["WIN_AUT"].ConnectionString.ToString().Trim(); obj_BD_DAL.obj_sql_cnx = new SqlConnection(obj_BD_DAL.sCadena_Conexion); if (obj_BD_DAL.obj_sql_cnx.State == ConnectionState.Closed) { obj_BD_DAL.obj_sql_cnx.Open(); } obj_BD_DAL.obj_sql_cmd = new SqlCommand(sNombre_SP, obj_BD_DAL.obj_sql_cnx); obj_BD_DAL.obj_sql_cmd.CommandType = CommandType.StoredProcedure; if (dtParametros.Rows.Count > 0) { foreach (DataRow dr in dtParametros.Rows) { SqlDbType dbt = SqlDbType.VarChar; switch (dr[1].ToString()) { case "1": { dbt = SqlDbType.Int; break; } case "2": { dbt = SqlDbType.VarChar; break; } case "3": { dbt = SqlDbType.NVarChar; break; } case "4": { dbt = SqlDbType.Char; break; } case "5": { dbt = SqlDbType.NChar; break; } case "6": { dbt = SqlDbType.Decimal; break; } case "7": { dbt = SqlDbType.DateTime; break; } case "8": { dbt = SqlDbType.TinyInt; break; } case "9": { dbt = SqlDbType.BigInt; break; } case "10": { dbt = SqlDbType.SmallInt; break; } case "11": { dbt = SqlDbType.Money; break; } default: dbt = SqlDbType.VarChar; break; } obj_BD_DAL.obj_sql_cmd.Parameters.Add(dr[0].ToString(), dbt).Value = dr[2].ToString(); } obj_BD_DAL.obj_sql_cmd.ExecuteNonQuery(); } sMsjError = String.Empty; return(true); } catch (Exception ex) { sMsjError = ex.Message.ToString(); return(false); } finally { if (obj_BD_DAL.obj_sql_cnx != null) { if (obj_BD_DAL.obj_sql_cnx.State == ConnectionState.Open) { obj_BD_DAL.obj_sql_cnx.Close(); } obj_BD_DAL.obj_sql_cnx.Dispose(); } } }
// Constructors public SqlDbTypeSize(SqlDbType sqlDbType, int size = -1) { SqlDbType = sqlDbType; Size = size; }
public override void SetPropertyValues(SettingsContext context, SettingsPropertyValueCollection collection) { string username = (string)context["UserName"]; bool userIsAuthenticated = (bool)context["IsAuthenticated"]; if (username == null || username.Length < 1 || collection.Count < 1) { return; } SqlConnection conn = null; SqlDataReader reader = null; SqlCommand cmd = null; try { bool anyItemsToSave = false; // First make sure we have at least one item to save foreach (SettingsPropertyValue pp in collection) { if (pp.IsDirty) { if (!userIsAuthenticated) { bool allowAnonymous = (bool)pp.Property.Attributes["AllowAnonymous"]; if (!allowAnonymous) { continue; } } anyItemsToSave = true; break; } } if (!anyItemsToSave) { return; } conn = new SqlConnection(_sqlConnectionString); conn.Open(); List <ProfileColumnData> columnData = new List <ProfileColumnData>(collection.Count); foreach (SettingsPropertyValue pp in collection) { if (!userIsAuthenticated) { bool allowAnonymous = (bool)pp.Property.Attributes["AllowAnonymous"]; if (!allowAnonymous) { continue; } } //Normal logic for original SQL provider //if (!pp.IsDirty && pp.UsingDefaultValue) // Not fetched from DB and not written to //Can eliminate unnecessary updates since we are using a table though if (!pp.IsDirty) { continue; } string persistenceData = pp.Property.Attributes["CustomProviderData"] as string; // If we can't find the table/column info we will ignore this data if (String.IsNullOrEmpty(persistenceData)) { // REVIEW: Perhaps we should throw instead? continue; } string[] chunk = persistenceData.Split(new char[] { ';' }); if (chunk.Length != 2) { // REVIEW: Perhaps we should throw instead? continue; } string columnName = chunk[0]; // REVIEW: Should we ignore case? SqlDbType datatype = (SqlDbType)Enum.Parse(typeof(SqlDbType), chunk[1], true); object value = null; // REVIEW: Is this handling null case correctly? if (pp.Deserialized && pp.PropertyValue == null) // is value null? { value = DBNull.Value; } else { value = pp.PropertyValue; } // REVIEW: Might be able to ditch datatype columnData.Add(new ProfileColumnData(columnName, pp, value, datatype)); } // Figure out userid, if we don't find a userid, go ahead and create a user in the aspnetUsers table Guid userId = Guid.Empty; cmd = new SqlCommand("SELECT u.UserId FROM vw_aspnet_Users u WHERE u.ApplicationId = '" + AppId + "' AND u.UserName = LOWER(@Username)", conn); cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@Username", username); try { reader = cmd.ExecuteReader(); if (reader.Read()) { userId = reader.GetGuid(0); } else { reader.Close(); cmd.Dispose(); reader = null; cmd = new SqlCommand("dbo.aspnet_Users_CreateUser", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@ApplicationId", AppId); cmd.Parameters.AddWithValue("@UserName", username); cmd.Parameters.AddWithValue("@IsUserAnonymous", !userIsAuthenticated); cmd.Parameters.AddWithValue("@LastActivityDate", DateTime.UtcNow); cmd.Parameters.Add(CreateOutputParam("@UserId", SqlDbType.UniqueIdentifier, 16)); cmd.ExecuteNonQuery(); userId = (Guid)cmd.Parameters["@userid"].Value; } } finally { if (reader != null) { reader.Close(); reader = null; } cmd.Dispose(); } // Figure out if the row already exists in the table and use appropriate SELECT/UPDATE cmd = new SqlCommand(String.Empty, conn); StringBuilder sqlCommand = new StringBuilder("IF EXISTS (SELECT 1 FROM ").Append(_table); sqlCommand.Append(" WHERE UserId = @UserId) "); cmd.Parameters.AddWithValue("@UserId", userId); // Build up strings used in the query StringBuilder columnStr = new StringBuilder(); StringBuilder valueStr = new StringBuilder(); StringBuilder setStr = new StringBuilder(); int count = 0; foreach (ProfileColumnData data in columnData) { columnStr.Append(", "); valueStr.Append(", "); columnStr.Append(data.ColumnName); string valueParam = "@Value" + count; valueStr.Append(valueParam); cmd.Parameters.AddWithValue(valueParam, data.Value); // REVIEW: Can't update Timestamps? if (data.DataType != SqlDbType.Timestamp) { if (count > 0) { setStr.Append(","); } setStr.Append(data.ColumnName); setStr.Append("="); setStr.Append(valueParam); } ++count; } columnStr.Append(",LastUpdatedDate "); valueStr.Append(",@LastUpdatedDate"); setStr.Append(",LastUpdatedDate=@LastUpdatedDate"); cmd.Parameters.AddWithValue("@LastUpdatedDate", DateTime.UtcNow); sqlCommand.Append("BEGIN UPDATE ").Append(_table).Append(" SET ").Append(setStr.ToString()); sqlCommand.Append(" WHERE UserId = '").Append(userId).Append("'"); sqlCommand.Append("END ELSE BEGIN INSERT ").Append(_table).Append(" (UserId").Append(columnStr.ToString()); sqlCommand.Append(") VALUES ('").Append(userId).Append("'").Append(valueStr.ToString()).Append(") END"); cmd.CommandText = sqlCommand.ToString(); cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); // Need to close reader before we try to update if (reader != null) { reader.Close(); reader = null; } UpdateLastActivityDate(conn, userId); } finally { if (reader != null) { reader.Close(); } if (cmd != null) { cmd.Dispose(); } if (conn != null) { conn.Close(); } } }
public ProfileColumnData(string col, SettingsPropertyValue pv, object val, SqlDbType type) { EnsureValidTableOrColumnName(col); ColumnName = col; PropertyValue = pv; Value = val; DataType = type; }
private void GetProfileDataFromTable(SettingsPropertyCollection properties, SettingsPropertyValueCollection svc, string username, SqlConnection conn) { List <ProfileColumnData> columnData = new List <ProfileColumnData>(properties.Count); StringBuilder commandText = new StringBuilder("SELECT u.UserID"); SqlCommand cmd = new SqlCommand(String.Empty, conn); int columnCount = 0; foreach (SettingsProperty prop in properties) { SettingsPropertyValue value = new SettingsPropertyValue(prop); svc.Add(value); string persistenceData = prop.Attributes["CustomProviderData"] as string; // If we can't find the table/column info we will ignore this data if (String.IsNullOrEmpty(persistenceData)) { // REVIEW: Perhaps we should throw instead? continue; } string[] chunk = persistenceData.Split(new char[] { ';' }); if (chunk.Length != 2) { // REVIEW: Perhaps we should throw instead? continue; } string columnName = chunk[0]; // REVIEW: Should we ignore case? SqlDbType datatype = (SqlDbType)Enum.Parse(typeof(SqlDbType), chunk[1], true); columnData.Add(new ProfileColumnData(columnName, value, null /* not needed for get */, datatype)); commandText.Append(", "); commandText.Append("t." + columnName); ++columnCount; } commandText.Append(" FROM " + _table + " t, vw_aspnet_Users u WHERE u.ApplicationId = '").Append(AppId); commandText.Append("' AND u.UserName = LOWER(@Username) AND t.UserID = u.UserID"); cmd.CommandText = commandText.ToString(); cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@Username", username); SqlDataReader reader = null; try { reader = cmd.ExecuteReader(); //If no row exists in the database, then the default Profile values //from configuration are used. if (reader.Read()) { Guid userId = reader.GetGuid(0); for (int i = 0; i < columnData.Count; ++i) { object val = reader.GetValue(i + 1); ProfileColumnData colData = columnData[i]; SettingsPropertyValue propValue = colData.PropertyValue; //Only initialize a SettingsPropertyValue for non-null values if (!(val is DBNull || val == null)) { propValue.PropertyValue = val; propValue.IsDirty = false; propValue.Deserialized = true; } } // need to close reader before we try to update the user if (reader != null) { reader.Close(); reader = null; } UpdateLastActivityDate(conn, userId); } } finally { if (reader != null) { reader.Close(); } } }
public void AddParameterWithValue(SqlCommand command, string parameterName, SqlDbType dbType, object value) { command.Parameters.AddWithValue(parameterName, value); }
public void AddParameter(SqlCommand command, string parameterName, SqlDbType dbType) { command.Parameters.Add(parameterName, dbType); }
/*Metodo para agregar los parametros a enviar al Procedure dentro de la BD*/ public static void Agrega_parametro(ref SqlDataAdapter sql_data_adapter, string nombre_parametro, string valor_parametro, SqlDbType tipo_dato) { SqlParameter param = new SqlParameter(); param.ParameterName = nombre_parametro; param.Value = valor_parametro; param.SqlDbType = tipo_dato; sql_data_adapter.SelectCommand.Parameters.Add(param); }
/*Metodo de la estrutura de los metodos para la instancia a los procedure*/ public static void agregar_datos_estructura_parametros(ref ParamStruct[] parametros, int posicion, string nombre_parametro, SqlDbType tipo_dato_parametro, object valor_parametro) { parametros[posicion].Nombre_Parametro = nombre_parametro.ToString(); parametros[posicion].Tipo_Dato = tipo_dato_parametro; parametros[posicion].Valor_Parametro = valor_parametro; }
protected static bool IsDate(SqlDbType type) { return(type == SqlDbType.Date || type == SqlDbType.DateTime || type == SqlDbType.DateTime2 || type == SqlDbType.SmallDateTime); }
public SqlDataType(SqlDbType sqlDbType, string sqlDataTypeName, string cSharpTypeName) { SqlDbType = sqlDbType; SqlDataTypeName = sqlDataTypeName; CSharpTypeName = cSharpTypeName; }
// Internal Methods /// <summary> /// Try to get the SQL Metadata for this SqlDbTypeSize. /// Note that for some types it would be possible for a metadata to be returned here, but they are not. /// This is because it makes more sense to send it to SQL Server as varchar in order to not lose precision or scale. /// These should be handled before calling this method. /// </summary> internal bool tryToSqlMetaData(string name, out SqlMetaData metaData) { // SqlMetaData requires a size the following DbTypes (from https://msdn.microsoft.com/en-us/library/ms127243(v=vs.110).aspx): // Binary, Char, Image, NChar, Ntext, NVarChar, Text, VarBinary, VarChar // It will get strongly typed by SQL Server later because of the table type being specified. // The following must not be supplied with a length // Bit, BigInt, DateTime, Decimal, Float, Int, Money, Numeric (doesn't exist in .NET), SmallDateTime, // SmallInt, SmallMoney, TimeStamp, TinyInt, UniqueIdentifier, Xml // Inferred types (that aren't in either of the lists above) appear to be: // DateTimeOffset, Time, Variant // So this method tries to convert types where possible to the first or second list, except // where it would be preferable to let SqlMetaData.InferFromValue handle it to get a type from the // second list bool success = false; SqlDbType type = SqlDbType; int size = Size; bool useSize = true; object inferFrom = null; switch (type) { /* * Any input type accepted with size */ case SqlDbType.Binary: case SqlDbType.Char: case SqlDbType.Image: case SqlDbType.NChar: case SqlDbType.NText: case SqlDbType.NVarChar: case SqlDbType.Text: case SqlDbType.VarBinary: case SqlDbType.VarChar: success = true; break; // This is a guess & is untested case SqlDbType.Udt: type = SqlDbType.VarBinary; success = true; break; /* * Any input type already accepted without size */ case SqlDbType.Bit: case SqlDbType.BigInt: case SqlDbType.DateTime: case SqlDbType.Decimal: case SqlDbType.Float: case SqlDbType.Int: case SqlDbType.Money: case SqlDbType.SmallDateTime: case SqlDbType.SmallInt: case SqlDbType.SmallMoney: case SqlDbType.Timestamp: case SqlDbType.TinyInt: case SqlDbType.UniqueIdentifier: case SqlDbType.Xml: success = true; useSize = false; break; // Real is equivelant to float case SqlDbType.Real: type = SqlDbType.Float; success = true; useSize = false; break; /* * Inferred */ case SqlDbType.DateTimeOffset: success = true; inferFrom = new DateTimeOffset(); break; case SqlDbType.Time: success = true; inferFrom = new TimeSpan(1, 0, 0); break; case SqlDbType.Variant: success = true; inferFrom = new object(); break; /* * Unsupported */ // Structured is used to send DataTables from .NET to SQL Server as a User-defined table type case SqlDbType.Structured: throw new InvalidOperationException( "Cannot use Structured SqlDbType in SqlMetaData and there is no equivelant"); } // If we've successfully determined what to do to get the meta data, do so if (success) { // If we're inferring from a value if (inferFrom != null) { metaData = SqlMetaData.InferFromValue(inferFrom, name); } // Else if we can constuct the SqlMetaData and must supply a length else if (useSize) { metaData = new SqlMetaData(name, type, size); } // Otherwise we can construct the SqlMetaData and must not supply a length else { metaData = new SqlMetaData(name, type); } } else // Otherwise, set to null, and log { metaData = null; Logging.Write("Couldn't calculate the SqlMetaData for SqlDbType {0}. " + "Please open a bug at https://github.com/JoshKeegan/SqlServerHelpers/issues with the SqlDbType in question and code to reproduce this.", type); } return(success); }
public abstract MemberInitExpression ParameterFactory(Expression parameterName, SqlDbType type, string udtTypeName, bool nullable, Expression value);
//公有方法,实例化一个用于调用存储过程的输入参数 //输入: // ParamName - 数据库存储过程的参数名称 // DbType - 参数类型 // Size - 参数大小 // Value - 传给参数的值 public static SqlParameter MakeInParam(string ParamName, SqlDbType DbTpye, int Size, object Value) { return(MakeParam(ParamName, DbTpye, Size, ParameterDirection.Input, Value)); }
/// <summary> /// 转换字段类型 /// </summary> /// <param name="sqlDbType"></param> /// <param name="length"></param> /// <returns></returns> public string ConversionFieldType(SqlDbType sqlDbType, int length) { string fieldType = string.Empty; switch (sqlDbType) { case SqlDbType.Int: { fieldType = $"Int({(length == 0 ? 11 : length)})"; break; } case SqlDbType.BigInt: { fieldType = $"BigInt({(length == 0 ? 11 : length)})"; break; } case SqlDbType.Decimal: { fieldType = $"Decimal({(length == 0 ? 8 : length)})"; break; } case SqlDbType.Bit: { fieldType = $"Bit({(length == 0 ? 2 : length)})"; break; } case SqlDbType.VarChar: { fieldType = $"VarChar({(length == 0 ? 50 : length)})"; break; } case SqlDbType.NVarChar: { fieldType = $"NVarChar({(length == 0 ? 50 : length)})"; break; } case SqlDbType.Date: { fieldType = $"Date"; break; } case SqlDbType.Time: { fieldType = $"Time"; break; } case SqlDbType.DateTime: { fieldType = $"DateTime({(length == 0 ? 6 : length)})"; break; } default: { throw new Exception.DapperExtensionException("不存在的数据类型,请参考文档设置SqlDbType"); } } return(fieldType); }
public StoredProcedureParameterAttribute(SqlDbType dataType) { DataType = dataType; Direction = ParameterDirection.Input; }
public static Query AddParam(this Query q, string name, SqlDbType dbtype, string val) { //q._parameters.Add(new SqlParameter(name, dbtype, 5, val)); return(q); }
private static bool IsUnicodeType(SqlDbType t) { return(t == SqlDbType.NChar || t == SqlDbType.NText || t == SqlDbType.NVarChar); }
public static void AddWithValueSafe(this SqlParameterCollection target, string parameterName, SqlDbType dbType, object value, string udtTypeName) { target.Add(new SqlParameter() { SqlDbType = dbType, ParameterName = parameterName, Value = value ?? DBNull.Value, UdtTypeName = udtTypeName }); }
private static SqlParameter GenerateSqlParameter(string parameterName, object paramValue, bool mandatory, int size, bool isUserDefinedTableParameter, string udtType, SqlDbType dataType, ParameterDirection direction) { var sqlParameter = new SqlParameter("@" + parameterName, paramValue ?? DBNull.Value) { Direction = direction, IsNullable = !mandatory, Size = SetSize(size, direction), }; if (isUserDefinedTableParameter) { sqlParameter.TypeName = udtType; } else { sqlParameter.SqlDbType = dataType; } return(sqlParameter); }
public StoredProcedureParameterAttribute(SqlDbType dataType, StoredProcedureParameterOptions options) { DataType = dataType; Options = options; Direction = ParameterDirection.Input; }
public SearchCriteriaParameter(string _parameterName, SqlDbType _dbType) { parameterName = _parameterName; dbType = _dbType; }
/// <summary> /// Constructor /// </summary> /// <param name="ID">ID of the parameter</param> /// <param name="Type">Database type</param> /// <param name="Value">Value of the parameter</param> /// <param name="Direction">Direction of the parameter</param> /// <param name="ParameterStarter">Parameter starter</param> public Parameter(string ID, SqlDbType Type, object Value = null, ParameterDirection Direction = ParameterDirection.Input, string ParameterStarter = "@") : base(ID, Type, Value, Direction, ParameterStarter) { }
private void حفظToolStripMenuItem_Click_1(object sender, EventArgs e) { if (string.IsNullOrEmpty(textBox1.Text) || string.IsNullOrEmpty(textBox4.Text) || string.IsNullOrEmpty(textBox6.Text) || string.IsNullOrEmpty(textBox25.Text)) { MessageBox.Show("قم باكمال البيانات"); return; } // try { con.OpenConection(); try { //int d = int.Parse(textBox15.Text); //int d1 = Int32.Parse(textBox19.Text); string[] s = new string[] { "@em", "@iden", "@pnam", "@gen", "@dob", "@pob", "@ag", "@datreg", "@ms", "@catcod", "@addw", "@addp", "@comwn", "@comwv", "@conn", "@prov", "@vill", "@nat", "@rel", "@jo", "@qual", "@gdat" }; string[] s2 = new string[] { textBox3.Text, textBox4.Text, textBox6.Text, comboBox20.Text, textBox1.Text, comboBox4.Text, textBox15.Text, textBox16.Text, comboBox5.Text, textBox19.Text, textBox21.Text, textBox25.Text, comboBox9.Text, textBox22.Text, comboBox12.Text, comboBox14.Text, textBox17.Text, comboBox2.Text, comboBox6.Text, comboBox16.Text, comboBox17.Text, textBox11.Text }; SqlDbType[] s3 = new SqlDbType[] { SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar }; // string[] s2 = new string[] { textBox3.Text.ToString(), textBox4.Text, textBox6.Text, comboBox20.Text, textBox1.Text, comboBox4.Text, d.ToString(), textBox16.Text, comboBox5.Text, d1.ToString(), textBox21.Text, textBox25.Text, comboBox9.Text, textBox22.Text, comboBox12.Text, comboBox14.Text, textBox17.Text, comboBox2.Text, comboBox6.Text, comboBox16.Text, comboBox17.Text, textBox11.Text }; // SqlDbType[] s3 = new SqlDbType[] {SqlDbType.Int, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.Int, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.Int, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar }; con.ExecuteInsertOrUpdateOrDeleteUsingStoredProc("insertPatient", s, s2, s3); MessageBox.Show("تم حفظ البيانات بنجاح"); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } // string s1 = "outer"; // string s2 = "inner"; /* string s = "insert into Registeration_patientRegisteration(employ_code,identity_type,patient_name,gender,date_of_birth,place_of_birth" + ",age,date_Regist ,marital_status,catogrical_code,address_working,address_of_patient,communication_way_name,communication_way_value" + ",country_name,provinence_name ,vcomboBox16.Textillage_name,nationality,religion,job,gualified_name,graduation_date) values(@em,@iden,@pnam,@gen,@dob," + "@pob,@ag,@datreg,@ms,@catcod,@addw,@addp,@comwn,@comwv,@conn,@prov,@vill,@nat,@rel,@jo,@qual,@gdat)"; + */ // cmd4 = new MySqlCommand("insertPatient", con); // cmd4.CommandType = CommandType.StoredProcedure; // // cmd4 = new MySqlCommand(s, con); // cmd4.Prepare(); // cmd4.Parameters.AddWithValue("@em", 1);//1 as a random value // cmd4.Parameters.AddWithValue("@iden", textBox4.Text); // // cmd4.Parameters.AddWithValue("@pi", Int32.Parse(textBox5.Text)); // //if (textBox6.Text.Length == 0) // //{ // // MessageBox.Show("قم بادخال الاسم"); // // } // cmd4.Parameters.AddWithValue("@pnam", textBox6.Text); // cmd4.Parameters.AddWithValue("@gen", comboBox20.SelectedItem); // cmd4.Parameters.AddWithValue("@dob", textBox1.Text); // cmd4.Parameters.AddWithValue("@pob", comboBox4.SelectedItem); // cmd4.Parameters.AddWithValue("@ag", Int32.Parse(textBox15.Text)); // cmd4.Parameters.AddWithValue("@datreg", textBox16.Text); // cmd4.Parameters.AddWithValue("@ms", comboBox5.SelectedItem); // cmd4.Parameters.AddWithValue("@catcod", Int32.Parse(textBox19.Text)); // cmd4.Parameters.AddWithValue("@addw", textBox21.Text); // cmd4.Parameters.AddWithValue("@addp", textBox25.Text); // cmd4.Parameters.AddWithValue("@comwn", comboBox9.SelectedItem); // cmd4.Parameters.AddWithValue("@comwv", textBox22.Text); // cmd4.Parameters.AddWithValue("@conn", comboBox12.SelectedItem); // cmd4.Parameters.AddWithValue("@prov", comboBox14.SelectedItem); // cmd4.Parameters.AddWithValue("@vill", textBox17.Text); // cmd4.Parameters.AddWithValue("@nat", comboBox2.SelectedItem); // cmd4.Parameters.AddWithValue("@rel", comboBox6.SelectedItem); // cmd4.Parameters.AddWithValue("@jo", comboBox16.Text); // cmd4.Parameters.AddWithValue("@qual", comboBox17.SelectedItem); // cmd4.Parameters.AddWithValue("@gdat", textBox11.Text); // cmd4.ExecuteNonQuery(); // MessageBox.Show("تم حفظ البيانات بنجاح"); ///* // try // { // if (radioButton1.Checked == true) // { // cmd4.Parameters.AddWithValue("@tp", "enter"); // } // else if (radioButton2.Checked == true) // { // cmd4.Parameters.AddWithValue("@tp", "out"); // } // cmd4.ExecuteNonQuery(); // } // catch (Exception ex) { MessageBox.Show(ex.Message); } // MessageBox.Show("sucess");*/ // } //// catch (Exception ex) // { // // MessageBox.Show(ex.ToString()); // } // /*validation*/ // /*end of validation*/ // con.CloseConnection(); } }
public SearchCriteriaParameter(SqlDbType _dbType) { dbType = _dbType; }
// This is a modified version of SmiMetaDataFromSchemaTableRow above // Since CoreCLR doesn't have GetSchema, we need to infer the MetaData from the CLR Type alone static internal SmiExtendedMetaData SmiMetaDataFromType(string colName, Type colType) { // Determine correct SqlDbType. SqlDbType colDbType = InferSqlDbTypeFromType_Katmai(colType); if (InvalidSqlDbType == colDbType) { // Unknown through standard mapping, use VarBinary for columns that are Object typed, otherwise we error out. if (typeof(object) == colType) { colDbType = SqlDbType.VarBinary; } else { throw SQL.UnsupportedColumnTypeForSqlProvider(colName, colType.ToString()); } } // Determine metadata modifier values per type (maxlength, precision, scale, etc) long maxLength = 0; byte precision = 0; byte scale = 0; switch (colDbType) { case SqlDbType.BigInt: case SqlDbType.Bit: case SqlDbType.DateTime: case SqlDbType.Float: case SqlDbType.Image: case SqlDbType.Int: case SqlDbType.Money: case SqlDbType.NText: case SqlDbType.Real: case SqlDbType.UniqueIdentifier: case SqlDbType.SmallDateTime: case SqlDbType.SmallInt: case SqlDbType.SmallMoney: case SqlDbType.Text: case SqlDbType.Timestamp: case SqlDbType.TinyInt: case SqlDbType.Variant: case SqlDbType.Xml: case SqlDbType.Date: // These types require no metadata modifiers break; case SqlDbType.Binary: case SqlDbType.VarBinary: // source isn't specifying a size, so assume the Maximum if (SqlDbType.Binary == colDbType) { maxLength = SmiMetaData.MaxBinaryLength; } else { maxLength = SmiMetaData.UnlimitedMaxLengthIndicator; } break; case SqlDbType.Char: case SqlDbType.VarChar: // source isn't specifying a size, so assume the Maximum if (SqlDbType.Char == colDbType) { maxLength = SmiMetaData.MaxANSICharacters; } else { maxLength = SmiMetaData.UnlimitedMaxLengthIndicator; } break; case SqlDbType.NChar: case SqlDbType.NVarChar: // source isn't specifying a size, so assume the Maximum if (SqlDbType.NChar == colDbType) { maxLength = SmiMetaData.MaxUnicodeCharacters; } else { maxLength = SmiMetaData.UnlimitedMaxLengthIndicator; } break; case SqlDbType.Decimal: // Decimal requires precision and scale precision = SmiMetaData.DefaultDecimal.Precision; scale = SmiMetaData.DefaultDecimal.Scale; break; case SqlDbType.Time: case SqlDbType.DateTime2: case SqlDbType.DateTimeOffset: // requires scale scale = SmiMetaData.DefaultTime.Scale; break; case SqlDbType.Udt: case SqlDbType.Structured: default: // These types are not supported from SchemaTable throw SQL.UnsupportedColumnTypeForSqlProvider(colName, colType.ToString()); } return(new SmiExtendedMetaData( colDbType, maxLength, precision, scale, Locale.GetCurrentCultureLcid(), SmiMetaData.GetDefaultForType(colDbType).CompareOptions, false, // no support for multi-valued columns in a TVP yet null, // no support for structured columns yet null, colName, null, null, null)); }
/// <summary> /// Create input SQL parametet, its name is @ and column name /// </summary> /// <param name="columnName">Column name which matches with parameter</param> /// <param name="dbType">Parameter type</param> /// <param name="value">Parameter value</param> /// <returns>Filled SQL parameter</returns> /// <remarks></remarks> public SqlParameter CreateSqlParameter(string columnName, SqlDbType dbType, object value) { return(CreateSqlParameter(columnName, dbType, value, ParameterDirection.Input)); }
// If we know we're only going to use this object to assign to a specific SqlDbType back end object, // we can save some processing time by only checking for the few valid types that can be assigned to the dbType. // This assumes a switch statement over SqlDbType is faster than getting the ClrTypeCode and iterating over a // series of if statements, or using a hash table. // NOTE: the form of these checks is taking advantage of a feature of the JIT compiler that is supposed to // optimize checks of the form '(xxx.GetType() == typeof( YYY ))'. The JIT team claimed at one point that // this doesn't even instantiate a Type instance, thus was the fastest method for individual comparisions. // Given that there's a known SqlDbType, thus a minimal number of comparisions, it's likely this is faster // than the other approaches considered (both GetType().GetTypeCode() switch and hash table using Type keys // must instantiate a Type object. The typecode switch also degenerates into a large if-then-else for // all but the primitive clr types. internal static ExtendedClrTypeCode DetermineExtendedTypeCodeForUseWithSqlDbType( SqlDbType dbType, bool isMultiValued, object value ) { ExtendedClrTypeCode extendedCode = ExtendedClrTypeCode.Invalid; // fast-track null, which is valid for all types if (null == value) { extendedCode = ExtendedClrTypeCode.Empty; } else if (DBNull.Value == value) { extendedCode = ExtendedClrTypeCode.DBNull; } else { switch (dbType) { case SqlDbType.BigInt: if (value.GetType() == typeof(Int64)) { extendedCode = ExtendedClrTypeCode.Int64; } else if (value.GetType() == typeof(SqlInt64)) { extendedCode = ExtendedClrTypeCode.SqlInt64; } break; case SqlDbType.Binary: case SqlDbType.VarBinary: case SqlDbType.Image: case SqlDbType.Timestamp: if (value.GetType() == typeof(byte[])) { extendedCode = ExtendedClrTypeCode.ByteArray; } else if (value.GetType() == typeof(SqlBinary)) { extendedCode = ExtendedClrTypeCode.SqlBinary; } else if (value.GetType() == typeof(SqlBytes)) { extendedCode = ExtendedClrTypeCode.SqlBytes; } else if (value.GetType() == typeof(StreamDataFeed)) { extendedCode = ExtendedClrTypeCode.Stream; } break; case SqlDbType.Bit: if (value.GetType() == typeof(bool)) { extendedCode = ExtendedClrTypeCode.Boolean; } else if (value.GetType() == typeof(SqlBoolean)) { extendedCode = ExtendedClrTypeCode.SqlBoolean; } break; case SqlDbType.Char: case SqlDbType.NChar: case SqlDbType.NText: case SqlDbType.NVarChar: case SqlDbType.Text: case SqlDbType.VarChar: if (value.GetType() == typeof(string)) { extendedCode = ExtendedClrTypeCode.String; } if (value.GetType() == typeof(TextDataFeed)) { extendedCode = ExtendedClrTypeCode.TextReader; } else if (value.GetType() == typeof(SqlString)) { extendedCode = ExtendedClrTypeCode.SqlString; } else if (value.GetType() == typeof(char[])) { extendedCode = ExtendedClrTypeCode.CharArray; } else if (value.GetType() == typeof(SqlChars)) { extendedCode = ExtendedClrTypeCode.SqlChars; } else if (value.GetType() == typeof(char)) { extendedCode = ExtendedClrTypeCode.Char; } break; case SqlDbType.Date: case SqlDbType.DateTime2: case SqlDbType.DateTime: case SqlDbType.SmallDateTime: if (value.GetType() == typeof(DateTime)) { extendedCode = ExtendedClrTypeCode.DateTime; } else if (value.GetType() == typeof(SqlDateTime)) { extendedCode = ExtendedClrTypeCode.SqlDateTime; } break; case SqlDbType.Decimal: if (value.GetType() == typeof(Decimal)) { extendedCode = ExtendedClrTypeCode.Decimal; } else if (value.GetType() == typeof(SqlDecimal)) { extendedCode = ExtendedClrTypeCode.SqlDecimal; } break; case SqlDbType.Real: if (value.GetType() == typeof(Single)) { extendedCode = ExtendedClrTypeCode.Single; } else if (value.GetType() == typeof(SqlSingle)) { extendedCode = ExtendedClrTypeCode.SqlSingle; } break; case SqlDbType.Int: if (value.GetType() == typeof(Int32)) { extendedCode = ExtendedClrTypeCode.Int32; } else if (value.GetType() == typeof(SqlInt32)) { extendedCode = ExtendedClrTypeCode.SqlInt32; } break; case SqlDbType.Money: case SqlDbType.SmallMoney: if (value.GetType() == typeof(SqlMoney)) { extendedCode = ExtendedClrTypeCode.SqlMoney; } else if (value.GetType() == typeof(Decimal)) { extendedCode = ExtendedClrTypeCode.Decimal; } break; case SqlDbType.Float: if (value.GetType() == typeof(SqlDouble)) { extendedCode = ExtendedClrTypeCode.SqlDouble; } else if (value.GetType() == typeof(Double)) { extendedCode = ExtendedClrTypeCode.Double; } break; case SqlDbType.UniqueIdentifier: if (value.GetType() == typeof(SqlGuid)) { extendedCode = ExtendedClrTypeCode.SqlGuid; } else if (value.GetType() == typeof(Guid)) { extendedCode = ExtendedClrTypeCode.Guid; } break; case SqlDbType.SmallInt: if (value.GetType() == typeof(Int16)) { extendedCode = ExtendedClrTypeCode.Int16; } else if (value.GetType() == typeof(SqlInt16)) { extendedCode = ExtendedClrTypeCode.SqlInt16; } break; case SqlDbType.TinyInt: if (value.GetType() == typeof(Byte)) { extendedCode = ExtendedClrTypeCode.Byte; } else if (value.GetType() == typeof(SqlByte)) { extendedCode = ExtendedClrTypeCode.SqlByte; } break; case SqlDbType.Variant: // SqlDbType doesn't help us here, call general-purpose function extendedCode = DetermineExtendedTypeCode(value); // Some types aren't allowed for Variants but are for the general-purpos function. // Match behavior of other types and return invalid in these cases. if (ExtendedClrTypeCode.SqlXml == extendedCode) { extendedCode = ExtendedClrTypeCode.Invalid; } break; case SqlDbType.Udt: throw ADP.DbTypeNotSupported(SqlDbType.Udt.ToString()); case SqlDbType.Time: if (value.GetType() == typeof(TimeSpan)) { extendedCode = ExtendedClrTypeCode.TimeSpan; } break; case SqlDbType.DateTimeOffset: if (value.GetType() == typeof(DateTimeOffset)) { extendedCode = ExtendedClrTypeCode.DateTimeOffset; } break; case SqlDbType.Xml: if (value.GetType() == typeof(SqlXml)) { extendedCode = ExtendedClrTypeCode.SqlXml; } if (value.GetType() == typeof(XmlDataFeed)) { extendedCode = ExtendedClrTypeCode.XmlReader; } else if (value.GetType() == typeof(System.String)) { extendedCode = ExtendedClrTypeCode.String; } break; case SqlDbType.Structured: if (isMultiValued) { if (value is IEnumerable <SqlDataRecord> ) { extendedCode = ExtendedClrTypeCode.IEnumerableOfSqlDataRecord; } else if (value is DbDataReader) { extendedCode = ExtendedClrTypeCode.DbDataReader; } } break; default: // Leave as invalid break; } } return(extendedCode); }
/// <summary> /// Gets the SQL parameter. /// </summary> /// <param name="name">The name.</param> /// <param name="direction">The direction.</param> /// <param name="dbType">Type of the db.</param> /// <param name="size">The size.</param> /// <param name="value">The value.</param> /// <returns></returns> private SqlParameter GetSqlParameter(string name, ParameterDirection direction, SqlDbType dbType, int size, object value) { SqlParameter p = new SqlParameter(name, dbType, size); p.Value = value; p.Direction = direction; return(p); }