Exemplo n.º 1
0
		private void BulkCopyToServer (DataTable table, DataRowState state)
		{
			if (connection == null || connection.State == ConnectionState.Closed)
				throw new InvalidOperationException ("This method should not be called on a closed connection");
			if (_destinationTableName == null)
				throw new ArgumentNullException ("DestinationTableName");
			if (isLocalConnection && connection.State != ConnectionState.Open)
				connection.Open();
			
			if ((copyOptions & SqlBulkCopyOptions.KeepIdentity) == SqlBulkCopyOptions.KeepIdentity) {
				SqlCommand cmd = new SqlCommand ("set identity_insert " +
								 table.TableName + " on",
								 connection);
				cmd.ExecuteScalar ();
			}
			DataTable [] columnMetaDataTables = GetColumnMetaData ();
			DataTable colMetaData = columnMetaDataTables [0];
			DataTable tableCollations = columnMetaDataTables [1];

			if (_columnMappingCollection.Count > 0) {
				if (_columnMappingCollection [0].SourceOrdinal != -1)
					ordinalMapping = true;
				ValidateColumnMapping (table, tableCollations);
			}

			SqlCommand tmpCmd = new SqlCommand ();
			TdsBulkCopy blkCopy = new TdsBulkCopy ((Tds)connection.Tds);
			if (((Tds)connection.Tds).TdsVersion >= TdsVersion.tds70) {
				string statement = "insert bulk " + DestinationTableName + " (";
				statement += GenerateColumnMetaData (tmpCmd, colMetaData, tableCollations);
				statement += ")";
				
				#region Check requested options and add corresponding modifiers to the statement
				if ((copyOptions & insertModifiers) != SqlBulkCopyOptions.Default) {
					statement += " WITH (";
					bool commaRequired = false;
					
					if ((copyOptions & SqlBulkCopyOptions.CheckConstraints) == SqlBulkCopyOptions.CheckConstraints) {
						if (commaRequired)
							statement += ", ";
						statement += "CHECK_CONSTRAINTS";
						commaRequired = true;
					}
					
					if ((copyOptions & SqlBulkCopyOptions.TableLock) == SqlBulkCopyOptions.TableLock) {
						if (commaRequired)
							statement += ", ";
						statement += "TABLOCK";
						commaRequired = true;
					}
					
					if ((copyOptions & SqlBulkCopyOptions.KeepNulls) == SqlBulkCopyOptions.KeepNulls) {
						if (commaRequired)
							statement += ", ";
						statement += "KEEP_NULLS";
						commaRequired = true;
					}
					
					if ((copyOptions & SqlBulkCopyOptions.FireTriggers) == SqlBulkCopyOptions.FireTriggers) {
						if (commaRequired)
							statement += ", ";
						statement += "FIRE_TRIGGERS";
						commaRequired = true;
					}
					
					statement += ")";
				}
				#endregion Check requested options and add corresponding modifiers to the statement

				blkCopy.SendColumnMetaData (statement);
			}
			blkCopy.BulkCopyStart (tmpCmd.Parameters.MetaParameters);
			long noRowsCopied = 0;
			foreach (DataRow row in table.Rows) {
				if (row.RowState == DataRowState.Deleted)
					continue; // Don't copy the row that's in deleted state
				if (state != 0 && row.RowState != state)
					continue;
				bool isNewRow = true;
				int i = 0;
				foreach (SqlParameter param in tmpCmd.Parameters) {
					int size = 0;
					object rowToCopy = null;
					if (_columnMappingCollection.Count > 0) {
						if (ordinalMapping) {
							foreach (SqlBulkCopyColumnMapping mapping
								 in _columnMappingCollection) {
								if (mapping.DestinationOrdinal == i && param.Value == null) {
									rowToCopy = row [mapping.SourceOrdinal];
									SqlParameter parameter = new SqlParameter (mapping.SourceOrdinal.ToString (),
														   rowToCopy);
									if (param.MetaParameter.TypeName != parameter.MetaParameter.TypeName) {
										parameter.SqlDbType = param.SqlDbType;
										rowToCopy = parameter.Value = parameter.ConvertToFrameworkType (rowToCopy);
									}
									string colType = string.Format ("{0}", parameter.MetaParameter.TypeName);
									if (colType == "nvarchar" || colType == "ntext" || colType == "nchar") {
										if (row [i] != null && row [i] != DBNull.Value) {
											size = ((string) parameter.Value).Length;
											size <<= 1;
										}
									} else if (colType == "varchar" || colType == "text" || colType == "char") {
										if (row [i] != null && row [i] != DBNull.Value)
											size = ((string) parameter.Value).Length;
									} else {
										size = parameter.Size;
									}
									break;
								}
							}
						} else {
							foreach (SqlBulkCopyColumnMapping mapping
								 in _columnMappingCollection) {
								if (mapping.DestinationColumn == param.ParameterName) {
									rowToCopy = row [mapping.SourceColumn];
									SqlParameter parameter = new SqlParameter (mapping.SourceColumn, rowToCopy);
									if (param.MetaParameter.TypeName != parameter.MetaParameter.TypeName) {
										parameter.SqlDbType = param.SqlDbType;
										rowToCopy = parameter.Value = parameter.ConvertToFrameworkType (rowToCopy);
									}
									string colType = string.Format ("{0}", parameter.MetaParameter.TypeName);
									if (colType == "nvarchar" || colType == "ntext" || colType == "nchar") {
										if (row [mapping.SourceColumn] != null && row [mapping.SourceColumn] != DBNull.Value) {
											size = ((string) rowToCopy).Length;
											size <<= 1;
										}
									} else if (colType == "varchar" || colType == "text" || colType == "char") {
										if (row [mapping.SourceColumn] != null && row [mapping.SourceColumn] != DBNull.Value)
											size = ((string) rowToCopy).Length;
									} else {
										size = parameter.Size;
									}
									break;
								}
							}
						}
						i++;
					} else {
						rowToCopy = row [param.ParameterName];
						string colType = param.MetaParameter.TypeName;
						/*
						  If column type is SqlDbType.NVarChar the size of parameter is multiplied by 2
						  FIXME: Need to check for other types
						*/
						if (colType == "nvarchar" || colType == "ntext" || colType == "nchar") {
							size = ((string) row [param.ParameterName]).Length;
							size <<= 1;
						} else if (colType == "varchar" || colType == "text" || colType == "char") {
							size = ((string) row [param.ParameterName]).Length;
						} else {
							size = param.Size;
						}
					}
					if (rowToCopy == null)
						continue;

					blkCopy.BulkCopyData (rowToCopy, isNewRow, size, param.MetaParameter);
                    
					if (isNewRow)
						isNewRow = false;
				} // foreach (SqlParameter)
				if (_notifyAfter > 0) {
					noRowsCopied ++;
					if (noRowsCopied >= _notifyAfter) {
						RowsCopied (noRowsCopied);
						noRowsCopied = 0;
					}
				}
			} // foreach (DataRow)
			blkCopy.BulkCopyEnd ();
		}
Exemplo n.º 2
0
		private void BulkCopyToServer (DataTable table, DataRowState state)
		{
			if (connection == null || connection.State == ConnectionState.Closed)
				throw new InvalidOperationException ("This method should not be called on a closed connection");
			if (_destinationTableName == null)
				throw new ArgumentNullException ("DestinationTableName");
			if (identityInsert) {
				SqlCommand cmd = new SqlCommand ("set identity_insert " +
								 table.TableName + " on",
								 connection);
				cmd.ExecuteScalar ();
			}
			DataTable [] columnMetaDataTables = GetColumnMetaData ();
			DataTable colMetaData = columnMetaDataTables [0];
			DataTable tableCollations = columnMetaDataTables [1];

			if (_columnMappingCollection.Count > 0) {
				if (_columnMappingCollection [0].SourceOrdinal != -1)
					ordinalMapping = true;
				ValidateColumnMapping (table, tableCollations);
			}

			SqlCommand tmpCmd = new SqlCommand ();
			TdsBulkCopy blkCopy = new TdsBulkCopy ((Tds)connection.Tds);
			if (((Tds)connection.Tds).TdsVersion >= TdsVersion.tds70) {
				string statement = "insert bulk " + DestinationTableName + " (";
				statement += GenerateColumnMetaData (tmpCmd, colMetaData, tableCollations);
				statement += ")";
				blkCopy.SendColumnMetaData (statement);
			}
			blkCopy.BulkCopyStart (tmpCmd.Parameters.MetaParameters);
			long noRowsCopied = 0;
			foreach (DataRow row in table.Rows) {
				if (row.RowState == DataRowState.Deleted)
					continue; // Don't copy the row that's in deleted state
				if (state != 0 && row.RowState != state)
					continue;
				bool isNewRow = true;
				int i = 0;
				foreach (SqlParameter param in tmpCmd.Parameters) {
					int size = 0;
					object rowToCopy = null;
					if (_columnMappingCollection.Count > 0) {
						if (ordinalMapping) {
							foreach (SqlBulkCopyColumnMapping mapping
								 in _columnMappingCollection) {
								if (mapping.DestinationOrdinal == i && param.Value == null) {
									rowToCopy = row [mapping.SourceOrdinal];
									SqlParameter parameter = new SqlParameter (mapping.SourceOrdinal.ToString (),
														   rowToCopy);
									if (param.MetaParameter.TypeName != parameter.MetaParameter.TypeName) {
										parameter.SqlDbType = param.SqlDbType;
										rowToCopy = parameter.Value = parameter.ConvertToFrameworkType (rowToCopy);
									}
									string colType = string.Format ("{0}", parameter.MetaParameter.TypeName);
									if (colType == "nvarchar") {
										if (row [i] != null) {
											size = ((string) parameter.Value).Length;
											size <<= 1;
										}
									} else {
										size = parameter.Size;
									}
									break;
								}
							}
						} else {
							foreach (SqlBulkCopyColumnMapping mapping
								 in _columnMappingCollection) {
								if (mapping.DestinationColumn == param.ParameterName) {
									rowToCopy = row [mapping.SourceColumn];
									SqlParameter parameter = new SqlParameter (mapping.SourceColumn, rowToCopy);
									if (param.MetaParameter.TypeName != parameter.MetaParameter.TypeName) {
										parameter.SqlDbType = param.SqlDbType;
										rowToCopy = parameter.Value = parameter.ConvertToFrameworkType (rowToCopy);
									}
									string colType = string.Format ("{0}", parameter.MetaParameter.TypeName);
									if (colType == "nvarchar") {
										if (row [mapping.SourceColumn] != null) {
											size = ((string) rowToCopy).Length;
											size <<= 1;
										}
									} else {
										size = parameter.Size;
									}
									break;
								}
							}
						}
						i++;
					} else {
						rowToCopy = row [param.ParameterName];
						string colType = param.MetaParameter.TypeName;
						/*
						  If column type is SqlDbType.NVarChar the size of parameter is multiplied by 2
						  FIXME: Need to check for other types
						*/
						if (colType == "nvarchar") {
							size = ((string) row [param.ParameterName]).Length;
							size <<= 1;
						} else {
							size = param.Size;
						}
					}
					if (rowToCopy == null)
						continue;
					blkCopy.BulkCopyData (rowToCopy, size, isNewRow);
					if (isNewRow)
						isNewRow = false;
				} // foreach (SqlParameter)
				if (_notifyAfter > 0) {
					noRowsCopied ++;
					if (noRowsCopied >= _notifyAfter) {
						RowsCopied (noRowsCopied);
						noRowsCopied = 0;
					}
				}
			} // foreach (DataRow)
			blkCopy.BulkCopyEnd ();
		}